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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics