locked
Grand Total Issue RRS feed

  • Question

  •  

    Hi There,

    I have an issue with proclarity now.

    I created the member (40-44) from the department number, and when I add grand total some measrues (in this case, End Inventory Unit) do not return a value. (FYI, Member set works fine with grand total)

     

    Below is what I set up the view:

     

    ROW                   : SEASON GROUP  (only select SEASON 1 , 2 and 3)

                                 DEPARTMENT ( 40,41,42,43) - Using new member

                                 (These two attributes are in product dimension)

     

    COLUMN              :Measures NET SALE U / End inventory Unit ( Semi additive)

     

    BACKGROUND     : Date Hierarchy ( 2007- W 44)

     

    Below is the MDX :

     

    Code Block

     

    WITH MEMBER [Product].[Season].[All].[Grand Total] AS 'AGGREGATE(EXISTING INTERSECT( { { EXTRACT( { { { [Product].[Season].&[SEASON 3], [Product].[Season].&[SEASON 4], [Product].[Season].&[SEASON R (RESORT)] } * { [<##<MEMBER!!40-44>##>] } } }, [Product].[Season] ) } * { EXTRACT( { { { [Product].[Season].&[SEASON 3], [Product].[Season].&[SEASON 4], [Product.[Season].&[SEASON R (RESORT)] } * { [<##<MEMBER!40-44>##>] } } },

    [Product].[Department] ) } }, { { { [Product].[Season].&[SEASON 3], [Product].[Season].&[SEASON 4], [Product].[Season].&[SEASON R (RESORT)] } * { [<##<MEMBER!40-44>##>] } } } ) )', SOLVE_ORDER = 1000 MEMBER [Product].[Department].[All].[ Grand Total] AS 'AGGREGATE({ [Product].[Department].DEFAULTMEMBER } )' , SOLVE_ORDER = 1000

     

    SELECT NON EMPTY { [Measures].[Net Sale U], [Measures].[EOP U] } ON COLUMNS ,

    NON EMPTY { { { { [Product].[Season].&[SEASON 3], [Product].[Season].&[SEASON 4], [Product].[Season].&[SEASON R (RESORT)] } * { [<##<MEMBER!40-44>##>] } } }, ( [Product].[Season].[All].[ Grand Total], [Product].[Department].[All].[ Grand Total] ) } ON ROWS

    FROM [CUBE]

    WHERE ( [Date].[hierarchy].[YEAR].&[2007].&[Q4].&[OCT].&[W 44] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

     

    I don't understand why grand total didn't work properly with some spcific measure now.

    Please advise me and any kind of advice would be greatly appreciated.

    Thank you.

     

     

    Thursday, January 17, 2008 6:10 PM

Answers

  • Hi! This issue is interesting and I think that Deepak Puri's answere to this in the SSAS forum might help. Thank's for the comment about ProClarity's design.

     

    /Thomas Ivarsson

     

    "Hi Thomas,

    Just curious as to the actual MDX (generated by Proclarity), which works in AS 2000 for this scenario. For example, replacing Aggregate() with Sum() in [Date].[Calendar].[All Periods].[ Grand Total] would return the results which you expect - but then the Grand Total would not be correct for measures with other than Sum or Count aggregation functions. So, if there's an option in Proclarity to select Sum() rather than Aggregate() for Grand Totals, you could choose that.

    From Mosha's blog: "Aggregate function can also work when the current measure is calculated measure, by switching solve orders with it". So, in the sample query,  [MEASURES].[ParallelPeriodSalesAggregate] and [MEASURES].[ParallelPeriodSalesSum] will be computed after the Grand Total aggregate - at which point, [Date].[Calendar].CurrentMember is now [Date].[Calendar].[All Periods].[ Grand Total], so applying ParallelPeriod() won't produce the desired results. One way for Aggregate() to work as you want in this case is to create a ParallelPeriod() cell calculation (in the MDX script or query) on another measure in the same measure group, with similar aggregation. Here's a modified version of the original query to illustrate this. The results also show that the ParallelPeriod() value may not be null when [Measures].[Internet Sales Amount] is (see Aug. 2004) - which is why Mosha mentioned that NON_EMPTY_BEHAVIOR should not be defined as {[Measures].[Internet Sales Amount]}.

    >>

    Code Snippet

    With Cell Calculation [ParallelPeriodSalesAggregate]

    for '({[Measures].[Internet Order Quantity]}, [Date].[Calendar].[Month].Members)' AS

    (ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),

    [Measures].[Internet Sales Amount]),

    FORMAT_STRING = 'Currency'

    MEMBER [MEASURES].[ParallelPeriodSalesSum]

    AS [Measures].[Internet Order Quantity],

    FORMAT_STRING = 'Currency'

    MEMBER [Date].[Calendar].[All Periods].[ Grand Total] AS

    'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS(

    [Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Month] ),

    DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) },

    [Date].[Calendar] ) } }, { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2003],

    [Date].[Calendar].[Month] ), DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004],

    [Date].[Calendar].[Month] ) } ) )', SOLVE_ORDER = 1000

    SELECT { [Measures].[Internet Sales Amount],

    [Measures].[ParallelPeriodSalesSum] } ON COLUMNS ,

    { { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2003],

    [Date].[Calendar].[Month] ), DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004],

    [Date].[Calendar].[Month] ) }, ( [Date].[Calendar].[All Periods].[ Grand Total] ) } ON ROWS

    FROM [Adventure Works]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

     

     

    "

    Sunday, January 27, 2008 12:46 PM

