COGNOS Tutorials

Cognos: to_char functionality without using to_char

2014-10-29T09:42:00.002-07:00

Some times to_char function may not work in cognos to change the date format into required format

for example cognos by default shows the oracle date into yyyy-mm-dd format by I need to show the date in mm-dd-yyyy format. for that use the below expression..

CASE
WHEN EXTRACT(MONTH, [Date Field]) < 10
THEN
'0' ||
CAST(EXTRACT(MONTH, [Date Field]),VARCHAR(1))
ELSE
CAST(EXTRACT(MONTH, [Date Field]),VARCHAR(2))
END
|| '-' ||

CASE
WHEN EXTRACT(DAY, [Date Field]) < 10
THEN
'0' ||
CAST(EXTRACT(DAY, [Date Field]),VARCHAR(1))
ELSE
CAST(EXTRACT(DAY, [Date Field]),VARCHAR(2))
END

|| '-' ||

CAST(EXTRACT(YEAR, [Date Field]),VARCHAR(4))

Breadcrumbs in cognos using DMR package

2014-09-18T08:48:00.001-07:00

Breadcrumbs in cognos using DMR package

Follow the below steps...

Version 10.2, Package: Go Dataware house(analysis) package

This example uses the Sales and Marketing (cube) sample Package.

Step 1.

Create a blank report and add a table with 2 rows and 1 column and add 2 crosstabs, one in the top row, one in the bottom. Make sure both crosstabs Query are based on Query1. This can be done by selecting the crosstab and looking at the Properties tab under Data.

Step 2.

Select the Query1 in Query Explorer. Under the Properties pane, set Define Member Set to Yes. This will add a tab at the bottom of the Query1 main window, the added tab is labeled “Member Sets,” this is found next to Projected Data Item, we will use this later.

Step 3.

Add the Sales->Time->Year(level), Sales->sales fact->Revenue and Sales->Products->membersfolder->Products (this is found in the Members folder in Products.Products) to Query1.

Step 4.

Add a new data item, the expression will be “ancestor([Products],0)”, call this ancestor 0, for to make it easier to find. Create 4 more new data items or make a copy of ancestor 0, each of these data items will have an expression similar to ancestor 0, each will be labelled with a number from 1 to 4, each having ancestor([Products],#) where # is it's number.

Step 5.

Create a new data item, this data items expression will be “children([Products])”. This can be named child.

Step 6.

Click the Member Sets tab. Double click the Products data item. Grab ancestor 0 and drag it onto Products in the main window, this will add ancestor 0 as part of Products, if done correctly you will see a minimize icon next to Products, which will hide ancestor 0 if clicked. Add all the other ancestors and the 1 child data item under ancestor 0, again, if the minimize icon is clicked, the child and all the ancestor items will be hidden.

Step 7.

In the menu bar at the top, click Data, then Drill Behavior. Click Enable drill-up and drill down.

Step 8.

Go to the Advanced tab in the Drill Behavior menu. Click the Products data item, and set both drill-up and drill-down behavior to Replace Item. For the child and all ancestor data items, set drill-up and drill-down behavior to Preserve.

Step 9.

On the bottom crosstab, add the child data item to Rows on the crosstab, add year to the Columns, add revenue to the Measure.

Step 10.

On the top crosstab, add ancestor 4 to ancestor 0, in that order, to the Columns section.

Do not add anything to the measures or rows section.

to test copy the following code and open in RS (tools menu->open repor from clipboard)

<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Data Warehouse (analysis)']/model[@name='model']</modelPath>

<style>

</defaultStyles>

</style>

<style>

</defaultStyles>

</style>

<contents><textItem><dataSource><staticValue>Level: </staticValue></dataSource></textItem></contents>

<style>

</defaultStyles>

</style>

</crosstabCorner>

<style>

</defaultStyles>

</style>

<style>

</defaultStyles>

</style>

</crosstabCorner>

<style>

</defaultStyles>

</style>

</pageBody>

</page>

</reportPages>

</layout>

</layouts>

<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" output="no" value="true"/><XMLAttribute name="listSeparator" output="no" value=","/><XMLAttribute name="RS_modelModificationTime" value="2013-01-08T15:38:02.967Z" output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItemMember name="Products"><dmMember><MUN>[Sales].[Products].[Products].[Products]->[all]</MUN><itemCaption>Products</itemCaption></dmMember><dmDimension><DUN>[Sales].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemMember><dataItem name="ancestor0"><expression>ancestor([Products],0)</expression></dataItem><dataItem name="ancestor1"><expression>ancestor([Products],1)</expression></dataItem><dataItem name="ancestor2"><expression>ancestor([Products],2)</expression></dataItem><dataItem name="ancestor3"><expression>ancestor([Products],3)</expression></dataItem><dataItem name="ancestor4"><expression>ancestor([Products],4)</expression></dataItem><dataItem name="Children"><expression>children([Products])</expression></dataItem><dataItemLevelSet name="Year"><dmLevel><LUN>[Sales].[Time].[Time].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[Sales].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[Sales].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>[Sales].[Sales fact].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[Sales].[Sales fact]</DUN><itemCaption>Sales fact</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/></XMLAttributes></dataItemMeasure></selection><memberSetStructure><memberSet name="Products" refDataItem="Products"><memberSets><memberSet name="ancestor0" refDataItem="ancestor0"/><memberSet name="ancestor1" refDataItem="ancestor1"/><memberSet name="ancestor2" refDataItem="ancestor2"/><memberSet name="ancestor3" refDataItem="ancestor3"/><memberSet name="ancestor4" refDataItem="ancestor4"/><memberSet name="Children" refDataItem="Children"/></memberSets></memberSet></memberSetStructure></query></queries><reportName>BreadCrumb</reportName><drillBehavior drillUpDown="true" modelBasedDrillThru="true"><drillQueryBehavior refQuery="Query1"><drillDataItemBehavior refDataItem="Products"><drillUpBehavior><drillSimpleType type="replaceItem"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="replaceItem"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor0"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor1"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor2"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor3"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor4"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="Children"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior></drillQueryBehavior></drillBehavior></report>

Cognos: Mouse over pop up with our own text box using java script

2014-09-08T01:27:00.002-07:00

Cognos: Mouse over pop up with our own text box using java script

Some times we need a popup message box when we over the mouse on some image like below..

Take Html object into RS

paste the following code

<style>
.tooltip
{
text-decoration:left;
position:relative;
}

.tooltip span
{
display:none;
-moz-border-radius:6px;
-webkit-border-radius:6px;
border-radius:6px;
color:black;
background:#f2f2f2;
}

.tooltip span img
{
float:left;
margin:0px 8px 8px 0;
}

.tooltip:hover span
{
display:block;
position:absolute; top:-40px; left:5px;
width:300px;
height:75px;
max-width:400px;
min-height:75px;
border:1px solid #888888;
margin-top:12px;
margin-left:32px;
overflow:hidden;
padding:8px;
}

</style>

<a class="tooltip" href="#" style="border:none">
<img src="../samples/images/red.jpg" style="border:0px solid #cc3300;" />
<span style="text-align=left">
Insert definition here
</span>
</a>

you can put your own image in the above script

IE 7 or Higher versions Cognos Connection issue - Undefined Popup

2014-06-20T07:06:00.002-07:00

1) Open IE

