Multi-select Prompt with Case When or AND/OR Logic

[Magdalina08]

Is there a way to apply the multi-select property to a prompt that uses this kind of logic?

Requirement:
We have offices A - Z
I need a multi-select prompt where users can choose A, B, F, R, T, and/or All (all will show all other values.)

I have this working with a case when statement filter for the prompt, but it's not multi-select. I applied a #promptmany macro to another issue like this, but am unsure how it would work in this scenario.  I can't find anything online about it, so I am not even sure that would be the correct solution. 

**Edit: This solution does not work!**  The other solution I came up with is to use a checkbox group with static values for the 5 desired values and then add a separate radio button to show all other values.  This works, but is not the best solution. 

[Andrei I]

I would use an optional prompt.
And add a Text explaining that if nothing is selected then data for all offices will be shown
Query would be executed faster

[Magdalina08]

I would use an optional prompt.
And add a Text explaining that if nothing is selected then data for all offices will be shown
Query would be executed faster

That would be the easy way out and I would be totally for it.  Unfortunately, they want to be able to select A and All or A, B, and All, etc.  The way I have it set up with the check box group and separate radio button works and I think it's fine, but they would prefer to have all in one check box group making it look nicer. 

[Andrei I]

You should have 'Select All/ Deselect All' check boxes in the Value Prompt (mutli-select=yes, Select UI=Check box group)

[Magdalina08]

You should have 'Select All/ Deselect All' check boxes in the Value Prompt (mutli-select=yes, Select UI=Check box group)

Typically I would have that, but because I have to have the extra logic of a "case when" or "And/Or" statement, it only allows for single select.

[Andrei I]

Of course you can use Javascript and build whatever custom prompting logic you need but it would be such an overkill.
I wouldn't ever advise it unless Cognos reporting is integrated with a custom Web-app

[seb24c]

I can't tell exactly from your description but it sounds like the "All" option would be the other letters NOT including the ones that are separate options, correct? (Otherwise your example of selecting A & All or A, B, & All would be exactly the same.)

If that is the case, you should be able to do this by creating a calculated field based on the offices.

Create prompt query q_Prompt with data items:
[Office]
[c_Use] = if ( [Office] in ('A', 'B', 'F', 'R', 'T') ) then ( [Office] ) else ( 'ALL' )

In your main query:
- copy in c_Use
- add filter: [c_Use] in ?p_Office? (optional)

Finally, create your prompt for p_Office on q_Prompt with Use value = c_Use.

[Magdalina08]

I can't tell exactly from your description but it sounds like the "All" option would be the other letters NOT including the ones that are separate options, correct? (Otherwise your example of selecting A & All or A, B, & All would be exactly the same.)

If that is the case, you should be able to do this by creating a calculated field based on the offices.

Create prompt query q_Prompt with data items:
[Office]
[c_Use] = if ( [Office] in ('A', 'B', 'F', 'R', 'T') ) then ( [Office] ) else ( 'ALL' )

In your main query:
- copy in c_Use
- add filter: [c_Use] in ?p_Office? (optional)

Finally, create your prompt for p_Office on q_Prompt with Use value = c_Use.

It's actually like this:

A = Office A
B = Office B
C = Office C
D = Office D
E = Office E
All = Offices F through Z

So the client would like a check box selection where they can choose A, B, All or B or All or C, D, A, All or any other sequence.  A through E are their most used offices, where F through Z are rarely used, but users may want to see them along with any other sequence, or just see them alone.

[seb24c]

Then the simplest option, if possible, would be to edit your prompt values so they don't overlap, and use my suggestion above.

A
B
C
D
E
All - offices F through Z

[Andrei I]

Now I got what you are trying to do  :)
Though the solution seems pretty obvious to me:

Add a Static choice to your prompt 'All'
Then filter would look like this:
[Office] in ?par_Office?
OR
'All' in ?par_Office?
AND [Office] in ('F','G',...,'Z'  ) <=  list all offices which make All.


Please let me know if it works

[Magdalina08]

Now I got what you are trying to do  :)
Though the solution seems pretty obvious to me:

Add a Static choice to your prompt 'All'
Then filter would look like this:
[Office] in ?par_Office?
OR
'All' in ?par_Office?
AND [Office] in ('D','E',...,'Z'  )  <=  list all offices which make All

Please let me know if it works

I will try this!  My problem is everything else I am doing makes it single select.  It's been a week long fight with Cognos! LOL

[Magdalina08]

Now I got what you are trying to do  :)
Though the solution seems pretty obvious to me:

Add a Static choice to your prompt 'All'
Then filter would look like this:
[Office] in ?par_Office?
OR
'All' in ?par_Office?
AND [Office] in ('F','G',...,'Z'  ) <=  list all offices which make All.


Please let me know if it works

You are amazing!!!!! Thank you so much.  It worked!   ;D