Conditional Rendering Based on Selected Prompt Values – Relational

One of my clients had a requirement where they wanted a footer to be conditional displayed based on prompt selection. This requirement was little different in the sense that when the user selected all the prompt values then the footer should display “All values were selected” else it should display only the Prompt values that were selected.

Below is how I approached the solution.

Create a Prompt:

• Drag and Drop Query from toolbox onto the Query explorer and rename it to “Prompt Query”
• Add the “Order Method” field onto the Data items pane and rename it to “Unique Order Method Categories”.
• From the Page Explorer, Create a prompt page and add a “Value Prompt” onto the prompt page.
• Name the parameter “pOrder” and click “Finish”.

• Go to the Properties of the Value prompt.
• Set the Query to use “Prompt Query”
• Set the Use/ Display Value to use “Unique Order Method Categories”.

Create a List Report:

• Add a Query onto the Query explorer and rename it to “Report Query”

• Double click the “Report Query”; add the “Order Method” and “Order Method Code” fields onto the Data items pane.
• Add a filter to the Detail Filters pane and enter the following in the filter expression “[Order method] IN ( ?pOrder? )”.

• Navigate back to the Page explorer and go to Page1.
• Drag and drop a List frame onto the page and link the Report Query we created to the List.
• Drag and drop the “Order Method” and “Order Method Code” query items from the Report Query onto the List.

Create Query for Calculate total number of Prompt Values:

• Add a Query onto the Query explorer and rename it to “Prompt Category Query”
• Double click the “Prompt Category Query”
• Add a data item to the data items pane and enter the calculated expression “Count( [Sales (query)].[Order method].[Order method] )” in the expression definition area.

• Name the Data Item “Unique Order Method Categories”.
• Add another data item to the data items pane and enter the static value “A” in the expression definition area.

• Name the Data Item “Join”.

Create Query for Calculating total number of Prompt Values selected.

• Add a Query onto the Query explorer and rename it to “Prompt Output Query”
• Double click the “Prompt Output Query”
• Add a data item to the data items pane and enter the calculated expression “Count( [Sales (query)].[Order method].[Order method] )” in the expression definition area.

• Name the Data Item “Total Order Method count from Prompt”.
• Add another data item to the data items pane and enter the static value “A” in the expression definition area.

• Name the Data Item “Join”.
• Add a filter to the Detail Filters pane and enter the following in the filter expression “[Sales (query)].[Order method].[Order method] IN (?pOrder? )

Joining two queries to create Conditional Variable.

• Drag and Drop “Join” item from the toolbox onto the Query explorer area.
• Rename the Query to “Footer Display Query”
• Now drag and drop the “Prompt Output Query” and “Prompt Category Query” to the Join queries drop zone.

• Double click the Join criteria and create a join between the two columns named “Join”.

• Double click the “Footer Display Query”
• Add the fields “Unique Order Method Categories”, Total Order Method count from Prompt to the data items.
• Add a data item to the data items pane and enter the below mentioned calculated expression in the expression definition area.

IF ( [Prompt Category Query].[Unique Order Method Categories] = [Prompt Output Query].[Total Order Method count from Prompt] )

THEN ( ‘A’)

ELSE( ‘B’ )

• Name the Data Item “Final Footer Variable”.

Create Conditional Variable.

• Navigate to the Conditional Explorer.
• Add String Variable to Variables pane.
• Rename the String Variable to “Footer Display”.
• Drag the “Final Footer Variable” query item from the “Footer Display Query” to the expression definition.
• Add two values “A” and “B” as values to the newly created variable.

Adding a Conditional Display Block.

• Navigate to the Report Page from the Page Explorer.
• Drag and Drop a Singleton onto the report page (depends on where you would like this to be displayed. I added the block on the middle page below the list for demo purpose)
• From the Singleton properties.
• Set the Query to “Footer Display Query”
• Select the properties ellipsis and click “Select All”

• Drag and Drop a Conditional Block within the Singleton.
• Set the Block Variable to “Footer Display”.
• Set the Current Block Variable to “A”. Then Drag and Drop text item from the toolbox within the Conditional Block.
• Enter the following text “All values were selected” and select ok.
• Now the Set the Current Block Variable to “B”. Then Drag and Drop text item from the toolbox within the Conditional Block.
• Enter the following text “The following values were selected:” and select ok.
• Drag a Layout Calculation from the toolbox and Drop it next to the Text item we created within the conditional block “B”.

Run the report and Conditional variable should display “All values were selected” when if all the prompt vales were selected and display “The following values were selected: < prompt values>”when only some of the prompt vales were selected.

Report Specification Below

 <report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Packages']/package[@name='GO Sales GEO (query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Report Query">
<source>
<model/>
</source>
<selection><dataItem name="Order method" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Order method].[Order method]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Order method code" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Order method].[Order method code]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="identifier" output="no"/></XMLAttributes></dataItem></selection>
<detailFilters><detailFilter><filterExpression> [Order method]  IN ( ?pOrder? ) </filterExpression></detailFilter></detailFilters></query>
<query name="Prompt Category Query">
<source>
<model/>
</source>
<selection><dataItem name="Unique Order Method Categories" aggregate="none" rollupAggregate="none"><expression>Count( [Sales (query)].[Order method].[Order method] )</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Join"><expression>'A'</expression></dataItem></selection>
</query><query name="Prompt Output Query">
<source>
<model/>
</source>
<selection><dataItem name="Total Order Method count from Prompt"><expression>Count( [Sales (query)].[Order method].[Order method] )</expression></dataItem><dataItem name="Join"><expression>'A'</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[Sales (query)].[Order method].[Order method] IN (?pOrder?  )</filterExpression></detailFilter></detailFilters></query><query name="Footer Display Query">
<source>

