MDX pull data dynamically for last 4 months

• PSB

  SSChampion

  Points: 13195

  More actions

  September 4, 2014 at 8:07 am

  #145222

  Hi,

  I have a MDX query which I use to import data into staging. I want this query to dynamically pull data for the last 4 months including current month. In the query below, the months are Jan,Feb,Mar,April and May . I want to pull data from June,July,Aug and September as September is the current month. When it will be October, I want to pull data from July,Aug ,September and October.

  --QUERY BELOW

  SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY { ([Company].[Company].[Company].ALLMEMBERS * [Library Job].[Library Job].[Library Job].ALLMEMBERS * [Library Job].[Library Name].[Library Name].ALLMEMBERS * [Business Unit].[Business Unit].[Business Unit].ALLMEMBERS * [Business Unit].[Business Unit Combined].[Business Unit Combined].ALLMEMBERS * [GL Account].[GL Account].[GL Account].ALLMEMBERS * [GL Account - New].[FNP Line Item No].[FNP Line Item No].ALLMEMBERS * [GL Account - New].[FNP Line Item].[FNP Line Item].ALLMEMBERS * [GL Account - New].[AvB Line Item No].[AvB Line Item No].ALLMEMBERS * [GL Account - New].[AvB Line Item].[AvB Line Item].ALLMEMBERS * [Cost Center].[Cost Center].[Cost Center].ALLMEMBERS * [Cost Center].[Cost Center Combined].[Cost Center Combined].ALLMEMBERS * [Posting Date].[Date YQMD].[Day].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Business Unit].[Business Unit].&[1100], [Business Unit].[Business Unit].&[1200] } ) ON COLUMNS FROM ( SELECT ( { [Business Unit].[Business Unit Combined].&[1100], [Business Unit].[Business Unit Combined].&[1200] } ) ON COLUMNS FROM ( SELECT ( { [Posting Date].[Year Month].&[2.01401E5], [Posting Date].[Year Month].&[2.01402E5], [Posting Date].[Year Month].&[2.01403E5], [Posting Date].[Year Month].&[2.01404E5], [Posting Date].[Year Month].&[2.01405E5] } ) ON COLUMNS FROM ( SELECT ( { [Posting Date].[Date YQMD].[Year].&[2014] } ) ON COLUMNS FROM ( SELECT ( { [Company].[Company].&[TGS Brazil], [Company].[Company].&[TGS Nopec ASA], [Company].[Company].&[TGS NOPEC US] } ) ON COLUMNS FROM [Finance]))))) WHERE ( [Posting Date].[Year Month].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  Please help.

  Thanks,

  PSB