The requirement to Display “All Prompt Values were selected” had to be applied to Dimensional reports as well. So the logic to come up with that had to be different from how it was done in a Relational package. Actually it needed a lot less coding in resolving it …there’s a reason I like working with MDX expression, they can be fun and a lot simpler.
Anyway the requirement was 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. The report specification is attached below and was created using Great Outdoors Sales cube.
Create a Prompt:
- Drag and Drop Query from toolbox onto the Query explorer and rename it to “Prompt Query”
- Add a Data item from the toolbox onto the Data items pane, Add the “Retailer Country” from the Retailers Dimension to the expression definition and rename it to “Retailer Country”.
- From the Page Explorer, Create a prompt page and add a “Value Prompt” onto the prompt page.
- Name the parameter “pPrompt” 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 “Retailer Country”.
Create Query for Calculating total number of Prompt Values selected.
- Add a Query onto the Query explorer and rename it to “Main Query”
- Double click the “Main Query”
- Add a data item to the data items pane and enter the calculated expression “ count( 1 Within set members( [GREAT_OUTDOORS_SALES].[Retailers].[By Retailer site].[Retailer country] ) ) ” in the expression definition area.
- Name the Data Item “Total Countries Count”.
- Add a data item to the data items pane and enter the calculated expression “count( 1 within set descendants (#promptmany( ‘pPrompt’,’memberuniquename’,”,’set(‘,'[GREAT_OUTDOORS_SALES].[Retailers].[By Retailer site].[Retailer country]’,’)’)#,[GREAT_OUTDOORS_SALES].[Retailers].[By Retailer site].[Retailer country]))” in the expression definition area.
- Name the Data Item “Selected Countries Count”.
Create Conditional Variable.
- Navigate to the Conditional Explorer.
- Add String Variable to Variables pane.
- Rename the String Variable to “Display Toggle Variable”.
- Add the following expression to the expression definition.
IF ( [Main Query].[Total Countries Count]= [Main Query].[Selected Countries Count] )
THEN ( ‘All’ )
ELSE ( ‘Some’ )
- Add two values “All” and “Some” 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 for demo purpose)
- From the Singleton properties.
- Set the Query to “Main Query”
- Select the properties ellipsis and click “Select All”
- Drag and Drop a Conditional Block within the Singleton.
- Set the Block Variable to “Display Toggle Variable”.
- Set the Current Block Variable to “All”. Then Drag and Drop text item from the toolbox within the Conditional Block.
- Enter the following text “All Prompt Values were selected” and select ok.
- Now the Set the Current Block Variable to “Some”. 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 xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='DataClarity Content Files']/package
[@name='GREAT_OUTDOORS_SALES']/model[@name='2014-02-28T00:54:56.095Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<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/><style><CSS value="padding-
top:20px;padding-
bottom:20px"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/><style><CSS
value="padding-top:20px;padding-bottom:20px;text-
align:center"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/><style><CSS
value="padding-top:20px;padding-
bottom:20px"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><singleton
name="Singleton1" refQuery="Main Query">
<contents><conditionalBlocks>
<conditionalBlockDefault>
<contents/>
</conditionalBlockDefault>
<conditionalBlockCases refVariable="Display Toggle Variable"><conditionalBlock
refVariableValue="All"><contents><textItem><dataSource><staticValue>All Prompt Values were
selected</staticValue></dataSource><style><CSS value="color:green"/></style></textItem></contents><style><CSS value="font-
weight:bold;font-size:22pt"/></style></conditionalBlock><conditionalBlock
refVariableValue="Some"><contents><textItem><dataSource><staticValue>The Following Values were selected:
</staticValue></dataSource><style><CSS value="font-size:14pt;color:red;font-
weight:bold"/></style></textItem><textItem><dataSource><reportExpression>ParamDisplayValue('pPrompt')
</reportExpression></dataSource><style><CSS value="color:green;font-size:14pt;font-
weight:bold"/></style></textItem></contents></conditionalBlock></conditionalBlockCases></conditionalBlocks></contents>
<propertyList><propertyItem refDataItem="Total Countries Count"/><propertyItem refDataItem="Selected
Countries Count"/></propertyList></singleton></contents><style><CSS value="padding-top:20px;padding-
bottom:20px"/></style></tableCell></tableCells></tableRow></tableRows></table></contents>
</pageBody>
</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><selectValue parameter="pPrompt" refQuery="Prompt Query" multiSelect="true"
selectValueUI="checkboxGroup"><useItem refDataItem="Retailer Country"><displayItem refDataItem="Retailer
Country"/></useItem></selectValue></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="2014-02-28T00:54:56.127Z" output="no"/></XMLAttributes><queries><query name="Main Query">
<source>
<model/>
</source>
<selection><dataItem name="Total Countries Count"><expression>count( 1 Within set members(
[GREAT_OUTDOORS_SALES].[Retailers].[By Retailer site].[Retailer country] ) )</expression></dataItem><dataItem
name="Selected Countries Count"><expression>count( 1 within set descendants (#promptmany(
'pPrompt','memberuniquename','','set(','[GREAT_OUTDOORS_SALES].[Retailers].[By Retailer site].[Retailer country]',')')#,
[GREAT_OUTDOORS_SALES].[Retailers].[By Retailer site].[Retailer country]))</expression></dataItem></selection>
</query><query name="Prompt Query"><source><model/></source><selection><dataItem name="Retailer
Country"><expression>[GREAT_OUTDOORS_SALES].[Retailers].[By Retailer site].[Retailer country]
</expression></dataItem></selection></query></queries><reportName>Conditional Footer Display Report -
Dimensional</reportName><reportVariables><reportVariable type="string" name="Display Toggle Variable">
<reportExpression>IF ( [Main Query].[Total Countries Count]= [Main Query].[Selected Countries
Count] )
THEN ( 'All' )
ELSE ( 'Some' )</reportExpression>
<variableValues><variableValue value="All"/><variableValue
value="Some"/></variableValues></reportVariable></reportVariables></report>
How Business Intelligence Works 