Some Active Form quirks in Cognos Express (9.0) TM1 (941)

-          In data validations, if you want to display a message make sure you have a title otherwise it will display null at the front of the message

-          If you have action buttons that navigate between sheets and leave the workbook name blank then the navigation will work but the workbook name will be lost from the tab bar. You can set the workbook name to be a named range if this is an issue

-          In the action button process options, don’t delete the messages, only untick them to not display anything

Active Form Top Ten Tips

In no particular order but hopefully a useful list for people to check through to ensure good performance:

1. Delete any Named references with #Ref in them, do an Excel find to see if there are any #Ref in cell formula, check for external links and change/delete
2. Delete any unused columns or rows. Use the slider as an indicator as to how many rows/columns are saved with the sheet
3. Keep SUBNM, ELCOMP formula to a minimum
4. Before publishing the Active Form delete all rows below the tm1rptrow formula
5. Any lookups to cube that are used in other formula’s need to be a DBR rather than DBRW
6. Workbooks perform best when uploaded to TM1 Server (copied to TM1 server through Add File route), subsequent navigation options via action buttons should then have “Look In: TM1 Applications” selected
7. Try to minimise the number of worksheets in a workbook. Definitely less than 8
8. Try not to use Action buttons formatted as hyperlinks
9. Don’t use dynamic named ranges e.g. ranges that are not visible to objects within the workbook
10. Use named ranges in action button properties rather than cell references e.g. =rngTM1Server not =’Sheet1’$A$1

TM1 Active Reports - Alternative Row shading

Alternative row shading is a good techniques for visual reporting.
With Active forms you can easily achieve it by using a formula in column A such as:

=IF(TM1RPTELISCONSOLIDATED($B$20,$B20),"C",MOD(ROW(),2))

This formula will then return a result of C, 1 or 0 and your format range just needs to cater for these with 1 and 0 being different shading.

TM1 Active Forms

TM1 Active Forms are very powerful but sometimes a few simple tips can improve performance dramatically.
The next few post will outline a few of these simple techniques.

The first tip is that first row of the ‘active’ section is the basis of the entire report … all formulas are copied down from there. Find the row with the TM1RptRow formula and this is the row that will be copied.

You can actually delete all rows below row with the TM1RptRow formula. All rows beneath are calculated at run time.

For example I took a client's active form report, that was a simple active form straight from the cube viewer but contained nearly 1000 rows and took almost 5 minutes to appear on tm1web, deleted all the rows beneath the TM1RptRow then saved and uploaded. The result was the form then appeared in tm1web in seconds