Mdx query

Hello guys,

Assume I have a date dimention in a cube and I want to retrive data based on the StartDate and EndDate value. In short, I want to filter all records between StartDate value and EndDate value which is given by a user. Can any body give a general syntax or mdx query to achive this goal?

Sincerely,

Amde

Hello Amde

Assuming Analysis Services 2005:

SELECT {[Measures].[MyMeasures]} ON 0,

[MyDimension].[MyHierarchy].[MyLevel].members ON 1

FROM [MyCube]

WHERE {[Time].[Date].[<StartDate>] : [Time].[Date].[<EndDate>]}

|||

Thanks a lot michael,

But what if the the <StartDate> and <EndDate> values are parameter values that should be specified by a user? What will be the where condition in this case?

Sincerely,

Amde

|||The query itself needs to be the same. How would the user specify the parameters? Which client is the user using?|||

Hey michael,

That is a good question. I am going to write this query in the Report Designer; there will be report parameters <StartDate>and<EndDate> by which the user will specify and based on the parameter, I want to filter the record. I think this makes sense.

Sincerely,

Amde

|||

Ahhh... OK. Then I guess you just need to create two report parameters (for instance, named @.StartDate and @.EndDate). The values have to contain the unique member name for the dates. This could be something like [Time].[Date].&[20060518]. This value can the be passed to the MDX query. Now, I don't remember the exact syntax when you want to use report parameters in an MDX query, but I believe the WHERE statement should be something like this:

WHERE {StrToSet(@.StartDate.Value + ":" + @.EndDate.Value)}

|||

Hey Michael,

Thank you for sharing your idea, I know the syntax in reporting service; I will change the syntax and use it properly.

Sincerely,

Amde

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.