2) Click on Tools

3) Compatibility View Settings

4) Click on ADD

5) Enter http://locahost/Ibmcognos or simply http://localhost

6) click OK..

Autosubmit Cognos prompt page using java script

2014-06-07T08:49:00.000-07:00

Take HTML to prompt page and paste the following code

<script type="text/javascript">

//get the form request
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);

if (fW)
{
// if the report is running from reportStudio or the Portal objects are different
if (fW.elements["cv.id"].value == "RS")
{
setTimeout('oCVRS.promptAction(\'next\')', 1000);
}else{
setTimeout('oCV_NS_.promptAction(\'next\')', 1000);
}
}
</script>

Hide tool bar in cognos viewer

2014-06-07T08:46:00.001-07:00

1) Take HTML drop in Header

paste the following code

<script language="JavaScript">

var y=getFormWarpRequest().elements["cv.id"];

// RS means it has been launched from Report Studio
// _NS_ means it has been launched from Cognos Connection
if(y.value == "RS") {
window.oCVRS.rvMainWnd.setToolbar(
{
"namespace":"RS",
"divId":"CVToolbarRS",
"style":"toolbar","S":[
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"openWith",
"M":
{"id":"openWithwindow.oCVRS",
"Y":"dropDown",
"H":"true",
"IS":[]}
}
}
]
}
);
}else{
window.oCV_NS_.rvMainWnd.setToolbar(
{"namespace":"_NS_",
"divId":"CVToolbar_NS_",
"style":"toolbar",
"S":[
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"openWith",
"M":
{"id":"openWithwindow.oCV_NS_",
"Y":"dropDown",
"H":"true",
"IS":[]}
}
}
]
}
);
}

</script>

2014-01-06T22:36:00.001-08:00

Cognos: Hide excel/xml/csv output options in cognos viewer using java script

create HTML item in header/footer of the report and copy and paste the following code

<script language="JavaScript">
var y=getFormWarpRequest().elements["cv.id"];

