IBM Cognos for Microsoft Office

This is my first glanace at “IBM Cognos for Microsoft Office”. What is “IBM Cognos for MS Office”? “IBM Cognos for MS Office” provides an integrated environment for IBM Cognos products and MS Office and it’s free if you are licensed for IBM Cognos BI. You can use IBM Cognos for MS Office to select pieces of reports to embed in MS Excel workbooks, MS Word documents, or MS PowerPoint presentations, including data, metadata, headers, footers, and charts. It doesn’t seem to be built in MS Access or Outlook and I’m OK with that. You can use predefined reports or you can create new content using IBM Cognos PowerPlay Web, IBM Cognos Query Studio, IBM Cognos Business Insight Advanced, or IBM Cognos Report Studio.

I’m testing it with Cognos BI 10.2.1 FP2 and MS Office 2013 (32bit) on Windows 8.1 PC (64bit).

Office 2013

Office 2013

The tool has to be downloaded from ibm passport advantage and you need to look for and download “ofc_10.2_win_ml.tar.gz”

Once you have the compressed file opened you can install the tool on your PC  by running “\ofc_10.2_win_ml\win32\win32\issetup.exe”  It should install the software to “C:\Program Files (x86)\ibm\cognos\Cognos for Microsoft Office”. The reason I point this out there is a interesting folder called Automation going have to check this out. “C:\Program Files (x86)\ibm\cognos\Cognos for Microsoft Office\Automation” If anyone has worked with this please share.

Open your MS Office product. You should see a new tab on the ribbon called “IBM Cognos”

The first thing you are going have to do is setup the tool options to point to your Cognos server one time deal:

isapi –> http://servername/ibmcognos/cgi-bin/cognosisapi.dll

or

cgi –> http://servername/ibmcognos/cgi-bin/cognos.cgi

Then you can start Dragging and Dropping or Click on Import Content to pick specific content from a report.

These are MS Excel worksheet example screenshots.

Office Excel 2013

Office Excel 2013

IBM_COGNOS_Office_Excel_2

Ribbon Options and Publish the Office Object to your Cognos connection environment:

IBM_COGNOS_Office_Excel_4

photofun-256526381

Logging Path: “C:\Users\username\AppData\Local\Cognos\Office Connection\Logs\”

IBM_COGNOS_Office_Excel_6

Review the imported elements on the second tab:

IBM_COGNOS_Office_Excel_3

When you run a report with prompt(s)  a html windows will open for you to select your prompt(s) and then the data will populate in the MS workspace. The tool seem to remembers your filter selections. Not sure how to pick new filters.

MS PowerPoint with Chart:

IBM_COGNOS_Office_PowerPoint

“IBM Cognos for Microsoft Office” seems to be replacement for CAFE (Cognos Analysis for Excel).

Have Fun!

Advertisements

Find reports in your COGNOS BI with old URL image paths or package names through content store

At my company we have a lot of reports with old image path URLs from old templates that were used in COGNOS BI 7.0 pointing to an old location on IIS DNS Alias that we wanted to decom. We have moved the images to a new location and updated the COGNOS 10.2.1 templates. But had no way of updating the existing reports. So I wrote a  little SQL select and update statements to fix the issue in the COGNOS content store. I hope this help others admins and has a lot of other useful practices. But a word of cation the content store need to be backed up and queries need to be tested before used. I always would copy the report in COGNOS and narrow the where clause down to the one report and test it before updating the bulks of the reports.

--Find reports in COGNOS BI 10.2.1 with Old URL Image or Package Name

--COGNOS BI 10.2.1

DECLARE @sql VARCHAR(50)

--Package Name

--SET @sql = '''Premier'''

--Image URL

SET @sql = '%http://essdev/cognos8/samples/images/UCB.gif%'

SELECT  r.[CMID] ,

c.[NAME] ,

[EXECERROR] ,

[CONFIGURATION] ,

[PORTALPREFS] ,

[CCREDENTIALS] ,

[CMODEL] ,

[SRC] ,

[SPEC] ,

[STATE] ,

[GOVERNORS] ,

[NAMESPFORMAT]

--,'%/content/package[@name='+@sql+']%'

FROM    [Cognos_10].[dbo].[CMOBJPROPS7] r

INNER JOIN [Cognos_10].[dbo].[CMOBJNAMES] c

ON r.[CMID] = c.[CMID]

INNER JOIN [Cognos_10].[dbo].[CMOBJECTS] cm

ON c.[CMID] = cm.[CMID]

INNER JOIN [Cognos_10].[dbo].CMCLASSES T3

ON T3.CLASSID = cm.CLASSID

WHERE   --Package Name

--[SPEC] LIKE '%/content/package[@name='+@sql+']%'

[SPEC] LIKE @sql

AND T3.CLASSID IN ( 10, 76 )

AND c.NAME = 'Mailing'

----------------------------------

-- UPDATE to Replace Company Image URL template

UPDATE r

--cast(replace(cast(myntext as nvarchar(max)),'find','replace') as ntext)

SET [SPEC] = cast(Replace(cast([SPEC] as nvarchar(max)),'http://essdev/cognos8/samples/images/UCB.gif','../samples/images/UCB.gif') as ntext)

FROM [Cognos_10].[dbo].[CMOBJPROPS7] r

INNER JOIN [Cognos_10].[dbo].[CMOBJNAMES] c

ON r.[CMID] = c.[CMID]

INNER JOIN [Cognos_10].[dbo].[CMOBJECTS] cm

ON c.[CMID] = cm.[CMID]

INNER JOIN [Cognos_10].[dbo].CMCLASSES T3

ON T3.CLASSID = cm.CLASSID

WHERE [SPEC] LIKE '%http://essdev/cognos8/samples/images/UCB.gif%'

AND T3.CLASSID IN (10,76)

--Test one report at a before you do the bulk.

AND c.NAME = 'Top_Deposit_Customers'