Cognos 10 Report Studio Tutorial

Cognos Burst Report Dynamic Email Subject and File Name

In this article, we will learn How to Dynamically change email, mail subject, body and file name for Cognos Burst Report?
In our previous article, we learned How to Use Cognos Report Burst Option? When we used Cognos Report Burst all the recipient receive an email with the same subject, body, and file name. We want to change it and make it dynamic.

This article helps to answer following questions

  • How to change Cognos burst report file names?
  • How to change Cognos burst report email, mail subject and mail body?
  • How to use burst key in Cognos?
  • How to burst Cognos report to the file system?

 

Before Start check following videos.

  1. Configure SMTP : IBM Cognos SMTP Configuration
  2. Cognos Report Bursting
  3. Create your own SMTP Server for testing

The following image shows the before and after output.

If you need to learn Cognos Report Burst check this article.
For dynamic email, subject, body and file name first we need to modify some configuration files. If you don't have admin privileges then you need someone with IBM Cognos Administration privileges,
For the first setting open IBM Cognos Configuration. You can find it in IBM Cognos installation directory.

IBM Cognos Configuration Save Report Output to File System

Save IBM Cognos Configuration and Restart the services. We need to save the report output to file system. Above change is for the same purpose. We need to save the report outputs on file system. We can use scripts to send email for reports stored on the file system. Next, we need to edit some configuration variables. For this follow steps showed in the following images.

First, open IBM Cognos Administration. Open properties for "ContentManagerService".

Click on the Settings tab and Edit Environment.

Override settings and add two parameters with respective values. Examples are for my machine setup
1. CM.OUTPUTLOCATION <OutPut Directory where you want to save your report output>
Ex: Parameter CM.OUTPUTLOCATION Value C:\Data\Reports\Output
2. CM.OUTPUTSCRIPT <Script files we need to run after reports are save on the file system>
Ex: Parameter CM.OUTPUTSCRIPT Value C:\Data\Reports\Output\RenameReport.bat

Above two parameters help us to control report output directory and script need to run after reports are saved in the mentioned directory. Next, we need to create "RenameReport.bat" file and "RenameReport.vbs" file. The batch file called VB Script. The VB Script rename all the report output available in the directory and send the renamed file to the respective users or email id.

Following is our Report Output Directory "C:\Data\Reports\Output" with following file and folder
File      RenameReport.bat – Call VB Script file
File      RenameReport.vbs – VB Script to rename and send email
Folder  Delete – Reports were delete and store inside this for cleaning purpose.

Why batch file and VB Script? We are using windows environment and we can use both without required to install any other software. You can also use C# or Java if you want to rename and sending the emails.

Code

<p>File Name:&nbsp;RenameReport.bat</p>

<pre class="brush:as3;">
@echo off
pushd %~dp0
cscript RenameReport.vbs</pre>

<p>&nbsp;</p>

Code

<p>FileName:&nbsp;RenameReport.vbs</p>

<pre class="brush:as3;">
Dim objFSO
Set objFSO = CreateObject(&quot;Scripting.FileSystemObject&quot;)

Set xmlDoc = CreateObject(&quot;Microsoft.XMLDOM&quot;)
xmlDoc.Async = &quot;False&quot;

