Print Page - Please suggest some alternative way to this DMR expression to filter Departments

Title: Please suggest some alternative way to this DMR expression to filter Departments
Post by: tarunkrdas2013 on 15 Sep 2015 02:28:20 PM

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?)

Title: Re: Please suggest some alternative way to this DMR expression to filter Departments
Post by: Lynn on 16 Sep 2015 02:57:51 AM

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


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

Title: Re: Please suggest some alternative way to this DMR expression to filter Departments
Post by: tarunkrdas2013 on 16 Sep 2015 11:23:08 AM

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

Title: Re: Please suggest some alternative way to this DMR expression to filter Departments
Post by: Francis aka khayman on 17 Sep 2015 12:43:23 AM

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

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: tarunkrdas2013 on 15 Sep 2015 02:28:20 PM