Parameters for report based on MDX

Hi
Can you pl. help me with something that is supposed to be really simple
however it is frustrating us no end. A client needs a reporting services
report based on an SSAS 2005 cube. He wants to have only two date filters
for the report that are shown in the form of calendars (default of RS report
parameters that are datetime type.) One is a 'From Date' and second is 'To
date'. The report data will be filtered on these two dates. Now to try this
out, I created a very simple report based on a cube. The MDX for the report
is as follows.
/<
SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Stat Can Sales]
Where (
{filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= datevalue('2006/01/01')
and datevalue(Dates.Currentmember.Name) <= datevalue('2007/01/01'))}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>/
AS you notice, I harded coded two dates in this MDX just to get the report
working with dataset and layout. Then I added two report parameters,
FromDate and ToDate of type datetime and ran the report again. Now of course
two calendar controls are shown when the report is previewed though the
parameters are not yet tied with the MDX query. Then I got to the dataset of
this MDX query, selected properties by clicking on the '.' on the toolbar
and in the parameters tab for the dataset, I created two parameters as
follows.
Name Value
pFromDate =Parameters!FromDate.Value
pToDate =Parameters!ToDate.Value
Finally I tried to modify the MDX by replacing the hardcoded date fields
with these parameters as
/<
SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Stat Can Sales]
Where (
{filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= @.
pFromDate
and datevalue(Dates.Currentmember.Name) <= @. pToDate)}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>/
Looks like straightforward thing to do. Right? However I cannot save this
parameterized MDX and get an error. So my question is, how to create and
pass parameters like this to MDX?
I also tried something I found on the net where it was mentioned that one
can start the MDX or a SQL query for that matter with "=" and then have the
rest of it is in double quotes. That way, the processing engine treats it as
an expression and then evaluates it at runtime. So the updated query would
be
/<
= "SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Stat Can Sales]
Where (
{filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= " + Parameters!FromDate.Value +
"and datevalue(Dates.Currentmember.Name) <= " + Paramters!ToDate.Value
+")}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
>/
This does not work either. Basically query editor does not like the starting
"=" itself.
Am I missing something?
ThanksHi Anant
You're second approach is the right one.
First, bin the parameters you defined on the paramter tab of the Dataset
properties dialog.
Then click the query tab. The value of you Querystring text area should be
something along the lines of:
"=Select
Non Empty { [Measures].[Number of copies sold] } ON COLUMNS,
Non Empty { ([Sales].[All Products].[Product Number].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
From
[Stat Can Sales]
Where
(
{
Filter(Dates.[Sales Date].Members, DateValue(Dates.Currentmember.Name) >=" + Parameters!FromDate.Value + " And DateValue(Dates.Currentmember.Name) <=" + Paramters!ToDate.Value +")
}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Bear in mind, you'll always need to run a hard coded version of your query
first to prepare your query.
Joe
"Anant Rege" wrote:
> Hi
>
> Can you pl. help me with something that is supposed to be really simple
> however it is frustrating us no end. A client needs a reporting services
> report based on an SSAS 2005 cube. He wants to have only two date filters
> for the report that are shown in the form of calendars (default of RS report
> parameters that are datetime type.) One is a 'From Date' and second is 'To
> date'. The report data will be filtered on these two dates. Now to try this
> out, I created a very simple report based on a cube. The MDX for the report
> is as follows.
>
> /<
>
> SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
> EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
>
> FROM [Stat Can Sales]
>
> Where (
> {filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >=> datevalue('2006/01/01')
> and datevalue(Dates.Currentmember.Name) <= datevalue('2007/01/01'))}
> )
> CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>
> >/
>
> AS you notice, I harded coded two dates in this MDX just to get the report
> working with dataset and layout. Then I added two report parameters,
> FromDate and ToDate of type datetime and ran the report again. Now of course
> two calendar controls are shown when the report is previewed though the
> parameters are not yet tied with the MDX query. Then I got to the dataset of
> this MDX query, selected properties by clicking on the '.' on the toolbar
> and in the parameters tab for the dataset, I created two parameters as
> follows.
>
> Name Value
> pFromDate =Parameters!FromDate.Value
> pToDate =Parameters!ToDate.Value
>
> Finally I tried to modify the MDX by replacing the hardcoded date fields
> with these parameters as
>
> /<
>
> SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
> EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
>
> FROM [Stat Can Sales]
>
> Where (
> {filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= @.
> pFromDate
> and datevalue(Dates.Currentmember.Name) <= @. pToDate)}
> )
> CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>
> >/
>
> Looks like straightforward thing to do. Right? However I cannot save this
> parameterized MDX and get an error. So my question is, how to create and
> pass parameters like this to MDX?
>
> I also tried something I found on the net where it was mentioned that one
> can start the MDX or a SQL query for that matter with "=" and then have the
> rest of it is in double quotes. That way, the processing engine treats it as
> an expression and then evaluates it at runtime. So the updated query would
> be
>
> /<
>
> = "SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
> EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
>
> FROM [Stat Can Sales]
>
> Where (
> {filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >=> " + Parameters!FromDate.Value +
> "and datevalue(Dates.Currentmember.Name) <= " + Paramters!ToDate.Value
> +")}
> )
> CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
>
>
> >/
>
> This does not work either. Basically query editor does not like the starting
> "=" itself.
>
> Am I missing something?
>
> Thanks
>
>
>
>|||Anant,
If you found a working solution, would you please email me? I have the same problem in trying to pass the parameter to MDX. I am using SSRS on SSAS 2005.
I tried filter and "=" method and have been getting an error on both methods whenever I insert the parameter.
Thanks
John
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com