Ironside Tech Tip: Working with IBM Cognos BI and Microsoft Office
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.
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.
4. Once you install you will see the following components in the program files.
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.
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.
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.
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.
5. Below is how you add and test the connection to IBM Cognos BI 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.
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.
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.
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.
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.
11. After positioning all the components, you can prepare a deck with multiple reports or a Word or Excel output with multiple pages.
12. To refresh the data on the report, use the Refresh All Data option on the IBM Cognos menu.
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.
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.
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.
