Ironside Tech Tip: Working with IBM Cognos BI and Microsoft Office

microsoft office on mobile phone

Using Cognos we can generate reports in Excel, Microsoft Word and Power Point. In this article we will see what components we need to produce these reports in Microsoft Office.

Required Components and Installations:

1. You need to have Cognos environment ready and useable for generating reports, as shown below.

Cognos Connection

2. You have to install Microsoft Office in all the destination systems or client machines where we are going to access Cognos reports using Microsoft Office components.

3. Install IBM Cognos for Microsoft Office based on the version of the IBM Cognos your organization uses. Here in my server I have IBM Cognos BI 10.2.0 and you have to install by clicking the issetup.exe file as shown below.

setup file

4. Once you install you will see the following components in the program files.


Cognos for Microsoft Office program folder


5. After seeing these components, close all Microsoft Office-related programs running on your system.

6. Now once you open any of the MS office components Excel, Word or Power Point you will see an extra Tool bar item (under Tool Items) which is IBM Cognos as shown in below screen shot.


Cognos tab in Microsoft Office


7. Once you are able to view IBM Cognos in Word, Excel, or PowerPoint, you are all set to develop or run Cognos reports using Microsoft Office components.


Developing or Designing Reports Using Microsoft Office:

1. Create a report in Cognos Report Studio. Below is a report developed and executed in HTML format in Cognos Connection.

Cognos report example

2. Open any one of the Office components. Here I am using PowerPoint as an example.

3. Go to IBM Cognos in the toolbar as shown below.

Cognos in ppt toolbar

4. To access IBM Cognos BI content from your IBM Cognos BI Server, you need to add your server URL to the Microsoft tool you’re using by clicking on Options on the IBM Cognos menu.

options for reports


5. Below is how you add and test the connection to IBM Cognos BI Server.

connect to server

6. Click on IBM Cognos to access the Contents of IBM Cognos BI Server. You will see a new window on the right-hand side.

bring in report data

7. Now you can import the content using the import content option at the bottom of the right-hand screen after selecting a report from Public Folders or My Folders.

  import content buttonselect report properties

8. Select the components you need to present in your PowerPoint, Excel, or Word file and click on Next until you reach Finish, selecting all options based on your needs.

select report properties wizard

9. Once you click on Finish, the report will prompt you to select values if you have any prompts. Here I have a  Year prompt.

prompt page

10. Once you select the prompt and click Finish, you will see all the components you have selected in PowerPoint, Word, or Excel. You then have to format the report to fit into a page, which is a one-time activity.

format in Microsoft

11. After positioning all the components, you can prepare a deck with multiple reports or a Word or Excel output with multiple pages.

report results

12. To refresh the data on the report, use the Refresh All Data option on the IBM Cognos menu.

refresh all data

13. Once you click on Refresh All Data, you will see a pop-up with two options. One is Refresh Data Only and the other is Refresh Data and Report Formatting. Refresh Data Only will fetch the latest data from the database, and Refresh Report Data and Formatting will change the formatting to the default format that was imported the first time you pulled report information in.

14. If you want to prompt for the prompt values whenever a user clicks on Refresh All Data, you have to enable prompting in the Manage Data area.

Cognos Jan 14-17

set prompt frequency

15. We can share static files with the other users outside a firewall, out of your network,  or without Cognos environment access by selecting the Convert to Static option. Once you click on Convert to Static you will see a pop-up as shown in the second screenshot.

 convert to static

convert to static warning

Here we discussed how we can integrate Cognos with Microsoft Office to add BI reporting into your presentations, spreadsheets, and documents. If you’d like to get more information on advanced reporting functions like this one, we invite you to attend an upcoming public Ironside course. Our instructors will put you on the path to success with the BI technologies that fit your organization.