<joinOperation>
<joinOperands>
<joinOperand cardinality="1:1"><queryRef refQuery="Prompt Category Query"/></joinOperand>
<joinOperand cardinality="1:1"><queryRef refQuery="Prompt Output Query"/></joinOperand>
</joinOperands>
<joinFilter>
<filterExpression>[Prompt Category Query].[Join] = [Prompt Output Query].[Join]</filterExpression>
</joinFilter>
</joinOperation></source>
<selection><dataItem name="Unique Order Method Categories"><expression>[Prompt Category Query].[Unique Order Method Categories]</expression></dataItem><dataItem name="Total Order Method count from Prompt"><expression>[Prompt Output Query].[Total Order Method count from Prompt]</expression></dataItem><dataItem name="Final Footer Variable"><expression>IF ( [Prompt Category Query].[Unique Order Method Categories] = [Prompt Output Query].[Total Order Method count from Prompt] )

THEN ( 'A')

ELSE( 'B' )</expression></dataItem></selection>
</query><query name="Prompt Query">
<source>
<model/>
</source>
<selection><dataItem name="Unique Order Method Categories" aggregate="none" rollupAggregate="none"><expression> [Sales (query)].[Order method].[Order method] </expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection>
</query></queries>
<layouts>
<layout>
<reportPages>
<page name="Page1" refQuery="Footer Display Query"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>

<table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><list horizontalPagination="true" name="List1" refQuery="Report Query">



<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="ls"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="background-color:black;font-family:'Arial Black';font-size:10pt;font-weight:bold;color:white"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Order method"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Order method"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="background-color:black;font-family:'Arial Black';font-size:10pt;font-weight:bold;color:white"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Order method code"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Order method code"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/><style><CSS value="padding-top:25px"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><singleton name="Singleton1" refQuery="Footer Display Query">
<contents><conditionalBlocks>
<conditionalBlockDefault>
<contents/>
</conditionalBlockDefault>
<conditionalBlockCases refVariable="footer display"><conditionalBlock refVariableValue="A"><contents><textItem><dataSource><staticValue>All values were selected</staticValue></dataSource><style><CSS value="color:red;font-family:'Comic Sans MS';font-size:24pt;text-decoration:none"/></style></textItem></contents></conditionalBlock><conditionalBlock refVariableValue="B"><contents><textItem><dataSource><staticValue>The following values were selected: </staticValue></dataSource><style><CSS value="color:red;font-family:'Comic Sans MS';font-size:24pt;text-decoration:none"/></style></textItem><textItem><dataSource><reportExpression>ParamDisplayValue('pOrder')</reportExpression></dataSource><style><CSS value="color:red;font-family:'Comic Sans MS';font-size:24pt;text-decoration:none"/></style></textItem></contents></conditionalBlock></conditionalBlockCases></conditionalBlocks></contents>
<propertyList><propertyItem refDataItem="Unique Order Method Categories"/><propertyItem refDataItem="Total Order Method count from Prompt"/><propertyItem refDataItem="Final Footer Variable"/></propertyList></singleton></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell></tableCells></tableRow></tableRows></table></contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles><CSS value="padding-top:50px;padding-bottom:50px"/></style>
<contents>

</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>

</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
<propertyList><propertyItem refDataItem="Unique Order Method Categories"/><propertyItem refDataItem="Total Order Method count from Prompt"/><propertyItem refDataItem="Final Footer Variable"/></propertyList></page>
</reportPages>
<promptPages><page name="Prompt Page1">
<pageHeader>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue/>
</dataSource>
<style>
<defaultStyles>
<defaultStyle refStyle="tt"/>
</defaultStyles>
</style>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ta"/>
</defaultStyles>
</style>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="hp"/>
</defaultStyles>
</style>
</pageHeader>
<pageBody>
<contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><selectValue parameter="pOrder" multiSelect="true" hideAdornments="true" name="listBox1" selectValueUI="listBox" refQuery="Prompt Query"><useItem refDataItem="Unique Order Method Categories"><displayItem refDataItem="Unique Order Method Categories"/></useItem></selectValue></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell></tableCells></tableRow></tableRows></table></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="py"/>
</defaultStyles>
</style>
</pageBody>
<pageFooter>
<contents>
<promptButton type="cancel">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="back">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="next">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="finish">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="fp"/>
</defaultStyles>
</style>
</pageFooter>
<style>
<defaultStyles>
<defaultStyle refStyle="pp"/>
</defaultStyles>
</style>
</page></promptPages></layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-09-27T17:03:05.357Z" output="no"/></XMLAttributes><reportName>Conditional Footer Display Report</reportName><reportVariables><reportVariable type="string" name="footer display">
<reportExpression>[Footer Display Query].[Final Footer Variable]</reportExpression>
<variableValues><variableValue value="A"/><variableValue value="B"/></variableValues></reportVariable></reportVariables></report>