How to write filter correctly?

[Kindness]

Hi.
I need to filter hierarchy depending on parameter.
I wrote filter:

Code Select Expand

if ( ?SectionParameter? = 'Other Sections' ) then
  (
    [Dimention_View].[Questions].[Questions] in (
          except ( [Dimention_View].[Questions].[Questions].[Section],
                        topCount ( [Dimention_View].[Questions].[Questions].[Section],
                                           3,
                                           [Dimention_View].[Corrective actions].[CA count]
                                         )
                      )
     )
  )
else
  ( [Dimention_View].[Questions].[Questions] = ?SectionParameter? )
But validation returns error

"OP-ERR-0061 Filter expressions must evaluate to Boolean. Error processing filter 'if (?SectionParameter? = 'Other Sections') then (members([__ns_0].[Questions].[Questions]) in (except(members([__ns_0].[Questions].[Questions].[Section]),topCount(members([__ns_0].[Questions].[Questions].[Section]),3,[__ns_0].[MEASURES].[CA count])))) else (members([__ns_0].[Questions].[Questions]) = ?SectionParameter?)'."

How to correct this?

thanks.

[bdouglas]

The best advice I've gotten is to not use if or case statements in filters.  Instead, use or/and logic to achieve your goal.  So here might be a way to convert your statement below to a usable filter

(?SectionParameter? = 'Other Sections and [Dimention_View].[Questions].[Questions] in (
          except ( [Dimention_View].[Questions].[Questions].[Section],
                        topCount ( [Dimention_View].[Questions].[Questions].[Section],
                                           3,
                                           [Dimention_View].[Corrective actions].[CA count])
                      )
     )
)
or
( [Dimention_View].[Questions].[Questions] = ?SectionParameter? )

Hope that helps

[CognosPaul]

When working with a dimensional query the best practice is to avoid using detail filters. Clever use of macros and static choices will get you what you need.

The first question: Is this DMR or a real cube?
The first assumption: You're using a value prompt, based on the top 3 Sections in the Question hierarchy, with a static choice of Other Sections.

If you're referencing the Questions hierarchy in the data container, use a data item, if it's not being referenced in the data container, put the following expression in the slicer:

#prompt('SectionParameter','token')#

Now set the use value of Other Sections to

Code Select Expand


  except ( [Dimention_View].[Questions].[Questions].[Section],
                        topCount ( [Dimention_View].[Questions].[Questions].[Section],
                                           3,
                                           [Dimention_View].[Corrective actions].[CA count]
                                         )
                      )


The token prompt will accept both MUNs and MDX fragments. So if the user selects the Other Sections, it would be as if you put in that except statement in the data item.