MDX, dates and Calendar Control in Reporting services 2005

Hi,
I need to develop several reports in RS 2005 where the report
parameters are dates. The reports are based on a SQL Server 2005
Analysis Services Cube and consequently the reports need to be based
on MDX. My dates are formatted according to Swedish date standard. I
would like the users to pick the dates from a Calendar control.
(I have seen several smilar problems with dates, but all referring to
SQ statements, not MDX)
Here's my problem:
When I design my query in the Data tab of the report, I choose the
field that I want to use as a parameter (in this case a date) and tick
the checkbox indicating that I want a Report parameter created.
When I inspect the report in Preview, the date is not displayed in a
calendar, but instead as a tremendously long list. (Not good!) When I
look at the report parameter in Layout it has the datatype String.
- If I change the parameter to DateTime (in order to get a calendar),
I get a runtime error: "an error occurred during local report
processing. The property 'Valid Values' of report paramater
'KalenderDatum' doesn't have the expected type."
My question:
- What can I do to make Reporting Services understand that I want to
display dates?
- Can calendar controls be used with MDX at all'
- Is there someting I need to do in MDX to format my date as a
DateTime field?
- Does RS just not like Swedish' ;-)
Additional info:
- My cube is built on a staging database, and if I design a report
against that database with ordinary SQL statements using the same
DateTime field, I get a calendar control, and RS understands that I
want to display dates. Everything works fine.
- I have set the language property on my report to Swedish (the date
format being yyyy-mm-dd)
Please let me know if there is a workaround for this. Unfortunately
MDX is not exactly my second language either...
CarinaI had the same problem (but with US date formats). What you need to do is on
the "Data" tab in your RS project, change the MDX query to an expression by
putting an = there followed by your whole MDX query in double quotes. Then
concantenate in your parameter date fields. Here is an example of how I did
it for a range on [Start Date] based on 2 datetime parameters called
Begin_Date and End_Date. Note, be sure there are no control characters or
carriage returns in your MDX expression.
="SELECT NON EMPTY { [Measures].[Login Count]} ON COLUMNS, NON EMPTY {
[GroupUsers].[Application - Name].[Login Name].ALLMEMBERS * [Date].[Start
Date].&[" & Format(Parameters!Begin_Date.Value,"MM/dd/yyyy") &
"]:[Date].[Start Date].&[" & Format(Parameters!End_Date.Value,"MM/dd/yyyy") &
"] } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
[MyCubeDataset])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
I just set up regular Datetime report parameters for Begin_Date and End_Date
and added them to the dataset on the parameters tab. I formatted them into
the format I wanted inside the expression above because I needed the leading
zeroes for the date comparisons to work in MDX. Also, here is a link that
helped me:
http://msdn2.microsoft.com/en-us/library/aa902647(sql.80).aspx
Hope this helps.
"carina.jones@.sogeti.se" wrote:
> Hi,
> I need to develop several reports in RS 2005 where the report
> parameters are dates. The reports are based on a SQL Server 2005
> Analysis Services Cube and consequently the reports need to be based
> on MDX. My dates are formatted according to Swedish date standard. I
> would like the users to pick the dates from a Calendar control.
> (I have seen several smilar problems with dates, but all referring to
> SQ statements, not MDX)
> Here's my problem:
> When I design my query in the Data tab of the report, I choose the
> field that I want to use as a parameter (in this case a date) and tick
> the checkbox indicating that I want a Report parameter created.
> When I inspect the report in Preview, the date is not displayed in a
> calendar, but instead as a tremendously long list. (Not good!) When I
> look at the report parameter in Layout it has the datatype String.
> - If I change the parameter to DateTime (in order to get a calendar),
> I get a runtime error: "an error occurred during local report
> processing. The property 'Valid Values' of report paramater
> 'KalenderDatum' doesn't have the expected type."
> My question:
> - What can I do to make Reporting Services understand that I want to
> display dates?
> - Can calendar controls be used with MDX at all'
> - Is there someting I need to do in MDX to format my date as a
> DateTime field?
> - Does RS just not like Swedish' ;-)
> Additional info:
> - My cube is built on a staging database, and if I design a report
> against that database with ordinary SQL statements using the same
> DateTime field, I get a calendar control, and RS understands that I
> want to display dates. Everything works fine.
> - I have set the language property on my report to Swedish (the date
> format being yyyy-mm-dd)
>
> Please let me know if there is a workaround for this. Unfortunately
> MDX is not exactly my second language either...
> Carina
>|||Thank you so much for your input! With your help and the explanation
below I did figure it out :-). I'm a happy camper!
/Carina
In short (found this explanation on another site):
1. Develop the report.
2. Change the Data Type of the date parameters to DateTime, and set
Available Values to Non-queried.
3. On the Data tab, edit the cube's Dataset (click on the ... button).
4. Click on the Parameters tab of the Dataset dialog box. Replace the
Value of the date parameters with ="[Dimension].[Member].&[" +
Format(CDate(Parameters!ParameterName.Value), "yyyy-MM-ddT00:00:00") +
"]" where [Dimension].[Member] and ParameterName are the dimensions
and parameter names of your particular cube. Depending on how your
dimension is set up, you may also need to change the format of the
date.
5. There is no need to change the MDX of the query.

MDX with parameters using OleDb?

I'm trying to run an MDX query with a parameter. It works fine with ADOMD.NET, but I can't get it to work using OleDb. (I have to use OleDb because of limitations of the calling application.) Is this possible?

The following code works:

//using Microsoft.AnalysisServices.AdomdClient;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

AdomdConnection conn = new AdomdConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

AdomdCommand cmd = new AdomdCommand(MDX, conn);