// RS means it has been launched from Report Studio
// _NS_ means it has been launched from Cognos Connection
if(y.value == "RS") {
window.oCVRS.rvMainWnd.setToolbar(
{
"namespace":"RS",
"divId":"CVToolbarRS",
"style":"toolbar","S":[
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"runIn",
"ALT":"Change report format",
"M":
{"id":"runInwindow.oCVRS",
"Y":"dropDown",
"IS":[
{"I":
{"N":"viewInHTML",
"A":"window.oCVRS.getRV().viewReport(\'HTML\');",
"E":"View in HTML Format",
"C":"/ibmcognos/rv/images/action_view_html.gif"}
},
{"I":
{"N":"viewInPDF",
"A":"window.oCVRS.getRV().viewReport(\'PDF\');",
"E":"View in PDF Format",
"C":"/ibmcognos/rv/images/action_view_pdf.gif"}
}
]
}
}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"openWith",
"M":
{"id":"openWithwindow.oCVRS",
"Y":"dropDown",
"H":"true",
"IS":[]}
}
}
]
}
);
}else{
window.oCV_NS_.rvMainWnd.setToolbar(
{"namespace":"_NS_",
"divId":"CVToolbar_NS_",
"style":"toolbar",
"S":[
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"runIn",
"ALT":"Change report format",
"M":
{"id":"runInwindow.oCV_NS_",
"Y":"dropDown",
"IS":[
{"I":
{"N":"viewInHTML",
"A":"window.oCV_NS_.getRV().viewReport(\'HTML\');",
"E":"View in HTML Format",
"C":"/ibmcognos/rv/images/action_view_html.gif"}
},
{"I":
{"N":"viewInPDF",
"A":"window.oCV_NS_.getRV().viewReport(\'PDF\');",
"E":"View in PDF Format",
"C":"/ibmcognos/rv/images/action_view_pdf.gif"}
}
]
}
}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"openWith",
"M":
{"id":"openWithwindow.oCV_NS_",
"Y":"dropDown",
"H":"true",
"IS":[]}
}
}
]
}
);
}

</script>

COGNOS: Add a Multimedia File to a Report

2013-11-25T05:44:00.001-08:00

Place your Video ( .wmv or .avi format) in the following path

C:\Program Files\ibm\cognos\c10\webcontent\samples\images\

Note: You must have Windows Media Player installed on your computer.

In the Insertable Objects pane, on the Toolbox tab, drag the HTML Item object to the report.
Select the HTML Item.
double-click the HTML Item
In the HTML dialog box, type the following:

<OBJECT classid="CLSID:6BF52A52-394A-11D3-B153-00C04F79FAA6"> <PARAM NAME="URL" VALUE="/c10/webcontent/samples/images/yourVideo.wmv"/> </OBJECT>

Run the report and click on Video and enjoy...

Cognos: Age calculation

2013-11-18T04:59:00.000-08:00

We have two ways to calculate age in years and age in yy years mm months and dd days format

Calculate Age in years...

Create a query Calculation as below...

_years_between (current_date, [Birth date column])

Calculate Age in yy years, mm months and dd days format...
(replace [Birth Date] with your own Data of birth column)

substring (cast(_ymdint_between (current_date, [Birth date]),char(6)),1,2) || ' Years '||

substring (cast(_ymdint_between (current_date, [Birth date]),char(6)),3,2)

|| ' months ' ||

substring (cast(_ymdint_between (current_date, [Birth date]),char(6)),5,2)

|| ' Days '

cognos : 100% stacked Bar chart displaying percentages in bars using Java Script

2013-11-14T03:30:00.001-08:00

We can't display percentages in middle of the bars in cognos 10.2, 100% stacked bar chart. But the same has been fixed in Cognos 10.2.1.

We can do the same thing using Java Script. just follow the below steps..

Suppose we have the following columns in List Report..

Region, Actual, 100% stacked bar chart with percentages, Budget as follows..

1) take the columns Region, Actual, and budget into List Report

2) goto Query Explorer

3) Open Query1

4) Add Data Item, enter the following expression to calculate Actual percentage over total of actual and budget

[actual]/([actual]+[Budget])* 100

5) Name this data Item in properties as "actp'

6) Add another Data Item to calculate Budget percentage and enter the below expression

[Budget]/([actual]+[Budget])* 100

7) Name this data Item in properties as "budp'

8) Goto Page explorer and select Page1 to see the list report

9) Drag one HTML item and drop between the columns Actual and Budget as a column

10) Click LOCK icon in tool bar to unlock

11) Add 4 more HTML items next to previously added HTML item, so you should have 5 HTML items

12) double click First HTML item and add the following script

var bar1Percent =

13) Click OK to close the HTML box

14) Just click to second HTML item to select (dont Open)

