Hide/Display Drill Through Links based on Levels

In a dimensional report, how do you hide/display drill through links based on the levels of a dimension when drills are enabled on the dimension? Layout calculations/Style Variables/Conditional Style conditions do not support MDX functions. So how do we identify the level of the dimension that you are at and apply conditions to the drill through links to hide or display them?

This is where JavaScript comes to our rescue. As always, JS should be used at the developer's discretion as they are not supported by IBM and also these get executed at the client side and hence should not be used in data intensive reports.

Solution:

Step 1: Create the base report and enable drills in the report.

Step 2: Create the drill through links in the crosstab for the Quantity measure.

Step 3: Insert another Quantity data item along side the Quantity measure without drill through links.

Step 4: Insert a singleton, pointing to the Crosstab Query and insert a calculated data item: Ordinal - ordinal(level(item([Product line],0))).

Step 5: Insert 2 HTML Items before and after the singleton.

             <div id='Lvl'>
             </div>

Step 6: Insert 2 HTML items before and after the Quantity measure in the crosstab that doesn't have the drill through link.

             <div id='NoLinks'>
             </div>
Step 7: Insert 2 HTML items before and after the Quantity measure in the crosstab that has the drill through link.

             <div id='Links'>
             </div>

Step 8: Insert a HTML item below the crosstab. Include the below JS:

<script>
var Lvl = document.getElementById('Lvl');
var LvlSpan = Lvl.getElementsByTagName('span')[0];
var LvlVal = LvlSpan.innerHTML;
if(LvlVal==4)
{
for(i=0;i < document.getElementsByTagName('div').length;i++)
{
if(document.getElementsByTagName('div')[i].id=='Links')
{
document.getElementsByTagName('div')[i].style.display = 'block';
}
if(document.getElementsByTagName('div')[i].id=='NoLinks')
{
document.getElementsByTagName('div')[i].style.display = 'none';
}
}
}
else
{
for(i=0;i < document.getElementsByTagName('div').length;i++)
{
if(document.getElementsByTagName('div')[i].id=='Links')
{
document.getElementsByTagName('div')[i].style.display = 'none';
}
if(document.getElementsByTagName('div')[i].id=='NoLinks')
{
document.getElementsByTagName('div')[i].style.display = 'block';
}
}
}
</script>

