SAC App: hide/unhide columns with JavaScript in table widget?

[former_member388480]

Hi All,

I am trying to create an analytic application where columns in a table widget are being hidden/unhidden depending on user actions.

One example: if it is a first month of the quarter I want to see all actuals from start of this year up to current month (excluding current month) and 3 months worth of forecast for this current quarter.

So the app should first set a filter from year start uptil current quarter end on monthly basis (of course year beginning will have a different logic).

Lets assume we are in April 2021. When I try filtering this is what I get in the end:

Actual |Forecast

Jan - Feb - Mar - Apr - May - Jun |Jan - Feb - Mar - Apr - May - Jun

What I want to do next is to hide the columns Apr-Jun actual and Jan-Mar forecast like so:

Actual |Forecast

Jan - Feb - Mar | Apr - May - Jun

Unfortunately I was unsuccessful in achieving this with APIs and JavaScript on my own. I tried the forecast display of table but it shows the whole year and doesn't allow much flexibility (but there I saw columns are properly hidden as I would wish to see myself) and client is adamant they want to have only specific months visible.

Thank you for any help you can provide.

Grzegorz Cieszkowski

[TuncayKaraca]

Hi Grzegorz,

The Table's Columns and Rows are not supporting Visibility (Hidden / Unhidden) but you may try with using Table's removeDimension and addDimensionToColumns methods. Basically instead of hiding / unhiding you can programmatically remove and add the dimensions based on the user's action.

Hope it helps.

Regards,
Tuncay

[former_member388480]

Thank you Tuncay for your input. In this case I will try creating some calculated column and try filtering on that. I will reply to this topic if I make it work.

[TuncayKaraca]

Grzegorz, thanks for the update. It sounds good.

[former_member388480]

Hi Tuncay,

I managed to achieve what I intended in the question. However the workaround isn't pretty. Details I explained in a reply to Jef Baeyens.

Thank you for your suggestions.

Best Regards,

Grzegorz

[TuncayKaraca]

Hi Grzegorz,

It's good to hear you've come up with a workaround. Thanks for the update and sharing the solution in details.

Regards,
Tuncay

[JefB]

It's a very common requirement.
We've tried a lot of technical workarounds as well, but found it too hard to achieve in SAC.

Eventually we just copied the needed actual periods into the forecast version and used data locking.
There are many incompatible features if you keep actuals in a separate version... for example you will find it hard to achieve: full year totals (aggregate versions), or similar forecast layout in charts vs tables, or variances across both date & version,.. and many, many other things...

[former_member388480]

Hi Jef,

thank you for your reply. I did a kind of workaround and was able to filter out what I needed. But it isn't pretty and indeed I worry how it will show on display for end users once I start adding advanced formulas to my data actions.

Never the less on a high level what I did is this:

1. Create concatenated column Category + Period

2. Script on initialization reads current month (can be overwritten by user after init)

3. Next script within init creates array with filter for the calcualted column using current month as reference point (cut over date) from point 2 (it concatenates results into an array like this ActualJan ActualFeb ActualMar ForecastApr ForecastMay Forecast Jun)

4. Apply filter on calculated column by member (then remove calculated column from display)

5. Allow the user to change the current month and recreate a private version (then filtering script is reused after new forecast version is created).

Issues I faced:

1 I had to make sure private forecast version is available and filled. If on init private forecast is not available don't create filter on forecast but instead only on actual. I added also a button to create a private version and then the filtering script is triggered once more so I have a proper months display.

2 BUT then I got another issue. You have to make sure your private version doesn't have nulls for months you are interested in because the calculated column will not concatenate Category with Period when key figures have only nulls - solution was to create a data action to enter 0 into one key figure of the new forecast for the months I am interested in and trigger said data action after new private version was created. Thenafter concatenated column worked for forecast columns where at least one key figure had zeros.

3 After that I realized adding a private version with script always displays unbooked data and the calculated column shows a lot of nulls for unbooked entries. I haven't found an option to turn off unbooked members with script. Workaround was - after creating new version - remove all columns from display - trigger data action to add zeros from point 2 and add the columns again thenafter trigger filter script and I get the result as expected:

Actual |Forecast

Jan - Feb - Mar | Apr - May - Jun

Honestly speaking if it wasn't that the client really wants to have this display done I wouldn't even bother as this workaround is very time consuming and hard to debug.But maybe this summary will help someone else in future who faced the same issue as I did.

Best Regards,

Grzegorz