Issue in expanding & collapsing the crosstab rows

[Shruthi]

Hi,
I am working on Cognos 8.4, and using Dimensional model for reporting.
I have a requirement in which I have a value prompt with static choices - product and Order Method.
when you select Product , the cross-tab should display in the following format...
               
================================
                        
  + Product(ALL)           
     + Product Line                 
         + Product Type           
     + Product Line                    
         + Product Type        
              
================================   

similalry for Order method also...

but when I am selecting Product in my value prompt I was not able see all the product lines under product...
please help me in this regard.

Please find the attached report specification code for your information

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en" ignoreFilterContext="false"><!--RSU-SPC-0093 The report specification was upgraded from &amp;amp;quot;http://developer.cognos.com/schemas/report/4.0/&amp;amp;quot; to &amp;amp;quot;http://developer.cognos.com/schemas/report/6.0/&amp;amp;quot; at 2010-9-14. 22:3:2--><!--RSU-SPC-0093 The report specification was upgraded from &amp;amp;amp;amp;amp;amp;quot;http://developer.cognos.com/schemas/report/2.0/&amp;amp;amp;amp;amp;amp;quot; to &amp;amp;amp;amp;amp;amp;quot;http://developer.cognos.com/schemas/report/4.0/&amp;amp;amp;amp;amp;amp;quot; at 2008-11-4. 15:26:14--><!--RS:8.1-->
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Data Warehouse (analysis)']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem aggregate="none" name="Products"><expression>[Sales].[Product].[Product]</expression></dataItem><dataItem aggregate="none" name="Year" rollupAggregate="none"><expression>[Sales].[Time dimension].[Time dimension].[Year]</expression></dataItem><dataItem name="Revenue"><expression>[Sales].[Sales fact].[Revenue]</expression></dataItem></selection>
</query>
<query name="Query2"><source><model/></source><selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales].[Time dimension].[Time dimension].[Year]</expression></dataItem><dataItem name="Order method"><expression>[Sales].[Order method].[Order method]</expression></dataItem><dataItem name="Revenue"><expression>[Sales].[Sales fact].[Revenue]</expression></dataItem></selection></query></queries>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<pageBody>
<contents>
<selectValue parameter="p1" required="false" autoSubmit="true"><selectOptions><selectOption useValue="[Product]"><displayValue>Product</displayValue></selectOption><selectOption useValue="[Order method]"><displayValue>Order method</displayValue></selectOption></selectOptions><defaultSelections><defaultSimpleSelection>[Product]</defaultSimpleSelection></defaultSelections></selectValue>
<conditionalBlocks>
<conditionalBlockDefault>
<contents><crosstab name="Crosstab1" refQuery="Query1" rowsPerPage="999999">



<style>
<CSS value="border-collapse:collapse;border-bottom-width:1pt;border-bottom-style:solid"/>
<defaultStyles><defaultStyle refStyle="xt"/></defaultStyles></style>
<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e1" refDataItem="Products"><contents><HTMLItem>
<dataSource>
<staticValue>&lt;span onclick='ExpandCollapse(this)' style='cursor:hand;'&gt;&lt;img src='../pat/hal/images/plus.gif' style='vertical-align:middle;'/&gt;</staticValue>
</dataSource>
</HTMLItem>
<textItem><dataSource><memberCaption/></dataSource></textItem><HTMLItem>
<dataSource>
<staticValue>&lt;/span&gt;</staticValue>
</dataSource>
</HTMLItem>
</contents><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><indentation indentationLength="10" indentationUnits="px" type="level"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell applySingleDefaultStyle="true"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e2" refDataItem="Year"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabCorner><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style></crosstabCorner><defaultMeasure refDataItem="Revenue"/></crosstab></contents>
</conditionalBlockDefault>
<conditionalBlockCases refVariable="String1"><conditionalBlock refVariableValue="N"><contents/></conditionalBlock></conditionalBlockCases></conditionalBlocks><conditionalBlocks>
<conditionalBlockDefault>
<contents><crosstab horizontalPagination="true" name="Crosstab2" refQuery="Query2">



