TM1 Web - hide empty columns

[John Hobson]

Is theer an easy way to hide empty columns in a web sheet.

We have 52 columns to accommodate the weeks in a year but sometimes we'll be looking at subsets of the year (say 10 weeks). This means that the first N columns will be full but there will be a number of columns on the right that want to be hidden.

Obviously we can't use macros, so are there any best practice ways of achieving what we want?

TIA.

[tomok]

You can't make the showing or not showing of a column dynamic in TM1 Web. You can, however, make the column "disappear" with conditional formatting, meaning you can make the font the same color as the background so that it appears there is no data. The columns will still be there so, depending on how many columns you have in the report, you may have a scroll area available to the right even though to the user it appears there is nothing there. That's the best I've been able to come up with so far (although I haven't really spent much time on it).

[John Hobson]

Thanks for that - it confirms what we thought.

I keep hoping TM1 Web will have evolved a bit from when I last visited it about 5 years ago

[TrailRunnerMark]

We figured out a way to have the columns show as blank by exploiting the fact that the INDIRECT function is not supported in TM1Web. We are displaying 60 possible weeks of data and the column headers are derived by an IF formula with it's False condition being INDIRECT("H8") or other blank cell. The formula evaluates to the weeks we are interested in, or zero for the weeks outside of that range, and in the Excel sheet all the DBRW formulas evaluate to #N/A. Happily, when viewed from TM1 web the columns are all just blank including the column headers.

Not sure how badly that hurts performance. But on the plus side, you can't enter data into the cube outside of the week ranges that we define. And the user isn't tempted to, since the cells and column headers are blank.

Still not happy with the way we do this, but it does work.

[tomok]

We figured out a way to have the columns show as blank by exploiting the fact that the INDIRECT function is not supported in TM1Web. We are displaying 60 possible weeks of data and the column headers are derived by an IF formula with it's False condition being INDIRECT("H8") or other blank cell. The formula evaluates to the weeks we are interested in, or zero for the weeks outside of that range, and in the Excel sheet all the DBRW formulas evaluate to #N/A. Happily, when viewed from TM1 web the columns are all just blank including the column headers.

Not sure how badly that hurts performance. But on the plus side, you can't enter data into the cube outside of the week ranges that we define. And the user isn't tempted to, since the cells and column headers are blank.

Still not happy with the way we do this, but it does work.

I would be very careful with breaking a portion of a web sheet in order to acheive formatting. TM1 Web is very finicky about stuff like this. Some formula errors will cause the web sheet to crash, with no error messaging to tell you what the problem is, other than the generic message.

[lotsaram]

I would be very careful with breaking a portion of a web sheet in order to acheive formatting. TM1 Web is very finicky about stuff like this. Some formula errors will cause the web sheet to crash, with no error messaging to tell you what the problem is, other than the generic message.

I concur. This is something to be wary of. Maybe this is no longer an issue in 9.5.2 and 10.1 but certainly in 9.5.1 there is a minor error threshold for websheets that once breached causes the sheet to simply not load (by "minor error" I mean in this case a cell value that you would see in Excel as #REF, #N/A, #VALUE or #KEY ERR). Did some testing on this a few years back and can't recall if we deduced the error limit was 1000 or 10000 but above the limit the websheet simply doesn't display although everything is fine in Excel. I don't believe there is anywhere in web.config where the tolerance for such evaluation errors can be played with.

[K3DubEU]

Any updates on this matter as the last post was over a year ago?

Having blank and empty columns in between of data is quite frustrating. Users using these reports on a daily basis are constantly required to scroll pass the blank columns.
We would have hoped that a proper solution would have surfaced by now as version 10.2 surfaced.

I'm sure that a solution can be achieved by using vb scripts within excel but how well does TM1Web play with vb scripts?
If someone found a workaround for this problem do enlighten us.