Hi,
I'm using CR Dev. 10 with MS SQL Server 2000 & Analysis Server to generate an OLAP Sales Report. I have 3 levels of Stores (Level 0 is All, Level 1 is Chains, Level 3 is Individual Stores). I have 5 levels for Time (Year,Q,Month,Week,Day). In it's current state, the cube is working, the reports work and everything functions as intended.
I have been able to use the Calculated Member wizard to create a Growth Calculated Member for individual stores or chains, however this wizard needs to be recreated everytime I change Dimension settings in Cube View.
What I would like to create is a generalized Calculated Member showing Growth that will function regardless of the dimension settings. So, e.g., if in Cube View I select All Stores and Quarterly Sales I see a table of total Quarterly Sales with a % number underneath that shows Quarter to Quarter increases/decreases in sales.
Q1 Q2 Q3 Q4
All Stores $100 $200 $50 $25
Growth All - 100% (75%) (50%)
If I select a 5 different Individual Stores and 1 Chain showing Quarterly & Weekly Sales I would see the same type of report with the different time periods and growth calculations for each individual store and one for the Chain. The Growth calculations would compare Q2 to Q1, Q3 to Q2, Q4 to Q3, and Week 2 to Week 1, Week 3 to Week 2, etc., within the OLAP cube reporting structure.
This is the formula that is currently working when I select a single Chain called Video Tepa:
IIf( [Stores].[Name].&[Video Tepa] = NULL, NULL, IIf( Count( { ([Stores].[Name].&[Video Tepa], [Time].PrevMember) } ) > 0, 100 * (([Stores].[Name].&[Video Tepa] - ([Stores].[Name].&[Video Tepa], [Time].PrevMember)) / ([Stores].[Name].&[Video Tepa], [Time].PrevMember)), 0))
Can this be done or am I violating some feature of how an OLAP cube or CR 10 works? If this can be done, does it make sense to do this at the Calculated Member Level of CR 10 or at Cube level of Analysis Server? I'm fairly new to all of this. Thanks in advance for your help.
T
I'm using CR Dev. 10 with MS SQL Server 2000 & Analysis Server to generate an OLAP Sales Report. I have 3 levels of Stores (Level 0 is All, Level 1 is Chains, Level 3 is Individual Stores). I have 5 levels for Time (Year,Q,Month,Week,Day). In it's current state, the cube is working, the reports work and everything functions as intended.
I have been able to use the Calculated Member wizard to create a Growth Calculated Member for individual stores or chains, however this wizard needs to be recreated everytime I change Dimension settings in Cube View.
What I would like to create is a generalized Calculated Member showing Growth that will function regardless of the dimension settings. So, e.g., if in Cube View I select All Stores and Quarterly Sales I see a table of total Quarterly Sales with a % number underneath that shows Quarter to Quarter increases/decreases in sales.
Q1 Q2 Q3 Q4
All Stores $100 $200 $50 $25
Growth All - 100% (75%) (50%)
If I select a 5 different Individual Stores and 1 Chain showing Quarterly & Weekly Sales I would see the same type of report with the different time periods and growth calculations for each individual store and one for the Chain. The Growth calculations would compare Q2 to Q1, Q3 to Q2, Q4 to Q3, and Week 2 to Week 1, Week 3 to Week 2, etc., within the OLAP cube reporting structure.
This is the formula that is currently working when I select a single Chain called Video Tepa:
IIf( [Stores].[Name].&[Video Tepa] = NULL, NULL, IIf( Count( { ([Stores].[Name].&[Video Tepa], [Time].PrevMember) } ) > 0, 100 * (([Stores].[Name].&[Video Tepa] - ([Stores].[Name].&[Video Tepa], [Time].PrevMember)) / ([Stores].[Name].&[Video Tepa], [Time].PrevMember)), 0))
Can this be done or am I violating some feature of how an OLAP cube or CR 10 works? If this can be done, does it make sense to do this at the Calculated Member Level of CR 10 or at Cube level of Analysis Server? I'm fairly new to all of this. Thanks in advance for your help.
T