Cognos: to_char functionality without using to_char

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

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)

<report xmlns="http://developer.cognos.com/schemas/report/10.0/" expressionLocale="en"><!--RSU-SPC-0093 The report specification was upgraded from &quot;http://developer.cognos.com/schemas/report/6.0/&quot; to &quot;http://developer.cognos.com/schemas/report/10.0/&quot; at 2014-9-18. 19:27:39-->

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

<layouts>

<layout>

<reportPages>

<page name="Page1">

<style>

<defaultStyles>

<defaultStyle refStyle="pg"/>

</defaultStyles>

</style>

<pageBody>

<style>

<defaultStyles>

<defaultStyle refStyle="pb"/>

</defaultStyles>

</style>

<contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab2" refQuery="Query1">

<crosstabCorner>

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

<style>

<defaultStyles>

<defaultStyle refStyle="xm"/>

</defaultStyles>

</style>

</crosstabCorner>

<style>

<CSS value="border-collapse:collapse"/>

<defaultStyles>

<defaultStyle refStyle="xt"/>

</defaultStyles>

</style>

<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e1" refDataItem="ancestor4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e2" refDataItem="ancestor3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e3" refDataItem="ancestor2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e4" refDataItem="ancestor1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e5" refDataItem="ancestor0"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">

<crosstabCorner>

<contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents>

<style>

<defaultStyles>

<defaultStyle refStyle="xm"/>

</defaultStyles>

</style>

</crosstabCorner>

<style>

<CSS value="border-collapse:collapse"/>

<defaultStyles>

<defaultStyle refStyle="xt"/>

</defaultStyles>

</style>

<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e1" refDataItem="Children"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Revenue"/></crosstab></contents></tableCell></tableCells></tableRow></tableRows></table></contents>

</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]-&gt;[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>

IE 7 or Higher versions Cognos Connection issue - Undefined Popup

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

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

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> 

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

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.

1. In the Insertable Objects pane, on the Toolbox tab, drag the HTML Item object  to the report.
2. Select the HTML Item.
3. double-click the HTML Item 
4. 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

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

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

     <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

Cognos Schedule report will be not delivered issue?

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)