All replies

  • Can you provide some information on the measures that you are using in the view?  Are they different formats? Also, are you receiving an error message or do the totals simply not appear?  Can you try replacing the AGGREGATE function with the SUM function to see if the query then completes successfully?

     

    Thanks,

     

    Bob

     

    Friday, January 18, 2008 9:24 PM
  •  

    Hi,

     

    • Measures Info

             -Net Sale Unit : Aggregate Function (SUM)

             -End Inventory Unit : Aggregate Function (Last Non Empty)

             -Two more calculated measures

    • I do not receive any error message whiling viewing grand totals, however the totals do not appear with only end inventory Unit.
    • If I change aggregate to sum in script, then I can get the total of net sale unit and end inventory unit however all totals of all calculated measures are wrong. (it's simply adding all of member value not calculated)

    Any idea or thoughts?

    Please let me know since we have so many troubles with sub/ grand totals now.

     

    Thank you.

     

     

    Friday, January 18, 2008 10:00 PM
  • I have requested that a number of my peers take a look at your post because I don't want to mispeak, but I was able to find this post on the Live blogs that may provide some additional information for you surrounding the issue of using grand totals in ProClarity with calculated measures.  While we wait for some of the more experienced engineers to weigh in, please take a look and see if it helps you at all:

     

    http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!387.entry

     

    Thanks,

     

    Bob

     

    Friday, January 18, 2008 11:25 PM
  • The blog Bob references is very informational, espcially the comment by Mosha at the bottom that explains some of the caveats of the aggregate function in SSAS.  In the ProClarity tool, the data is not "owned" by the grid, by which I mean the grid has no real concept of a numerical value in a cell, as Excel would for instance.  ProClarity relies on the cube for all of the data is displays.  This was a fundamental architectural decision that was made in the ProClarity software.  What this means for totals is that the grid cannot simply look at the values for each cell in a column and provide a number.  It must instead build a query that will do that.  As you have pointed out, this query cannot use the sum function because this will give incorrect data for measures that are averages, ratios, etc.  So, aggregate must be used.  However, as Mosha points out, there are some caveats to using aggregate with calculated members, as you yourself have experienced.

     

    You may be able to get around these problems by modifying the solve order in your query or implementing the "scope_isolation = cube" property in the query, but of course if you are able to get this modified query to work, any navigation in ProClarity will remove your custom MDX and replace it with the generated MDX once again.  This may not be satisfactory for you.

     

    Something else you might consider is having a look at Excel 2007 for some of your solution needs.  Excel 2007 can be more straightforward in situations where the requirements begin to move closer to reporting than analysis.  Often times it is necessary to augment ProClarity with a tool that is more adept at reporting operations, such as Excel or Reporting Services simply because ProClarity was designed to be a world class analysis tool, and therefore is not as efficient when it comes to reporting operations, as would be a tool that were designed for that purpose.

     

    Saturday, January 26, 2008 12:52 AM
  • Hi! This issue is interesting and I think that Deepak Puri's answere to this in the SSAS forum might help. Thank's for the comment about ProClarity's design.

     

    /Thomas Ivarsson

     

    "Hi Thomas,

    Just curious as to the actual MDX (generated by Proclarity), which works in AS 2000 for this scenario. For example, replacing Aggregate() with Sum() in [Date].[Calendar].[All Periods].[ Grand Total] would return the results which you expect - but then the Grand Total would not be correct for measures with other than Sum or Count aggregation functions. So, if there's an option in Proclarity to select Sum() rather than Aggregate() for Grand Totals, you could choose that.

    From Mosha's blog: "Aggregate function can also work when the current measure is calculated measure, by switching solve orders with it". So, in the sample query,  [MEASURES].[ParallelPeriodSalesAggregate] and [MEASURES].[ParallelPeriodSalesSum] will be computed after the Grand Total aggregate - at which point, [Date].[Calendar].CurrentMember is now [Date].[Calendar].[All Periods].[ Grand Total], so applying ParallelPeriod() won't produce the desired results. One way for Aggregate() to work as you want in this case is to create a ParallelPeriod() cell calculation (in the MDX script or query) on another measure in the same measure group, with similar aggregation. Here's a modified version of the original query to illustrate this. The results also show that the ParallelPeriod() value may not be null when [Measures].[Internet Sales Amount] is (see Aug. 2004) - which is why Mosha mentioned that NON_EMPTY_BEHAVIOR should not be defined as {[Measures].[Internet Sales Amount]}.

    >>

    Code Snippet

    With Cell Calculation [ParallelPeriodSalesAggregate]

    for '({[Measures].[Internet Order Quantity]}, [Date].[Calendar].[Month].Members)' AS

    (ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),

    [Measures].[Internet Sales Amount]),

    FORMAT_STRING = 'Currency'

    MEMBER [MEASURES].[ParallelPeriodSalesSum]

    AS [Measures].[Internet Order Quantity],

    FORMAT_STRING = 'Currency'

    MEMBER [Date].[Calendar].[All Periods].[ Grand Total] AS

    'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS(

    [Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Month] ),

    DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) },

    [Date].[Calendar] ) } }, { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2003],

    [Date].[Calendar].[Month] ), DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004],

    [Date].[Calendar].[Month] ) } ) )', SOLVE_ORDER = 1000

    SELECT { [Measures].[Internet Sales Amount],

    [Measures].[ParallelPeriodSalesSum] } ON COLUMNS ,

    { { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2003],

    [Date].[Calendar].[Month] ), DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004],

    [Date].[Calendar].[Month] ) }, ( [Date].[Calendar].[All Periods].[ Grand Total] ) } ON ROWS

    FROM [Adventure Works]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

     

     

    "

    Sunday, January 27, 2008 12:46 PM