Active form claustrophobia
- Apr. 5, 2019
Introduction
Yes, you are reading this correct. I will devote a blog post to active forms and how they can bring you a good deal of claustrophobia. It was an esteemed colleague of mine, for whom I was creating a number of active forms, who told me: "Nice active forms, but I feel claustrophobic !" What she means was that the last row of the active form touches the bottom border and no whitespace is added to the bottom part of the screen:
This article discusses a number of active form best practices.
First off, we all know that it is a good idea to define the rows with either an MDX, either a dimension subset. If you choose for a subset it will often be a dynamic subset containing an MDX. If user actions can change the row output, most probably the MDX will have to be set up and stored in the file itself.
Another important point to note is that you can customize the formatting logic. Have a look at the parts in yellow - it can allow you to have alternating background colors in rows for example (color banding).
Column widths
When you are building an active form based on an existing report, you can save time by applying column widths in an easy way: Go to the source report. Copy cells in 1 (or a few) rows in the columns for which you want to set the column width. Go to the new report, navigate to the first column that you want to change (no matter what row) and paste special the column widths. Done !
The used range and file bloat
That's all fine and dandy. But what happens if you refresh (rebuild) an active form ? TM1 will copy the first row of the active form, the row containing one or more TM1RPTROW functions, and paste it any number of times to match what is shown in the rows. It could lead to 0, 1, 2, 100, ..., 1000s of rows depending on cube data among other things.
But what is a bug, at least to me, is the fact that TM1 uses space for 1000 columns in Excel. If you refresh the active form and you press Ctrl + End, you will end up here:
523 rows is what TM1 needs to output all relevant, zero-suppressed, rows. Column ALL in Excel is exactly column number 1,000:
Why do we need to have a used range in Excel containing about 523,000 cells ? The file size is 66 kb which isn't a lot but I have seen many more extreme cases. Like active forms of 2 MB.
So the question is: can't we get rid of the excess columns and rows and keep the file size minimal ? The ANSWER is: YES, we can ! And I will tell you how.
Basically you physically delete a bunch of cells:
- all columns to the right of the last column of the active form
- all columns below the row in the active form containing TM1RPTROW
Action 1 is done by positioning the cursor on the next column to the right of the active form. Then press Ctrl + End while the Shift key is pressed. With Shift still pressed arrow to the right to select some more columns to the right of column 'ALL' (1,000). Then, delete the selected columns. Pressing Ctrl + - is quickly done. Finally, you need to save the file to have Excel reset the used range from ALL523 to the last cell in the active form.
Action 2 is done by positioning the cursor on the next row below the row with TM1RPTROW. Then press Ctrl + End while the Shift key is pressed. With Shift still pressed arrow down to select some more rows below the last row. Then, delete the selected rows. Pressing Ctrl + - is quickly done. Finally, you need to save the file to have Excel reset the used range from ALL523 to the last cell in the active form.
Of course, you don't need to save the file twice. Saving the file once after both excess columns and rows are removed, is fine. If, after saving, you were to press Ctrl + End again then you will be taken to the last cell of the active form, an active form containing only 1 row!
The file size goes back to acceptable levels:
This brings me to my next best practices tip: store all websheets in a separate folder outside of TM1. Then upload / update websheets as needed and TM1 will store a copy of the file in its }Externals subfolder. But do keep a good version of all websheets in a separate folder. This folder can be backed up on a regular basis.
Claustrophobia
I bet you are asking yourself what the claustrophobia is that I was writing about. Well, it's this (according to the colleague):
The last row "sticks" to the taskbar or at least it snaps to the border of the browser page. It sucks all breath out of the colleague and in an extreme case she would start choking. Well maybe that's an exaggeration but at any rate, the last row and the first column should not coincide with the boundaries of the browser.
But wait, isn't that caused by our removal of excess rows ? Yes it is. Therefore, I still stand by my approach of deleting all rows below the first row of the active form. However, add an extra step: select a cell in the row below the first row of the active form. Type an apostrophe followed by a space. Hit Save. Then clear the cell contents again. You will notice that Ctrl + End will now allow for whitespace of 1 row. It will select as the last cell of the used range the cell below the previous last cell. That was what we wanted. For columns, it's easy enough to insert a new column to the left. The end result is:
A very concise report definition above. Below I highlighted the extra row and column to escape from the dreaded active form claustrophobia.
Summarized we can say that with my best practices, you have the best of both worlds:
- small, concise, websheets in Excel
- active forms that open up quickly in TM1 Web
I did not talk about that last point but indeed: in general, smaller websheets will open up quicker in TM1 Web compared to bigger files. So no need to have these excess rows and columns on board ! Another added benefit of clearing unnecessary cells can be obtained when you use conditional formatting in the formatting range (which is hidden by default). Conditional formatting should only be in the hidden formatting area, and not active in many report rows. You can be sure that whenever the report is opened or refreshed, TM1 will apply the conditional formatting again. To conclude this article I add that I wrote my own VBA-code to automate all of the above manual steps. Even more the best of both worlds ! :-)
Enjoy the tips !
