Automatically Add Formatting to a Cognos Report on a TM1 Cube

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.

  1. 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.
  2. Re-publishing the TM1 cube to BI.
  3. Open the BI report
  4. Select the Condition Explorer from the bar in the middle of the screen.
    1. Drag the String Variable from the toolbox to the Variables section
    2. Over in Values, add 1 and 2.
    3. Name the new Variable, I called it BI Subtotals for consistency
  5. Select Query Explorer from the bar in the middle of the screen
    1. Drag in the new attribute from the Source to your query, ie BI Subtotals
  6. Select Page Explorer from the bar in the middle of the screen
    1. Select the descriptions on the rows, so that you have Crosstab Node Member selected in the Properties box
    2. Scroll down to Properties and add a check mark to the new field, in my case “BI Subtotals”
  7. 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.
  8. Make your changes – in my case I added bolding adn a single line above in the Crosstab Fact Cells.
  9. Then select the second value (2, in my report) from the Condition and also made those bold and applied a line above and below.

Sweet!

 

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

/* THIS WAS THE CODE USED FOR EMBEDDING A SURVEYMONKEY POPUP */