How to determine if optional mutil-select prompt has any selections

[sbk12]

Does anyone know if there is a way to determine with an filter whether or not an optional multi-select value prompt has had any values selected by the user?

[melee]

Does anyone know if there is a way to determine with an filter whether or not an optional multi-select value prompt has had any values selected by the user?

Well, I think knowing what you're trying to do might help a little. If you just want to filter when there's options selected, make an optional filter with [Data item] in ?Prompt Name? and it should take care of it. If you're looking for more advanced functionality, let us know what it is and we might have some ideas.

[CognosPaul]

If you're looking to do this through a variable you could try something like:

paramValue('Parameter') is not null

But as melee said, to give a meaningful answer we need to have more information. Without it we can just say, "Yes".

[sbk12]

I have a report with a filter based on several multi-select prompts, all of which are different product levels (Product Group, SubGroup within group, individual model numbers).  So, the user may want to select entire product groups, and/or one or more product sub-groups (but not all) within a product group, and/or individual models.

For example, based on the following example data...

Product Group: A
   SubGroup: A1 - Models: A1M1, A1M2, A1M3
   SubGroup: A2 - Models: A2M1, A2M2, A2M3
   SubGroup: A3 - Models: A3M1, A3M2, A3M3

Product Group: B
   SubGroup: B1 - Models: B1M1, B1M2, B1M3
   SubGroup: B2 - Models: B2M1, B2M2, B2M3
   SubGroup: B3 - Models: B3M1, B3M2, B3M3

Product Group: C
   SubGroup: C1 - Models: C1M1, C1M2, C1M3
   SubGroup: C2 - Models: C2M1, C2M2, C2M3
   SubGroup: C3 - Models: C3M1, C3M2, C3M3


...the user may want to select the following:

all models in Product Group 'A' (by selecting Product Group 'A' from the ProductGroup prompt)
-and-
all models from Product SubGroups 'B1' and 'B2' of Product Group 'B' (by selecting Product SubGroups 'B1' and 'B2' from the Product SubGroup prompt)
-and-
only the individual models 'C1M2' and 'C3M3' from Product Group 'C' (by selecting them from the individual models prompt)

My optional filter is set up as follows:

([Product Group] in (?Product_Group?)) or ([SubGroup] in (?SubGroups?)) or ([Model] in (?Models?))

However, because the prompts are optional, if nothing is selected in any one of the prompts, then EVERYTHING is selected. 

[CognosPaul]

So if I understand correctly, you want data to be returned only when a value is selected.

Try this:

Code Select Expand

([Product Group] in (#promptmany('Product_Group','string',sq('None'))#)) or ([SubGroup] in (#promptmany('SubGroups','string',sq('None'))#)) or ([Model] in (#promptmany('Models','string',sq('None'))#))

The third parameter in the prompt macro is the default value. So if the user selects only Product Groups A and B the filter will look like:

Code Select Expand

([Product Group] in ('A','B')) or ([SubGroup] in ('None')) or ([Model] in ('None'))

[sbk12]

Thanks, PaulM

I finally got a chance to test this and it works perfectly.... on a relational data source.  However, I neglected to mention that I'd like to be able to do this with a multi-dimensional data source. 

Unfortunately, when I use this with a multi-dimensional data source I receive the following error:

QE-DEF-0478

Invalid coercion from 'level' to 'string' for '[SalesCube].[All Products].[All Products].[Group]' in '[SalesCube].[All Products].[All Products].[Group] in ('[SalesCube].[All Products].[All Products].[Group]->:[PC].[@MEMBER].[A]') or [SalesCube].[All Products].[All Products].[SubGroup] in ('None') or [SalesCube].[All Products].[All Products].[Product Code] in ('[SalesCube].[All Products].[All Products].[Model]->:[PC].[@MEMBER].[C1M2]')'.

Is there any work-around for this?

[CognosPaul]

And you were using detail filters with a multi-dimensional model? For shame! Since you're working with OLAP you need to use slicers, not detail filters. It also makes things much simpler.

To start, drag in a slicer with the following expression:

#promptmany('Product_Group','memberuniquename','[Cube].[Dim].[Hier].[Level]->[Whatever].[AllMember]','set(','',')')#

If the user doesn't select any values then it returns the all member to the slicer. If they do select some members then it's wrapped in a set().

Drag in another slice using the same expression, replacing the parameter name and the all member, for each of the other parameters.

And don't let me catch you using detail filters on olap sources again.  ;)

[sgoode]

This a very old topic but I was able to use this in my solution to provide the users what I consider and unorthodox solution where I have four optional prompts and if no data is selected in any of them they wanted the result screen to be blank.  By default the result screen was providing all data due to no filters being selected.  I was able to use this method to now when an optional prompt is not selected the result screen is blank.

Just wanted to say thanks.

My solution filter ended up as:

[Presentation View].[TEST].[IN] in (#promptmany('Inc','string',sq('None'))#)
or
[Presentation View].[TEST].[VEND] in (#promptmany('Inc','string',sq('None'))#)
or
[Presentation View].[TEST].[PRO]  in (#promptmany('Mod','string',sq('None'))#)
or
[Presentation View].[TEST].[SER] in (#promptmany('Ser','string',sq('None'))#)
or
[Presentation View].[TEST].[COM] in (#promptmany('Key','string',sq('None'))#)

[CognosPaul]

For something like that I would actually use a variable to conditionally render the data container.


case
  when paramValue('Inc') is null
    and paramValue('Mod') is null
    and paramValue('Ser') is null
    and paramValue('Key') is null
    then 'Don''t Render'
else 'Render'
end

This way your database won't be hit with the query at all.