Roll up in subset using MDX query

[Chuks]

Hi All,

I'm trying to create a report for end user by using a filter on cube value which is entered by the user. For Eg:

Cube A:
Product -(p1,p2,p3)
Line - (L1,L2,L3..)
Measures -(Amount,Type)

Data for P1:

Line Type Amount
L1 CategoryA 5000
L2 CategoryA 5000
L3 CategoryC 100
All Lines 10100

Expected Result: Show only Lines with CategoryA as Type with total.

Line Type Amount
L1 CategoryA 5000
L2 CategoryA 5000
RollupTotal 10000

I created a MDX query to find out the lines which is having type as categoryA and it works fine,but what Im missing is the roll up total.. Please let me know if the total can be done?

Query used:

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Line] )}, 0)},[Cube A].([Product].[P1],[Cube A Measures].[Type]) = "Category A")}

Since its an active form i cannot keep an excel sum to do the sum of selected items.

Thanks in advance!

[qml]

I created a MDX query to find out the lines which is having type as categoryA and it works fine,but what Im missing is the roll up total.. Please let me know if the total can be done?

THE way to aggregate numbers in TM1 is via consolidations defined in dimensions. Other ways exist, but always have their caveats. In your case you should just create the appropriate rollups for each category in your dimension. Do it in a TI process which you will execute to rebuild the hierarchies when attribute values change.

[Mark RMBC]

Not sure why you say

since its an active form I cannot keep an excel sum to do the sum of selected items.

I have done this on occasions by using MDX on the TM1RPTROW to return the required rows and then using excel sum to sum the entire relevant column, so something like =SUM($G:$G) on the active form

Another option is the use of DBRW on the active form but it may not be appropriate if you have lots of companies and they are forever changing!

[tomok]

Not sure why you say

since its an active form I cannot keep an excel sum to do the sum of selected items.

I have done this on occasions by using MDX on the TM1RPTROW to return the required rows and then using excel sum to sum the entire relevant column, so something like =SUM($G:$G) on the active form

Another option is the use of DBRW on the active form but it may not be appropriate if you have lots of companies and they are forever changing!

The key is you have to put the SUM formula below or above the active form report area AND have the SUM formula start in the first line of the active report and go one row beyond where the active form area ends. This way when it destroy and rebuilds the rows it won't leave you with a broken formula. You can always hide that extra row so it doesn't show up.

[Chuks]

Not sure why you say

since its an active form I cannot keep an excel sum to do the sum of selected items.

I have done this on occasions by using MDX on the TM1RPTROW to return the required rows and then using excel sum to sum the entire relevant column, so something like =SUM($G:$G) on the active form

Another option is the use of DBRW on the active form but it may not be appropriate if you have lots of companies and they are forever changing!

The key is you have to put the SUM formula below or above the active form report area AND have the SUM formula start in the first line of the active report and go one row beyond where the active form area ends. This way when it destroy and rebuilds the rows it won't leave you with a broken formula. You can always hide that extra row so it doesn't show up.

Hi Tomok,

Thanks for the reply, i tried the Sum with a hidden row after active form and it worked.

[Mark RMBC]

I guess if you want the formula in the same column as the values, so on the active form you see the total directly above the values then one option would be to insert a row above TM1RPTROW and in the relevant cell put in something like:

=IF($G$25="",0,SUM(OFFSET($G$25,,,COUNT($G:$G))))

where G is the column you are summing up and 25 is the row containing TM1RPTROW

The above assumes the active form defaults to a blank view when first opened and rows are returned based on user filtering!

An addition: the above formula would be put in cell G24

regards, Mark

[Mark RMBC]

It has dawned on me that I do not need to use the code =IF($G$25="",0,SUM(OFFSET($G$25,,,COUNT($G:$G)))) because as TOMOK repeatedly says as long as you go one row beyond the where the active form area ends a simple Sum will suffice!

Apologies for any confusion.

Still if you didn't know about the Excel offset function before you do now!