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.