15) Goto properties, got to the property "Source Type"

16) Change to "Data Item Value"

17) For the Property Data Item Value, select the column "actp"

18) Double click 3rd HTML item and enter the following expression (dont forget semicolon as first line)

;

var bar2Percent =

19) Select 4th HTML item, goto properties and change the following propertiesr

Source Type: Data Item Value

Data Item Value: budp (this is data item that we have created in 6 the step)

20) double click 5th HTML item and enter the following script as it is (dont forget about semicolon in first line)

;

var bar1=bar1Percent;

var bar2=bar2Percent;

var barL=bar1Percent+bar2Percent;

var width_driver = 200;

var barheight = ((width_driver) / 10);

var myTotalBar = '<div style="background-color:red;width:' + barL + ';height:' + barheight + '">';

var percent1 = '<div style="background-color:#F15A29; font-family:Source Sans Pro; font-size:9px; color:white; float:left; text-align:center; display:table-cell; vertical-align: middle; width:' + bar1Percent + ';height:' + barheight + '"> <span style="display:table-cell; width:inherit;height:inherit; vertical-align: middle"> <strong>' + bar1.toFixed(1) + '%</strong></span></div>';

var percent11 = '<div style="background-color:#F15A29; font-family:Source Sans Pro; font-size:9px; color:white; float:left; text-align:center; display:table-cell; vertical-align: middle; width:' + bar1Percent + ';height:' + barheight + '"> <span style="display:table-cell; width:inherit;height:inherit; vertical-align: middle"> </span></div>';

var percent2 = '<div style="background-color:#2B3990; font-family:Source Sans Pro; font-size:9px;color:white; float:right;text-align:center; width:' + bar2Percent + ';height:' + barheight + '"> <span style="display:table-cell; height:inherit; width:inherit; vertical-align: middle"> <strong>' + bar2.toFixed(1) + '%</strong></span></div>';

var percent22 = '<div style="background-color:#2B3990; font-family:Source Sans Pro; font-size:9px;color:white; float:right;text-align:center; width:' + bar2Percent + ';height:' + barheight + '"> <span style="display:table-cell; height:inherit; width:inherit; vertical-align: middle"> </span></div>';

if (bar1Percent<20)

{

percent1=percent11;

}

if (bar2Percent<20)

{

percent1=percent22;

}

myTotalBar = myTotalBar + percent1 + percent2 + '</div>';

document.write(myTotalBar);

</script>

You will get the output as below...

Note: This chart does not show percentage on bar if percentage < 20 as the width bar is very low so we can't show text on it.

Please note that, we can do the same thing using Micro Chart, but we will get extra space around the bar therefore, the lines appear with unnecessary space that we can't avoid

filter a report based on comma separated values entered in a text box prompt

2013-06-18T23:32:00.000-07:00

Create a text box prompt with parameter like 'p'

enter the filter expression in detail filter as follows:

cast(column name,varchar(200)) in ( #csv ( split(' ', split(',', split(' ,', split(' , ', split(', ', prompt('p','token') ) ) ) ) ) ) # )

Cognos insight free download

2013-05-17T01:48:00.001-07:00

Now you can download IBM Cognos insight 10.2 personal edition free from the following link

https://www.analyticszone.com/

1) click download

2) select IBM cognos insight personal edition

3) register (free)

4) download

IBM Cognos Perfomance tips

2013-03-26T02:23:00.000-07:00

IBM has uploaded several videos which walk you through some of the Tips and techniques to achieve Maximum performance

IBM Cognos BI - Designing Reports for Performance - Part 5
http://www.youtube.com/watch?v=c170hFlmdWA

IBM Cognos BI - Designing Reports for Performance - Part 1
http://www.youtube.com/watch?v=OiRvUrUTs-c

IBM Cognos BI - Designing Reports for Performance - Part 3
http://www.youtube.com/watch?v=zLS26-zKsCI

IBM Cognos BI - Designing Reports for Performance - Part 2
http://www.youtube.com/watch?v=QiIIPlaCMOU

IBM Cognos BI - Designing Reports for Performance - Part 4
http://www.youtube.com/watch?v=XbSbXoxxzeA

Cognos Schedule report will be not delivered issue?

2013-03-25T11:50:00.000-07:00

Some times Cognos sccheduled Report output attachment will not be delivered even the the output of the report is around 1.5 MB.

Resolution:

