Show/Hide Columns using Prompts in Cognos Reportnet

[PRTdev]

Hi, I have a Cognos list report with several items (rows) and 12-month revenues per item (12 columns, JAN2007 thru DEC2007). This report is being developed in Report Studio v1.1

I want a value prompt with 12 check boxes so that the report will contain only the months selected by the user (multiple values allowed).

I read and tried the solution posted in thread 401-1089845 (very helpful) but I am unable to extend this solution to my particular needs.

Any help will be much appreciated.

Regards

 

[almeids]

Example below using the GOSAR data is one way of doing this for 3 columns (I would have attached as a text file but can't seem to figure out how). Note that the "filter" doesn't actually do any filtering, it's just to force multiselect in the prompt.

<report xml:lang="en-us" xmlns="

http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->

<modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
<querySet xml:lang="en-us">
<BIQuery name="Report Query">
<cube><factList><item refItem="Product name" aggregate="none"/><item refItem="Year" aggregate="none"/><item refItem="Jan quantity" aggregate="none"/><item refItem="Feb quantity" aggregate="none"/><item refItem="Mar quantity" aggregate="none"/><item refItem="month_param" aggregate="none"/></factList></cube>
<tabularModel><dataItem name="Product name" aggregate="none"><expression>[gosales_goretailers].[Product forecasts].[Product name]</expression></dataItem><dataItem name="Year" aggregate="none"><expression>[gosales_goretailers].[Product forecasts].[Year]</expression></dataItem><dataItem name="Jan quantity" aggregate="total"><expression>if ([gosales_goretailers].[Product forecasts].[Month]=1) then ([gosales_goretailers].[Product forecasts].[Actual quantity]) else (0)</expression></dataItem><dataItem name="Feb quantity" aggregate="total"><expression>if ([gosales_goretailers].[Product forecasts].[Month]=2) then ([gosales_goretailers].[Product forecasts].[Actual quantity]) else (0)</expression></dataItem><dataItem name="Mar quantity" aggregate="total"><expression>if ([gosales_goretailers].[Product forecasts].[Month]=3) then ([gosales_goretailers].[Product forecasts].[Actual quantity]) else (0)</expression></dataItem><dataItem name="month_param" aggregate="none"><expression>?month_list_param?</expression></dataItem><filter><condition>([gosales_goretailers].[Product forecasts].[Month] in (?month_list_param?)) or (1=1)</condition></filter></tabularModel></BIQuery>
</querySet>
<layoutList>
<layout>

<promptPageList><page name="Prompt Page1">
<pageHeader>
<block class="reportTitle">
<textItem class="reportTitleText">
<text/>
</textItem>
</block>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageBody>
<style>
<CSS value="padding:12px"/>
</style>
<selectValue selectUI="listBox" parameter="month_list_param" multiSelect="true"><selectOptions><selectOption useValue="1"><displayValue value="Jan"/></selectOption><selectOption useValue="2"><displayValue value="Feb"/></selectOption><selectOption useValue="3"><displayValue value="Mar"/></selectOption></selectOptions></selectValue>
</pageBody>
<pageFooter>
<style>
<CSS value="padding:12px;border-top:1pt solid #999999"/>
</style>
<promptButton type="cancel">
<style>
<CSS value="margin-right:7px"/>
</style>
</promptButton>
<promptButton type="back">
<style>
<CSS value="margin-right:7px"/>
</style>
</promptButton>
<promptButton type="next">
<style>
<CSS value="margin-right:7px"/>
</style>
</promptButton>
<promptButton type="finish"/>
</pageFooter>
</page>
</promptPageList><pageSet>

<page name="Page1">
<pageBody>
<list refQuery="Report Query">
<listColumnTitles><listColumnTitle><textItem><queryItemRef content="label" refItem="Product name"/></textItem></listColumnTitle><listColumnTitle><textItem><queryItemRef content="label" refItem="Year"/></textItem></listColumnTitle><listColumnTitle><textItem><queryItemRef content="label" refItem="Jan quantity"/></textItem><conditionalStyle refVariable="Include Jan"><style><CSS value="display:none"/></style><style refVariableValue="1"/></conditionalStyle></listColumnTitle><listColumnTitle><textItem><queryItemRef content="label" refItem="Feb quantity"/></textItem><conditionalStyle refVariable="Include Feb"><style><CSS value="display:none"/></style><style refVariableValue="1"/></conditionalStyle></listColumnTitle><listColumnTitle><textItem><queryItemRef content="label" refItem="Mar quantity"/></textItem><conditionalStyle refVariable="Include Mar"><style><CSS value="display:none"/></style><style refVariableValue="1"/></conditionalStyle></listColumnTitle></listColumnTitles>
<listColumns><listColumn><textItem><queryItemRef refItem="Product name"/></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Year"/></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Jan quantity"/></textItem><conditionalStyle refVariable="Include Jan"><style><CSS value="display:none"/></style><style refVariableValue="1"/></conditionalStyle></listColumn><listColumn><textItem><queryItemRef refItem="Feb quantity"/></textItem><conditionalStyle refVariable="Include Feb"><style><CSS value="display:none"/></style><style refVariableValue="1"/></conditionalStyle></listColumn><listColumn><textItem><queryItemRef refItem="Mar quantity"/></textItem><conditionalStyle refVariable="Include Mar"><style><CSS value="display:none"/></style><style refVariableValue="1"/></conditionalStyle></listColumn></listColumns>
<style>
<CSS value="border-collapse:collapse"/>
</style>
<XMLAttribute name="RS_ListGroupInfo" value=""/></list>
</pageBody>


</page></pageSet></layout>
</layoutList>
<variableList><variable name="Include Jan" type="boolean"><expression>ParamDisplayValue("month_list_param") contains 'Jan'</expression><variableValueList><variableValue value="1"/></variableValueList></variable><variable name="Include Feb" type="boolean"><expression>ParamDisplayValue("month_list_param") contains 'Feb'</expression><variableValueList><variableValue value="1"/></variableValueList></variable><variable name="Include Mar" type="boolean"><expression>ParamDisplayValue("month_list_param") contains 'Mar'</expression><variableValueList><variableValue value="1"/></variableValueList></variable></variableList></report>

 

[sln007]

I've performed this same operation of "Conditianally Formatting" the view/do-not-view columns based on radio buttons from a prompt page. It works.
I used Report Studio from v8.2
I'm not sure if your v1.1 would work the same (I assume so).
Would you like more info on this operation?

Steve N.
State of Ohio, MIS