Active form hell

[Wim Gielis]

Hi all,

A longer post/question but I want to explain it as best as I can.

In the past I have been fighting with Active forms a lot. In fact, I still do, from time to time.

I have the Active form below, where zero suppression clearly is not working. 3 rows are correct, 3 other rows should be removed from the report. I verified in the cube that it's all 0 there. No rules calculations, it's just actuals data. The cube contains 10 dimensions, 7 of them are listed in the area D26:F32. The other 3 dimensions are Rp_Product, Rp_Year, Rp_Period. They are in rows and columns of the Active form.

The question is probably two-fold:
- what can I do to get it working ?
- if this is not possible when the Active form is already there and I need to start from scratch: what cube view layout and what dimension selections do I need to have to get it working. The year and period dimensions are separate, and the report happens to be a rolling window of 13 periods, ending with the period the user chooses. Do I need to make a total of 2019 and 2020 and put it in my cube viewer view before creating the Active form ? Select Total Year ? Select all products ?

I removed all formulas here below but I still have my main report to test any suggestions you might have.

The formula in cell D24:
=TM1RPTVIEW("SERVER:Rp_Irp_ProductGroup:ARPT1", 1, TM1RPTTITLE("SERVER:Rp_Currency",$F$28), TM1RPTTITLE("SERVER:Rp_Scenario",$F$27), TM1RPTTITLE("SERVER:Rp_ProfitCenter",$F$31), TM1RPTTITLE("SERVER:Rp_AccountTO",$F$32), TM1RPTTITLE("SERVER:Rp_Tradingpartner",$F$30), TM1RPTTITLE("SERVER:Rp_Company",$F$26), TM1RPTTITLE("SERVER:Rp_Irp_ProductGroup_Msr",$F$29),TM1RPTFMTRNG,TM1RPTFMTIDCOL)

The formula in cell D36:
=TM1RPTROW($D$24,"SERVER:Rp_ProductGroup","Inventory Products")

"Inventory Products" is a dynamic subset:
TM1Sort( TM1FilterByLevel( Descendants( [Rp_ProductGroup].[Total ProductGroup Inventory] ), 0 ), Asc )

The formula in cell F36:
=DBRW($D$24,$F$28,$F$27,F$34,F$35,$F$31,$F$32,$F$30,$D36,$F$26,$F$29)

Thanks a lot for removing the doubts here !

Capture.PNG (130.1 KiB) Viewed 6466 times

[tomok]