Check your mail server for incoming/outgoing attachment size limit
(you can't do it, ask your admins)

How to burst a crosstab report

2013-03-22T05:25:00.002-07:00

Resolving the problem

Add the burst key to a data container with one dimension, such as a list report. To burst a data container with multiple dimensions such as a chart or crosstab, create a master-detail relationship between this query and a single dimensional query, and then add the burst key to the single dimensional query.

Drop the crosstab within a list object that contains the burst key. Please see the attached Cognos 8.2 report spec going against the GO Sales and Retailers package.

Create a list report with the data item (Staff Name (Multiscript)) that you want to burst on as your first column. This will be the master list.
Insert your chart or crosstab as the second column of the list. This automatically creates a second query, which will be the detail query.
Add data items to your crosstab as needed. (Order Method as Rows, Order Year as Column, Quantity as Measure)
In the list, create a section with the item you want to group on. This is for formatting only.
Hide the list column title under the List property General -> Column Titles -> Hide.
In the Query Explorer, add the burst key data item that is in Query1 to Query2 (Staff Name and Email)
Select a crosstab cell and use the ancestor tool to move up to 'Crosstab' In the properties for the crosstab, edit the Master Detail Relationship property to link the burst key in both queries. (Staff Name)
Click on the row section of the crosstab (Order Method in the example provided). In the properties pane under Data, open the Properties dialog and click on the box for the burst key so it is checked.
Set Burst Options to burst on the list object's burst key.
Burst the report and view the results.

Source: http://www-01.ibm.com/support/docview.wss?uid=swg21341874

Cognos Schedule Issue

2013-03-22T05:21:00.002-07:00

Some times Cognos Scheduled jobs may not run even though we do not either disable it or modify it.

The reason is,, if password is expired of the user whoever scheduled the job, then Cognos schedules stop working,

Resolution:

Very simple, The person who have scheduled the jobs has to login into the cognos once with new password and run the schedule manually once from Cognos administration.

Cognos: Bursting Report to a file system

2012-07-25T03:18:00.000-07:00

Bursting cognos report to Local File system and also run a script so that bursted reports can be renamed.

1) Go to the dispatcher -- ( See the picture below for reference )

2) Click on the dispatcher link to see the content manager service.

3) Go to the settings tab and click on edit on advance setting line -

4) Create following 2 variables and provide the path where you want to get the files bursted.

5) Save it and go to cognos configuration to do the following setting.

6) Run the report that you have to burst with the following settings.

7) You will get the output in D:/Report folder, now you want that bursted reports should get proper names or they should get names according to there bursted key.

8) Create a notepad file giving it a name "burstKey.bat" and paste the following contents and save it in D:\Reports folder.

@echo off

Rem this batch file is launched with a working directory of \bin

set dest=D:/Reports

echo "BurstKey Script" >> %dest%/batch.txt
echo Batch File Log >> %dest%/batch.txt

rem       Set the values for the PDF file and XML
rem       to environment variables
rem       the reason for this is the scripting language
rem       cannot read variables as parameters
set parameter_pdf=%1%
set parameter_xml=%2%

rem      Logs the variables to a batch log file
echo values set >> %dest%/batch.txt
echo Parameter_pdf: %parameter_pdf% >> %dest%/batch.txt
echo Parameter_xml: %parameter_xml% >> %dest%/batch.txt

rem      Calls the script file
call %dest%/burstKeyRename.vbs

echo Completed >> %dest%/batch.txt

9) create a file "burstKeyRename.vbs" and paste the following contenets in there and save it in D:\Reports folder.

'
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set xmlDoc = CreateObject("MSXML.DOMDocument")
Set WshShell = WScript.CreateObject("WScript.Shell")
Set WshSysEnv = WshShell.Environment("process")

' Values for Environment can be "user", "system", "volitile" and "process
' The value "process" is the only valid one for this type of operation.
Set WshSysEnv = WshShell.Environment("process")

' To check the value for process environment variable
' this can be tested at the command prompt.
' Uncomment the next line and run this from command line
' WScript.Echo WshSysEnv("parameter_xml")

' Initializing Variables
xFile = WshSysEnv("PARAMETER_XML")    'Name of the XML file
sFile = WshSysEnv("PARAMETER_PDF")    'Name of the PDF file

' ***** Start override section for testing outside Cognos environment *****
'
' Set the following if-then condition to "true" for local testing using files that
' have already been burst. If the if=then condition is set to "false", then the
' environment variables must be set properly otherwise a run-time error will occur.
'
If (false) Then
    'The filename component of the output file. Do not include the extension.
    tempName = "D:\C83Outputs\wottcattondxp\TestOutputs\911_1190657394280"
    ' Derive the name of the XML descriptor file
    xFile = tempName & "_desc.xml"
    ' Derive the name of the new output file.
    sFile = tempName & ".pdf"
End If
'
' ***** End override section for testing outside Cognos environment *****

sMsg = ""
sNewFileName = ""

