Suppress zero row/column in a chart in websheet

Post Reply
User avatar
Elessar
Community Contributor
Posts: 258
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia
Contact:

Suppress zero row/column in a chart in websheet

Post by Elessar » Fri Jul 16, 2021 2:52 pm

Hi,

I have a websheet with stacked column chart. There are zero-rows and zero-columns. Is there a way to hide them?
Websheet suppress.png
Websheet suppress.png (27.75 KiB) Viewed 711 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: First article - Primary sales forecasting using PA, SPSS Modeler and python.

burnstripe
Posts: 45
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Suppress zero row/column in a chart in websheet

Post by burnstripe » Fri Jul 16, 2021 4:28 pm

Hiding the column m1, m4 and hiding the row c3 will get the graph to display as desired. To get this dynamically suppressing there's no silver bullet i'm aware off and each solution is not completely ideal.

If you have workspace, you can suppress blanks on both rows and columns and the graph will be automatically updated
If you have PAX, you can suppress both rows and columns using an exploration and editing the mdx for the view. You'll then need to update the graph to the view.

If you have perspectives or want to use TM1Web, the closest I think you could get would be to have a additional blank key, but m1 and m3 are suppressed. To get this I would convert the tm1 view to a tm1rtpview (active form), suppress null values, this will hide c3. And then use index function to return the results in the desired format or a pivot table.

To get it fully dynamic in Perspectives I think you would need a macro to update the series.

Wim Gielis
MVP
Posts: 2741
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Suppress zero row/column in a chart in websheet

Post by Wim Gielis » Fri Jul 16, 2021 11:43 pm

For PAW, I saw this one: https://www.ibm.com/docs/en/planning-an ... alizations
Maybe this helps. Version 2.0.66 though.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

User avatar
Elessar
Community Contributor
Posts: 258
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia
Contact:

Re: Suppress zero row/column in a chart in websheet

Post by Elessar » Mon Jul 19, 2021 6:23 am

Thanks guys,

It's a completely websheet model, so custom macros and PAW could not be used :(
And yes, of course, I need this to be dynamic
Best regards, Alexander Dvoynev

TM1 and Data Science blog: First article - Primary sales forecasting using PA, SPSS Modeler and python.

burnstripe
Posts: 45
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Suppress zero row/column in a chart in websheet

Post by burnstripe » Thu Jul 22, 2021 1:10 pm

The attached is the closest I reckon you can get the graph in TM1Web to be fully dynamic.

Create an exploration with zero suppression on both the rows and columns and then set the data source for the chart to a range that would cover the exploration plus a bit extra for expansion.

Any columns that are not shown, will be show as an icon at the bottom of the graph, see grey and yellow icons.
Attachments
Capture.PNG
Capture.PNG (83.1 KiB) Viewed 231 times

User avatar
Elessar
Community Contributor
Posts: 258
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia
Contact:

Re: Suppress zero row/column in a chart in websheet

Post by Elessar » Thu Jul 22, 2021 2:00 pm

Thanks burnstripe,

But it is not what I need:
Grey and yellow icons are what I need to hide. And you have an empty ZSTAT, which should also be hidden
Best regards, Alexander Dvoynev

TM1 and Data Science blog: First article - Primary sales forecasting using PA, SPSS Modeler and python.

declanr
MVP
Posts: 1728
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Suppress zero row/column in a chart in websheet

Post by declanr » Thu Jul 22, 2021 2:21 pm

I haven't spent a lot of time with TM1 Web in recent years but the old approach for hiding rows was to create an Excel table off the Active Form and then use the table as the source for the chart. From memory it worked well.
I wonder if this would also work with an Exploration View in order to handle the hiding of columns also.
Declan Rodger

burnstripe
Posts: 45
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Suppress zero row/column in a chart in websheet

Post by burnstripe » Thu Jul 22, 2021 6:54 pm

The empty home currency and zstat, would disappear, I just hadn't suppressed rows on my exploration example.

The grey and yellow icons, I can't see how this can be hidden using tm1web dynamically. For sure you can hide columns from the graph, but unless you can hide them with conditional formatting it's never going to be dynamic.

This is because for the type of graph you require to add/remove an item from a stack you would need to add or remove a series from the graph

If there's an excel function that be used to update the series automatically then that may work.

Or if you can create multiple series from a single named range then that would also work

Post Reply