Passing parameter in MDX query

Hi,
I am facing problem during passing the parameters in MDX query. I need tp
pass two parameter in my dataset, but not sure whether I am writing correct
query or not. as I am getting error during parsing the query.
here the query
SELECT {
[Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
} on columns,
NON EMPTY NonEmptyCrossJoin(
[Component Parts].[Part].Members,
NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
Code].Members)
)
on rows FROM [CUBE NAME]
WHERE (" + Parameters!ManufactureDate.Value + ", " +
Parameters!Suppliername.Value + ")
--
The two parameter I have declared in parameter box with multiselect value
option.
while executiing the query I am gettiign belwo message.
"The tuple function expects a tuplet set of expression for the argument.A
string or numeric was used"
Please help me to write the correct syntax for this mdx.
Appreciate your help
Regards
SomuMosha Pasumansky has written an article called "Writing multiselect friendly
MDX calculations":
http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
Maybe you'll find your answer there. :)
Have you tried your query without parameters in an MDX parser? If not, try
it and you might figure out how the query should be before trying with the
parameters.
Kaisa M. Lindahl
"Somu" <Somu@.discussions.microsoft.com> wrote in message
news:65941D3D-8CBB-494C-9E95-3F4FD983E31F@.microsoft.com...
> Hi,
> I am facing problem during passing the parameters in MDX query. I need tp
> pass two parameter in my dataset, but not sure whether I am writing
> correct
> query or not. as I am getting error during parsing the query.
> here the query
> SELECT {
> [Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
> } on columns,
> NON EMPTY NonEmptyCrossJoin(
> [Component Parts].[Part].Members,
> NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
> Code].Members)
> )
> on rows FROM [CUBE NAME]
> WHERE (" + Parameters!ManufactureDate.Value + ", " +
> Parameters!Suppliername.Value + ")
> --
> The two parameter I have declared in parameter box with multiselect value
> option.
> while executiing the query I am gettiign belwo message.
> "The tuple function expects a tuplet set of expression for the argument.A
> string or numeric was used"
> Please help me to write the correct syntax for this mdx.
> Appreciate your help
> Regards
> Somu

Passing OLAP Parameters Within a URL

Hi there!

My report uses an OLAP query with a (multi-valued) parameter, the MDX generated query is something like "...ON ROWS FROM ( SELECT ( STRTOSET(@.Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".

The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:

https://servername/ReportServer?/PathTo/MyReport&rs:Command=Render&rs:Format=HTML4.0&Region=[foo].[bar 123].&[baz]

But I cannot get Reporting Services to accept the parameter instead of complaining about a missing parameter value. I've already tried quoting the braces, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.

How do I quote this parameter correctly?

I do this befor but so it was as follow

Report server URL(http://Machinename/reportserver/) + Reports Folder (MyReports/)+Reportname (My report) +"&Firstparametername"+Value+"&secondparametername"+Value

it will be like this

http://Machinename/reportserver/(MyReports/My repor&Firstparametername=Value1&secondparametername=Value

|||

Thanks for your reply.

Normal query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. ReportServer won't accept them, I think it doesn't like the way I'm trying to quote them...

Thanks and best regards,
Thomas

|||

Hi Tamer,

I was reading your question and i have the same problem.

Please, tell me, Have you resolved this issue ?

email me to :megch00@.hotmail.com ormanuelgo@.cr-dss.com

Thanks a lot !!

|||

Hi there!

It was indeed quoting related, I must have confused hex with decimal notation in my earlier attempts.

All you have to do is urlencode the parameter names and values. Here is how to quote correctly:

InputEncoded[%5B]%5D&%26spaces+ or%20

Or you let ASP.Net do the job:

1string encodedParam = HttpContext.Current.Server.UrlEncode(myParam);

So ...&Region=[foo].[bar 123].&[baz]... becomes...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D...

If you want this parameter to have multiple values, just use it multiple times in your URL: ...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bheureka%5D...

HTH and best regards,
Thomas

sql

Passing mdx parameters to RS through URL (SharePoint Integrated Mode)

Hi there,

I'm running MOSS 2007 in Integrated Mode, and I have a number of reports that are deployed to the Reports Library, and are working just fine when you go there and run them interactively.

Now however, I need to address the report via a URL, and pass it a parameter. Just to add a bit more pain, the report is in mdx, and expects an mdx parameter.

Here is the parameter "prm_cost_centre":

[DIM LEARNER AIM].[COST CENTRE DESC].&[Business]

Here is the URL of the report:

http://vmmoss:88/ReportsLibrary/Report1.rdl

(If you hit that URL as is, it runs fine, using the default parameter)

Now, I know that we have to escape the ampersand, so I'm thinking that this URL should do it.

http://vmmoss:88/ReportsLibrary/Report1.rdl&rs:Command=Render&prm_cost_centre=[DIM LEARNER AIM].[COST CENTRE DESC].%26[Business]

But it doesn't - It throws a 400 Bad Request error.

I know I must be very close, but I just can't work out what URL I need to pass in order to get the damn thing to run.

Any ideas?

I don't have very much experience with MDX params, but I might recommend escaping the periods or possibly the [brackets] too. Sorry I can't offer much more advice than that, but as in any debug scenario, try truncating the param string until you get something that works, then go from there!

|||

It's not possible - you can't pass parameters through the URL when running Reporting Services in Integrated Mode.

http://technet.microsoft.com/en-us/library/bb326290.aspx

How lame is that? All of a sudden, Integrated Mode becomes...worthless to most people, surely.

Passing mdx parameters to RS through URL (SharePoint Integrated Mode)

Hi there,

I'm running MOSS 2007 in Integrated Mode, and I have a number of reports that are deployed to the Reports Library, and are working just fine when you go there and run them interactively.

Now however, I need to address the report via a URL, and pass it a parameter. Just to add a bit more pain, the report is in mdx, and expects an mdx parameter.

Here is the parameter "prm_cost_centre":

[DIM LEARNER AIM].[COST CENTRE DESC].&[Business]

Here is the URL of the report:

http://vmmoss:88/ReportsLibrary/Report1.rdl

(If you hit that URL as is, it runs fine, using the default parameter)

Now, I know that we have to escape the ampersand, so I'm thinking that this URL should do it.

http://vmmoss:88/ReportsLibrary/Report1.rdl&rs:Command=Render&prm_cost_centre=[DIM LEARNER AIM].[COST CENTRE DESC].%26[Business]

But it doesn't - It throws a 400 Bad Request error.

I know I must be very close, but I just can't work out what URL I need to pass in order to get the damn thing to run.

Any ideas?

I don't have very much experience with MDX params, but I might recommend escaping the periods or possibly the [brackets] too. Sorry I can't offer much more advice than that, but as in any debug scenario, try truncating the param string until you get something that works, then go from there!

|||

It's not possible - you can't pass parameters through the URL when running Reporting Services in Integrated Mode.

http://technet.microsoft.com/en-us/library/bb326290.aspx

How lame is that? All of a sudden, Integrated Mode becomes...worthless to most people, surely.

sql

Passing DateTime Parameter to MDX Query

HI All,
Looking for a little help with this issue: I currently have an MDX
query which is accepting 2 parameters for a date range. The query is
as follows:
SELECT NON EMPTY { [Measures].[Line Item Sales Amount] } ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@.FromDateDateForReporting, CONSTRAINED) :
STRTOMEMBER(@.ToDateDateForReporting, CONSTRAINED) ) ON COLUMNS FROM
[KIT Retail Sales]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The parameters pull their respective values from a dataset populated
with this query:
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date For
Reporting].CURRENTMEMBER.MEMBER_CAPTION' MEMBER
[Measures].[ParameterValue] AS '[Date].[Date For
Reporting].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel]
AS '[Date].[Date For Reporting].CURRENTMEMBER.LEVEL.ORDINAL' SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date For
Reporting].ALLMEMBERS ON ROWS FROM [KIT Retail Sales]
In the Report Parameters window, I would like these parameters to be
DateTime datatypes so my users would get the handy Calendar control.
The only way I can get them to work now is by setting them to string
datatype. This works, but the dropdown list is populated with
thousands of date strings (in the form 01/01/2005, 01/02/2005, etc...)
which makes it difficult for users.
Whenver I attempt to make them DateTime datatypes, I get the error:
"The Property 'ValidValues' of the report parameter
'FromDateDateForReporting' doesn't have the expected type" when I run
the report.
My question is: How can I do the conversion to get the datetime back
to a string to use in the first MDX query above?Quick followup: In the report parameters window, if I choose to make
the available values "Non-queried", I can see the calendar control on
the preview page. However when choosing dates for the range, and then
clicking "view report", I get the following error:
The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
function have been violated.|||Guys-
This appears to work fine. However, now go add a column to your main query
(the one with the parameter) and now your report parameter is trashed because
SSRS decided it needed to refresh it. Anybody know of a way to tell it to
leave that parameter alone?
"Bret Updegraff" wrote:
> This is the method from my class file. It is in C# so if you are using
> VB.net or if you are putting this in the Code tab under Report-->
> Properties you will need to convert to VB.NET
> public string ParseDateToMDX( DateTime sDate )
> {
> string year = sDate.Year.ToString();
> string month = sDate.Month.ToString();
> string day = sDate.Day.ToString();
> StringBuilder MDXValue = new StringBuilder();
> if (month.Length == 1)
> {
> month = "0" + month; //pad month
> }
> if (day.Length == 1)
> {
> day = "0" + day; //pad day
> }
> MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> MDXValue.Append(month.ToString()).Append("-");
> MDXValue.Append(day.ToString()).Append("T00:00:00]");
> return MDXValue.ToString();
> }
> Hope this helps
> "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> > Quick followup: In the report parameters window, if I choose to make
> > the available values "Non-queried", I can see the calendar control on
> > the preview page. However when choosing dates for the range, and then
> > clicking "view report", I get the following error:
> > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > function have been violated.
>
> --
> Bret Updegraff, MCAD,MCSD,MCDBA
> Microsoft MVP - SQL Server
> Crowe Chizek and Company LLC
> President - Indianapolis Professional Association for SQL Server
> Join our SQL Server Community http;//www.IndyPASS.org
> 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
>|||I hear this may be fixed in SP1:
http://prologika.com/CS/forums/thread/1069.aspx
"FurmanGG" wrote:
> Guys-
> This appears to work fine. However, now go add a column to your main query
> (the one with the parameter) and now your report parameter is trashed because
> SSRS decided it needed to refresh it. Anybody know of a way to tell it to
> leave that parameter alone?
> "Bret Updegraff" wrote:
> > This is the method from my class file. It is in C# so if you are using
> > VB.net or if you are putting this in the Code tab under Report-->
> > Properties you will need to convert to VB.NET
> >
> > public string ParseDateToMDX( DateTime sDate )
> > {
> > string year = sDate.Year.ToString();
> > string month = sDate.Month.ToString();
> > string day = sDate.Day.ToString();
> > StringBuilder MDXValue = new StringBuilder();
> >
> > if (month.Length == 1)
> > {
> > month = "0" + month; //pad month
> > }
> > if (day.Length == 1)
> > {
> > day = "0" + day; //pad day
> > }
> >
> > MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> > MDXValue.Append(month.ToString()).Append("-");
> > MDXValue.Append(day.ToString()).Append("T00:00:00]");
> >
> > return MDXValue.ToString();
> > }
> >
> > Hope this helps
> >
> > "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> > news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> >
> > > Quick followup: In the report parameters window, if I choose to make
> > > the available values "Non-queried", I can see the calendar control on
> > > the preview page. However when choosing dates for the range, and then
> > > clicking "view report", I get the following error:
> > > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > > function have been violated.
> >
> >
> > --
> > Bret Updegraff, MCAD,MCSD,MCDBA
> > Microsoft MVP - SQL Server
> > Crowe Chizek and Company LLC
> > President - Indianapolis Professional Association for SQL Server
> > Join our SQL Server Community http;//www.IndyPASS.org
> > 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
> >
> >

