Is this possible in MDX queries

Hi

The cube i have conatins the ProductName , Time as dim and Sales as measure. I need to query from the cube the product name and sales for multiple date ranges. As an example, The I/P shall be in the format

ProductName : A,B

DateRange : 1/1/2004:2/1/2004,1/1/2005:2/1/2005,and so on.

The number of date ranges is not fixed and also is the productname but the date ranges are common across both the Products. I have to query for the above I/P and use SSRS to get the O/P.

Thanking you in advance

regards

saishyam

I am not sure exactly what you are looking for, but here is an example using Adventure Works that list products with sales by total for different date ranges:

WITH

SET [Selected Products] AS

{[Product].[Product Categories].[Subcategory].[Mountain Bikes].Children}

SET [Date Range 2002] AS

{[Date].[Date].[January 1, 2002]:[Date].[Date].[February 1, 2002]}

MEMBER [Date].[Date].[Date Range 2002 Total] AS

Aggregate([Date Range 2002])

SET [Date Range 2003] AS

{[Date].[Date].[January 1, 2003]:[Date].[Date].[February 1, 2003]}

MEMBER [Date].[Date].[Date Range 2003 Total] AS

Aggregate([Date Range 2003])

SET [All Date Range Totals] AS

{[Date].[Date].[Date Range 2002 Total],

[Date].[Date].[Date Range 2003 Total]}

SELECT

{[All Date Range Totals]} ON COLUMNS,

NonEmpty({[Selected Products]},{[All Date Range Totals]}) ON ROWS

FROM

[Adventure Works]

WHERE

([Measures].[Internet Sales Amount])

You can substitute another measure for "Internet Sales Amount" by changing the WHERE clause and get the totals for a different measure.

HTH,

Steve

|||

Hi Steve

Thank you for the response.

The query that you have mentioned conatins the date ranges fixed, in my case the number of date ranges is not fixed. So how will i know , how many Sets i need to create? Is there any way to do that. I hope now my quesition is more clear.

regards

Saishyam

|||

You don't have to define the sets explicitly, you should be able to just do this:

{ { Date1member : Date2member}, { Date3member : Date4member } }

Alternatively, MDX has a function called Union.

sql