Active Forms - Conditional formatting issue

[gtonkin]

After many, many hours trying to work out why an Active Form report with about 9000 rows was never finishing i.e. taking more than 2 hours to do what appeared to be nothing then killing Excel, I tracked it down to conditional formatting on the first row of the AF i.e. the 1st in the TM1RPTDATARNG. There were two conditional formatting conditions - both referred to #REF! in their formulae.

After removing these invalid conditional formulae, refresh completes in 1m35s.

For sanity's sake, I added back a conditional formatting rule, to the first DBRW in the top row, pointed to a cell to the right (AC13) i.e. =AC13=1, added some red fill for effect.
I then deleted column AC and refreshed - 5 minutes later I killed Excel - check seemed to verify the issue.

I deleted all but the first row and added valid conditional formatting to it - rebuilt the Active Form, formatting from the Format Range was applied and my conditional formatting was replaced/removed as expected. Refresh took the same time as no conditional formatting.

Conclusion:
Add checking (and possibly removing) Conditional Formatting to one of the things to do when receiving reports from others or inheriting them with a "working" model.
I hope this saves some of you the time I have already spent!


Other Background:
-Server running TM1 10.2.2 FP2
-The Report Cube has 12 dimensions - One dimension is about 31Mb (956,000 elements), the rest more realistic
-The query is basically an ALL N across all dimensions, except for Period, specified in Context area.
-Cube view opens in seconds after server restart.
-Whilst watching Task Manager, TM1SD.EXE CPU Time during AF Refresh is always a few seconds, CPU time is always on the EXCEL.EXE process
-AF is referencing a pure reporting cube, no rules, only input data, measures dimension contains numeric elements only
-Excel set to Manual calculation, single-threaded
-Workbook contained sheet with basic AF, column A was changed to return a 1 or 0 based on MOD(ROW(),2)
-Format range contained two rows with IDs 0;1 - basic formatting - alternate row colouring

Test Cases:
For those who are curious this is what I tested too:

1. Triggering via TI Button vs macro "TM1REFRESH" has no impact on run time
2. 9.5.2 FP3 client vs 10.2.2 FP1 vs 10.2.2 FP2 (9.5.2 FP3 client is still faster than any v10 client - 9.5.2 FP3=1m35s; 10.2.2 FP2 2m06s)
3. Ran the same refresh multiple times to ensure that 2nd and subsequent runs would use the cached view (no data changes, single user, VMM set to 500Mb) - same completion times.
4. Tried a Static Subset of 8178 elements known to have data populated vs ALL N (956852 elements) - made no real difference - 10 seconds - cube view suppression working its magic properly.

[paulsimon]

Hi

I have had a lot of the same issues in 10.1.1. It appears that the Active Form is copying Conditional Formats from the Format Area to the Data Area, each time it is refreshed so you end up with several copies of the same Conditional Format being applied to the cells in the Data Area. The only cure I have found for this is to clear all Conditional Formats from the Data Area before uploading the sheet to the Application Folders, where it is used via TM1 Web.

I have also found that any #REF Range Names, Wrap Text, Merged Cells, etc, will also cause issues.

Active Forms are still quite flaky and do not cope well as the number of nested dimensions increases.

Regards

Paul Simon