(RS)Static Choice in prompt box

[Arsenal]

Hi all,

I have a prompt(checkbox) in a prompt page that has 3 static choices coming off a query item that uses a code

The choices are:

Use A Display Apples
Use B Display Ball
Use C Display Color

The report works fine with that

But, there is a new requirement that cuts down the choices...basically, users want to see only two choices, but if they select A, then values of C should also be pulled in and if they select B then only B values should be used

Obviously, in the static choices I can't say Use A, C Display Apples. I tried adding a filter in the main query which says:

If (?cd? = 'A')
THEN ([view].[file].

Code Select Expand

in ('A', 'C'))
ELSE IF (?cd? = 'B')
THEN ([view].[file].[code] ='B')
ELSE ('error')

But now nothing gets pulled in if A is selected.

Any suggestions? Thanks[/code]

[MFGF]

Hi Arsenal,

Your basic approach seems fine.  My guess is there's a problem with your filter syntax.  Try modifying the syntax as follows:

Code Select Expand

if (?cd? = 'A') then ([view].[file].[code] in ('A','C')) else ([view].[file].[code] = 'B')

I put together a quick example using the GO Sales & Retailers package, and it works fine for me.  The report XML is pasted below.

Regards,

MF.

<report xmlns="http://developer.cognos.com/schemas/report/2.0/" expressionLocale="en"><!--RS:8.1-->
   <modelPath>/content/package[@name='GO Sales and Retailers']/model[@name='model']</modelPath>
   <queries>
      <query name="Query1">
         <source>
            <model/>
         </source>
         <selection><dataItem name="Product line code" aggregate="none"><expression>[gosales_goretailers].[Products].[Product line code]</expression></dataItem><dataItem name="Product line" aggregate="none"><expression>[gosales_goretailers].[Products].[Product line]</expression></dataItem><dataItem name="Revenue" aggregate="total"><expression>[gosales_goretailers].[Orders].[Revenue]</expression></dataItem></selection>
      <detailFilters><detailFilter use="required" postAutoAggregation="false"><filterExpression>if (?param? = 'A') then ([gosales_goretailers].[Products].[Product line code] in (1,5)) else ([gosales_goretailers].[Products].[Product line code] = 2)</filterExpression></detailFilter></detailFilters></query>
   </queries>
   <layouts>
      <layout>
         <reportPages>
            <page class="pg" name="Page1">
               <pageBody class="pb">
                  <contents>
                     <list class="ls" refQuery="Query1">
                        
                        
                        
                        <style>
                           <CSS value="border-collapse:collapse"/>
                        </style>
                     <listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Product line code"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Product line code"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Product line"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
                  </contents>
               </pageBody>
               <pageHeader class="ph">
                  <contents>
                     <block class="ta">
                        <contents>
                           <textItem class="tt">
                              <dataSource>
                                 <staticValue/>
                              </dataSource>
                           </textItem>
                        </contents>
                     </block>
                  </contents>
                  <style>
                     <CSS value="padding-bottom:10px"/>
                  </style>
               </pageHeader>
               <pageFooter class="pf">
                  <contents>
                     <table class="tb">
                        <tableRows>
                           <tableRow>
                              <tableCells>
                                 <tableCell>
                                    <contents>
                                       <textItem>
                                          <dataSource>
                                             <reportExpression>AsOfDate()</reportExpression>
                                          </dataSource>
                                       </textItem>
                                    </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%"/>
                        </style>
                     </table>
                  </contents>
                  <style>
                     <CSS value="padding-top:10px"/>
                  </style>
               </pageFooter>
            </page>
         </reportPages>
      <promptPages><page class="pp" name="Prompt Page1">
               <pageHeader class="hp">
                  <contents>
                     <block class="ta">
                        <contents>
                           <textItem class="tt">
                              <dataSource>
                                 <staticValue/>
                              </dataSource>
                           </textItem>
                        </contents>
                     </block>
                  </contents>
               </pageHeader>
               <pageBody class="py">
                  <contents><selectValue parameter="param" multiSelect="false" range="false" required="true"><selectOptions><selectOption useValue="A"><displayValue>Apples</displayValue></selectOption><selectOption useValue="B"><displayValue>Ball</displayValue></selectOption></selectOptions></selectValue>
                  </contents>
               </pageBody>
               <pageFooter class="fp">
                  <contents>
                     <promptButton type="cancel" class="bp">
                        <contents/>
                     </promptButton>
                     <promptButton type="back" class="bp">
                        <contents/>
                     </promptButton>
                     <promptButton type="next" class="bp">
                        <contents/>
                     </promptButton>
                     <promptButton type="finish" class="bp">
                        <contents/>
                     </promptButton>
                  </contents>
               </pageFooter>
            </page>
         </promptPages></layout>
   </layouts>
</report>[/code][/code]

[newmans_99]

Another option would be to let the static prompt values drive the filter. It is a bit more straight forward and can be used for other techniques as well. For example, create a list of static values in a prompt named "promptFilter":

Code Select Expand


Display     Use
A            [view].[file].[code] in ('A','C')
B            [view].[file].[code] = 'B'


Then in your filter add:

Code Select Expand


prompt('promptFilter','token','')


This way no matter what they select the value of the Use data will be put into your filter. This reduces the conditional logic that Cognos has to process and provides for a more straight forward manipulation of the filter results (i.e., more flexibility and less quote management).[/code][/code]

[MFGF]

Nice technique!  Thanks for sharing.  :)

MF.

[Arsenal]

MFGF and newmans, thanks a lot for your replies.

Initially, I tried the IF then thing, but it wouldn't work if both A and B were selected.

So, I then tried the #prompt thing and created a new category All, so that the choices were A, B and All. That worked.

Thanks guys :)