Tag: vbs

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 (1639 downloads)
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.