Exploring TM1 - a Chartertech Company
Search
Close this search box.

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!

 

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

John Vaughan

John Vaughan

John is a CPA, MBA and has been a Performance Management consultant for over 25 years. He is the founder of ExploringTM1 and highly regarded for his experience combining financial management with corporate planning, reporting and analysis. He lives in Sydney with his wife, two of his three children, their cat, Freckles, a bunch of chooks and some fish. John is a sports nut, who played rugby until he was 40, started playing football at 54 and loves being outdoors.

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In