Active forms in IBM Cognos TM1 can provide a lot of flexibility in terms of formatting your data. After creating an active form, the formatting of the rows is handled in the Format Range of the worksheet. To expose the Format Range you can click the Show Format Area button on the TM1 ribbon or right click in the worksheet and navigate to Active Form->Show Format Area. In order to provide different formatting on the rows for different levels of a hierarchy TM1 uses a formula to drive the values in column A. This formula returns a value that pulls a specific line of formatting from the Format Range into the data grid.
Any formatting applied to a row in the Format Range, will be placed on a row in the active form area that uses the corresponding key value. Most Excel formatting can be used in the format range and will translate to the format area. This includes Number Formatting, Alignment, Font, Fill, Bordering, etc.
The formula that is in the first column of the first row of the active form area provides the key value that will drive the formatting for the row. This formula can be manipulated to make the formatting even more robust.
Let’s say we have a lot of zero level elements that drive our rows, and we want some formatting to make the values easier to read. First, we can add more rows inside the format range to help make our formatting flexible. Notice in the screenshot below, we added a new row labeled M. Next we can make a simple change to formula. In the spot where N would be populated, we made a change that says if the previous row had a value of N, then populate an M, otherwise populate an N. Upon a rebuild of the sheet the new formatting is applied.
Below is another example of manipulating the row format formula. Let’s say we have an
important account that we want to highlight when it appears in a view. We added
a new line, P, to the format range that will hold the formatting for the
special row. Next we manipulate the formula to check for the account number and
if it appears we assign it to row P.
Bonus Tip: If you have the pesky gridlines on your reports once you publish them to the web. Turn off the Gridlines option on the View tab to make your web forms look nicer.
Free eBook Download
In the eBook “Having a Conversation with Data”, learn what the current BI infrastructure has been and associated challenges with the traditional approach. How important the user experience is in order to best maximize data’s value (think visualizations!!) to your organization and how to gain a competitive advantage with modern analytics platforms.