<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 refDataItem="Order method" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><HTMLItem>
<dataSource>
<staticValue>&lt;span onclick='ExpandCollapse(this)' style='cursor:hand;'&gt;&lt;img src='../pat/hal/images/plus.gif' style='vertical-align:middle;'/&gt;</staticValue>
</dataSource>
</HTMLItem><textItem><dataSource><memberCaption/></dataSource></textItem><HTMLItem>
<dataSource>
<staticValue>&lt;/span&gt;</staticValue>
</dataSource>
</HTMLItem></contents><indentation type="level" indentationLength="10" indentationUnits="px" outdent="false"/></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><crosstabCorner><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style></crosstabCorner><defaultMeasure refDataItem="Revenue"/></crosstab></contents>
</conditionalBlockDefault>
<conditionalBlockCases refVariable="String1"><conditionalBlock refVariableValue="Y"><contents/></conditionalBlock></conditionalBlockCases></conditionalBlocks></contents>
<style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style></pageBody>
<pageHeader>
<contents>
<block>
<contents>
<HTMLItem>
<dataSource>
<staticValue>&lt;script&gt;
//&lt;!--
function ExpandCollapse(sp){
  var tr = sp.parentElement.parentElement;
  var tbl = tr.parentElement;
  var trClickP = tbl.rows(tr.rowIndex).cells(0).style.paddingLeft;
  var trFirstP = tbl.rows(tr.rowIndex + 1).cells(0).style.paddingLeft;
  if(sp.firstChild.src.indexOf("minus")==-1){
    sp.firstChild.src = "../pat/hal/images/minus.gif";
    for (var i = tr.rowIndex + 1; i &lt; tbl.rows.length; i++ ){
      var trCurrent = tbl.rows(i);
      if(trCurrent.cells(0).style.paddingLeft==trClickP){break;}
      if(trCurrent.cells(0).style.paddingLeft==trFirstP){trCurrent.style.display = "";}
    }
  }
  else{
    sp.firstChild.src = "../pat/hal/images/plus.gif";
    for (var i = tr.rowIndex + 1; i &lt; tbl.rows.length; i++ ){
      var trCurrent = tbl.rows(i);
      if(trCurrent.cells(0).style.paddingLeft&lt;=trClickP){break;}
      if(trCurrent.cells(0).getElementsByTagName("IMG").length&gt;0){
        trCurrent.cells(0).getElementsByTagName("IMG").item(0).src = "../pat/hal/images/plus.gif";}
      trCurrent.style.display = "none";
    }
  }
}

// If the row has been 'padded' via the indent, it will be collapsed initially.
function StartHidden(){
  var tbls = document.getElementsByTagName("TABLE")
  var tblCnt = tbls.length;
  for(var t=0; t&lt;tblCnt; t++){
    var tbl = tbls.item(t);
    if((tbl.className == "xt") || (tbl.className == "ls")){
      for (var i = 0; i &lt; tbl.rows.length; i++){
        var trCurrent = tbl.rows(i);
        if (trCurrent.cells(0).style.paddingLeft.length &gt; 0){
          trCurrent.style.display = "none";
        }
      }
    }
  }
}
//--&gt;
&lt;/script&gt;
</staticValue>
</dataSource>
</HTMLItem>

</contents>
<style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style></block>
</contents>
<style>
<CSS value="padding-bottom:10px"/>
<defaultStyles><defaultStyle refStyle="ph"/></defaultStyles></style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfDate()</reportExpression>
</dataSource>
</textItem>
<HTMLItem>
<dataSource>
<staticValue>&lt;img onload="StartHidden()" src="../pat/hal/images/blank.gif" height="1"/&gt;</staticValue>
</dataSource>
</HTMLItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<staticValue>- </staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<reportExpression>PageNumber()</reportExpression>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue> -</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfTime()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
<defaultStyles><defaultStyle refStyle="tb"/></defaultStyles></style>
</table>
</contents>
<style>
<CSS value="padding-top:10px"/>
<defaultStyles><defaultStyle refStyle="pf"/></defaultStyles></style>
</pageFooter>
<style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style></page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" output="no" value="false"/><XMLAttribute name="listSeparator" output="no" value=","/></XMLAttributes><reportVariables><reportVariable type="string" name="String1">
<reportExpression>if(ParamDisplayValue('p1') = 'Product') then ('Y')
else ('N')</reportExpression>
<variableValues><variableValue value="Y"/><variableValue value="N"/></variableValues></reportVariable></reportVariables></report>



Thanks
      

[technomorph]

Hi,

I noticed you posted a similar question previously. I think cognostechie suggested configuring the Drill Up/Down options under the Data menu. This is definitely the way to go. I would avoid trying to write complicated JavaScript as this will be difficult to maintain.

As a general approach for building dimensional reports, it is sometimes handy to create a report in Analysis Studio and then opening it in Report Studio to see how it works. This can sometimes provide a cheat for achieving complex tasks.

Cheers