Prompts

[Rookie]

I have a report requirement where my DB has 50 transaction types but the report needs to only report on a total of 6 but not necessarily ALL 6.  So I have a prompt on the prompt page where the 6 are in a list box as a multi select (alphaneumeric field).  Then in the report query I need something like
If ?transtype? is missing then [View].[TRANSTYPE] in ('01', '05', '10', '23', '45', '99')
else ?transtype? in [View].[TRANSTYPE] but I get parsing errors nomatter how I code it.  Bottom line is the user can select all 6 manually, any combination of the 6 and I want to return what is selected.  If they don't select anything then I only want to return rows that contain the 6 listed above.  This is in Cognos 8.4.1 Report Studio.
Thanks in advance.

[kalyan_y]

Hi,

If your report displays data for only 6 'transtypes', then have a filter exp as [View].[TRANSTYPE] in ?transtype? and make it optional.

This way, if user selects one or more 'transtypes', then data gets filtered in the report as per the filter exp. If user doesnot select any of the 'transtypes', report by default displays data for all 6 'transtypes'.


If your filter brings in data for all 50 'transtypes', then have two filters

1.  [TRANSTYPE] in ('01', '05', '10', '23', '45', '99') and make it required.
2. [TRANSTYPE] in ?transtype? and make it optional.

This should do...

[blom0344]

I have a report requirement where my DB has 50 transaction types but the report needs to only report on a total of 6 but not necessarily ALL 6.  So I have a prompt on the prompt page where the 6 are in a list box as a multi select (alphaneumeric field).  Then in the report query I need something like
If ?transtype? is missing then [View].[TRANSTYPE] in ('01', '05', '10', '23', '45', '99')
else ?transtype? in [View].[TRANSTYPE] but I get parsing errors nomatter how I code it.  Bottom line is the user can select all 6 manually, any combination of the 6 and I want to return what is selected.  If they don't select anything then I only want to return rows that contain the 6 listed above.  This is in Cognos 8.4.1 Report Studio.
Thanks in advance.

I would add a static choice 'All' and then use:

CASE WHEN
?transtype? = 'All' then
[View].[TRANSTYPE] in ('01', '05', '10', '23', '45', '99')
else
[View].[TRANSTYPE] in ?transtype?
end

[Rookie]

 :) Thanks for the help.  It worked perfectly.

[blom0344]

:) Thanks for the help.  It worked perfectly.

Just checking, but which proposed solution solved your issue?