New post
Article
Cheng Cheng · Jul 11, 2016 3m read

Summary row with different aggregate function for each column

#InterSystems IRIS BI (DeepSee)

Today my customer ask me a question about how to write the MDX with a summary row however this row with different aggregate functions for each column.

We know in DeepSee analyzer has pivot table option "Summary",  user can select sum, avg ... aggregate functions to get the summary row/column. However we can not specify different aggregate function for each measure column.

Here show the example to use All level and IIF function achieve that. see the example (Holefood cube in Sample namespace) below

 

WITH MEMBER measures.[with avg summary info] AS

                              'IIF(Product.CurrentMember.Properties("LEVEL_NUMBER") = 1,

                                 AVG(Product.[Product Category].Members, measures.[amount sold]),

                                 SUM(Product.[Product Category].Members, measures.[amount sold]))'

               MEMBER measures.[total count] AS 'SUM(Product.[Product Category].Members)'

SELECT {Product.[Product Category].Members, [Product].[All Product]} ON 1,

               {measures.[with avg summary info],measures.[total count]} ON 0

FROM Holefoods

 

                  with avg summary i          total count

1 Candy                    149,187.22               83,833

2 Cereal                   513,770.68               83,852

3 Dairy                    518,826.37               56,481

4 Fruit                    744,168.60               85,819

5 Pasta                  1,940,939.62              273,971

6 Seafood                2,655,134.41               75,355

7 Snack                  3,111,815.51              267,996

8 Vegetable                884,450.97              110,844

9 All Products           1,314,786.67            1,038,151

============================================

 

Cheng Cheng

2
0 1,042
Discussion (6)1
Log in or sign up to continue
Evgeny Shvarov · Jul 11, 2016

HI, Cheng! 

Thanks, great example!

What is the "LEVEL_NUMBER"? Is it a kind of internal property?

0 0
Cheng Cheng · Jul 11, 2016

Hi Evgeny

level_number is one of intrinsic member properties compared with user defined properties like UnitPrice.  It is the distance of the member from the root of the hierarchy. The root level is zero.

I tried to find out some Cache defined MDX intrinsic member properties here (Maybe document team can give a full list)

with member measures.Parentlevel As '[Product].P1.[Product Name].CurrentMember.Parent.Properties("LEVEL_NUMBER")' member measures.ParentID As '[Product].P1.[Product Name].CurrentMember.Parent.Properties("ID")' member measures.ParentKey As '[Product].P1.[Product Name].CurrentMember.Parent.Properties("KEY")' member measures.Price As '[Product].P1.[Product Name].CurrentMember.Properties("UnitPrice")'  select [product].[product name].members on 1, {measures.parentlevel,measures.parentid, measures.Parentkey, measures.Price} on 0 from holefoods
 
                   Parentlevel       ParentID      ParentKey          Price
 1 Bagels (dozen)             2              4          Snack           2.95
 2 Bundt Cake                 2              4          Snack          19.95
 3 Calamari (frozen           2              6        Seafood          22.95
 4 Cheerios (box)             2              1         Cereal           3.95
 5 Donuts (dozen)             2              4          Snack           2.95
 6 Free-range Donut           2              4          Snack          12.95
 7 Fruit Loops (box           2              8          Fruit           4.95
 8 Lifesavers (roll           2              3          Candy           1.15
 9 Onion ring                 2              7      Vegetable           4.95
10 Onion ring                 2              7      Vegetable           5.95
11 Penne (box)                2              5          Pasta           1.95
12 Pineapple Rings            2              8          Fruit           8.95
13 Pretzels (bag)             2              4          Snack           3.95
14 Swiss Cheese (sl           2              2          Dairy           5.95
15 Tortellini (froz           2              5          Pasta           6.95
16 Unsalted Pretzel           2              4          Snack           4.25
17 Ziti (box)                 2              5          Pasta           1.95
---------------------------------------------------------------------------

1 0
Evgeny Shvarov · Jul 12, 2016

Hi, Cheng! 

Thank you for the explanation! Great use case, btw. Now it's more clear with your initial post.

It would be great if you include Level_number in the query too to see what rows has different Level_number.

Like this:

WITH MEMBER measures.[with avg summary info] AS

                              'IIF(Product.CurrentMember.Properties("LEVEL_NUMBER") = 1,

                                 AVG(Product.[Product Category].Members, measures.[amount sold]),

                                 SUM(Product.[Product Category].Members, measures.[amount sold]))'

               MEMBER measures.[total count] AS 'SUM(Product.[Product Category].Members)'

SELECT {Product.[Product Category].Members, [Product].[All Product]} ON 1,

               {Product.CurrentMember.Properties("LEVEL_NUMBER"),measures.[with avg summary info],measures.[total count]} ON 0

FROM Holefoods

I suppose 'All Product' level should have the Sum of all Product levels. But in your example 'All product' lesser than 'Snack' for example.

0 0
Evgeny Shvarov · Jul 12, 2016

Sorry, Cheng Cheng!

Got the idea now: you wanted Sum function for each Product Category, and Avg function for All Products in the same pivot)

And this use case of IIF and LEVEL_NUMBER solves the task. 

Great! Thanks!

0 0
Eduard Lebedyuk · Jul 12, 2016

Here's the list of all intrinsic member properties:

  • ID
  • KEY
  • NAME
  • MEMBER_NAME
  • CAPTION
  • CUBE_NAME
  • LEVEL_NUMBER
  • LEVEL
  • HIERARCHY
  • DIMENSION
  • LEVEL_CAPTION
  • DIMENSION_CAPTION
  • HIERARCHY_CAPTION
1 0