Multi-select value prompt error

[Web2CRM]

Hi,

I have a multi-select value prompt ?Show1? created with Static choices "use value" DT and AC.  I want to filter the report and return a result based on the value selected by the user.  Selecting 'DT' will pull all data with value 'Y' in DT data item and 'AC' will pull all data with value 'Y' in AC data item. I have a filter expression that works perfectly when I selected one of the values in multi-select value prompt.  The problem is when I try to select both 'DT' and 'AC' value.  It is returning an error "An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'." UDA-SQL-0358 Line 20: Syntax error near "=".RSV-SRV-0042 Trace back:RSReportService.cpp(722): etc......

Here's the filter expression I used that return no error when validated:

if ('DT' in ?Show1?) then
([Drill Through Reports Used]='Y') else
if ('AC' in ?Show1?) then
([Show Ac Icon]='Y') else
if (('DT' in ?Show1?) and ('AC' in ?Show1?)) then
([Drill Through Reports Used]='Y' or [Show Ac Icon]='Y') else (?Show1? is null)



Thanks!!

[Satheesh]

Hi,
In my Knowledge for Static choice you can't apply multi select....
For Static choices U can select Single select only...

[MFGF]

Hi,
In my Knowledge for Static choice you can't apply multi select....
For Static choices U can select Single select only...

Hi,

You will be delighted to know that this isn't true :) You can have a multi-select prompt based on Static Choices as easily as one based on database values. Perhaps you are thinking of only being able to have one Default Selection?

Cheers!

MF.

[Lynn]

Hi,
In my Knowledge for Static choice you can't apply multi select....
For Static choices U can select Single select only...

You can make a static choice prompt multi-select the same way as you would for a prompt fed by a query.

The problem with the filter is that you shouldn't use if/then/else or case expressions. You need to write the various conditions so that a Boolean result is returned. Separate each with "or".

Code Select Expand


(
'Something' in ( ?Parameter? )
and
[QueryItem] = 'Whatever'
)
or
(
etc
)
or
(
etc
)


[Web2CRM]

Thank You Lynn!!!

Your solution works perfectly in my report.. ;)

[Satheesh]

Thanx all.....


I didn't think in that way...thanks to all

[CognosPaul]

Another solution.

In the static choices, set the display/use to:

Display |             Use
--------+---------------------------------
DT      | [Drill Through Reports Used]='Y'
AC      | [Show Ac Icon]='Y'


In the filter, use the a macro expression to transform the selected values correctly.

Code Select Expand

#
join(
  ' or '
  , split(
    ';'
    , promptmany('Show1','token')
  )
)
#
promptmany will return [Drill Through Reports Used]='Y';[Show Ac Icon]='Y', The split will convert it into an array, and join will separate them with " or ". If needed, you can set the default values in the macro very easily.

Code Select Expand

#
join(
  ' or '
  , split(
    ';'
    , promptmany('Show1','token','[Drill Through Reports Used]='+sq('Y')+';[Show Ac Icon]='+sq('Y'))
  )
)
#

This way allows you to add more filter possibilities into the prompt, without needing to touch the filter.

[Web2CRM]

Thanks PaulM, I will surely try that approach in my report.  Thanks everyone for the help, really appreciate it!!!!