none
Previous Month From Previous Year

    Question

  • I have a report that needs to return results from a chosen months and the results from te prior two months. I have things working fine if the months are in the same year. An example:

    I want to get the hour that certain employees in a certain department have worked in a given month for a certain client. Along with that month I choose, I also want to provide the results from the prior two months for comparison. The report itself takes three parameters: calendar year (in the form of Calendar Year 2012, Calendar Year 2011, etc.), selected month (January 2012, December 2011, etc.), and department (Accounting, Finance, etc.).

    The values for selected month that are available are determined by what is chosen for Calendar Year. So if I choose Calendar Year 2012, the list of available values populated in the parameter for Selected Month would be January 2012 through December 2012.

    For the report, if I choose Calendar Year 2013 and choose June 2013 as the month, I get results for June, May, and April - which is fine. See the attached screen shot:

    But let's say I choose January 2013, I'm only shown January 2013 and not December 2012 and November 2012.

    Part of this is I know because I'm choosing the calendar year and then constraining to it. Here's the actual MDX:

    SELECT 
      NON EMPTY 
        {
          [Measures].[Employee Hours],
          [Measures].[Client % of Employee]
         } ON COLUMNS
     ,NON EMPTY 
        {
            [Department].[Department].[Department].ALLMEMBERS*
             EXCEPT([Project].[Client].[Client].ALLMEMBERS, {([Project].[Client].[])})*
            [Employee].[Employee Full Name].[Employee Full Name].ALLMEMBERS*
            [Time].[Year - Month].[Month].ALLMEMBERS
        }
      DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM 
    (
      SELECT 
       {StrToMember(@SelectedMonth,CONSTRAINED).Lag(2) : StrToMember(@SelectedMonth,CONSTRAINED) } ON COLUMNS
        FROM 
    
     (
        SELECT 
          StrToSet
          (@CalendarYear
           ,CONSTRAINED
          ) ON COLUMNS
    FROM
    
        (
          SELECT 
            StrToSet
            (@Departments
             ,CONSTRAINED
            ) ON COLUMNS
          FROM [OLSON BI]
        )
      )
    )
    
    CELL PROPERTIES 
      VALUE
     ,BACK_COLOR
     ,FORE_COLOR
     ,FORMATTED_VALUE
     ,FORMAT_STRING
     ,FONT_NAME
     ,FONT_SIZE
     ,FONT_FLAGS;

    I'm thinking to accomplish what I need, I need to forego choosing the calendar year and just choose the Selected Month, but not sure how to rework the MDX to accomplish that.

    Any help or insight would be greatly appreciated!


    A. M. Robinson


    • Edited by ansonee Tuesday, January 28, 2014 4:11 PM pictures
    Tuesday, January 28, 2014 4:01 PM

Answers

  • I was actually able to solve my own problem...

    Below is the final MDX I came up with:

    SELECT 
      NON EMPTY 
        {
           [Measures].[Employee Hours],
    [Measures].[Employee % of Client]
    } ON COLUMNS
     ,NON EMPTY 
        {
            [Department].[Department].[Department].ALLMEMBERS*
             EXCEPT([Project].[Client].[Client].ALLMEMBERS, {([Project].[Client].[])})*
            [Employee].[Employee Full Name].[Employee Full Name].ALLMEMBERS*
            [Time].[Year - Month].[Month].ALLMEMBERS
        }
      DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM 
    (
      SELECT 
       {StrToMember(@SelectedMonth,CONSTRAINED).Lag(2) : StrToMember(@SelectedMonth,CONSTRAINED) } ON COLUMNS
       FROM 
    
    -- (
    --    SELECT 
    --    StrToSet
    --    (@CalendarYear
    --    ,CONSTRAINED
    --    ) ON COLUMNS
    --  FROM
    
        (
          SELECT 
            StrToMember
            (@Departments
            -- ,CONSTRAINED
            ) ON COLUMNS
          FROM [OLSON BI]
        )
      )
    --)
    
    CELL PROPERTIES 
      VALUE
     ,BACK_COLOR
     ,FORE_COLOR
     ,FORMATTED_VALUE
     ,FORMAT_STRING
     ,FONT_NAME
     ,FONT_SIZE
     ,FONT_FLAGS;

    The CONSTRAINED error was due to the fact that in the query, the Departments parameter was set up to allow multiple values but had the CONSTRAINED key word. Was also using the Calendar Year.

    Everything is good!!


    A. M. Robinson

    • Marked as answer by ansonee Wednesday, January 29, 2014 3:21 AM
    Wednesday, January 29, 2014 3:21 AM

All replies

  • What is also odd is that when I parse the above MDX, it fails with a CONSTRAINED error, but yet this report runs fine from the report server and runs fine in Visual Studio. For some reason I'm unable to "compile" the existing MDX...

    Not sure how the exact same MDX works fine in the report, but fails preparation in query builder??


    A. M. Robinson

    Tuesday, January 28, 2014 4:54 PM
  • I was actually able to solve my own problem...

    Below is the final MDX I came up with:

    SELECT 
      NON EMPTY 
        {
           [Measures].[Employee Hours],
    [Measures].[Employee % of Client]
    } ON COLUMNS
     ,NON EMPTY 
        {
            [Department].[Department].[Department].ALLMEMBERS*
             EXCEPT([Project].[Client].[Client].ALLMEMBERS, {([Project].[Client].[])})*
            [Employee].[Employee Full Name].[Employee Full Name].ALLMEMBERS*
            [Time].[Year - Month].[Month].ALLMEMBERS
        }
      DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM 
    (
      SELECT 
       {StrToMember(@SelectedMonth,CONSTRAINED).Lag(2) : StrToMember(@SelectedMonth,CONSTRAINED) } ON COLUMNS
       FROM 
    
    -- (
    --    SELECT 
    --    StrToSet
    --    (@CalendarYear
    --    ,CONSTRAINED
    --    ) ON COLUMNS
    --  FROM
    
        (
          SELECT 
            StrToMember
            (@Departments
            -- ,CONSTRAINED
            ) ON COLUMNS
          FROM [OLSON BI]
        )
      )
    --)
    
    CELL PROPERTIES 
      VALUE
     ,BACK_COLOR
     ,FORE_COLOR
     ,FORMATTED_VALUE
     ,FORMAT_STRING
     ,FONT_NAME
     ,FONT_SIZE
     ,FONT_FLAGS;

    The CONSTRAINED error was due to the fact that in the query, the Departments parameter was set up to allow multiple values but had the CONSTRAINED key word. Was also using the Calendar Year.

    Everything is good!!


    A. M. Robinson

    • Marked as answer by ansonee Wednesday, January 29, 2014 3:21 AM
    Wednesday, January 29, 2014 3:21 AM
  • Hi Robision,

    It seems that your issue had been solved, thank you for your useful information, it will help for other forum members who have the similar issue.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Wednesday, January 29, 2014 6:08 AM