IBM Cognos Analysis for Microsoft Excel (Café) brings the benefits, power and familiarity of Excel to enable business analysts, financial analysts, line-of-business managers and others to explore and analyze data from a variety of different sources. These sources include IBM Cognos TM1 and IBM Cognos Business Intelligence.
With Café, TM1 users can explore and analyze data in a familiar spreadsheet format. They can retain Microsoft Excel functionality and formatting while moving data between Cognos TM1 models and Excel spreadsheets.
Café also lets TM1 users incorporate TM1 formulas in their spreadsheets. This integration is enabled through the use of a simple add-in to Excel. The steps to setup this up are as follows:
- Open Excel.
- Open the Café add-in by;
Navigate to <café installation=”” location=””>Cognos for Microsoft Office.
The file name is CognosOfficeTM1.xll. Please see the example below:
- Insert a TM1 function into your sheet and logon to TM1 to read your model.
Please note: be sure and do not have TM1 Perspectives running. Perspectives and Café cannot be active at the same time.
Tip: ‘Pin’ this file to your recent workbooks so you can open the file without having to navigate to its folder.
TM1 users can both read the TM1 data and write TM1 data back to their model with formula-based spread sheets.
To enable the TM1 formulas to write back to the Tm1 model, the user must have the proper TM1 security and a setting in a local configuration file must be set to True. The file is in the <…Users|AppData\Local\Cognos\OfficeConnections>\ folder and is named CognosOfficeReportingSettings.xml. An example of this is below:
Once located, open the file with a text editor, such as Windows Notepad and update one setting. An example of this is below:
Update the “AllowFormulaWriteBack”>False</setting> to
“AllowFormulaWriteBack”>True</setting>
Save the file and restart Café.
Once this is set, the user can contribute to Forecasts, Budgets, etc. using familiar TM1 formulas in Excel.
Read other Tech Tips blog posts:
Important Things to Consider When Changing your COA Structure
Securing Data from IBM Cognos TM1 On-Prem to IBM Planning Analytics
How to use IBM Planning Analytics Workspace