The issue you are having is due to the fact columns are not dynamic in an active form. If you had just the one time dimension then the form would work but since you have it split into two it doesn't. I don't know of any way to make it zero suppress in this scenario except to do a "pseudo suppression". You create another active form in the report where your years and columns are on rows (so the zero suppression works) and then you populate the column headers for the year and period with OFFSET formulas pointing to the second active form (which you can hide so it's not visible). Just make sure you have enough columns with the OFFSET formula in it so that it can pick up the maximum number of periods which might have a value. You can use conditional formatting to hide any extras.

[burnstripe]

Struggling to see the cause of your problem here, I can see the first three rows of data are showing but should be suppressed.

May I suggest create it in a cubeview first. In the cube view place all your dimensions as titles (top) bar 3
Year/period/product.
Place year and period in you columns (year first)
Place products on your rows
Suppress zeros on rows for the view
That should hopefully then show just products 4/5/6 as the others are suppressed. (if it doesn't something in the row has a value so check the formatting isn't hiding anything)

If that's working just create the active form using that cubeview and adapt anything you need in excel from there

[Wim Gielis]

Hello burnstripe,

Thanks but that was what I did.
I took 2019 and 2020 as the years in the columns, as this is when the report was created.
By selecting 2019 and 2020, does TM1 somehow remember that the zero suppression for rows will be with reference to 2019 and 2020, even if I take a different company ? Or if we are 2 years later and we have 2021 and 2022 in the columns in Excel, will the zero suppression be calculated for 2021 and 2022 ? (I would hope and assume this in fact, without needing to do any change in the view or setup).

If you are struggling to see the problem, it means that this kind of setup should work out-of-the-box in an active form ? (please refer to my comments to Tomok's contribution).

[Wim Gielis]

Hi Tom,

Thank you too. I fail to understand why this setup of 2 different column dimensions, implies that we need to make such a difficult workaround.
I mean, what if we have scenario's and years/months in the columns whereby years/months are in a single dimension. Then this should also be done differently, rather than just create the view in the cube viewer and bring it to Excel. (No zero suppression on the columns, take 'enough' columns and manually delete columns in Excel we don't need). In fact, every TM1 model has such active forms, no? And I never did these workarounds.

[Mark RMBC]

Hi Wim,

Tomok is correct of course. One dimension would solve this.

Or forgetting 13 periods and settling for any given 2 years, possibly conditional formatting the columns you want to focus on. I.e. some other workaround.

The reason you are seeing Product 1,2 and 3 must be because there is a value against these Products in 2019 0r 2020, in some past or future period?

If you created that view in the cube viewer, i.e. Years 2019, 2020 and all leaf periods in columns and Products in rows. Then zero suppressed the view you would get the same rows as you do on your active form.

Mark

[Wim Gielis]

If you created that view in the cube viewer, i.e. Years 2019, 2020 and all leaf periods in columns and Products in rows. Then zero suppressed the view you would get the same rows as you do on your active form.

Mark

Hi Mark,

Thanks !

That’s exactly what I did, 24 columns in my view and manually deleting 11 of them. Then add Excel conditional logic to retrieve the correct periods.

If I understand you well: will this work if I take 2025 + 2026 in my report and I enter values on these years ? I could try this myself but I am not on the PC right now and I would like to know what to expect.

[Mark RMBC]

Hi Wim,

Not sure I follow you, the 24 column workaround does not involve deleting any columns.
That workaround basically throws the 13 rolling period out of the window and settles for 24 columns each and every time.

Because what you are trying to do can only, as far as I know, be achieved by following Tomoks advice.

If this is truly rolling, i.e. no values in future months, then you could have some process which copies the relevant data to another cube and use that. You might have to think a bit laterally on this one.

Regards,

Mark

[Wim Gielis]

Hello Mark,

I see what you mean and I read that we need to use workarounds. However, active forms are not new. In any project we have them in place, with scenario/version/year/period and all permutations in the columns. I never did those workaround. Am I being lucky for 7-8 years that the active forms still work ? This is, I create them in 2017 with the year 2017 in the columns and now they still work and return the correct rows. This is not driven by data on past years like 2017 to give me the correct years in 2020 because clearly data is posted against new accounts, customers, products, ... that even did not exist in 2017. Have I been lucky all the time ?

[Mark RMBC]

Hi Wim,

I am not aware this has ever worked differently, though happy to be corrected.

So not sure if I would say you have been lucky, more possibly you have not had a form set up which exposes this limitation?

But as I said if you create your view in the cube viewer and supress zero rows you would see Products 1, 2 and 3 in the rows because somewhere in 2019 or 2020 those products have values.

regards,

Mark

[Wim Gielis]

Hi all,

I did some testing in the meantime with the 24Retail TM1 sample menu, "Income Statement" cube.

First, I have the default view but only for Massachusetts, and for 3 years (2014-2015-2016), which IBM implemented as an alias on the elements Y1-Y2-Y3. I removed all rules from the cube. The rows are a static subset containing also the account "5999 Cost of sales" but for Budget 2014-2015-2016 in Massachusetts, it's 0. Cube viewer result:

01.png (40.78 KiB) Viewed 6330 times

I bring the view as an Active form to Excel, same result:

02.png (36.6 KiB) Viewed 6330 times

I add some logic in Excel to have the yellow cell with 2014 and the other years as +1 and +2.

In the Year dimension I manually add the years 2020-2021-2022. Hence, they are empty in the cube.

I load 3 numbers, all on account '5999 Cost of Sales' in the future years:

Code: Select all

CellPutN( 1000, 'Income Statement','Local','Massachusetts','2020','Jan','5999 Cost of Sales','Budget');
CellPutN( 2000, 'Income Statement','Local','Massachusetts','2021','Feb','5999 Cost of Sales','Budget');
CellPutN( 3000, 'Income Statement','Local','Massachusetts','2022','Mar','5999 Cost of Sales','Budget');

Then in the cube viewer I change the selection to 2020-2021-2022, and the same in Excel after that I put 2020 in the yellow cell, rebuild the active form on the current sheet:

03.png (74.53 KiB) Viewed 6330 times

Seems perfect, no ?

Am I missing something ? This approach is what I always did, also with multiple dimensions stacked in the columns. If it works in the cube viewer, why wouldn't it work in the active form ? Isn't the TM1RPTVIEW supposed to take care of this, when we do NOT put the column and row dimensions in the TM1RPTTITLE functions ? To me, the TM1RPTVIEW function would/should take care of 2 things:
- calculation optimizations and view management of cells
- zero suppression of the rows given the selections in titles, rows, columns.

[Wim Gielis]

Any update from those who have an interest in this topic ?

By the way I solved my original query, which in fact was a VBA solution, to burst reports.
The rows in the active forms where not updating. When I use:

Code: Select all

Application.Run "TM1RebuildCurrentSheet"

it works fine. PDF's are generated in 1 folder, in the correct sequence.
Last step is to merge all PDF's to 1 report book with a separate tool.
This exercise is done only once a month so a semi-automatic approach is good.

[Silverik]

Isn't just that your Active Form is defined to display the data for:
- 2 Years (2019 and 2020)
- 12 Months (03, 04,05, 06, 07, 08, 09, 10, 11, 12, 01,03)

The Active form will display the data for the 24 combinations even though you don't query them.

For this product you probably have data on one of these combination
2019 - 01
2019 - 02
2020 - 04
2020 - 05
...
2020 - 12

That's the AF behaviour and always have been like that...

[Wim Gielis]

Hi Silverik,

Not sure I follow you. Did you see my post here (2 posts up):
viewtopic.php?f=3&t=15488&view=unread#p77198

There I show data, I change selections and it still works.

[Mark RMBC]

Hi Wim,

But it worked surely because those rows are the only rows to have values in any month in the years selected (i.e. 2020, 21 and 22)?

the other rows disappear because they have no values in any of those months?

Which is why your post 2 up just confused me and I didn't respond!

This bit really confused me:

Code: Select all

This approach is what I always did, also with multiple dimensions stacked in the columns. If it works in the cube viewer, why wouldn't it work in the active form ? 

Especially as the cube viewer and active form looked the same!

Mark

[Silverik]

Yes I've seen it.

In your second example all the data are on the same line (5999 Cost of sales) and you display all the combinations of column... So this will work...

In your original issue 3 lines "seems" to be empty (product 1, product 2, product 3) but you don't display half of the columns (the one mentionned above... 2019 - 01, 2019 - 02, 2020 - 04, 2020 - 05 ..., 2020 - 12).

in the TM1RPTTITLE functions ? To me, the TM1RPTVIEW function would/should take care of 2 things:
- calculation optimizations and view management of cells
- zero suppression of the rows given the selections in titles, rows, columns.

For the columns part it doesn't look at the columns you are querying but at the combinations of elements in dimensions....
In your original example 24 combinations (2 year, 12 months...).

I don't have perspectives installed here, but I'll provide an example this weekend...

[Wim Gielis]

Hi Wim,

But it worked surely because those rows are the only rows to have values in any month in the years selected (i.e. 2020, 21 and 22)?

the other rows disappear because they have no values in any of those months?

Hello Mark,

Surely the other lines disappear because they are zero in 20-22 but they stay if they have values in 20-22. The thing is that the rows with data on 14-16 disappear in 20-22: my original active form year selection does not play a role anymore when computing the zero suppressed rows when I select 20-22. It’s as if new column selections lead to new zero suppressed rows.

[Mark RMBC]

Hi Wim,

your original had only 13 rolling months, so if some there was data in any month in the same year but not part of the 13 months then you would see extra rows, even though the months were not in the columns.

But in your new example you have 2 years worth of data and every month, and because those rows have no data in any of those months for those years they are not shown as rows?

So the form is not behaving any differently between your rolling 13 months and now, at least from what you have shown me.

regards,

Mark

[Wim Gielis]

Hi Mark,

Thanks. Let's see if I understand what you are saying.

So if this is a completely new cube without rules, with only 2 data points for 4 dimensions:

02.png (29.22 KiB) Viewed 6179 times

Then I have data on account 4999 in January (Year 1) which is a column dimension.

If in my active active form I ask the months of Jan and Feb, in Year 1, I see account 4999:

03.png (15.08 KiB) Viewed 6179 times

If in my active active form I ask the months of Jan and Feb, in Year 2, I see account 5999 only, no 4999:

04.png (11.01 KiB) Viewed 6179 times

Shouldn't I see account 4999 if I follow your line of reasoning ? Because somewhere, for Massachussets, I have on 4999 data on a different year but still on January which is in my column selections.

The rows is each time a level 0 dynamic subset.

Thanks.

[Mark RMBC]

Hi Wim,

No because you only have one year in each example. In your original rolling 13 you had 2 years in the columns.

Try 2 years and put the value in a month that you don't include on the report, so have Y1 and Y2, Jan and Feb but put 5999 in March.

That way you sort of replicate your original problem.

regards,

Mark