I have a scenario where I have a crosstab with ProductCategory in the row edge, Year/Quarter in the column edge. I would take the measure to be OrderQuantity. Now what I need is something like this :
2006 - Q1 2006 - Q2 2006 - Q3 Variance Q1 vs Q2 Variance Q2 vs Q3
OrderQty OrderQty OrderQty OrderQty OrderQty
Bikes
Accessories
Clothing
Components
The Year and Quarter values comes from 2 different prompts. The columns for variance is displayed ONLY when the user selects MORE THAN 1 CONSECUTIVE quarters. So I have decided to first create derived data items having Order Quantity for all the 4 quarters, then displaying them based on conditions as per prompt selection. My package is a DMR model.
1) I am able to write the expression for OrderQuantity for a Quarter in a derived data item as :
tuple ([OrderQuantity] , [1] , currentMember ( [Dimensional View].[Product].[Product] ) )
So here '1' indicates the first quarter. Similarly I have created derived data items for the other Quarters. But this '1', '2', '3' and '4' are essentially 'Members' that CAN be fetched only from one of the Year levels in the hierarchy. So that makes it static ( belonging to only one Year value).
When I write the expression as :
tuple ( [OrderQuantity] ,
currentMember ( hierarchy ( member ( [Qtr1] , 'OrderQuantity_Qtr1', 'OrderQuantity_Qtr1', [Dimensional View].[Date].[Date] ) ) ) , currentMember ( [Dimensional View].[Product].[Product] ) )
where [Qtr1] is a data item having value 1 (which indicates 1st quarter being fetched by substring function on prompt value). The output is
1 2 3 4
OrderQty OrderQty OrderQty OrderQty
Bikes 3250 3700 8285 6996
Accessories 397 934 7828 4717
Clothing 734 1218 8676 6299
Components 245 577 2625 1760
which is the same when I simply use tuple ( [OrderQuantity] ). Hence I am not getting values for individual quarters by this way. Removing the currentMember and Hierarchy functions from the [Qtr1] yields no result.
Any idea?