Union in the BOE Object in the Universe

building semantic layer
1

Hi,

I have a requirement wherein I need to create a BOE Object i the Universe as a measure and have the SQL behind the object to pull data from different tables based on conditions.

Example:

CASE WHEN (end_date.end_date <= convert(char(10),@variable(‘Enter Report Date(MM/DD/YYYY)’),101))
THEN Table 1.a
ELSE 0.00
END

UNION
CASE WHEN (end_date.end_date <= convert(char(10),@variable(‘Enter Report Date(MM/DD/YYYY)’),101))
THEN Table 2.b
ELSE 0.00
END

UNION
CASE WHEN (end_date.end_date <= convert(char(10),@variable(‘Enter Report Date(MM/DD/YYYY)’),101))
THEN Table 3.c
ELSE 0.00
END

I need to do the UNION on three tables to get my measure values. When I try implementing this ligic BOE does not like and throws errors.

Please suggest a way to getb this done at the Universe level.

Thank You,
dpad

dpad :us: (BOB member since 2010-05-10)

2

Very brief:
Create a derived table in the universe.

Andreas :de: (BOB member since 2002-06-20)

3

And use UNION ALL if you want duplicates to returned

jemstar :ireland: (BOB member since 2006-03-30)