Year to Month Report. How do I generate appropriate month based on a Year?
Need to generate a report for a Year to Month view of a certain KPI.
I want to use the Year as a parameter.
Right now I have the Periods hard coded. For example, the [Period].[Period]..[Period].&[326] represents 2010-01 period.
How would I code the Period that will change according to which year I am reporting for?
Appreciate your input.
Thanks,
WITH
MEMBER [Measures].[Jan] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period]..[Period].&[326],[Measures].[WLS Value])
MEMBER [Measures].[Feb] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[326], [Measures].[WLS Value])
MEMBER [Measures].[Mar] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[327], [Measures].[WLS Value])
MEMBER [Measures].[Q1] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Quarter].[Qtr].&[201001], [Measures].[WLS Value])
MEMBER [Measures].[Apr] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[328], [Measures].[WLS Value])
MEMBER [Measures].[May] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[329], [Measures].[WLS Value])
MEMBER [Measures].[Jun] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[330], [Measures].[WLS Value])
MEMBER [Measures].[Q2] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Quarter].[Qtr].&[201002], [Measures].[WLS Value])
MEMBER [Measures].[Jul] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[331], [Measures].[WLS Value])
MEMBER [Measures].[Aug] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[332], [Measures].[WLS Value])
MEMBER [Measures].[Sep] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[333], [Measures].[WLS Value])
MEMBER [Measures].[Q3] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Quarter].[Qtr].&[201003], [Measures].[WLS Value])
MEMBER [Measures].[Oct] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[334], [Measures].[WLS Value])
MEMBER [Measures].[Nov] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[335], [Measures].[WLS Value])
MEMBER [Measures].[Dec] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Period].[Period].&[336], [Measures].[WLS Value])
MEMBER [Measures].[Q4] AS ([Value Type].[Value].[Value Family].&[KPI].[CHURN], [Period].[Quarter].[Qtr].&[201004], [Measures].[WLS Value])
MEMBER [Province].[Province].[BC] AS ([Province].[Province].[Province Cd].&[BC])
MEMBER [Province].[Province].[AB] AS ([Province].[Province].[Province Cd].&[AB])
MEMBER [Province].[Province].[SK] AS ([Province].[Province].[Province Cd].&[SK])
MEMBER [Province].[Province].[MB] AS ([Province].[Province].[Province Cd].&[MB])
MEMBER [Province].[Province].[ON] AS ([Province].[Province].[Province Cd].&[ON])
MEMBER [Province].[Province].[PQ] AS ([Province].[Province].[Province Cd].&[QC])
MEMBER [Province].[Province].[NS] AS ([Province].[Province].[Province Cd].&[NS])
MEMBER [Province].[Province].[NB] AS ([Province].[Province].[Province Cd].&[NB])
MEMBER [Province].[Province].[NF] AS ([Province].[Province].[Province Cd].&[NF])
MEMBER [Province].[Province].[PEI] AS ([Province].[Province].[Province Cd].&[PE])
MEMBER [Province].[Province].[ALL2] AS ([Province].[Province].[Province Cd].&[ALL])
SELECT {[Period].[Period].[Year].&[2010].CHILDREN} ON COLUMNS,
{[Province].[Province].[BC],
[Province].[Province].[AB],
[Province].[Province].[SK],
[Province].[Province].[MB],
[Province].[Province].[ON],
[Province].[Province].[PQ],
[Province].[Province].[NS],
[Province].[Province].[NB],
[Province].[Province].[NF],
[Province].[Province].[PEI],
[Province].[Province].[ALL2]} ON ROWS
FROM [Summary]
December 17th, 2010 2:32pm
Hi,
In a cube, "[Period].[Period]..[Period].&[326] represents 2010-01 period", the 326 should be the key value and 2010-01 is name value. So, you can write a MDX to retrieve its key and name values, the key can be used in the query and the name values
can be shown on the report.
thanks,
Jerry
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 3:05am
Thank you!
January 10th, 2011 2:37pm