Excel Subtotals at the top of Active Form

Post Reply
tm123
Posts: 95
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Excel Subtotals at the top of Active Form

Post by tm123 » Thu Feb 01, 2018 10:03 pm

Hi Guys,

I have an active form, which is used for Input. I have a few String Measures (user will select through Pick Lists) (Employee Type, Employee Pay Cycle etc)

The users want to be able to Filter EMployees by Employee Type, Employee Pay Cycle etc. For this, I am creating Dynamic MDX statement to be used in my TM1RPTRow for the Employee Dim (Filtering By Cube Cell Values) and this works perfect.

But they want also Subtotals of only Visible Rows, in the Top of Active Form. I cannot make all of these EMployee ATtributes as DImensions since this is an Input Cube.

I tried 2 approaches, they both work, but I don't know which one to go with:

1. Use Excel SUM Functions but my issue is if I delete the Rows after the Active Form Master row by accident, then those Excel Formulas do not include all rows in the Totals

2. By Using a User Params Cube, I store the User Selected Filter Values in cube, and then I created an MDX Subset that looks at the User Params cube. Then I refer that MDX SUbset Name in my DBRW Formulas in the Active Form. It is working fine, but I am little bit concerned since this is a Heavy Dimension and having an MDX Subset might slow down performance. I have used this approach for other reasons, mainly for Filtering or Cascading selections but in this case I want to be able refer the subset name in DBRW formulas

Any advice?

Thanks

User avatar
gtonkin
MVP
Posts: 579
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Excel Subtotals at the top of Active Form

Post by gtonkin » Fri Feb 02, 2018 6:14 am

You can try this:

Code: Select all

=SUM(OFFSET(TM1RPTDataRng1,0,3,ROWS(TM1RPTDataRng1),1))
Replace TM1RPTDataRng1 with the relevant range per your workbook. The range starts in column A, so an offset of 3 in my example reads from column D.

tm123
Posts: 95
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Excel Subtotals at the top of Active Form

Post by tm123 » Mon Feb 05, 2018 6:08 pm

gtonkin wrote:
Fri Feb 02, 2018 6:14 am
You can try this:

Code: Select all

=SUM(OFFSET(TM1RPTDataRng1,0,3,ROWS(TM1RPTDataRng1),1))
Replace TM1RPTDataRng1 with the relevant range per your workbook. The range starts in column A, so an offset of 3 in my example reads from column D.
Thank you George!
This is exactly what I was looking for.

tm123
Posts: 95
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Excel Subtotals at the top of Active Form

Post by tm123 » Mon Feb 05, 2018 7:42 pm

gtonkin wrote:
Fri Feb 02, 2018 6:14 am
You can try this:

Code: Select all

=SUM(OFFSET(TM1RPTDataRng1,0,3,ROWS(TM1RPTDataRng1),1))
Replace TM1RPTDataRng1 with the relevant range per your workbook. The range starts in column A, so an offset of 3 in my example reads from column D.
I thought this resolved my issue since in Perspectives it works perfect, but when I published my sheet in and opened it from TM1Web, it does not work. Even if I display the value of =ROWS(TM1RPTDataRng1) in a visible cell, in Perspectives this value gets updated with the number of rows in Active Form, but in TM1Web, it shows the value 1

User avatar
gtonkin
MVP
Posts: 579
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Excel Subtotals at the top of Active Form

Post by gtonkin » Tue Feb 06, 2018 9:37 am

Try this alternative then:

Code: Select all

=SUM(INDEX(TM1RPTDATARNG1,,COLUMN()))
Assuming the formula will be in the column it is summing, otherwise change accordingly.

tm123
Posts: 95
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Excel Subtotals at the top of Active Form

Post by tm123 » Tue Feb 06, 2018 12:57 pm

gtonkin wrote:
Tue Feb 06, 2018 9:37 am
Try this alternative then:

Code: Select all

=SUM(INDEX(TM1RPTDATARNG1,,COLUMN()))
Assuming the formula will be in the column it is summing, otherwise change accordingly.
Damn it, it still works perfect in Excel but not in TM1Web, it includes only the First Row of ACtive Form in the Totals, so I guess eventhough we know that TM1RPTDATARNG1 expands automatically each time an Active Form is built, TM1Web still keeps the TM1RPTDATARNG1 as of the time the Websheet was published/updated

I am on Planning Analytics 2.02 I guess ( TM1 Build Number: 11.0.00200.998 )

I have never tried this in earlier versions of TM1

User avatar
gtonkin
MVP
Posts: 579
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Excel Subtotals at the top of Active Form

Post by gtonkin » Tue Feb 06, 2018 12:59 pm

Tried it on PA 2.0.1 before posting-seems to work fine-had an Active form with levels and it summed as I collapsed and expanded-numbers obviously nonsense but functioned as expected.

lotsaram
MVP
Posts: 3042
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Excel Subtotals at the top of Active Form

Post by lotsaram » Tue Feb 06, 2018 1:06 pm

