Where Condition issue in IDT

[Former Member]

Hello Gurus,

I am not that familiar with relational warehousing, I am trying to create a report over infoset using UNX unviverse. I need a measure in the infoset to be divided into multiple buckets based on prompt.

I am succesfull in creating custom measure obejcts at Business layer with @prompt function. but when i create a web i report over it, the SQL generated is combining all the where conditions for the objects dragged into report.

For ex :

At universe level i have three measures.

Measure A = Key figure X where Charectistic X < Prompt Value

Measure B = Keyfigure X where charectarictis X = Prompt Value

Measure C = Keyfigure X where Charectarisic X > Prompt  Value

all these measures are working fine independently, but when i drag two or more into report, it is showing blank values, the query scipt is using AND condition like below

select

key figure X

keyfigure X

kefigure X

from

table

where

Charectistic X < Prompt Value 

and

charectarictis X = Prompt Value

and

Charectarisic X > Prompt  Value

how can i deal with this situation? i need to evaluate the where conditions separatly,

[Former Member]

Instead of using where use CASE statements if possible:

Measure A:

sum(CASE WHEN Characteristic X < @Prompt(.....) THEN table.Value END)

This means that your where clauses won't cancel each other out.

[Former Member]

Hi kathik,

Try building three webi queries at the query panel to bring three KF's one each separately, to ensure you don't deal with and operator between those mutually exclusive conditions.

Other way around is to bring the base key figure to the webi report without those restrictions and split the measure into three using where clause at report layer as below with variables.

v_Measure A = [Key figure X] where([Charectistic X]< UserRsponse("Prompt text"))

v_Measure B = [Keyfigure X] where([charectarictis X] = UserRsponse("Prompt text"))

v_Measure C = [Keyfigure X]] where([Charectarisic X] > UserRsponse("Prompt text"))

Thanks

Mallik

[former_member182521]

Have you tried creating 3 different SQL's each for a Key figure (based on Prompt condition) and combine them?

[Former Member]

Hello Manikandhan,

Not sure if i understand you correctly, At present, all the three keyfigures have there own separate condition based on prompt in where clause boxes.