8.2: Export all report XMLs to file system

March 4, 2012March 25, 2014 | cognospaul

One of my clients decided they want to maintain a local copy of the folder structure and report XMLs. So if they had report “Sales” under “Public Folders\Sales\Western Europe\” they would want a copy at “c:\Cognos\Sales\Western Europe\Sales.xml”. They don’t have any of the third party tools like Meta Manager or Motio, so they need to do everything manually.

While I’m not a scripting expert, I have done some very minor work with vbscript. With vbscript I could open a spreadsheet, copy cell values, and create the file in the appropriate place.

The first thing to do is to create the report that will be used as the base for the script.

The report should be a simple list that contains the report path, report name and the XML. In order to do this we to create a datasource connection to the Cognos content store. Once that’s done a small change needs to be made to the database. Cognos stores report details with the folder ID it’s in. The folder ID contains a reference to the parent folder, but without creating a loop it will be impossible (not really, but I’m lazy) to get the full folder path.

Instead we can create a User Defined Function in the content store. The UDF is database dependent, so it will need to be modified when working with another database (like SQL Server or DB2) or when working against a different version (8.4, 10).

I found this UDF at Mogawad Blog:

CREATE OR REPLACE FUNCTION C8.getpath (p_pcmid IN NUMBER)
RETURN NVARCHAR2
IS
l_parentname   NVARCHAR2 (100);
l_pcmid        Number;
BEGIN
IF p_pcmid = 0
THEN
RETURN 'root';
ELSE
SELECT n.NAME object_name, o.PCMID
INTO l_parentname, l_pcmid
FROM cmobjnames n, cmobjects o
WHERE o.cmid = n.cmid AND o.cmid = p_pcmid and isdefault=1;
END IF;

RETURN getpath (l_pcmid)||'\'||l_parentname;
EXCEPTION
WHEN OTHERS
THEN
RETURN sqlerrm;
END getpath;

With this UDF saved in the content store it is now possible to write the following SQL in RS:

SELECT n.NAME object_name, getpath (o.pcmid) path, c.spec
FROM cmobjnames n, cmobjects o, cmobjprops7 c, cmclasses cls
WHERE o.cmid = n.cmid
AND o.cmid = c.cmid
AND o.classid = cls.classid
AND isdefault = 1
AND UPPER (cls.NAME) = 'REPORT'
and getpath (o.pcmid) like 'root\Public Folders\Sales%'

Before we can run the report, it’s important to mention that some reports XMLs can be very long. The longest report at this client is roughly 650000 characters long. Some of my other clients have significantly larger reports. Because the vbscript uses Excel to access the data, we’d run into a problem. Excel can’t handle large fields – it would simply truncate them. Inside the query, it’s necessary to create seperate data items to split the xml. substring([SQL1].[SPEC],1,30000), substring([SQL1].[SPEC],30001,30000), substring([SQL1].[SPEC],60001,30000) and so on. The report I mentioned before took 22 of these fields.

Finally, inside the list itself, wrap each of the fields with quotes. Cognos delimited the fields with tabs, and considering that the XML might have tabs and newlines embedded inside it, it’s best to put a delimited that Excel can easily recognize.

Report XML below:


	/content/package[@name='Agg CN ISUP Quality 15 Min']/model[@name='model']




 """"""""""""""""""""""""""""""""""""""""""""""""""""""






 SELECT n.NAME object_name, getpath (o.pcmid) path, c.spec
