Suppress zero row/column in a chart in websheet

[Elessar]

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 (27.75 KiB) Viewed 5170 times

[burnstripe]

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]

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

[Elessar]

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

[burnstripe]

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.

[Elessar]

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

[declanr]

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.

[burnstripe]

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