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]