Passing a SQL Query / MDX Query to RDL

Has anybody passed a SQL Query or an MDX Query as a source to an RDL file?
This is what I am trying to do:
I have a Report whose format is exactly the same but I need to use it
for creating different reports.
For Example) The Report prints Employee name and address and I want to use
the same RDL to print Customer Name and Address.
I am redefining the query so, that column names are same
Ex) EMP_NAME as NAME and EMP_ADDRESS as ADDRESS
ThanksOn Jul 13, 11:36 am, jvn <sha...@.online.nospam> wrote:
> Has anybody passed a SQL Query or an MDX Query as a source to an RDL file?
> This is what I am trying to do:
> I have a Report whose format is exactly the same but I need to use it
> for creating different reports.
> For Example) The Report prints Employee name and address and I want to use
> the same RDL to print Customer Name and Address.
> I am redefining the query so, that column names are same
> Ex) EMP_NAME as NAME and EMP_ADDRESS as ADDRESS
> Thanks
You should be able to do this if you are dynamically creating the RDL
file in a custom application. You will want to change the query found
in this location: <DataSets><DataSet><Query><CommandText>...</
CommandText></Query></DataSet></DataSets>
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'm relatively new to Reporting Services. I'm working with SSRS 2000,
perhaps you are working with SSRS 2005.
I'm reading the book 'Hitchikers Guide to SQL Server 2000 Reporting
Services'.
The suggested approach in this book is to add a report parameter (e.g.
ReportSubject) with the values 'employees' and 'customers'.
Create a new dataset, select command type 'Text'. Manually add the
field names referring tot database field names NAME and ADRESS.
Enter the following expression:
= IIF(Parameters!ReportSubject.Value = 'employees', "SELECT EMP_NAME
as NAME, EMP_ADDRESS as ADDRESS FROM ...", "SELECT CUST_NAME as
NAME, CUST_ADDRESS as ADDRESS FROM ...")
This may become rather complex when you also want to use the report
for other purposes (nested IFF statements). But in that case you could
create a custom code function in VB so you can you CASE-statements
etc. This is not as complex as you might think.
Regards,
Willy