gtonkin wrote:
Tue Feb 06, 2018 9:37 am
Try this alternative then:

Code: Select all

=SUM(INDEX(TM1RPTDATARNG1,,COLUMN()))
Assuming the formula will be in the column it is summing, otherwise change accordingly.
tm123 wrote:
Tue Feb 06, 2018 12:57 pm
Damn it, it still works perfect in Excel but not in TM1Web, it includes only the First Row of ACtive Form in the Totals, so I guess eventhough we know that TM1RPTDATARNG1 expands automatically each time an Active Form is built, TM1Web still keeps the TM1RPTDATARNG1 as of the time the Websheet was published/updated.
I am pretty sure that whatever fancy Excel formula you try it just won't work in TM1Web if the active form contains additional rows which haven't yet been fetched.

TM1Web isn't Excel. The Excel formula support is all done with java functions acting on the available html grid. If the extra rows and the data they contain haven't yet been fetched from the server then as far as the websheet (and it's sub-total formula) is concerned they don't exist.

The only way to go is to either ensure that all rows are fetched by setting WebsheetRowThreshold to a large enough value to ensure the active form is always fully rendered (which will have definite negative performance impacts!) Or else to go with an all in TM1 approach by using an action button to either dynamically create a subset or consolidation on the fly and query that with DBR for the sub-total.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
gtonkin
MVP
Posts: 579
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Excel Subtotals at the top of Active Form

Post by gtonkin » Tue Feb 06, 2018 6:15 pm

lotsaram wrote:
Tue Feb 06, 2018 1:06 pm
... setting WebsheetRowThreshold to a large enough value to ensure the active form is always fully rendered (which will have definite negative performance impacts!)
Apologies tm123-Lotsaram has highlighted the flaw-I was testing on a smallish sample. Thanks for your input Lotsaram makes perfect sense for larger record sets.

User avatar
gtonkin
MVP
Posts: 579
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Excel Subtotals at the top of Active Form

Post by gtonkin » Fri Feb 09, 2018 5:58 pm

This one has been worrying me as I know I have some reports with 3000+ rows and am running on 10.2.2 FP4 i.e. Java, where we currently get a sum at the top of the column due to the way we derive the Active Form via MDX. One day we will need to migrate to PA and I wanted to confirm the impact.

I built a test active form with 10195 elements and tested on TM1 Web using a SUM vs SUM(OFFSET...) vs SUM(INDEX...)
It seems like the plain SUM which includes the row below the active form (which is kept blank), always works as expected.
As soon as I reference the named range for the active form's data area, the formula breaks and behaves as per Lotsaram's feedback.

As at today, would recommend using a SUM formula and including a blank rows (hidden), below the active form, but only if you cannot read directly from the relevant cube-reading from the cube would always be first prize.

tm123
Posts: 95
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Excel Subtotals at the top of Active Form

Post by tm123 » Tue Feb 13, 2018 7:06 pm

Thank you guys,

I know that excel SUM (with an extra row below active form rows) works, but since we always delete all the Active Form Rows (except the TM1RPTRow), that additional row can be deleted by accident also, so I think I should try creating an MDX subset and use that subset in my DBRW formulas.

Since I have the MDX statement in the front end (the MDX Statement that is referenced in my TM1RptRow), I could store this in a Cube in the backend, but it looks like there is no way to compose an MDX Expression in Subset Editor that refers an MDX String that is stored in a Cube.

For example, if I store the MDX: {TM1FILTERBYLEVEL( {TM1SUBSETALL( [TestDim] )}, 0)} in a cube cell, then is there a way to create an MDX subset under TestDim dimension, that reads the MDX Expression from my cube?

I tried GENERATE statement but was not able to make it work

Thanks

lotsaram
MVP
Posts: 3042
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Excel Subtotals at the top of Active Form

Post by lotsaram » Wed Feb 14, 2018 7:11 am

If you have TI at your disposal and can pass in parameters to identify the location of the cell with the MDX string then obviously the answer is yes. I guess you want this to work without TI?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

tm123
Posts: 95
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Excel Subtotals at the top of Active Form

Post by tm123 » Wed Feb 14, 2018 1:04 pm

lotsaram wrote:
Wed Feb 14, 2018 7:11 am
If you have TI at your disposal and can pass in parameters to identify the location of the cell with the MDX string then obviously the answer is yes. I guess you want this to work without TI?
I want to be done without TI, I want an MDX Subset that would include the MDX Statement composed in Websheet. My websheet has multiple search criteria and depending on Selections other Search Criteria become available. Also there are options for Sorting and other options (like TOP 10 rows etc). So when I compose the MDX Statement to be used in TM1RPTROW, I have to consider all these options and it works perfect. My only problem is subtotals. I know I could store all these parameters that I use in Excel in a User-Parameters cube and then try to build the MDX in the backend but does not seem to be that easy, excel has more options when it comes to compose a dynamic string.

Post Reply