locked
Can I GENERATE a Customer subset based on Time and Status? RRS feed

  • Question

  • I have calculated member, [Measures].[Confirmed Amt], that is aggregates confirmed contract amounts of over Time by Customer .  The subset of customers that have these contracts is fairly small.  For a report, I would like to query the value for only the Customer subset over multiple years.  I thought I could use GENERATE to create the Customer subset but have failed more than once.  My thought was along the following:

    WITH
     SET [Customer subset] AS
      GENERATE( [Customer].[Customer].MEMBERS
       , {
        { [Time].[Calendar Year].CURRENTMEMBER }
        * { [Contract].[Status].[Confirmed] }
        * { [Measures].[Budget Sales] }
       }
      )
    SELECT
     { [Measures].[Confirmed Amt] }
     ON COLUMNS,
     {
      { [Time].[Calendar Year].[2005]:[Time].[Calendar Year].[2015] }
      * [Customer subset]
     }
     ON ROWS
    FROM [MyCube]

    Any guidance would be greatly appreciated.

    Monday, March 29, 2010 8:43 PM

Answers

  • Since the subset of customers varies with year, you could use the year range as the 1st parameter of Generate() instead, like:

    WITH
     SET [Customer subset] AS
      GENERATE({ [Time].[Calendar Year].[2005]:[Time].[Calendar Year].[2015] }

    , { [Time].[Calendar Year].CURRENTMEMBER } 
      * NonEmpty([Customer].[Customer].[Customer].MEMBERS
       , { ([Contract].[Status].[Confirmed] 
            , [Measures].[Budget Sales]) } )
      )


    SELECT
     { [Measures].[Confirmed Amt] }
     ON COLUMNS, 
    [Customer subset]
     ON ROWS
    FROM [MyCube]


    - Deepak
    • Marked as answer by G Brakel Friday, April 2, 2010 5:46 PM
    Monday, March 29, 2010 9:48 PM

All replies

  • Since the subset of customers varies with year, you could use the year range as the 1st parameter of Generate() instead, like:

    WITH
     SET [Customer subset] AS
      GENERATE({ [Time].[Calendar Year].[2005]:[Time].[Calendar Year].[2015] }

    , { [Time].[Calendar Year].CURRENTMEMBER } 
      * NonEmpty([Customer].[Customer].[Customer].MEMBERS
       , { ([Contract].[Status].[Confirmed] 
            , [Measures].[Budget Sales]) } )
      )


    SELECT
     { [Measures].[Confirmed Amt] }
     ON COLUMNS, 
    [Customer subset]
     ON ROWS
    FROM [MyCube]


    - Deepak
    • Marked as answer by G Brakel Friday, April 2, 2010 5:46 PM
    Monday, March 29, 2010 9:48 PM
  • Thanks, Deepak. 

     I tried to extend your suggestion and still don't understand well enough.  Should have been more specific to begin with.  I also have a Filing Year role-playing dimension to contend with.  Customers vary over years within each filing year.  Ultimately, I need to have a query variable for the Time:Time.LEAD(10) and another for Filing Year.

     WITH
     SET [Customer subset] AS
      GENERATE( { [Time].[Calendar Year].[2005]:[Time].[Calendar Year].[2015] }
       , { [Time].[Calendar Year].CURRENTMEMBER }
        * NonEmpty(
         [Customer].[Customer].[Customer].MEMBERS
         , {
          (
           [Filing Year].[Calendar Year].&[2009]
           , [Contract Status].[Status].[Confirmed]
           , [Measures].[Budget Sales]
          )
         }
        )
      )
     MEMBER [Customer Code] AS
      [Customer].[Customer].CURRENTMEMBER.PROPERTIES("Customer Code")

    SELECT
     {
      [Measures].[Budget Sales]
            , [Customer Code]
     }
     ON COLUMNS,
     [Customer subset]
     DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
     ON ROWS
    FROM [Budget]
    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    • Proposed as answer by Jerry Nee Friday, April 2, 2010 7:07 AM
    Wednesday, March 31, 2010 2:48 PM