locked
Grand Total Field in Analytic Grid Report RRS feed

  • Question

  •  

    Hi

     

    I am asked to get a Grand total field in an Analytic grid report..

     

    Can this be done?

     Any Idea?

     

    Please help !!!

    Wednesday, June 11, 2008 2:44 PM

Answers

  • Here is a simple subtotal sample:

    WITH MEMBER [Customer].[Customer Geography].[All Customers].[ Subtotal] AS ' AGGREGATE( EXISTING { [Customer].[Customer Geography].[All Customers].CHILDREN }) ', SOLVE_ORDER = 1000

    SELECT { [Date].[Calendar].[All Periods].CHILDREN } ON COLUMNS ,

    { { [Customer].[Customer Geography].[All Customers].CHILDREN }, { [Customer].[Customer Geography].[All Customers].[ Subtotal] } } ON ROWS

    FROM [Adventure Works]

    WHERE ( [Measures].[Internet Sales Amount] )

     

     

    Obviously it can get much more complicated quite quickly. I would recommend you download a tool like ProClarity or another tool that allows you to see the MDX generated to get your query built and then strip the MDX from there.

    Hope that helps.

    Thanks,

    Alyson

    Monday, June 16, 2008 5:54 PM

All replies

  • This can only  be accomplished by writing custom MDX for the analytic grid/chart.

    Or, alternatively, you can use an Excel Services or SSRS report.

    sorry,

    Alyson

     

     

    Friday, June 13, 2008 4:05 PM
  • Thanks Alyson!!!

    I am new to MDX.

     Can i get any help in writing the query for grand total field???

     

    Regards,

    Monday, June 16, 2008 7:45 AM
  • Here is a simple subtotal sample:

    WITH MEMBER [Customer].[Customer Geography].[All Customers].[ Subtotal] AS ' AGGREGATE( EXISTING { [Customer].[Customer Geography].[All Customers].CHILDREN }) ', SOLVE_ORDER = 1000

    SELECT { [Date].[Calendar].[All Periods].CHILDREN } ON COLUMNS ,

    { { [Customer].[Customer Geography].[All Customers].CHILDREN }, { [Customer].[Customer Geography].[All Customers].[ Subtotal] } } ON ROWS

    FROM [Adventure Works]

    WHERE ( [Measures].[Internet Sales Amount] )

     

     

    Obviously it can get much more complicated quite quickly. I would recommend you download a tool like ProClarity or another tool that allows you to see the MDX generated to get your query built and then strip the MDX from there.

    Hope that helps.

    Thanks,

    Alyson

    Monday, June 16, 2008 5:54 PM
  • Hi Alyson !!!

    Need help again..

    I have to create a Business Rule for my Grand Total field, a dynamic one.

     

    I could create one where i gave my members,which all needed to be aggregated.

    But, I need to get one,which should be dynammic.In the sense, when i select some things on the rows,These shoule be aggregated and give me the total value.

    I'll be selecting various ,whichervr i wanted as per the requirement.

     

    Can this be done? Please help !!!!

     

     

    Wednesday, June 25, 2008 12:16 PM
  • This is much more challenging - PPS does not include native totaling functionality and I know of no way to do this using MDX without being able to take multiple passes on the MDX expression which can't be done in a single statement.  If you are willing to integrate ProClarity views into your dashboards, the ProClarity product has the ability to execute grand totals in this manner and you can include those views in your dashboards.

    Thanks,

    Alyson

     

     

    Wednesday, June 25, 2008 3:01 PM
  •  

    Hi again Chaitanya,

     

    a new puzzle for me?

     

    You could make a query like this. Put customers, first level, as Alyson did, on rows. Put that measure from filter on columns. No need to complicate with dates for now. OK? So, it's a simple resultset.

     

    Now, two things can be done. First one is, you can add another columns with calculated measure that displays the same value in all rows and that would be sum of row members, whatever they are (no set predefined). But, this sum is in wrong position. So we need it down, below the last element.

    Well, since on rows we have customers, we cannot put calc measure 'cause measures are on columns. What we need on rows is one extra calc member in customer dimension. Just like Alyson did. After that, we need to define a new calc measure, the one that will replace the original one (sales amount). That can also be the only measure in columns in case two.

     

    Behaviour of that calc measure is this: if you come across regular member, leave sales amount value, but if you hit a predefined one (exactly that one), do some magic :-).

     

    Now, let's put thoughts in practice:

     

    Code Snippet

    WITH

     

    MEMBER

    [Customer].[Customer Geography].[All Customers].[Grand Total]

    AS

    '

    [Customer].[Customer Geography].[All Customers] -- dummy, so why not a root

    '

    , SOLVE_ORDER = 10

     

    MEMBER

    [Measures].[Magic measure] AS

    '

    iif

    ( [Customer].[Customer Geography].CurrentMember Is

           [Customer].[Customer Geography].[All Customers].[Grand Total],

     

      Sum( Except( Axis(1), {Axis(1).Item(Axis(1).Count - 1) },

           [Measures].[Internet Sales Amount]

         ), -- that's magic on action

     

      [Measures].[Internet Sales Amount] -- regular behaviour

    )

    '

    , FORMAT_STRING = '#,##0.00', SOLVE_ORDER = 0 -- must be lower than Grand Total's!!!

     

    SELECT

    { [Measures].[Internet Sales Amount],

      [Measures].[Magic measure] } ON COLUMNS ,

    {

      { [Customer].[Customer Geography].[All Customers].CHILDREN },

      { [Customer].[Customer Geography].[All Customers].[Grand Total] }

    } ON ROWS

    FROM

    [Adventure Works]

     

     

    It should work, I even tested it this time (I usually write MDX in these posts straight from head). If you put something else instead of CHILDREN, it will calculate Grand Total of that. Try it!

     

    Now, two things should be noticed. First, formula is set up to expect that the last row is that total element. Watch for its name, it compares by that. If it isn't there it won't do magic or worse, if it is there but on some other place, it will calculate wrong so be careful. The other thing is - you can remove original measure from this query since I put it for you to see that it works.

     

    Now, these kind of things we have on right-click in our application (CubePlayer OLAP client). Ok, not exactly this one, but similar ones. We have percantage of elements in column 2 / column 1 no matter dimensions or stuff like that. We generate sum easily because everything is under control once in grid. But, maybe we'll put things like this also, so that a query (MDX) we generate can be easily executed in other systems. Take a look at CubePlayer if I interested you.

     

    And if I solved your puzzle, please mark as answered/helpful/solved because this is a general approach to your grand total problem, that doesn't require predefined sets. Thanx!

     

    PS: You know, one day soon I'll figure out the other problem you had (http://forums.microsoft.com/technet/showpost.aspx?postid=3569137&siteid=17&mode=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0), also using general approach, and then I'll let you know.

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Thursday, July 3, 2008 3:44 PM
  •  

    Thankss so much Tomislav....

    It dint worked for me..

    Actuallly , wht i need is a Business rule(Total), which aggregates the dimension members i give on rows.And I need the same Business Rule to be at the bottom of my rows(displaying the total)

    Dim1 nd Dim2 are dimension members(say..level dimension)

    T1to T5 are Time dimension memers.

     

     

    Dim1

    Dim2

    T1

    X1

    Y1

    T2

    X2

    Y2

    T3

    X3

    Y3

    T4

    X4

    Y4

    Total

    X5(x1+x2+x3+x4)

    Y5(y1+y2+y3+y4)

     

    WITH MEMBER [Time].[Year].[Total] AS

     

    'AGGREGATE( EXISTING {Axis(1) })', 

     

    SELECTDim1,Dim2} ON 0 ,

     {  {T1,T2,T3,T4,T5   }, {[Time].[Year].[Total]} } ON 1

     

    FROM [CUBE]

     

     

    With this query,I am getting the proper result..But,I have a doubt.

    When i dragged nd dropped Dimension members and measures on to the rows in dashboard designer,it takes the crossjoin,vch i dont want.

    Here, for time being..i have created a query sessioned calculated member(using, WITH MEMBER).But later..i'll create a business rule (using CREATE MEMBER) and  deploy,vth vch,i'll be able to drag and drop.

     

    Regards,

    Friday, July 4, 2008 10:10 AM
  •  

    Hm, now you tell me ...

     

    Ok, my MDX works for one dimension on rows. If you put that calc measure in filter or in columns (crossjoined with other dimension), it should also work. Now, it won't work if you put measures on rows or another dimension on rows. Can you avoid that and make crossjoins on columns only?

     

    If not, then, there is a possibility to adjust my MDX to work even for multiple dimensions on rows. But please make an example, visually, just like you did now for this T-D combination.

     

    Regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Friday, July 4, 2008 10:34 AM
  • Thankss,

     

    Cant we put measures nd dimensions on rows??? Wont it work??

    I am asked to do that...

    cant this be done??

     

    Regards,

    Tuesday, July 8, 2008 7:22 AM
  •  

    Normally, you can put anything anywhere as long as the same attributes are only on one axis. Measures can go to rows along with some other dimensions, but sometimes, a calculated members (measures) need adjustments.

     

    Like in your case where you have that Aggregate of Axis(1). I think you should Extract dimensions other than Measures and then do aggregate on them. Take a look at Extract() function and try it.

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr


     

    Tuesday, July 8, 2008 1:37 PM