FROM cmobjnames n, cmobjects o, cmobjprops7 c, cmclasses cls
WHERE o.cmid = n.cmid
AND o.cmid = c.cmid
AND o.classid = cls.classid
AND isdefault = 1
AND UPPER (cls.NAME) = 'REPORT'
and getpath (o.pcmid) like 'root\Public Folders\Sales%'


 [SQL1].[OBJECT_NAME][SQL1].[PATH]substring([SQL1].[SPEC],1,30000)substring([SQL1].[SPEC],30001,30000)substring([SQL1].[SPEC],60001,30000)substring([SQL1].[SPEC],90001,30000)substring([SQL1].[SPEC],120001,30000)substring([SQL1].[SPEC],150001,30000)substring([SQL1].[SPEC],180001,30000)substring([SQL1].[SPEC],210001,30000)substring([SQL1].[SPEC],240001,30000)substring([SQL1].[SPEC],270001,30000)substring([SQL1].[SPEC],300001,30000)substring([SQL1].[SPEC],330001,30000)substring([SQL1].[SPEC],360001,30000)substring([SQL1].[SPEC],390001,30000)substring([SQL1].[SPEC],420001,30000)substring([SQL1].[SPEC],450001,30000)substring([SQL1].[SPEC],480001,30000)substring([SQL1].[SPEC],510001,30000)substring([SQL1].[SPEC],540001,30000)substring([SQL1].[SPEC],570001,30000)substring([SQL1].[SPEC],600001,30000)substring([SQL1].[SPEC],630001,30000)substring([SQL1].[SPEC],660001,30000)substring([SQL1].[SPEC],690001,30000)substring([SQL1].[SPEC],720001,30000)

Run it as CSV. Depending on the number of reports you’re retrieving, I recommend stopping here to take a break. Get a coffee or something. Once it finishes running and you save it, check it to make sure it’s worked correctly. The first column should be the Report name, the second the full path. Every field onwards contains the xml.

Now for the script. As I said before, I’m not a scripting expert. Save it as whatever.vbs, and drag the csv export to it. It should loop through, creating directories and report xmls.

vbscript below:

Option Explicit

Dim objExcel, objWorkbook, objWorksheet, row, rows, col, msg, ThisTxt, ThisLen, ctrCells, objFSO, Path
Dim ipFN, objFileName, objTextFile, ipLen, opLen, WshShell, ReportFullPath
Dim i
Const swDebug = false

If Wscript.Arguments.Count = 0 then
  wscript.echo "Please drag a file to the icon!"
  wscript.quit
end if

msg = ""
i = 0

' Start the application
IpFN = trim(wscript.arguments(0)) ' Complete input file name
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.visible = false ' make Excel invisible
Set objWorkbook = objExcel.Workbooks.Open(IPFN)
Set objWorksheet = objWorkbook.Worksheets(1) 'rows = objWorksheet.UsedRange.Rows.Count

