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>

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

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

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>