So I have my TM1 cube successfully published to Cognos BI. I have the sort order defined so that I have my elements of my PnL appearing in the right order. All good. Now I look at the resulting report and there is no formatting of different elements. Simple formatting like a single line above subtotals and a double underline below the grand total. What accountants really want in a report! Grrr!
Another conversation with the Chimney Sweep and he gave me a great method for doing this.
- Define a numeric attribute on the dimension in TM1. I just called it “BI Subtotals”. Then I inserted 1 or 2 depending if I wanted a single or a double line.
- Re-publishing the TM1 cube to BI.
- Open the BI report
- Select the Condition Explorer from the bar in the middle of the screen.
- Drag the String Variable from the toolbox to the Variables section
- Over in Values, add 1 and 2.
- Name the new Variable, I called it BI Subtotals for consistency
- Select Query Explorer from the bar in the middle of the screen
- Drag in the new attribute from the Source to your query, ie BI Subtotals
- Select Page Explorer from the bar in the middle of the screen
- Select the descriptions on the rows, so that you have Crosstab Node Member selected in the Properties box
- Scroll down to Properties and add a check mark to the new field, in my case “BI Subtotals”
- Pause your mouse over Condition Explorer and then select the first value (1) from the Condition BI Subtotals. There should now be a green bar going down the middle of the screen, indicating that formatting changes now will be used for the selected condition.
- Make your changes – in my case I added bolding adn a single line above in the Crosstab Fact Cells.
- Then select the second value (2, in my report) from the Condition and also made those bold and applied a line above and below.