How to calculate the number of rows in DeepSee?

Question

Question

Evgeny Shvarov · Feb 13, 2017

#InterSystems IRIS BI (DeepSee) #Analyzer #MDX

Hi!

How can I show a number of rows in a given DeepSee pivot?

Discussion (3)1

Log in or sign up to continue

Alessandro Marin · Feb 14, 2017

In the options for pivot table you can use Count instead of Grand Total as Summary. However, you will have to choose between the Grand Total and Count. If you calculate the grand total in some other way (for example by placing another [Outlet].[H1].[Region].Members and then Gear icon > Compute Aggregate > SUM), that COUNT will also count the cell with the Grand Total.

Otherwise you can create a "Row Number" calculated measure with the expression below and place it on measures next to Revenue

COUNT(%CELL(-1,0))-1+%CELL(0,-1)

0 0

score 0 · Answer 1 · 2017-02-14T15:41:02-05:00

If you are asking how to show the number of rows in a given DeepSee pivot, edit your current MDX and wrap the expression on rows in the COUNT function.

If you are asking how to limit the number of rows, click on the gear on the Rows box and use the "Return the first n members" checkbox.

score 0 · Answer 2 · 2017-02-14T16:00:25-05:00

Hi, Alessandro! Thanks!

Yes, I was asking about how to show the number of rows.

I managed one approach with Expression on the Column and introducing Total like this:

With the MDX Expression:

SELECT NON EMPTY {[Measures].[Amount Sold],%LABEL(1,"To calculate the rows","")} ON 0,NON EMPTY [Outlet].[H1].[Region].Members ON 1 FROM [HOLEFOODS]

Along with the setting for the Pivot in Analyser to show the Grand Total.

So, do you have the way to make it via MDX only?