'
' Read the XML descriptor file created by CRN/Cognos 8 to get the value of the element. This
' value will be used as the new filename. If there is no value in the element, a new file will not
' be created. It is assumed that the value of the element does not contain any of the
' reserved characters the underlying OS uses for filename.
'
xmlDoc.async = False
xmlDoc.Load xFile

If (xmlDoc.parseError.errorCode <> 0) Then
    sMsg = xFile & " : Error parsing XML " + xmlDoc.parseError.reason
Else
    Set rootXML = xmlDoc.documentElement
    Set oNodeList = rootXML.childNodes
    iNode = 0

    For Each Item in oNodeList
        If (xmlDoc.documentElement.childNodes.Item(iNode).basename = "burstKey") Then
            sNewFileName = xmlDoc.documentElement.childNodes.Item(iNode).text
            If (sNewFileName = "") Then
                sMsg = xFile & " : element in '" & xFile & "' is empty. Unable to copy file."
            Else
                sMsg = xFile & " : Burst key value is '" & sNewFileName & "'"
            End If

            ' This if condition was added to support the use of CAMID for the burst key
            if (instr(sNewFilename, "CAMID")) Then
                intFirstPosition = instr(sNewFilename, ":")
                intSecondPosition = instr(intFirstPosition, sNewFilename, ")")
                sNewFileName = mid (sNewFilename, intFirstPosition + 1, intSecondPosition - (intFirstPosition + 2))
                sMsg = xFile + " : Value extracted from CAMID is '" & sNewFileName & "'"
            End If
        End If
        iNode = iNode + 1
    Next

    Set oNodeList = Nothing
    Set rootXML = Nothing
End If

'
' If there is a new filename, make of copy of it otherwise just log an error. A local log file with a name
' that is derived using the filename given to the output by CRN/Cognos 8 will be created so it is easy
' to determine if the file copied successfully or not. Since this information will also be written to the
' persistent log file, writing to the local log file can be bypassed if writing it creates too much clutter in the
' location specified in CM.OUTPUTLOCATION.
'
if (sNewFileName <> "") Then
    sNewFileName = getPath(sFile) & sNewFileName & ".pdf"
    writeLocalLogFile sFile, sMsg & vbCrLf & "Copying file named '" & sFile & "' to '" & sNewFileName & "'" & vbCrLf
    objFSO.CopyFile sFile, sNewFileName
Else
    ' Set this variable to an error message so it will be picked up below when writing to the persistent log file.....
    sNewFileName = ""
    writeLocalLogFile sFile, sMsg & vbCrLf
End If

'
' Update the persistent log file with the result. This log file will have data appended to it in order to keep the history.
'
sMsg = "----- Start of entry -----" & vbCrLf
sMsg = sMsg & "Date : " & date & vbTab & "Time : " & time & vbCrLf
sMsg = sMsg & vbCrLf & "Original Name :" & sFile & vbCrLf
sMsg = sMsg & "New Name : " & sNewFileName & vbCrLf
sMsg = sMsg & "----- End of entry -----" & vbCrLf & vbCrLf

sPersistLogFile = getPath(sFile) & "crn_pdf_rename.txt"
writePersistLogFile sPersistLogFile, sMsg
'
' All done.. Release references to the objects used in this app.
'
Set objFSO = Nothing
Set xmlDoc = Nothing
Set WshShell = Nothing
Set WshSysEnv = Nothing

Function getPath(sFileName)
    sPathOnly = ""
    lastSlashPos = InStrRev(sFileName, "\")

    if (lastSlashPos > 0) Then
        getPath = mid (sFileName, 1, lastSlashPos)
    End If
End Function

'
' writeLocalLogFile
'    Create a log file using a name derived from the filename that was generated by
'    CRN/C8 before it was written to the location specified by CM.OUTPUTLOCATION.
'
Sub writeLocalLogFile(sFileName, sData)
    sLogFile = left(sFileName, instr(sFileName,".")-1) +   "_log.txt"
    writeLogFile sLogFile, sData
End Sub

'
' writePersistLogFile
'    Write a record to the persistent log file
'
Sub writePersistLogFile(sFileName, sData)
    writeLogFile sFileName, sData
End Sub

'
' writeLogFile
'    Generic routine to open a file, append a record to it and close the file
'
Sub writeLogFile(sFileName, sData)
    If (objFSO.FileExists(sFileName)) Then
        Set logFile = objFSO.GetFile(sFileName).OpenAsTextStream(8)
    Else
        Set logFile = objFSO.CreateTextFile(sFileName)
    End If

    logFile.Write sData
    logFile.Close

    Set logFile = Nothing
End Sub

now follow the 6th step again and you will get proper pdf output with name same as burst key.

Regards

Cognos Teacher..

(concept taken from cognos-ibm.blogspot.com)

Cognos PDF output in Single page

2012-07-25T02:58:00.001-07:00

When we have more columns in cross tab or list and run in PDF format, we get some columns in one page and some columns on another page and so on.. We can simply avoid this by setting one property. By doing this, we can have all the columns of cross tab or list report in single page in PDF output format.

Cognos Chart limitations in EXCEL

2012-07-25T02:45:00.000-07:00

The following Cognos chart properties are not supported in EXCEL

1) Tool tips

