Please suggest some alternative way to this DMR expression to filter Departments

[tarunkrdas2013]

Hi ,
Can anyone suggest me any alternative to filter departments ,currently its like below :-

filter(
(UNION(
UNION(
UNION(
UNION(
UNION(
UNION(
UNION(
union (children([FOOD]),[FOOD])
,
union (children([HARDLINES]), [HARDLINES])
)
,
union (children([SOFTLINES]),[SOFTLINES])
)
,
member(aggregate( currentMeasure within set set([HARDLINES],[SOFTLINES])),'NON-FOOD','NON-FOOD',[Hierarchical].[Department].[Department])
)

,
union (children([FRESH FOOD]),[FRESH FOOD])
)
,
member(aggregate( currentMeasure within set set([HARDLINES],[SOFTLINES],[FOOD],[FRESH FOOD])),'MERCH SALES','MERCH SALES',[Hierarchical].[Department].[Department])
)
,
union (children([OTHER BUS.]),[OTHER BUS.])

),

parent([FOOD])
)
),

roleValue('_memberCaption',currentMember(hierarchy((UNION(
UNION(
UNION(
UNION(
UNION(
UNION(
UNION(
union (children([FOOD]),[FOOD])
,
union (children([HARDLINES]), [HARDLINES])
)
,
union (children([SOFTLINES]),[SOFTLINES])
)
,
member(aggregate( currentMeasure within set set([HARDLINES],[SOFTLINES])),'NON-FOOD','NON-FOOD',[Hierarchical].[Department].[Department])
)
,
union (children([FRESH FOOD]),[FRESH FOOD])
)
,
member(aggregate( currentMeasure within set set([HARDLINES],[SOFTLINES],[FOOD],[FRESH FOOD])),'MERCH SALES','MERCH SALES',[Hierarchical].[Department].[Department])
)
,
union (children([OTHER BUS.]),[OTHER BUS.])

),

parent([FOOD])
)))
)) in ?p_Department?)

[Lynn]

You have posted a 65 line expression and asked people for alternatives without providing any information about what your issue is, if any. Nor have you given any reasoning or even the barest explanation about why the current logic is doing the Cognos equivalent of a triple somersault with two half-twists and a back flip.

So I'll suggest that an expression to filter departments could simply be this:

Code Select Expand


set ( [Your Namespace].[Your Dimension].[Your Hierarchy].[Your Level]  -> ?p_Department? )


[tarunkrdas2013]

Sorry actually my requirement to change the  big expression is due to performance it is degrading the performance due to lot of Unions

[Francis aka khayman]

might be better to take a look at the bigger picture... the design based on requirement.

a re design and re development might be in order