Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2286
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post by Steve Rowe »

One of those Fridays.....

So I am seeing a difference in behaviour in Dynamic Reports in Exel and PAW.

My underlying data looks like this.
Data.png
Data.png (5.48 KiB) Viewed 226 times
In Excel if I create a Dynamic Report versus this data consisting of only columns 2 and 3 in yellow, I correctly only get back Acc 0003.

Once I publish this to the web and look at in PAW then I also get back Acc 0001 and Acc 0002, making it appear as if the zero suppression is misbehaving. It appears to be suppressing across all possible tuples derived from the column headers rather than just those used.

Anyone seen this before? Is this expected?

I did not build the real world example of this form myself and I've not had chance to follow up on how it was created. The column headers are correctly set-up, i.e. they are not reference by the TM1RPTView / TM1RPTTITLE formula.

Trying to get a sense if this is a known wrinkle in the behaviour before we put lots of effort into raising the issue. Using fairly recent releases PAW 2.0.74 and 2.0.9.12 on PAL and PASS
Technical Director
www.infocat.co.uk
tomok
MVP
Posts: 2822
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post by tomok »

This looks to be what I would expect based on past experience. I am assuming that Actual and Budget are in one dimension, the time periods are in another and the accounts are in yet another. Since you've brought both April YTD and Full Year into the view, both Budget and Actual, as well as a subset on the account dimension that includes at least those 4 accounts then you are going to get a row zero suppression that includes all intersections that are non-zero for any combination of the accounts in the subset for Budget, Actual, April YTD and Full Year.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
burnstripe
Posts: 141
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post by burnstripe »

This is also what I'd expect from my experience, it's not new and has been this way as long as I can remember with tm1web
Mark RMBC
Community Contributor
Posts: 284
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post by Mark RMBC »

Hi,

think this was discussed in more depth here:

https://www.tm1forum.com/viewtopic.php?t=15488

regards,

Mark
User avatar
Steve Rowe
Site Admin
Posts: 2286
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post by Steve Rowe »

Thanks for the cross link Mark,

Rebecca H states
rbhansen22 wrote: Sun Sep 13, 2020 11:40 am Hi all,

Active forms in Perspectives zero suppress on what would be the the symmetric view created from the elements referenced ... so if you have a stacked column axis ... even if you’ve removed some combinations, the symmetric view will still contain them which is why the view test is helpful.
Note that in PA for Excel that is not the case ... as it’s now calculating with MDX the zero suppression is a true zero suppression of what is in the form. (Also be aware that TM1 web still uses the older mode so you could end up with some differences if publishing).

Rebecca
Which is exactly what I see, suppression happening correctly in PAfE but not in TM1 Web / PAW. I'll put in an enhancement request...
Technical Director
www.infocat.co.uk
Wim Gielis
MVP
Posts: 2893
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post by Wim Gielis »

Mark RMBC wrote: Fri Jun 10, 2022 6:07 pm Hi,

think this was discussed in more depth here:

https://www.tm1forum.com/viewtopic.php?t=15488

regards,

Mark
Glad my rant in that topic has helped someone 😃
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis
User avatar
Steve Rowe
Site Admin
Posts: 2286
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post by Steve Rowe »

The "idea" is here....if anyone would like to cast a vote

https://ibm-data-and-ai.ideas.ibm.com/ideas/PAOC-I-965
Technical Director
www.infocat.co.uk
Wim Gielis
MVP
Posts: 2893
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post by Wim Gielis »

I added my vote.
I never understood why the default (in any client) would not be catering for asymmetric reports.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis
User avatar
gtonkin
MVP
Posts: 1054
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.10; PAW 2.0.71
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Dynamic Reports, Asymmetric Column Headers, Excel and TM1 Web / PAW

Post by gtonkin »

Up to 4 votes now. Would be great to get this sorted finally.
Post Reply