2) Conditional Text

3) Depth, Visual angle

4) Show values

5) Marker Text location

6) Show baseline

7) n-degree rotation lables

8) border, Margin, box type

9) Font, subtitle, Include 0 for Auto scale

10) Scale

and many more .....

Determinants Explained with Example

2012-01-17T23:40:00.000-08:00

Granularity is a simple concept describing the level data relates to.

Imagine sales areas:

Level1: Country
Level2: Sales Area
Level3: State

Level4: City

Level5: Store

Level6: Department
Level7: Employee

We can summarise sales volume (quantity) at each of the levels above.
The level at which we report is the grain (or granularity) of the table.

Tables or query subjects in Framework Manager often have multiple levels (grains) which you may have to join to another table with a different level grain.

An example of this is budgets.
Using our example above, we want to join our sales area table to our budgets table.

Unfortunately the grain of the sales area table is employee but the budgets have been completed at Store level.

In Framework Manager, if we join directly from store in sales areas to store in the budget data the budget data value will be repeated for how many employees are part of that store.

New Query Subject (join on Store with no determinants set):
Sales Area.City
Sales Area.Store
Budget.Month
Budget.Sales_Target

Result:

As there are four employees at the Bayside store this forces the model query subject to return four rows repeating the sales budget.

To correct this Framework Manager has to be instructed what level of granularity the budget applies to. This is done using determinants.

In our example we need to set the determinants on the Sales Area query subject.
Right click the query subject and select the determinants tab.

One determinant should be created for each level of granularity as shown below.
Create the determinant by clicking Add.
Rename the determinant and then drag the identifying column (Key) into the key window (bottom left). If the level has any attributes that exist at the same level of granularity these should be added to the Attribute window.

If the new determinant you have created is the lowest level grain (Employee in this case) the Uniquely Identified check box should be ticked.
The Group By tickbox should be ticked if the level is ever to be used for aggregating values.

Ensure that once you have created all of your determinants for the query subject that they are in the correct order (use the up and down buttons to sort them correctly).

After setting the determinants click OK to save the changes.

When the model query subject is now run, only one row of data is returned:

Configuring Cognos 8.4 with Apache 2.2 Module Gateway

2011-11-17T04:02:00.000-08:00

Most Cognos systems are set up to run the standard Cognos CGI gateway but Cognos also comes with Apache module which can give more performance through the Apache web server.

Unfortunately the instructions provided by IBM to set up this module are not enough to configure apache module instead of the standard cgi.

First Install Apache 2.2

We need to make a few additions to the Apache configuration file `httpd.conf`.

open `httpd.conf`. in wordpad (you can open it start->programs->apache)

at the end of the file, go the new line and enter the following command to load the Cognos Apache 2.2 module

LoadModule cognos_module "C:/Program Files/cognos/c8/cgi-bin/mod2_2_cognos.dll"

Then add the following settings for virtual directories and security.

# Set some directory aliases and security settings

ScriptAlias /cognos8/cgi-bin "C:/Program Files /Cognos/c8/cgi-bin"

Alias /cognos8 "C:/Program Files (x86)/Cognos/c8/webcontent"

<Directory "C:/Program Files (x86)/Cognos/c8/webcontent">

Options Indexes MultiViews

                AllowOverride None
                Options None
                Order allow,deny
                Allow from all
</Directory>

<Directory "C:/Program Files/Cognos/c8/cgi-bin">
                AllowOverride None
                Options None
                Order allow,deny
                Allow from all
</Directory>

Alias /Documentation "C:/Program Files/Cognos/c8/documentation"

<Directory "C:/Program Files (x86)/Cognos/c8/documentation">
                Options Indexes MultiViews
                AllowOverride None
                Options None
                Order allow,deny
                Allow from all
</Directory>

(Optional Settings start)

Continuing with the same file, add settings for gateway diagnostics and the user directory like so...

# Enable the gateway diagnostics
<Location /cognos8/cgi-bin/mod2_2_cognos.dll>
              SetHandler cognos-handler
                Order allow,deny
                Allow from all
</Location>

# Configure the user directory
<IfModule mod2_2_cognos.c>
              CGIBinDir "C:/Program Files (x86)/Cognos/c8/cgi-bin"
</IfModule>

