0
votes

We need in a MDX query to group by Date column by number of days from parameter. Example:

Date       Requestor  Duration  Duration RFC
-------    --------   -------   -------
1/1/2011    C1         100      150
1/15/2011   C1         200      250
2/1/2011    C1         300      350
2/1/2011    C2         400      450

Parameter Days=30 Begin =1/1/2011 Result:

Period                Requestor  Duration  Duration RFC
----                  --------   -------   -------
1/1/2011-1/30/2011     C1         300      400
1/31/2011-2/2/2011     C1         300      350
1/31/2011-2/2/2011     C2         400      450

can somebody help me with MDX query?
Sample MDX query(without grouping I need):

SELECT NON EMPTY {[Close Date].[Year  -  Month -  Date].[Date]*[Requestor].[Requestor].  [Requestor]} ON ROWS,
NON EMPTY {[Measures].[Duration] ,[Measures].[RFC Audit  Stage Count]} ON
COLUMNS FROM (SELECT ( STRTOSET (@CategoryCategory, CONSTRAINED) ) ON COLUMNS 
FROM ( select (STRTOSET(@RequestorRequestor,CONSTRAINED)) ON COLUMNS FROM [RFC Cube]))

Using SSAS 2008.
Cube Name: RFC Cube.
Dimensions(with hierarchies):
1. Date= [Close Date].[Year - Month - Date].[Date]
2. Customer = [Requestor].[Requestor].[Requestor]
3. Amount1(Measure) = [Measures].[Duration]
4. Amount2(Measure) = [Measures].[RFC Audit Stage Count]

1
Is this MDX or SQL ? If mdx what are your dimensions, hierarchies, measures and what OLAP vendor are you using ?ic3
Yes, MDX. Using SSAS 2008.<br/> Cube Name: RFC Cube. Dimensions(with hierarchies): 1. Date= [Close Date].[Year - Month - Date].[Date] 2. Customer = [Requestor].[Requestor].[Requestor] 3. Amount1(Measure) = [Measures].[Duration] 4. Amount2(Measure) = [Measures].[RFC Audit Stage Count]user1057829

1 Answers

0
votes

If the grouping has a static number of members, you can use the Aggregate function the create calculated members in an hierarchy of the Date dimension.

In your case, however, it seems the grouping is dynamic, which you can't do in MDX; you'll have to create a new attribute in your Date (?) dimension.