Hi.
I need to filter hierarchy depending on parameter.
I wrote filter:
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.
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
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
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.