Note: These techniques use JavaScript against underlying report objects in a IBM Cognos 8 BI report. For this reason, there is no guarantee that reports created using these techniques will migrate or upgrade successfully to future versions without requiring modifications. Any such modifications are the responsibility of the report designer.

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">     <modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (analysis)']/model[@name='model']</modelPath>         <queries>      <query name="Query1">       <source>        <model/>       </source>       <selection><dataItemMeasure name="Quantity"><dmMember><MUN>[Sales (analysis)].[Sales].[Quantity]</MUN><itemCaption>Quantity</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/></XMLAttributes></dataItemMeasure><dataItemMeasure name="Unit cost"><dmMember><MUN>[Sales (analysis)].[Sales].[Unit cost]</MUN><itemCaption>Unit cost</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/></XMLAttributes></dataItemMeasure><dataItemMeasure name="Revenue"><dmMember><MUN>[Sales (analysis)].[Sales].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/></XMLAttributes></dataItemMeasure><dataItemLevelSet name="Product line"><dmLevel><LUN>[Sales (analysis)].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel><dmDimension><DUN>[Sales (analysis)].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet><dataItem name="Ordinal"><expression>ordinal(level(item([Product line],0)))</expression></dataItem></selection>      </query>     </queries>     <layouts>      <layout>       <reportPages>        <page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>         <pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>          <contents>           <singleton name="Singleton1" refQuery="Query1">    <contents><HTMLItem>    <dataSource>     <staticValue>&lt;div id='Lvl'&gt;</staticValue>    </dataSource>   </HTMLItem><textItem><dataSource><dataItemValue refDataItem="Ordinal"/></dataSource></textItem><HTMLItem>    <dataSource>     <staticValue>&lt;/div&gt;</staticValue>    </dataSource>   </HTMLItem></contents>   </singleton><crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">            <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents/></crosstabCorner>                                  <noDataHandler>             <contents>              <block>               <contents>                <textItem>                 <dataSource>                  <staticValue>No Data Available</staticValue>                 </dataSource>                 <style>                  <CSS value="padding:10px 18px;"/>                 </style>                </textItem>               </contents>              </block>             </contents>            </noDataHandler>            <style>             <defaultStyles>              <defaultStyle refStyle="xt"/>             </defaultStyles>             <CSS value="border-collapse:collapse"/>            </style>           <crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Unit cost" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabIntersections><crosstabIntersection row="e4" column="e1"><contents><HTMLItem>    <dataSource>     <staticValue>&lt;div id='NoLinks'&gt;</staticValue>    </dataSource>   </HTMLItem><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem><HTMLItem>    <dataSource>     <staticValue>&lt;/div&gt;</staticValue>    </dataSource>   </HTMLItem><HTMLItem>    <dataSource>     <staticValue>&lt;div id='Links'&gt;</staticValue>    </dataSource>   </HTMLItem><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource><reportDrills><reportDrill name="Drill-Through Definition1"><drillLabel><dataSource><staticValue/></dataSource></drillLabel><drillTarget><reportPath path="CAMID(&quot;Zynga_AD:u:2a4aa6305669dc40b168f26119e56912&quot;)/folder[@name='My Folders']/folder[@name='PoC']/report[@name='Tgt']"><XMLAttributes><XMLAttribute name="ReportName" value="Tgt" output="no"/><XMLAttribute name="class" value="report" output="no"/></XMLAttributes></reportPath></drillTarget></reportDrill></reportDrills><style><defaultStyles><defaultStyle refStyle="hy"/></defaultStyles></style></textItem><HTMLItem>    <dataSource>     <staticValue>&lt;/div&gt;</staticValue>    </dataSource>   </HTMLItem></contents></crosstabIntersection></crosstabIntersections></crosstab>          <HTMLItem>    <dataSource>     <staticValue>&lt;script&gt; var Lvl = document.getElementById('Lvl'); var LvlSpan = Lvl.getElementsByTagName('span')[0]; var LvlVal = LvlSpan.innerHTML; if(LvlVal==4) { for(i=0;i &lt; document.getElementsByTagName('div').length;i++) { if(document.getElementsByTagName('div')[i].id=='NoLinks') { document.getElementsByTagName('div')[i].style.display = 'block'; } } } else { for(i=0;i &lt; document.getElementsByTagName('div').length;i++) { if(document.getElementsByTagName('div')[i].id=='NoLinks') { document.getElementsByTagName('div')[i].style.display = 'none'; } } } &lt;/script&gt;</staticValue>    </dataSource>   </HTMLItem></contents>         </pageBody>         <pageHeader>          <contents>           <block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>            <contents>             <textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>              <dataSource>               <staticValue/>              </dataSource>             </textItem>            </contents>           </block>          </contents>          <style>           <defaultStyles>            <defaultStyle refStyle="ph"/>           </defaultStyles>           <CSS value="padding-bottom:10px"/>          </style>         </pageHeader>         <pageFooter>          <contents>           <table>            <tableRows>             <tableRow>              <tableCells>               <tableCell>                <contents>                 <date>                  <style>                   <dataFormat>                    <dateFormat/>                   </dataFormat>                  </style>                 </date>                </contents>                <style>                 <CSS value="vertical-align:top;text-align:left;width:25%"/>                </style>               </tableCell>               <tableCell>                <contents>                 <pageNumber/>                </contents>                <style>                 <CSS value="vertical-align:top;text-align:center;width:50%"/>                </style>               </tableCell>               <tableCell>                <contents>                 <time>                  <style>                   <dataFormat>                    <timeFormat/>                   </dataFormat>                  </style>                 </time>                </contents>                <style>                 <CSS value="vertical-align:top;text-align:right;width:25%"/>                </style>               </tableCell>              </tableCells>             </tableRow>            </tableRows>            <style>             <defaultStyles>              <defaultStyle refStyle="tb"/>             </defaultStyles>             <CSS value="border-collapse:collapse;width:100%"/>            </style>           </table>          </contents>          <style>           <defaultStyles>            <defaultStyle refStyle="pf"/>           </defaultStyles>           <CSS value="padding-top:10px"/>          </style>         </pageFooter>        </page>       </reportPages>      </layout>     </layouts>    <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-09T13:51:23.483Z" output="no"/></XMLAttributes><reportName>Src</reportName><drillBehavior drillUpDown="true" modelBasedDrillThru="true"/></report>

Business Insight Bugs 10.1.1

Some Business Insight bugs discovered in Cognos 10.1.1 version:

• Data formats and formats like Background colors, Fonts, borders not maintained after drill up/down.
• Drilling up on an object containing a single member brings back sibling members rather than parent members.
• Nested Metrics in crosstab columns lost on Drill up/down.
• Prompts not reflected on report objects after Drill up/down.
• Layout calculations in crosstabs lost on drill up/down.

Drill Through - Passing ParamDisplayValue of Parameters

How do you pass ParameDisplayValue of a Parameter through a drill through link rather than a ParamValue? This is a common requirement when you are dealing with passing OLAP values to Relational reports.

To do the above, set the "Property to Pass" property to "Member Caption" for the parameter in the Drill Through properties.