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