Tag: third party tools

10

Export all report XMLs to file system – Updated

| cognospaul

2 years ago, I posted a method for exporting all of the reports to the file system, using the same folder path. I was never happy with that method, as it required adding a UDF to the content store, and large reports would fail. Since getting these reports was a requirement at one of my clients, I finally had the time to tweak it.

Unlike the previous version, I don’t have access to Oracle, so this is on SQL server. The connection string to the server is handled from inside the script (row 151), all you need to do is change the data source and catalog details to connect.

The reports will be saved under c:\temp\reports. You may want to change that as well (row 244), but don’t forget that windows has an absolute limit in the number of characters in a file path.

Unlike the old version, this will also pull reports from users’ My Folders, including orphaned reports from deleted users. The user reports will be saved under \users\<user name>\my folders\. This will also generate a log file including the sql query sent to the db, and all of the reports generated.

Due to security concerns, the file has been renamed to getreports.vbs.txt. Simply rename that to getreports.vbs to enable it.

I tested this on Cognos 10.2, on SQL Server 2005. If anyone has access to Oracle and wants to adapt the SQL, I’ll  gladly amend the post to include it.

 

getreports.vbs (1611 downloads)
Uncategorized

Playing with MotioCI

| cognospaul

A few posts back I gave a sneak peek of a new tool Motio was working on. Recently I was fortunate enough to get into the beta.

The installation, for the most part, is fairly straight forward. You download, decompress, modify a few config files to declare which version of Cognos you’re running, location of your JDK. The only major stumbling block I had was Motio requires some sort of authentication against Cognos. Since I was running Cognos anonymously on my laptop, it simply wouldn’t connect. To get around this, I set up OpenDJ and was able to progress.

Once MotioCI is installed and set up, it will create an instance. Each instance is a connection to a specific Cognos server. You can have as many instances as you have Cognos gateways. You can communicate between instances. So, for example, you can a report in Instance 1 to get specific results, and compare them to results in Instance 2. This is all part of the assertions, which I will get into later.

After you create an instance, you will be prompted to create a new project. Create the new project with a descriptive name and continue. The wizard will walk you through selecting which folders you want to test against which assertions. It comes with a suite of default assertions created by Motio and the Best Practices team. Once selected, it will generate test cases and run them.

Generate Test Cases

After the test cases run, you can see which reports have failures or warnings. You can see the results of the test cases, and the outputs of the reports themselves (if the test cases required the reports to run).
failing on assertions
The most interesting part of this tool is the Assertion Studio. This is where you can define the assertions.

Assertions allow you to test almost every aspect of any Cognos object. Do you have a corporate look and feel that every report must follow? Set up a template report and compare each and every report against that. Do you want to find every report that has a run time of more than 5 minutes? Do you want to automatically compare the output of a series of reports against specific SQL queries? Are you upgrading from 8.2 and need to find all instances of the old JS API? Do you want to test your dispatchers for certain settings and response times? The possibilities are endless.

When you design your own assertions you can specify whether the report needs to be executed or not. Executing the report allows you to check things like run time, or results match. Does the third row in the list match the first row of another report? Not executing allows you to use combinations of xpath and regex to parse the report (or model) xml. You might use that to find all reports that contain “formwarprequest” in HTML items.

There is a bit of a learning curve when it comes to MotioCI. It is definitely not something you’d give end users. It does seem to be an invaluable tool for administrators.

You can read more about it at the Motio Website.

Uncategorized

8.2: Export all report XMLs to file system

| 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.