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
<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en" ignoreFilterContext="false"><!--RSU-SPC-0093 The report specification was upgraded from &amp;quot;http://developer.cognos.com/schemas/report/4.0/&amp;quot; to &amp;quot;http://developer.cognos.com/schemas/report/6.0/&amp;quot; at 2010-9-14. 22:3:2--><!--RSU-SPC-0093 The report specification was upgraded from &amp;amp;amp;amp;amp;quot;http://developer.cognos.com/schemas/report/2.0/&amp;amp;amp;amp;amp;quot; to &amp;amp;amp;amp;amp;quot;http://developer.cognos.com/schemas/report/4.0/&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><span onclick='ExpandCollapse(this)' style='cursor:hand;'><img src='../pat/hal/images/plus.gif' style='vertical-align:middle;'/></staticValue>
</dataSource>
</HTMLItem>
<textItem><dataSource><memberCaption/></dataSource></textItem><HTMLItem>
<dataSource>
<staticValue></span></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><span onclick='ExpandCollapse(this)' style='cursor:hand;'><img src='../pat/hal/images/plus.gif' style='vertical-align:middle;'/></staticValue>
</dataSource>
</HTMLItem><textItem><dataSource><memberCaption/></dataSource></textItem><HTMLItem>
<dataSource>
<staticValue></span></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><script>
//<!--
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 < 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 < tbl.rows.length; i++ ){
var trCurrent = tbl.rows(i);
if(trCurrent.cells(0).style.paddingLeft<=trClickP){break;}
if(trCurrent.cells(0).getElementsByTagName("IMG").length>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<tblCnt; t++){
var tbl = tbls.item(t);
if((tbl.className == "xt") || (tbl.className == "ls")){
for (var i = 0; i < tbl.rows.length; i++){
var trCurrent = tbl.rows(i);
if (trCurrent.cells(0).style.paddingLeft.length > 0){
trCurrent.style.display = "none";
}
}
}
}
}
//-->
</script>
</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><img onload="StartHidden()" src="../pat/hal/images/blank.gif" height="1"/></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
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