Recurse objFSO.GetFolder(&quot;C:\Data\Reports\Output\&quot;)
Sub Recurse(objFolder)
    Dim objFile, objSubFolder
    For Each objFile In objFolder.Files
        If LCase(objFSO.GetExtensionName (objFile.Name)) = &quot;xml&quot; Then
            xmlDoc.Load (objFile.Name)
            Set Root = xmlDoc.documentElement
            If Not Root Is Nothing Then
                Set NodeList = Root.getElementsByTagName(&quot;burstKey&quot;)
                Dim BurstKey
                For Each Elem In NodeList
                BurstKey = Elem.text
                Next
                Set NodeList = Root.getElementsByTagName(&quot;fileName&quot;)
                Dim FileName
                For Each Elem In NodeList
                FileName = Elem.text  
                Next
                &#39;MsgBox(FileExtension)
                If objFSO.FileExists(objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+FileName) Then 
                    If objFSO.FileExists(objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+replace(BurstKey, &quot; &quot;, &quot;&quot;)+&quot;Gross_Profit.&quot;+objFSO.GetExtensionName(FileName)) Then
                        objFSO.DeleteFile objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+replace(BurstKey, &quot; &quot;, &quot;&quot;)+&quot;Gross_Profit.&quot;+objFSO.GetExtensionName(FileName)
                    End If
                    objFSO.MoveFile objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+FileName, objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+replace(BurstKey, &quot; &quot;, &quot;&quot;)+&quot;Gross_Profit.&quot;+objFSO.GetExtensionName(FileName)
                    &#39;objFSO.DeleteFile objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+replace(BurstKey, &quot; &quot;, &quot;&quot;)+&quot;Gross_Profit.&quot;+objFSO.GetExtensionName(FileName)
                    &#39;MsgBox(objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+FileName)
                    Set objMsg = CreateObject(&quot;CDO.Message&quot;)
                    objMsg.Subject  = BurstKey + &quot;: Gross Profit By Country &quot;  &amp; vbTab &amp; Now()
                    objMsg.Sender = &quot;[email protected]&quot; 
                    objMsg.To =  replace(BurstKey, &quot; &quot;, &quot;&quot;)+&quot;@localhost&quot;
                    objMsg.From      = &quot;[email protected]&quot;
                    objMsg.CC         = &quot;[email protected]&quot;
                    objMsg.TextBody = &quot;Hi, Please find the attached regional report.&quot; &amp; vbTab &amp; Now()
                    objMsg.AddAttachment objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+replace(BurstKey, &quot; &quot;, &quot;&quot;)+&quot;Gross_Profit.&quot;+objFSO.GetExtensionName(FileName) &#39;NOTE: DO NOT USE AN &quot;=&quot; SIGN AFTER &quot;AddAttachment&quot;
                    objMsg.Configuration.Fields.Item(&quot;http://schemas.microsoft.com/cdo/configuration/sendusing&quot;) = 2
                    objMsg.Configuration.Fields.Item(&quot;http://schemas.microsoft.com/cdo/configuration/smtpserver&quot;) = &quot;localhost&quot;
                    objMsg.Configuration.Fields.Item(&quot;http://schemas.microsoft.com/cdo/configuration/smtpserverport&quot;) = 25
                    objMsg.Configuration.Fields.Update 
                    objMsg.Send
                    objFSO.MoveFile objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+replace(BurstKey, &quot; &quot;, &quot;&quot;)+&quot;Gross_Profit.&quot;+objFSO.GetExtensionName(FileName), objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\Delete\&quot;+replace(BurstKey, &quot; &quot;, &quot;&quot;)+&quot;Gross_Profit&quot;+sprintf(&quot;{0:yyyyMMddhhmmss}&quot;, Array(now()))+&quot;.&quot;+objFSO.GetExtensionName(FileName)
                    objFSO.MoveFile objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\&quot;+objFile.Name, objFSO.GetParentFolderName(WScript.ScriptFullName)+&quot;\Delete\&quot;+sprintf(&quot;{0:yyyyMMddhhmmss}&quot;, Array(now()))+objFile.Name
                End If
            End IF
        End If
    Next

    For Each objSubFolder In objFolder.SubFolders
        Recurse objSubFolder
    Next
End Sub




Function sprintf(sFmt, aData)
   Dim g_oSB : Set g_oSB = CreateObject(&quot;System.Text.StringBuilder&quot;)
   g_oSB.AppendFormat_4 sFmt, (aData)
   sprintf = g_oSB.ToString()
   g_oSB.Length = 0
End Function</pre>

<p>&nbsp;</p>

We can now run our report with following options

When Cognos Reports execution complete check Report Output directory.

Report output folder is empty, Why? Because of RenameReport.bat and RenameReport.vbs files. When report execution completed successfully, Cognos trigger RenameReport.bat file which triggers RenameReport.vbs. VB Script then loops through all the available files in Report Output directory and Send it to respective users. Then on success email respective file moved to Delete folder. Let's check the Delete folder.

In the delete folder, we have two sets of file one is report output which excels for the last execution and respective XML file. In XML file we have the following information for each report.

Code

<pre class="brush:as3;">
&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;
&lt;outputDescriptor
		xmlns=&quot;http://developer.cognos.com/schema/OutputDescriptor/1.0&quot;
		xmlns:SOAP-ENC=&quot;http://schemas.xmlsoap.org/soap/encoding/&quot;
		xmlns:xs=&quot;http://www.w3.org/2001/XMLSchema&quot;
		xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;
		&gt;
	&lt;tenantID&gt;&lt;/tenantID&gt;
	&lt;asOfTime&gt;2018-05-06T13:19:22.313Z&lt;/asOfTime&gt;
	&lt;burstKey&gt;United States&lt;/burstKey&gt;
	&lt;contact/&gt;
	&lt;defaultDescription&gt;&lt;/defaultDescription&gt;
	&lt;defaultName&gt;2018-05-06T13:19:38.194Z&lt;/defaultName&gt;
	&lt;fileName&gt;568_1525592978194.xlsx&lt;/fileName&gt;
	&lt;locale&gt;en-us&lt;/locale&gt;
	&lt;owner&gt;admin&lt;/owner&gt;
	&lt;ownerNamespace&gt;CognosExpress&lt;/ownerNamespace&gt;
	&lt;parameterValues/&gt;
	&lt;reportSearchPath&gt;/content/folder[@name=&amp;apos;Tutorials&amp;apos;]/report[@name=&amp;apos;Cognos Report Burst&amp;apos;]&lt;/reportSearchPath&gt;
	&lt;reportViewSearchPath xsi:nil=&quot;true&quot;/&gt;
&lt;/outputDescriptor&gt;</pre>

<p>&nbsp;</p>
<?xml version="1.0" encoding="utf-8"?>
<outputDescriptor
        xmlns="http://developer.cognos.com/schema/OutputDescriptor/1.0"
        xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
        xmlns:xs="http://www.w3.org/2001/XMLSchema"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        >
    <tenantID></tenantID>
    <asOfTime>2018-05-06T21:10:16.326Z</asOfTime>
    <burstKey>Americas</burstKey>
    <contact/>
    <defaultDescription></defaultDescription>
    <defaultName>2018-05-06T21:10:31.161Z</defaultName>
    <fileName>558_1525621231161.xlsx</fileName>
    <locale>en-us</locale>
    <owner>admin</owner>
    <ownerNamespace>CognosExpress</ownerNamespace>
    <parameterValues/>
    <reportSearchPath>/content/folder[@name=&apos;Tutorials&apos;]/report[@name=&apos;Cognos Report Burst&apos;]</reportSearchPath>
    <reportViewSearchPath xsi:nil="true"/>
</outputDescriptor>

 

In the delete folder, we have two sets of file one is report output which excels for the last execution and respective XML file. In XML file we have the following information for each report.
We are interested in burstKey and fileName. Cognos Report execution output report name is “568_1525592978194.xlsx”. We renamed it to "AmericasGross_Profit20180507024032.xlsx" based on burstKey and send it to [email protected] We can edit RenameReport.vbs to get the current email based on the burstkey.

Finally, we can check the email client for the email.

...

About The Author

Hello, I am Mayank Sanghvi I am primarily a BI developer with experience in MSBI and Cognos BI Suit. Also having interest in various other technologies such as Android, Java, C#, ASP .NET and other. I enjoy learning new technologies and share my experience with others.

0 Comments
Leave A Comment

Please login to post your valuable comments.

add