(Optional Settings end)

Now save the file ….
restart Apache.
open Cognos Configuration
Click Enivronment in the leftpane

in the right pane, change the end of the `Gateway URI` from ...

....cgi-bin/cognos.cgi to.... ....cgi-bin/mod2_2_cognos.dll

Restart the cognos services.. and use the following kind of URL to start cognos HOME page

http://servername/cognos8/cgi-bin/mod2_2_cognos.dll

Enjoy with this Technique

Cognos filter & prompts ?

2011-07-06T00:32:00.000-07:00

Filters:

Filters are used to remove unwanted data from the report.

Filters are two types 1) detailed filter 2) summary filter

Detail filter: The working of detail filter is based on APPLICATION

For Ex:

The report has the following columns

[year], [product name],[Quantity]

The filter I applied as follows….

If I select “Before auto aggregation”, what will happen?

The data will be filtered based on qty<500 at database level. Whatever the records are there which are less than 500 will be summed up for year, product line and result will be displayed.

If I select “After auto aggregation”, what will happen,

First the records are aggregated for year, productname then the filter qty<500 will be applied on those aggregated records, If there are any records statisfying qty<500 then we will get the output

Summary filter:

IF I want to use this summary filter, I should have groups and groups totals in the report.

While applying summary filter, we should set scope to specify particular subtotals to apply the filter weather filter to be applied on product type totals, or product line total or year totals…

PROMPTS:

All the filter are hardcoded. IF u run the report any number of times the same filter will be applied and the same output we will get.

Suppose If you want to select filter values at run time, then we use prompts..

There are several prompts that cognos supports

Text box prompt
Value prompt
Select & search prompt
Data prompt
Time prompt
Date & time prompt
Tree prompt
Interval prompt
Generated prompt

There are several ways to get the prompts…

1) When we use parameter( for ex: ?x? ) in the filter then we will get prompt automatically..

When we run the report, Cognos will create a prompt automatically as follows...

The automatic generated prompt may not consistent…, suppose if you want prompt at center of the page, it is not possible here, suppose if I want select & search prompt, I cant change here..

2) Select the columns in the report page for which u want to create prompts, then go to tools menu, select Build prompt page option as follows…

Then the prompt page will be created automatically with prompts for all the three columns..

This method is the quickest way to create prompts…

3) Fully customized method. In this method we can create prompt page and prompts manually..

Point on Page explorer and click on “Prompt page” folder as follows..

Drag “page” from tool box to prompt page folder…

Doubl click on Prompt page1 to maximize prompt page..

Text box prompt

It does not display values in it, we have to imagine values

Value prompt

This will display values from database.

Drawbacks:

1) 1) Performance is slower (Every time u run the report, it has to connect to the database and bring the values..)
2) It can't display more than 5000 values

3) 3) We have to scroll down/up for searching the values..

Select & search prompt….

All the above drawback will be resolved in S&S prompt…

COGNOS: Difference between Two values in a COLUMN

2011-03-02T02:02:00.000-08:00

Suppose I have report output as follows…

Now, I want to find difference between 2006’s Revenue and 2007’s Revenue in its footer…

To Resolve this follow the steps

1) Create a list report as follows

2) Create a Filter to only show 2006 and 2007 as follows

3) Create "list overall" footer as follows..

4) Select the summary row(The overall footer ) and split it

5) Unlock the report

6) Drag query calculation to the Cell as follows..

7) Give any name and enter the following expression in Expression definition box..

8) Click OK and Run the report, you will get output as follows..

Cognos: NTLM security

2011-02-19T04:13:00.000-08:00

restart the services and enjoy logging with your windows usernames and passwords..

Regards
KSR

COGNOS: CSV or Excel output limitatoins

2011-02-04T23:07:00.000-08:00

When saving a report in Excel 2003, we can't save all the report output if the report output contains more than 65535 rows due to the limitation of Excel .

Excel 2007 limits : Worksheet size 1,048,576 rows by 16,384 columns

By default, .CSV files are opened in Excel, so even though .CSV files don't have the 65k row limitation, the file will be truncated if it is opened in Excel.

Resolving the problem

View the file as a CSV and change the format from Excel to a text file. Steps:

After generating the report in HTML format, select the toolbar button to View in CSV Format
Once the File Download dialogue appears?click Save and the Save As dialogue will appear
(Note: If you do not see a Save As dialogue, verify in Internet Explorer you have Automatic prompting for file download set to ENABLE under Tools -> Internet Options -> Security -> Custom level)
From the Save as Type list?change the format from Microsoft Excel Worksheet to All Files
Change the File Name from reportfile.xls to reportfile.txt and click Save

This will create a Tab-Delimited text file that can be used as a data source.