for Row = 2 to rows
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  Path = strCleanPath( Replace(objWorksheet.Cells(Row,2),"root\Public Folders\",""))
  Path = "c:\" & Path
  CreateFolderTree(Path)
  ReportFullPath = Path & "\" & strClean(objWorksheet.Cells(Row,1).Value)
  ReportFullPath = left(ReportFullPath,255)
  objFileName = ReportFullPath & ".xml"
  Set objTextFile = objFSO.CreateTextFile (objFileName, True, True)
  objTextFile.WriteLine(objWorksheet.Cells(Row,3).Value&objWorksheet.Cells(Row,4).Value&objWorksheet.Cells(Row,5).Value&objWorksheet.Cells(Row,6).Value&objWorksheet.Cells(Row,7).Value&objWorksheet.Cells(Row,8).Value&objWorksheet.Cells(Row,9).Value&objWorksheet.Cells(Row,10).Value&objWorksheet.Cells(Row,11).Value&objWorksheet.Cells(Row,12).Value&objWorksheet.Cells(Row,13).Value&objWorksheet.Cells(Row,14).Value&objWorksheet.Cells(Row,15).Value&objWorksheet.Cells(Row,16).Value&objWorksheet.Cells(Row,17).Value&objWorksheet.Cells(Row,18).Value&objWorksheet.Cells(Row,19).Value&objWorksheet.Cells(Row,20).Value&objWorksheet.Cells(Row,21).Value&objWorksheet.Cells(Row,22).Value&objWorksheet.Cells(Row,23).Value&objWorksheet.Cells(Row,24).Value&objWorksheet.Cells(Row,25).Value&objWorksheet.Cells(Row,26).Value)
  objTextFile.close
next

objExcel.Application.quit ' Close the spreadsheet

Set objWorksheet = nothing
Set objWorkbook = nothing
Set objExcel = nothing

  wscript.echo "Done!"

'--------------------
'- Create Folder Path
'--------------------
Sub CreateFolderTree(strTempPath)
   If Not objFSO.FolderExists(objFSO.GetParentFolderName(strTempPath)) Then CreateFolderTree(objFSO.GetParentFolderName(strTempPath))
   If Not objFSO.FolderExists(strTempPath) Then objFSO.CreateFolder(strTempPath)
End Sub

Function strClean (strtoclean)
  Dim objRegExp, outputStr
  Set objRegExp = New Regexp

  objRegExp.IgnoreCase = True
  objRegExp.Global = True
  objRegExp.Pattern = "[(?*"",\\<>&#~%{}+_.@:\/!;]+"
  outputStr = objRegExp.Replace(strtoclean, "-")

  objRegExp.Pattern = "\-+"
  outputStr = objRegExp.Replace(outputStr, "-")

  strClean = outputStr
End Function

Function strCleanPath (strtoclean)
  Dim objRegExp, outputStr
  Set objRegExp = New Regexp

  objRegExp.IgnoreCase = True
  objRegExp.Global = True
  objRegExp.Pattern = "[(?*"",<>&#~%{}+_.@:\/!;]+"
  outputStr = objRegExp.Replace(strtoclean, "-")

  objRegExp.Pattern = "\-+"
  outputStr = objRegExp.Replace(outputStr, "-")

  strCleanPath = outputStr
End Function

Important to note: if the length of the report name plus path exceeds 255 characters, it will truncate it.

10, 8.4, Internals

Hiding Cognos Connection Elements

September 11, 2011May 5, 2014 | cognospaul

Hiding Cognos Connection Elements

By default Cognos offers a ride range of UI options. Based on the group or role you can programmatically decide who can see which UI Elements. A simple modification of the system.xml file is all that is needed to take care of this.

The Admin and Security guide, which can be found in the \webcontents\documentation\en\ug_cra.pdf, has an excellent article on how to accomplish this. You can also find the article online here.

The full list of elements that you can hide can also be found in the guide, or online here.

While those links are for 8.4, they are essentially identical for 8.4.1 and 10.1.

But what happens when you have a requirement to remove elements that don’t appear in the list? This is possible by modifying one of the template files that controls the structure of the portal. These files are saved as XSL files, and are easily modified using any text editor. I strongly recommend using a text editor with XML support, such as Notepad++.

Before I continue, it’s worth mentioning the following. Changing these files may be risky. A misplaced semicolon will prevent Cognos from loading, and IBM has a tendancy not to support installations that have modified the Cognos internals. These changes will also be overridden by any patches or upgrades.

Always make a backup of any files you modify. When trouble does occur and you need IBM’s help, simply switch the active files with the backup, and IBM will be none the wiser (and if the problem is fixed, you know where to look).
You should also maintain a change log of everything you do. Upgrades will replace the template files, and there may be differences between the versions. Instead of simply overwriting the upgraded version with your modified version, you should make all the changes again manually. Fortunately (unfortunately?) patches and version upgrades tend to be rare occurrences.

A brief explanation of the files to modify:

There are two primary XSL files which control the portal.
1. \templates\ps\logicsheets\presentation\controls\presentation.xsl
2. \templates\ps\logicsheets\presentation\main\presentation.xsl

The \controls\presentation.xsl renders, among other things, the HTML behind tabs, and the rows and columns in the Public Folders/My Folders table.

The \main\presentation.xsl renders the links to the correct style sheets, the page headers, and the individual links in the Public Folders/My Folders table.

Between the two, they control the HTML between most of the objects you see on the page.

The system.xml file that contains the UI black list can be found in \templates\ps\portal\system.xml

And now the problem. The client has decided that no user, except administrators and report authors, should be able to see the Properties or More… links for any reports or folders.

First, hiding the properties.

In the \main\presentation.xsl do a search for action_properties.gif. There should be four instances of that string. Each of these instances controls the properties for a different type of object. Series 7 object, CRN object, Job and… well, I’m not entirely sure what the fourth one is for, but I’m sure it’s very important. 5 points to whoever enlightens me.

In each of these four cases the action_properties.gif is part of a xsl:call-template reference.

This statement is calling the renderActions template with values for the onclick, icon and tooltip parameters.

In order to prevent this segment from being rendered we need to add it to the “Elements you can hide” list. The code that controls items on the list is as follows:

<out:if test="not(contains($ui_black_list, 'NAME'))">			
</out:if>

This will check the system.xml for an appearance of NAME in the ui_hide list. Should it appear, it will not render anything inside the code block except for users or groups listed in the show parameter. Knowing this, all that is needed to hide the properties is to add the black list code to all four appearances of the renderAction call.

		<out:if test="not(contains($ui_black_list, ' PROPERTIES '))">			
			<xsl:call-template name="renderAction">
				<xsl:with-param name="onclick">actions('{xtsext:javascriptencode(string($obj-name))}', '{$obj-class}', '{xtsext:javascriptencode(string($obj-path))}','properties_general.xts');</xsl:with-param>
				<xsl:with-param name="icon" select="'action_properties.gif'"/>
				<xsl:with-param name="tooltip" select="'IDS_PROPERTIES'"/>
			</xsl:call-template>
		</out:if>

In this case, I set the black list name to PROPERTIES. Now, in the system.xml simply add the following to the ui_hide param:

	<PROPERTIES show ="Administrators RSUsers"/>

When you restart, only administrators and authors will be able to see the properties for any object.

Now for the More…

The technique to hide the “More…” link is almost identical.

Simply find all occurences of IDS_ACT_MORE in the same file. In my modified file all occurences appear between lines 4640 and 4722. Thanks to Notepad++’s XML support, I can see that all of these are held inside a single group.

Instead of black listing each individual appearance of the More… link, let’s try black listing the entire group.

Now to add MORE to the system.xml black list

As before, only administrators and authors will be able to see the More… link.

The following shows how folders look to normal users:

And this is what reports look like:

It’s very important to do extensive tests to ensure this meets the client needs. You could probably also reverse the black list by getting rid of the “not()” from the code. Remember that the idea behind this article is not to simply show how to hide specific elements from the Cognos Connection. It’s to show how easily it is to shape Cognos to meet your needs. The XSL sheets are there to be modified.

10, 8.4, Internals

Adding a Favicon

March 22, 2011March 25, 2014 | cognospaul

Favicons are the little images you have to the left of the URL in the URL bar. If you’re reading this through WordPress you should see a white “W” inside a blue circle. That is the WordPress Favicon.

The absolutely easiest way to add a Favicon is to add the image to the root folder of the web server. If you’re using IIS the default would be “C:inetpubwwwroot”. Add the Favicon, flush the cache and reload Cognos. You should now see the Favicon.

This works great, but what happens if you have more than one site, or if you want different Favicons for different skins? You’ll need to modify the XSL.

While Cognos can (and probably should) be used with Firefox, a majority of users still use IE. So the favicon must be in the Microsoft Icon filetype (.ico).

To begin, decide which skin has what favicon and place them under ..portalimages for each skin.

Next backup and open ..templatespslogicsheetspresentationmainpresentation.xls

Search for “mt:stylesheet”. It should be on line 68. I’ll be entirely honest, I don’t know what this choose expression is testing. Either way, add the following line after the last stylesheet link in the “otherwise group”

<link rel="shortcut icon" href="{'{$skin_images}'}favicon.ico"/>

Restart Cognos and check if it works.

I’ve added the IBM favicon to the business skin and added the portal to my favorites:

Now I’ve added the Google favicon to the classic skin, and switched to it:

The favicons in the tab and the URL bar are immediately updated. However the favicons in the favorites will stay with the previous icon. Easy enough to fix – delete and re-add the favorite.

A few postscripts:
It’s worth noting that while this was testing on Cognos 10, I have no reason to believe it would not work on Cognos 8.
This method also relies on modifying Cognos internals. This will need to be redone every time you upgrade.
Also, make sure you make a backup of any file you modify.

CognosPaul

Tag: administration

8.2: Export all report XMLs to file system

Hiding Cognos Connection Elements

Adding a Favicon