cmd.Parameters.Add("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

AdomdDataAdapter adp = new AdomdDataAdapter(cmd);

adp.Fill(ds);

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

The following code fails:

//using System.Data.OleDb;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

OleDbCommand cmd = new OleDbCommand(MDX, conn);

cmd.Parameters.AddWithValue("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

adp.Fill(ds); //produces error: "The following system error occurred:The parameter is incorrect. . Error Code = 0x80070057, External Code = 0x00000000:."

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

Try to add @. in front of the parameter name. It seems that ADOMD.NET and OledbClient are not constent in this regard.

cmd.Parameters.AddWithValue("@.Param1", "abcde");

|||

Irina-

I still get the same error even after adding the @. sign. If that code works for you, what version are you running on? Is this maybe an SP2 fix?

|||

Actually I'm getting the same error. Debugging OLEDB provider shows that OledbClient passes it

DBPARAMBINDINFO with NULL parameter name. So it seems like a bug in OledbClient. Currently I don't have access to OledbClient sourses. I'll try to find sorces tomorrow, but can't promise, so may be you should ask the same question on OledbClient forum.

|||

I cross-posted in the forum (I think) you were talking about:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1088407&SiteID=1&mode=1

|||

I haven't heard anything from the other forum, so I'm afraid I'm never going to get an answer on this. Irina, have you had a chance to look at the source? Or should I post this to connect.microsoft.com?

I also ran across the following Books Online page which mentions that you're required to use the ICommandWithParameters interface when doing parameterized MDX with OleDb. I'm not optimistic that will produce any different results, and I haven't really found a good code sample that shows how to implement that interface and use it.
http://msdn2.microsoft.com/en-us/library/ms145518.aspx

|||

reported to connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251601

MDX with parameters using OleDb?

I'm trying to run an MDX query with a parameter. It works fine with ADOMD.NET, but I can't get it to work using OleDb. (I have to use OleDb because of limitations of the calling application.) Is this possible?

The following code works:

//using Microsoft.AnalysisServices.AdomdClient;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

AdomdConnection conn = new AdomdConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

AdomdCommand cmd = new AdomdCommand(MDX, conn);

cmd.Parameters.Add("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

AdomdDataAdapter adp = new AdomdDataAdapter(cmd);

adp.Fill(ds);

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

The following code fails:

//using System.Data.OleDb;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

OleDbCommand cmd = new OleDbCommand(MDX, conn);

cmd.Parameters.AddWithValue("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

adp.Fill(ds); //produces error: "The following system error occurred:The parameter is incorrect. . Error Code = 0x80070057, External Code = 0x00000000:."

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

Try to add @. in front of the parameter name. It seems that ADOMD.NET and OledbClient are not constent in this regard.

cmd.Parameters.AddWithValue("@.Param1", "abcde");

|||

Irina-

I still get the same error even after adding the @. sign. If that code works for you, what version are you running on? Is this maybe an SP2 fix?

|||

Actually I'm getting the same error. Debugging OLEDB provider shows that OledbClient passes it

DBPARAMBINDINFO with NULL parameter name. So it seems like a bug in OledbClient. Currently I don't have access to OledbClient sourses. I'll try to find sorces tomorrow, but can't promise, so may be you should ask the same question on OledbClient forum.

|||

I cross-posted in the forum (I think) you were talking about:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1088407&SiteID=1&mode=1

|||

I haven't heard anything from the other forum, so I'm afraid I'm never going to get an answer on this. Irina, have you had a chance to look at the source? Or should I post this to connect.microsoft.com?

I also ran across the following Books Online page which mentions that you're required to use the ICommandWithParameters interface when doing parameterized MDX with OleDb. I'm not optimistic that will produce any different results, and I haven't really found a good code sample that shows how to implement that interface and use it.
http://msdn2.microsoft.com/en-us/library/ms145518.aspx

|||

reported to connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251601

MDX statement using Parameters as Calendar

Hello People,
I'm developing reports using SQL2005 and Reporting Service 2005 where I'm
using MDX to extract the information from the cube, but now I need to get a
range of data as a parameter, but using the Calendar date picker from
Reporting server, do you know if is it possible ?
Thanks,
ElvisHi Elvis,
This issue was discussed a couple of months back (June CTP time-frame) in
the SQL Server 2005 Beta newsgroup, but unfortunately I can no longer locate
it. Anyway, what I recall is that I had to resort to using the old OLE DB for
OLAP Provider (which worked with MDX in RS 2000), because in that case the
MDX query is dynamically built as an expression; and the data type of date
parameters can then be set to DateTime, which will enable the Calendar date
picker. Of course, in that case, the expression has to transform the returned
date string to an appropriate MDX Date Dimension member name.
"elvispires" wrote:
> Hello People,
> I'm developing reports using SQL2005 and Reporting Service 2005 where I'm
> using MDX to extract the information from the cube, but now I need to get a
> range of data as a parameter, but using the Calendar date picker from
> Reporting server, do you know if is it possible ?
> Thanks,
> Elvis
>
>|||hi,
do you have an example of dynamically built mdx ? Becaus i also have that
problem.
with
member [month_list].[jan-sept] AS 'sum([month_list].[All
month_list].[January]:[Month_list].[All month_list].[September])'
the last [September] is supposed to be tha name of the current month
Thanks!
"Deepak" wrote:
> Hi Elvis,
> This issue was discussed a couple of months back (June CTP time-frame) in
> the SQL Server 2005 Beta newsgroup, but unfortunately I can no longer locate
> it. Anyway, what I recall is that I had to resort to using the old OLE DB for
> OLAP Provider (which worked with MDX in RS 2000), because in that case the
> MDX query is dynamically built as an expression; and the data type of date
> parameters can then be set to DateTime, which will enable the Calendar date
> picker. Of course, in that case, the expression has to transform the returned
> date string to an appropriate MDX Date Dimension member name.
> "elvispires" wrote:
> > Hello People,
> >
> > I'm developing reports using SQL2005 and Reporting Service 2005 where I'm
> > using MDX to extract the information from the cube, but now I need to get a
> > range of data as a parameter, but using the Calendar date picker from
> > Reporting server, do you know if is it possible ?
> >
> > Thanks,
> > Elvis
> >
> >
> >
> >

MDX question about Parameters and two different types of hierarchies....

I was looking at creating cascading parameters within a report I am developing in BIDS.

The parameters I am creating are for an organization structure that looks something like this.

Organization Hierarchy

===========

-Division

-Community

The problem is Division and City are part of a parent child hierarchy inside of a separate DIM named Organization.

Community is part of an attribute hiearachy defined in another DIM named Lot.

I want to be able to filter down the Communitys based upon which Division is selected. The code below returns all Communitys. I try to use a CrossJoin but I cant get it to filter the Communitys down correctly.

Code Snippet

WITH MEMBER [Measures].[ParameterCaption] AS '[Lot].[Community Rollup].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Lot].[Community Rollup].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Lot].[Community Rollup].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Lot].[Community Rollup].Levels(1) ON ROWS FROM [Cube]

Since Division and Community are in different dimensions, I assume that you want to select Communities with cube data for the selected Division(s). You could try NonEmpty(), like:

WITH MEMBER [Measures].[ParameterCaption] AS '[Lot].[Community Rollup].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Lot].[Community Rollup].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Lot].[Community Rollup].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

NonEmpty([Lot].[Community Rollup].Levels(1), CrossJoin(StrToSet(@.Community, CONSTRAINED), {[Measures].[FilterMeasure]})) ON ROWS

FROM [Cube]

where [Measures].[FilterMeasure] is a cube measure from the measure group which is used to determine whether a Community has data.

|||Thank you, I will give this a try and let you know how it turns out.

MDX Query with parameters

Hi there,

I'm developing one MDX query to place in my report, but I'm having a lot of troubles when using one of my report parameters.

I hope I can explain me good enough.

I have a report parameter that's used in the WHERE clause to choose the value of one of my dimensions, the problem is that I'm also trying to compare the value of this parameter to decide the value of another dimension, but the comparison isn't working at all.

Let me show one example:

The parameter name in question is: DimFolderWalletDesciption.

Code Snippet

IIF(
@.DimFolderWalletDesciption = [Dim Folder].[Wallet Desciption].&[Investimento],
[Dim Indexation].[Indexation Group].[Rendimento Variável],
[Dim Indexation].[Indexation Group].[Indexation Group].ALLMEMBERS
)

This is suppost to do the following: If my parameter as the selected value "Investimento", then the "Indexation Group" selected should be "Rendimento Fixo" if not, there shouldn't be any "filter" in the Dimension "Indexation Group".

I've tryed a lot of combinations, but none works, what am I doing wrong?

Thanks in advance.
Regards.
Problem solved.

Best regards,

MDx Query Parameters

Hi,
I'm trying to use mdx query in reporting services with a parameters.
But I am getting an error telling me my token is invalid my query
string is this:
SELECT NON EMPTY { [Measures].[Executions] } ON COLUMNS, NON EMPTY
{ Members("[Dates].[All Dates].[" + @.Year + "]." + "[June]") }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
[Procedure Completions] CELL PROPERTIES VALUE
I've declared Year right now I am just using the default value of 2007
but I keep on getting this error:
Formula error - syntax error - token is not valid: "{ Members("[Dates].
[All Dates].[" + ^@.^Year+"]." + "[June]") }" (urn:schemas-microsoft-
com:xml-analysis)
Any ideas of what I could be doing wrong?Try using the following:
{ Members(STRTOSET("[Dates].[All Dates].[" + @.Year + "]." + "[June]"),
CONSTRAINED) }
Although if I were doing it I would do it like this.
{Members(STRTOSET(@.Dateparameter,CONSTRAINED))} and then do the string
manipulation in the parameters section of the data set properties. The DS
properties are the "..." next to the name of the DS. You can use VB to
create an expression in there. This way you can use the query builder to
create your MDX for you.
Hope this helps,
Al
<RMurjani85@.gmail.com> wrote in message
news:1185475682.673977.86900@.g4g2000hsf.googlegroups.com...
> Hi,
> I'm trying to use mdx query in reporting services with a parameters.
> But I am getting an error telling me my token is invalid my query
> string is this:
> SELECT NON EMPTY { [Measures].[Executions] } ON COLUMNS, NON EMPTY
> { Members("[Dates].[All Dates].[" + @.Year + "]." + "[June]") }
> DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
> [Procedure Completions] CELL PROPERTIES VALUE
> I've declared Year right now I am just using the default value of 2007
> but I keep on getting this error:
> Formula error - syntax error - token is not valid: "{ Members("[Dates].
> [All Dates].[" + ^@.^Year+"]." + "[June]") }" (urn:schemas-microsoft-
> com:xml-analysis)
> Any ideas of what I could be doing wrong?
>

MDX Query Filters

Hi,

I am creating report using reporting service and datasource is SQL Server 2005 Cube.

I am using query builder and i am creating query parameters in the query builder but when i run the report is showing the filters but data is not filtering based on which i selected from the list.

How do i create filters? Please help out on this.

Thanks

Switch to MDX mode and see the underlying query. There should be a subselect filter in the WHERE clause.|||

Thanks,

Now i am able to see the subqueries in MDX, but i am getting error when select the 'select all' from the list the following error message getting.

"An error occured during local report processing. Query execution failed for data set 'Dataset1' Query (1,461) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.

Please help on this.

Thanks

|||

The CONSTRAINED flag requires the selected member name to be resolved to a qualified member name. Drop a textbox on your report and set it to expression:

=Join(Parameters!<parameter name>.Value,", ")

Run the report. Does the output looks like valid member names?

MDX query

I'm building a report from SSAS 2005 with three parameters.

start date

end date

project stream

I would filter only the non empty project stream in the actual cost fact table included in the time interval specified with the first two paramenters.

SSRS automatically compose an MDX query that I try to modify to get what I want:

WITHMEMBER [Measures].[ParameterCaption] AS'[PROJECT].[STREAM].CURRENTMEMBER.MEMBER_CAPTION'MEMBER [Measures].[ParameterValue] AS'[PROJECT].[STREAM].CURRENTMEMBER.UNIQUENAME'MEMBER [Measures].[ParameterLevel] AS'[PROJECT].[STREAM].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel], [Measures].[EUR] } ONCOLUMNS ,

NONEMPTY [PROJECT].[STREAM].ALLMEMBERSONROWS

FROM [PMO_MDB]

WHERE { STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END)}

In this way I get all project streams and I'm not able to filter away the empty cell (using the [Measures].[EUR] defined in the actual cost fact table). In fact SSRS put some intrisict member properties on columns as calculated measure !

There is another way to extract the same info but filtering away the empty cell ?

Cosimo

Take a look at the EXISTING function. I believe you could use it on your row axis to limit Project.Stream members to those associated with a fact entry.

Bryan

|||

Thanks Brjan ... I tried but I get the same result also using EXISTING !

I think the problem is that SSRS put on columns as a calculated measure some properties.

There is another way to extract such properies ?

I try the dimension properties clause ... but nothing happen !

Cosimo

|||

Sorry. I meant the EXISTS function. (The names are so similar I always screw them up.) Here is an example of what I'm describing:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

EXISTS(

[Product].[SubCategory].Members,

{ STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")},

'Reseller Sales'

) on 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

For comparison:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

[Product].[SubCategory].Memberson 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

Sorry for the confusion,

Bryan

MDX Parser

Hello,
Im currently running SEPT CTP, and im trying to create a report based on a
dataset filled by a MDX query. That query must have parameters.
I have defined the query in query designer, but after that I must edit the
query because some of my parameters include spaces ' ' and I must edit
StrToSet to surround my @.var with '[ ]' .
The problem is that i cant make any change to the query, it always gives the
error "An MDX expression was expected. An empty expression was found.". Even
when I switch from designer to hand-edit it gives the same error if I process
it.
I think it has somethin to do with the parser, because the StrTo... family
of functions expect a valid MDX expression as a parameter but all I have is
@.varname.
Help anyone? Thanks.hello Fernando,
How exactly are you changing your mdx query to accept the parameter values?
If you show me i may be able to help.
I've done it by running the query without any parameters to get the fields,
then editing the query to accept the parameters. This is done by placing an
'=' sign at the beginning of the query then at the end of each string of text
insert " & "
i.e
="with" & "member" &
and
"select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
etc etc
dont know if taht helps but i tried.
bobfoc
"Fernando Marçal" wrote:
> Hello,
> Im currently running SEPT CTP, and im trying to create a report based on a
> dataset filled by a MDX query. That query must have parameters.
> I have defined the query in query designer, but after that I must edit the
> query because some of my parameters include spaces ' ' and I must edit
> StrToSet to surround my @.var with '[ ]' .
> The problem is that i cant make any change to the query, it always gives the
> error "An MDX expression was expected. An empty expression was found.". Even
> when I switch from designer to hand-edit it gives the same error if I process
> it.
> I think it has somethin to do with the parser, because the StrTo... family
> of functions expect a valid MDX expression as a parameter but all I have is
> @.varname.
> Help anyone? Thanks.|||sorry made an error the parameter string should say [" &
Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
the wrong way round!
"bobfoc" wrote:
> hello Fernando,
> How exactly are you changing your mdx query to accept the parameter values?
> If you show me i may be able to help.
> I've done it by running the query without any parameters to get the fields,
> then editing the query to accept the parameters. This is done by placing an
> '=' sign at the beginning of the query then at the end of each string of text
> insert " & "
> i.e
> ="with" & "member" &
> and
> "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> etc etc
> dont know if taht helps but i tried.
> bobfoc
> "Fernando Marçal" wrote:
> > Hello,
> >
> > Im currently running SEPT CTP, and im trying to create a report based on a
> > dataset filled by a MDX query. That query must have parameters.
> > I have defined the query in query designer, but after that I must edit the
> > query because some of my parameters include spaces ' ' and I must edit
> > StrToSet to surround my @.var with '[ ]' .
> > The problem is that i cant make any change to the query, it always gives the
> > error "An MDX expression was expected. An empty expression was found.". Even
> > when I switch from designer to hand-edit it gives the same error if I process
> > it.
> >
> > I think it has somethin to do with the parser, because the StrTo... family
> > of functions expect a valid MDX expression as a parameter but all I have is
> > @.varname.
> >
> > Help anyone? Thanks.|||Hi bobfoc,
I've manage to use the parameters by starting the query with query designer.
What is absurd is this: I start the query with query designer, and when I
switch to query mode the query is auto-generated and the error is still
produced.
"bobfoc" wrote:
> sorry made an error the parameter string should say [" &
> Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
> the wrong way round!
> "bobfoc" wrote:
> > hello Fernando,
> >
> > How exactly are you changing your mdx query to accept the parameter values?
> > If you show me i may be able to help.
> >
> > I've done it by running the query without any parameters to get the fields,
> > then editing the query to accept the parameters. This is done by placing an
> > '=' sign at the beginning of the query then at the end of each string of text
> > insert " & "
> > i.e
> > ="with" & "member" &
> > and
> > "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> >
> > etc etc
> >
> > dont know if taht helps but i tried.
> >
> > bobfoc
> >
> > "Fernando Marçal" wrote:
> >
> > > Hello,
> > >
> > > Im currently running SEPT CTP, and im trying to create a report based on a
> > > dataset filled by a MDX query. That query must have parameters.
> > > I have defined the query in query designer, but after that I must edit the
> > > query because some of my parameters include spaces ' ' and I must edit
> > > StrToSet to surround my @.var with '[ ]' .
> > > The problem is that i cant make any change to the query, it always gives the
> > > error "An MDX expression was expected. An empty expression was found.". Even
> > > when I switch from designer to hand-edit it gives the same error if I process
> > > it.
> > >
> > > I think it has somethin to do with the parser, because the StrTo... family
> > > of functions expect a valid MDX expression as a parameter but all I have is
> > > @.varname.
> > >
> > > Help anyone? Thanks.|||that's just wierd, I've had some strange problems with reporting services
before but nothing that a reboot hasn't fixed.
I've never used query designer, i prefer writing from scratch then you
understand it better, but then i also work on the philosophy of if it works,
leave it unless you have to change it.
Glad you kid of sorted it,
bob
"Fernando Marçal" wrote:
> Hi bobfoc,
> I've manage to use the parameters by starting the query with query designer.
> What is absurd is this: I start the query with query designer, and when I
> switch to query mode the query is auto-generated and the error is still
> produced.
> "bobfoc" wrote:
> > sorry made an error the parameter string should say [" &
> > Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
> > the wrong way round!
> >
> > "bobfoc" wrote:
> >
> > > hello Fernando,
> > >
> > > How exactly are you changing your mdx query to accept the parameter values?
> > > If you show me i may be able to help.
> > >
> > > I've done it by running the query without any parameters to get the fields,
> > > then editing the query to accept the parameters. This is done by placing an
> > > '=' sign at the beginning of the query then at the end of each string of text
> > > insert " & "
> > > i.e
> > > ="with" & "member" &
> > > and
> > > "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> > >
> > > etc etc
> > >
> > > dont know if taht helps but i tried.
> > >
> > > bobfoc
> > >
> > > "Fernando Marçal" wrote:
> > >
> > > > Hello,
> > > >
> > > > Im currently running SEPT CTP, and im trying to create a report based on a
> > > > dataset filled by a MDX query. That query must have parameters.
> > > > I have defined the query in query designer, but after that I must edit the
> > > > query because some of my parameters include spaces ' ' and I must edit
> > > > StrToSet to surround my @.var with '[ ]' .
> > > > The problem is that i cant make any change to the query, it always gives the
> > > > error "An MDX expression was expected. An empty expression was found.". Even
> > > > when I switch from designer to hand-edit it gives the same error if I process
> > > > it.
> > > >
> > > > I think it has somethin to do with the parser, because the StrTo... family
> > > > of functions expect a valid MDX expression as a parameter but all I have is
> > > > @.varname.
> > > >
> > > > Help anyone? Thanks.

MDX parameters in Reporting Services 2005

Hello,
I've been trying to create a report using a dataset filled with a MDX query.
That query needs to have parameters (they come from report parameters). I've
done everything, including what is described in msdn2 and still cant get it
to work.
(The problem is not with the query, if i substitute the @.vars with strings
it works!)
Has everyone ever done this?
Thx.You should use the filter area in the Analysis Services query designer and
check the parameter checkbox. The MDX query will contain:
STRTOSET(@.variablename, CONSTRAINED)
And your parameters will need to be member unique names. The MSDN article
might be for SQL 2000 RS and therefore outdated.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
> Hello,
> I've been trying to create a report using a dataset filled with a MDX
> query.
> That query needs to have parameters (they come from report parameters).
> I've
> done everything, including what is described in msdn2 and still cant get
> it
> to work.
> (The problem is not with the query, if i substitute the @.vars with strings
> it works!)
> Has everyone ever done this?
> Thx.|||Hello Brian,
Im not using query designer, i am creating my own query.
Below I provide my previous query and the query with the change you
sugested. It still doensn't work... it gives the following error:
"Parser: The syntax for '=' is incorrect. (Microsoft SQL Server 2005
Analysis Services)"
The query im using is this:
WITH MEMBER [Measures].[Visitas Previstas]
AS '[Measures].[OBJECTIVE]'
SELECT
{[Measures].[Visitas],
[Measures].[Visitas Previstas],
[Measures].[% Real],
[Measures].[% Por Potencial],
[Measures].[% nos VH e H]}
on columns,
{[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
on rows
FROM TEAMS
WHERE CROSSJOIN([TEAMS DIM LINE].[LINE DESC].[@.lines1],
[TEAMS DIM PRODUCT].[PRODUCT DESC].[@.products1],
[TEAMS DIM BRICK].[TEAMS DIM BRICK].[ALL].[@.bricks1])
The query with changes:
WITH MEMBER [Measures].[Visitas Previstas]
AS '[Measures].[OBJECTIVE]'
SELECT
{[Measures].[Visitas],
[Measures].[Visitas Previstas],
[Measures].[% Real],
[Measures].[% Por Potencial],
[Measures].[% nos VH e H]}
on columns,
{[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
on rows
FROM TEAMS
WHERE CROSSJOIN(strtoset(@.lines1,CONSTRAINED),
strtoset(@.products1,CONSTRAINED),
strtoset(@.bricks1,CONSTRAINED))
Thanks for the help... I really need to get this to work...
"Brian Welcker [MSFT]" wrote:
> You should use the filter area in the Analysis Services query designer and
> check the parameter checkbox. The MDX query will contain:
> STRTOSET(@.variablename, CONSTRAINED)
> And your parameters will need to be member unique names. The MSDN article
> might be for SQL 2000 RS and therefore outdated.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
> news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
> > Hello,
> >
> > I've been trying to create a report using a dataset filled with a MDX
> > query.
> > That query needs to have parameters (they come from report parameters).
> > I've
> > done everything, including what is described in msdn2 and still cant get
> > it
> > to work.
> > (The problem is not with the query, if i substitute the @.vars with strings
> > it works!)
> >
> > Has everyone ever done this?
> >
> > Thx.
>
>|||Sorry, forgot to mencion that i must use report parameters (I have cascading
parameters that i must use in the MDX query).
I've defined this parameters before defining the MDX query, and now i want
to use them in the query.
Thanks again.
"Fernando Marçal" wrote:
> Hello Brian,
> Im not using query designer, i am creating my own query.
> Below I provide my previous query and the query with the change you
> sugested. It still doensn't work... it gives the following error:
> "Parser: The syntax for '=' is incorrect. (Microsoft SQL Server 2005
> Analysis Services)"
> The query im using is this:
> WITH MEMBER [Measures].[Visitas Previstas]
> AS '[Measures].[OBJECTIVE]'
> SELECT
> {[Measures].[Visitas],
> [Measures].[Visitas Previstas],
> [Measures].[% Real],
> [Measures].[% Por Potencial],
> [Measures].[% nos VH e H]}
> on columns,
> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
> on rows
> FROM TEAMS
> WHERE CROSSJOIN([TEAMS DIM LINE].[LINE DESC].[@.lines1],
> [TEAMS DIM PRODUCT].[PRODUCT DESC].[@.products1],
> [TEAMS DIM BRICK].[TEAMS DIM BRICK].[ALL].[@.bricks1])
> The query with changes:
> WITH MEMBER [Measures].[Visitas Previstas]
> AS '[Measures].[OBJECTIVE]'
> SELECT
> {[Measures].[Visitas],
> [Measures].[Visitas Previstas],
> [Measures].[% Real],
> [Measures].[% Por Potencial],
> [Measures].[% nos VH e H]}
> on columns,
> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
> on rows
> FROM TEAMS
> WHERE CROSSJOIN(strtoset(@.lines1,CONSTRAINED),
> strtoset(@.products1,CONSTRAINED),
> strtoset(@.bricks1,CONSTRAINED))
> Thanks for the help... I really need to get this to work...
>
> "Brian Welcker [MSFT]" wrote:
> > You should use the filter area in the Analysis Services query designer and
> > check the parameter checkbox. The MDX query will contain:
> >
> > STRTOSET(@.variablename, CONSTRAINED)
> >
> > And your parameters will need to be member unique names. The MSDN article
> > might be for SQL 2000 RS and therefore outdated.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
> > news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
> > > Hello,
> > >
> > > I've been trying to create a report using a dataset filled with a MDX
> > > query.
> > > That query needs to have parameters (they come from report parameters).
> > > I've
> > > done everything, including what is described in msdn2 and still cant get
> > > it
> > > to work.
> > > (The problem is not with the query, if i substitute the @.vars with strings
> > > it works!)
> > >
> > > Has everyone ever done this?
> > >
> > > Thx.
> >
> >
> >|||Why do you have an = sign in your query? Don't use an expression for your
query, just type the literal MDX in the MDX window. Ignore the SQL 2000 RS
directions - I would first build the query with the query designer and then
you can hand edit the generated MDX.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
news:E2AFD1FD-8909-4028-AC86-3592CCCE69C6@.microsoft.com...
> Sorry, forgot to mencion that i must use report parameters (I have
> cascading
> parameters that i must use in the MDX query).
> I've defined this parameters before defining the MDX query, and now i want
> to use them in the query.
> Thanks again.
> "Fernando Marçal" wrote:
>> Hello Brian,
>> Im not using query designer, i am creating my own query.
>> Below I provide my previous query and the query with the change you
>> sugested. It still doensn't work... it gives the following error:
>> "Parser: The syntax for '=' is incorrect. (Microsoft SQL Server 2005
>> Analysis Services)"
>> The query im using is this:
>> WITH MEMBER [Measures].[Visitas Previstas]
>> AS '[Measures].[OBJECTIVE]'
>> SELECT
>> {[Measures].[Visitas],
>> [Measures].[Visitas Previstas],
>> [Measures].[% Real],
>> [Measures].[% Por Potencial],
>> [Measures].[% nos VH e H]}
>> on columns,
>> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
>> on rows
>> FROM TEAMS
>> WHERE CROSSJOIN([TEAMS DIM LINE].[LINE DESC].[@.lines1],
>> [TEAMS DIM PRODUCT].[PRODUCT DESC].[@.products1],
>> [TEAMS DIM BRICK].[TEAMS DIM BRICK].[ALL].[@.bricks1])
>> The query with changes:
>> WITH MEMBER [Measures].[Visitas Previstas]
>> AS '[Measures].[OBJECTIVE]'
>> SELECT
>> {[Measures].[Visitas],
>> [Measures].[Visitas Previstas],
>> [Measures].[% Real],
>> [Measures].[% Por Potencial],
>> [Measures].[% nos VH e H]}
>> on columns,
>> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
>> on rows
>> FROM TEAMS
>> WHERE CROSSJOIN(strtoset(@.lines1,CONSTRAINED),
>> strtoset(@.products1,CONSTRAINED),
>> strtoset(@.bricks1,CONSTRAINED))
>> Thanks for the help... I really need to get this to work...
>>
>> "Brian Welcker [MSFT]" wrote:
>> > You should use the filter area in the Analysis Services query designer
>> > and
>> > check the parameter checkbox. The MDX query will contain:
>> >
>> > STRTOSET(@.variablename, CONSTRAINED)
>> >
>> > And your parameters will need to be member unique names. The MSDN
>> > article
>> > might be for SQL 2000 RS and therefore outdated.
>> >
>> > --
>> > Brian Welcker
>> > Group Program Manager
>> > Microsoft SQL Server Reporting Services
>> >
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in
>> > message
>> > news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
>> > > Hello,
>> > >
>> > > I've been trying to create a report using a dataset filled with a MDX
>> > > query.
>> > > That query needs to have parameters (they come from report
>> > > parameters).
>> > > I've
>> > > done everything, including what is described in msdn2 and still cant
>> > > get
>> > > it
>> > > to work.
>> > > (The problem is not with the query, if i substitute the @.vars with
>> > > strings
>> > > it works!)
>> > >
>> > > Has everyone ever done this?
>> > >
>> > > Thx.
>> >
>> >
>> >|||editing is the problem. I cant change anything in the query, it always gives
the error "An MDX expression was expected. An empty expression was found"
invariably!
PS: The = sign didnt come from the query but from the parameters I was
referencing in the query parameters window.
"Brian Welcker [MSFT]" wrote:
> Why do you have an = sign in your query? Don't use an expression for your
> query, just type the literal MDX in the MDX window. Ignore the SQL 2000 RS
> directions - I would first build the query with the query designer and then
> you can hand edit the generated MDX.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
> news:E2AFD1FD-8909-4028-AC86-3592CCCE69C6@.microsoft.com...
> > Sorry, forgot to mencion that i must use report parameters (I have
> > cascading
> > parameters that i must use in the MDX query).
> >
> > I've defined this parameters before defining the MDX query, and now i want
> > to use them in the query.
> >
> > Thanks again.
> >
> > "Fernando Marçal" wrote:
> >
> >> Hello Brian,
> >>
> >> Im not using query designer, i am creating my own query.
> >>
> >> Below I provide my previous query and the query with the change you
> >> sugested. It still doensn't work... it gives the following error:
> >>
> >> "Parser: The syntax for '=' is incorrect. (Microsoft SQL Server 2005
> >> Analysis Services)"
> >>
> >> The query im using is this:
> >>
> >> WITH MEMBER [Measures].[Visitas Previstas]
> >> AS '[Measures].[OBJECTIVE]'
> >> SELECT
> >> {[Measures].[Visitas],
> >> [Measures].[Visitas Previstas],
> >> [Measures].[% Real],
> >> [Measures].[% Por Potencial],
> >> [Measures].[% nos VH e H]}
> >> on columns,
> >> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
> >> on rows
> >> FROM TEAMS
> >> WHERE CROSSJOIN([TEAMS DIM LINE].[LINE DESC].[@.lines1],
> >> [TEAMS DIM PRODUCT].[PRODUCT DESC].[@.products1],
> >> [TEAMS DIM BRICK].[TEAMS DIM BRICK].[ALL].[@.bricks1])
> >>
> >> The query with changes:
> >>
> >> WITH MEMBER [Measures].[Visitas Previstas]
> >> AS '[Measures].[OBJECTIVE]'
> >> SELECT
> >> {[Measures].[Visitas],
> >> [Measures].[Visitas Previstas],
> >> [Measures].[% Real],
> >> [Measures].[% Por Potencial],
> >> [Measures].[% nos VH e H]}
> >> on columns,
> >> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
> >> on rows
> >> FROM TEAMS
> >> WHERE CROSSJOIN(strtoset(@.lines1,CONSTRAINED),
> >> strtoset(@.products1,CONSTRAINED),
> >> strtoset(@.bricks1,CONSTRAINED))
> >>
> >> Thanks for the help... I really need to get this to work...
> >>
> >>
> >>
> >> "Brian Welcker [MSFT]" wrote:
> >>
> >> > You should use the filter area in the Analysis Services query designer
> >> > and
> >> > check the parameter checkbox. The MDX query will contain:
> >> >
> >> > STRTOSET(@.variablename, CONSTRAINED)
> >> >
> >> > And your parameters will need to be member unique names. The MSDN
> >> > article
> >> > might be for SQL 2000 RS and therefore outdated.
> >> >
> >> > --
> >> > Brian Welcker
> >> > Group Program Manager
> >> > Microsoft SQL Server Reporting Services
> >> >
> >> > This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >> >
> >> > "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in
> >> > message
> >> > news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
> >> > > Hello,
> >> > >
> >> > > I've been trying to create a report using a dataset filled with a MDX
> >> > > query.
> >> > > That query needs to have parameters (they come from report
> >> > > parameters).
> >> > > I've
> >> > > done everything, including what is described in msdn2 and still cant
> >> > > get
> >> > > it
> >> > > to work.
> >> > > (The problem is not with the query, if i substitute the @.vars with
> >> > > strings
> >> > > it works!)
> >> > >
> >> > > Has everyone ever done this?
> >> > >
> >> > > Thx.
> >> >
> >> >
> >> >
>
>

MDX Parameter

Hi,

I want to create an arbitrary datetime picker parameters (StartDate and EndDate) to use it as a parameter in MDX. But, I couldn't do this, because in the filter pane it always expect me to select from the dimension(always associate with the dimension values). But what I want to do is pass a value from the datetime picker (which is not associated with the dimension) and filter the data where the parameters value is satisfied with the Date dimension value.

This issue is creating me a lot of headache, please somebody help me!

I appreciate any help.

Sincerely,

Amde

I've also spent a lot of time dealing with MDX parameters, so I can sympathize with your headache.

Anyway, I'm not totally sure I understand your problem. When you mention datetime picker parameters (startDate and endDate), why can't you just declare these in your Date dimension? If you want to return values between the two dates, just do something like this in the Report Designer View:

Dimension = Date Dim
Hierarchy = FullDate
Operator = Range (Inclusive)
Then select both check boxes for the parameters

This should create two report parameters entitled: FromFullDate and ToFullDate

Then you will be able to filter the data between the given dates.

|||

Hi Joel,

Thanks for your understanding. Actually, the thing you mentioned above is not the case. It is a little bit complex. As you said I can create those parameters in my dimension. However, this solution is not applicable for my requirement. I have to provide a user with the standard datatime picker, so that users can select whatever dates they want and the report should be filtered based on the parameter they pass.

There are two annoying things I have observed in mdx parameter:

1. If you create a parameter using Report-->Report parameters... dialog box, your mdx query doesn't recognize it.

2.If you want to create a parameter in your query, the parameter should come from the dimension. So this will be difficult if you want to do something outside the box, just like my situation.

This thing works fine in OLTP based report, however, I find it difficult to implement the same functionality in cubes.

Sincerely,

Amde

|||

#1 - I would create a date dimension in your cube that has everyday in it (for a wide date range). That way, you're not limiting the users to only those dates that have transactions in your cube.

#2 - Here is some MDX that uses paramters. When using paramters in MDX you have to enclose the entire statement in quotes. This MDX query uses two parameters: Time and business entity.

="WITH

MEMBER [Measures].[FundingRatio] AS
'([Measures].[Signeddata], [Account].[Funding Volume Loan Count] ) / [Measures].[AvgSubs]'

SELECT
{ ADDCALCULATEDMEMBERS ([Measures].Members) }
ON COLUMNS ,

{{" & parameters!pTime.value & " } *
{ [Account].[Submission Units] }}
ON ROWS

FROM [MyCube]

WHERE ( [Location].[TotalLocations], [Category].[ABC], " & parameters!pEntity.value & ")"

|||

If you want to create a report parameter without selecting a dimension (and it sounds like that is the case), I have an idea you can try.

Create your start and end time parameters using Report -> Report Parameters. Then, go to the "Data" tab for the current report. Next to the dataset name, click the "..." to edit the dataset. Click the "Parameters" tab and add the two parameters (start and end time). For the value field, select: expression and choose: =Parameters!ParamName.Value

This will define your parameters for the report and will let you use them in the MDX query.

|||

Thanks Joel,

I think that could be one possible solution, I will try it.

Sincerely,

Amde

|||

hi,

#2 is the good one.

Thanks

Amde

|||

Hi Joel,

We are also facing with exactly the same problem.

I followed the steps mentioned by you. I then changed my MDX query statement to reflect the new date parameter in the where clause. I actually wanted query like dt >=StartDate and dt<=EndDate. Right now I am just trying with a single date parameter as

SELECT NON EMPTY { [Measures].[Count] } ON COLUMNS,
NON EMPTY { ([DIM].[XX].[XX].ALLMEMBERS ) } ON ROWS FROM CUBE
WHERE [DATE DIM].[Dt].&[parameters!StartDate.value]

When I do prepare query I get the following error:

The 'parameters!StartDate.value' string cannot be converted to the date type.

I then tried the following:

SELECT NON EMPTY { [Measures].[Count] } ON COLUMNS,
NON EMPTY { ([DIM].[XX].[XX].ALLMEMBERS ) } ON ROWS FROM CUBE
WHERE [DATE DIM].[Dt].[" & parameters!StartDate.value &"]

When I do prepare query I get no error but my preview report always fails.

I have a record for 2006-12-07T20:07:08 datetime in my table. When I paste the same value in the date control I can see the date in 12/7/2006 8:07:08 PM format. Is it because of this that my query is not returnign any result?

If yes, what do I do in the MDX query statement to get the date in the format that is there in the table?

Any help would be appreciated. We are really stuck with this problem.

Thanks in advance!

|||

Hi,

I tried the same but I didn't get anything in the report. I have defined parameters at Report ->Report Parameters and mapped thme as you said. but how the parameters will be mapped to the MDX query which, do not have any parameter defined.

Thank,

Srik

|||

Again, if you use parameters in an MDX query, you need to enclose the ENTIRE query in quotes, not just the parameter. And, the query needs to be prefaced with an '=' sign. See my example above.

MDX Parameter

Hi,

I want to create an arbitrary datetime picker parameters (StartDate and EndDate) to use it as a parameter in MDX. But, I couldn't do this, because in the filter pane it always expect me to select from the dimension(always associate with the dimension values). But what I want to do is pass a value from the datetime picker (which is not associated with the dimension) and filter the data where the parameters value is satisfied with the Date dimension value.

This issue is creating me a lot of headache, please somebody help me!

I appreciate any help.

Sincerely,

Amde

I've also spent a lot of time dealing with MDX parameters, so I can sympathize with your headache.

Anyway, I'm not totally sure I understand your problem. When you mention datetime picker parameters (startDate and endDate), why can't you just declare these in your Date dimension? If you want to return values between the two dates, just do something like this in the Report Designer View:

Dimension = Date Dim
Hierarchy = FullDate
Operator = Range (Inclusive)
Then select both check boxes for the parameters

This should create two report parameters entitled: FromFullDate and ToFullDate

Then you will be able to filter the data between the given dates.

|||

Hi Joel,

Thanks for your understanding. Actually, the thing you mentioned above is not the case. It is a little bit complex. As you said I can create those parameters in my dimension. However, this solution is not applicable for my requirement. I have to provide a user with the standard datatime picker, so that users can select whatever dates they want and the report should be filtered based on the parameter they pass.

There are two annoying things I have observed in mdx parameter:

1. If you create a parameter using Report-->Report parameters... dialog box, your mdx query doesn't recognize it.

2.If you want to create a parameter in your query, the parameter should come from the dimension. So this will be difficult if you want to do something outside the box, just like my situation.

This thing works fine in OLTP based report, however, I find it difficult to implement the same functionality in cubes.

Sincerely,

Amde

|||

#1 - I would create a date dimension in your cube that has everyday in it (for a wide date range). That way, you're not limiting the users to only those dates that have transactions in your cube.

#2 - Here is some MDX that uses paramters. When using paramters in MDX you have to enclose the entire statement in quotes. This MDX query uses two parameters: Time and business entity.

="WITH

MEMBER [Measures].[FundingRatio] AS
'([Measures].[Signeddata], [Account].[Funding Volume Loan Count] ) / [Measures].[AvgSubs]'

SELECT
{ ADDCALCULATEDMEMBERS ([Measures].Members) }
ON COLUMNS ,

{{" & parameters!pTime.value & " } *
{ [Account].[Submission Units] }}
ON ROWS

FROM [MyCube]

WHERE ( [Location].[TotalLocations], [Category].[ABC], " & parameters!pEntity.value & ")"

|||

If you want to create a report parameter without selecting a dimension (and it sounds like that is the case), I have an idea you can try.

Create your start and end time parameters using Report -> Report Parameters. Then, go to the "Data" tab for the current report. Next to the dataset name, click the "..." to edit the dataset. Click the "Parameters" tab and add the two parameters (start and end time). For the value field, select: expression and choose: =Parameters!ParamName.Value

This will define your parameters for the report and will let you use them in the MDX query.

|||

Thanks Joel,

I think that could be one possible solution, I will try it.

Sincerely,

Amde

|||

hi,

#2 is the good one.

Thanks

Amde

|||

Hi Joel,

We are also facing with exactly the same problem.

I followed the steps mentioned by you. I then changed my MDX query statement to reflect the new date parameter in the where clause. I actually wanted query like dt >=StartDate and dt<=EndDate. Right now I am just trying with a single date parameter as

SELECT NON EMPTY { [Measures].[Count] } ON COLUMNS,
NON EMPTY { ([DIM].[XX].[XX].ALLMEMBERS ) } ON ROWS FROM CUBE
WHERE [DATE DIM].[Dt].&[parameters!StartDate.value]

When I do prepare query I get the following error:

The 'parameters!StartDate.value' string cannot be converted to the date type.

I then tried the following:

SELECT NON EMPTY { [Measures].[Count] } ON COLUMNS,
NON EMPTY { ([DIM].[XX].[XX].ALLMEMBERS ) } ON ROWS FROM CUBE
WHERE [DATE DIM].[Dt].[" & parameters!StartDate.value &"]

When I do prepare query I get no error but my preview report always fails.

I have a record for 2006-12-07T20:07:08 datetime in my table. When I paste the same value in the date control I can see the date in 12/7/2006 8:07:08 PM format. Is it because of this that my query is not returnign any result?

If yes, what do I do in the MDX query statement to get the date in the format that is there in the table?

Any help would be appreciated. We are really stuck with this problem.

Thanks in advance!

|||

Hi,

I tried the same but I didn't get anything in the report. I have defined parameters at Report ->Report Parameters and mapped thme as you said. but how the parameters will be mapped to the MDX query which, do not have any parameter defined.

Thank,

Srik

|||

Again, if you use parameters in an MDX query, you need to enclose the ENTIRE query in quotes, not just the parameter. And, the query needs to be prefaced with an '=' sign. See my example above.

MDX Parameter

Hi,

I want to create an arbitrary datetime picker parameters (StartDate and EndDate) to use it as a parameter in MDX. But, I couldn't do this, because in the filter pane it always expect me to select from the dimension(always associate with the dimension values). But what I want to do is pass a value from the datetime picker (which is not associated with the dimension) and filter the data where the parameters value is satisfied with the Date dimension value.

This issue is creating me a lot of headache, please somebody help me!

I appreciate any help.

Sincerely,

Amde

I've also spent a lot of time dealing with MDX parameters, so I can sympathize with your headache.

Anyway, I'm not totally sure I understand your problem. When you mention datetime picker parameters (startDate and endDate), why can't you just declare these in your Date dimension? If you want to return values between the two dates, just do something like this in the Report Designer View:

Dimension = Date Dim
Hierarchy = FullDate
Operator = Range (Inclusive)
Then select both check boxes for the parameters

This should create two report parameters entitled: FromFullDate and ToFullDate

Then you will be able to filter the data between the given dates.

|||

Hi Joel,

Thanks for your understanding. Actually, the thing you mentioned above is not the case. It is a little bit complex. As you said I can create those parameters in my dimension. However, this solution is not applicable for my requirement. I have to provide a user with the standard datatime picker, so that users can select whatever dates they want and the report should be filtered based on the parameter they pass.

There are two annoying things I have observed in mdx parameter:

1. If you create a parameter using Report-->Report parameters... dialog box, your mdx query doesn't recognize it.

2.If you want to create a parameter in your query, the parameter should come from the dimension. So this will be difficult if you want to do something outside the box, just like my situation.

This thing works fine in OLTP based report, however, I find it difficult to implement the same functionality in cubes.

Sincerely,

Amde

|||

#1 - I would create a date dimension in your cube that has everyday in it (for a wide date range). That way, you're not limiting the users to only those dates that have transactions in your cube.

#2 - Here is some MDX that uses paramters. When using paramters in MDX you have to enclose the entire statement in quotes. This MDX query uses two parameters: Time and business entity.

="WITH

MEMBER [Measures].[FundingRatio] AS
'([Measures].[Signeddata], [Account].[Funding Volume Loan Count] ) / [Measures].[AvgSubs]'

SELECT
{ ADDCALCULATEDMEMBERS ([Measures].Members) }
ON COLUMNS ,

{{" & parameters!pTime.value & " } *
{ [Account].[Submission Units] }}
ON ROWS

FROM [MyCube]

WHERE ( [Location].[TotalLocations], [Category].[ABC], " & parameters!pEntity.value & ")"

|||

If you want to create a report parameter without selecting a dimension (and it sounds like that is the case), I have an idea you can try.

Create your start and end time parameters using Report -> Report Parameters. Then, go to the "Data" tab for the current report. Next to the dataset name, click the "..." to edit the dataset. Click the "Parameters" tab and add the two parameters (start and end time). For the value field, select: expression and choose: =Parameters!ParamName.Value

This will define your parameters for the report and will let you use them in the MDX query.

|||

Thanks Joel,

I think that could be one possible solution, I will try it.

Sincerely,

Amde

|||

hi,

#2 is the good one.

Thanks

Amde

|||

Hi Joel,

We are also facing with exactly the same problem.

I followed the steps mentioned by you. I then changed my MDX query statement to reflect the new date parameter in the where clause. I actually wanted query like dt >=StartDate and dt<=EndDate. Right now I am just trying with a single date parameter as

SELECT NON EMPTY { [Measures].[Count] } ON COLUMNS,
NON EMPTY { ([DIM].[XX].[XX].ALLMEMBERS ) } ON ROWS FROM CUBE
WHERE [DATE DIM].[Dt].&[parameters!StartDate.value]

When I do prepare query I get the following error:

The 'parameters!StartDate.value' string cannot be converted to the date type.

I then tried the following:

SELECT NON EMPTY { [Measures].[Count] } ON COLUMNS,
NON EMPTY { ([DIM].[XX].[XX].ALLMEMBERS ) } ON ROWS FROM CUBE
WHERE [DATE DIM].[Dt].[" & parameters!StartDate.value &"]

When I do prepare query I get no error but my preview report always fails.

I have a record for 2006-12-07T20:07:08 datetime in my table. When I paste the same value in the date control I can see the date in 12/7/2006 8:07:08 PM format. Is it because of this that my query is not returnign any result?

If yes, what do I do in the MDX query statement to get the date in the format that is there in the table?

Any help would be appreciated. We are really stuck with this problem.

Thanks in advance!

|||

Hi,

I tried the same but I didn't get anything in the report. I have defined parameters at Report ->Report Parameters and mapped thme as you said. but how the parameters will be mapped to the MDX query which, do not have any parameter defined.

Thank,

Srik

|||

Again, if you use parameters in an MDX query, you need to enclose the ENTIRE query in quotes, not just the parameter. And, the query needs to be prefaced with an '=' sign. See my example above.