Limitation - Descendants MDX Function against Sets/Hierarchy

Though it is documented on the IBM Support Site that Descendants against Set functions may result in incorrect outcome, wish there was a way we could work around this limitation.

Some reasons why we may need Descendants function against a Hierarchy name rather than a member is:

• When we use TM1 as the source and have security set up such that a user has access to the lower level members of a dimension hierarchy but not to the root member of the hierarchy (which is perfectly valid in our business scenario).
• And we do not have named levels set up since we deal with unbalanced hierarchies, again valid when dealing with Accounts dimension.
• And we have multiple alternate hierarchies set up against a dimension.

So in this case we are limited to using the descendants function against the hierarchy name rather than the root member which does not result in the required outcome.

So one of the work-around in this case is to use other MDX expressions like Descendants with Except to build the set required.

Defect - Drill on MDX expression based members

Another one for the defect list and this in my opinion impacts a feature that is most important and that was working in earlier releases.

The defect relates to drill up functionality on MDX based lowest level members.

To re-create the issue:

Create an MDX based query calculation, say children of Cooking Gear.

Drill up on TrailChef Water Bag                                        

Drill up on Cooking Gear         
                                    

Drill down on Golf Equipment        
                                  

Drill down on Putters       
                                          

Drill down on Course Pro Putter   
                                    

Drill up on Course Pro Putter 109110           

                       











We see children of Cooking Gear (the list we started with) instead of Course Pro Putter.

Seems like the drill functionality in 10.1.1 has a lot of defects not just in Business Insight but in Cognos Viewer as well.

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>