How to use Isnull() in Universe

building semantic layer
1

Hi,

How i can use Isnull function in Object definition while designing universe.

My requirement:

If an object [Obj] contains empty values(Null values) then i should show them as 0 instead of null/empty.

I know i can do it in report and succeeded but i wanna it in universe.

I tried:

 
CASE WHEN tab.col1=NULL THEN 0 ELSE col1 END

It got parsed correctly. But i can’t see any 0(Zero’s) for null values in report.

Am i missing anything, please,

Thanks

BOCP (BOB member since 2007-07-02)

2

You posted:

CASE WHEN tab.col1=NULL THEN 0 ELSE col1 END

You can use either

CASE WHEN tab.col1 IS NULL THEN 0 ELSE col1 END

or

isnull(tab.col1,0)

Regards,
Mark

3

Hello Mark, thanks for your prompt reply.

I tried CASE but it not showing 0(Zero) in report where col1(Amount) is null.

Thanks

BOCP (BOB member since 2007-07-02)

4

Are you working with a crosstab?

5

YES Mark.

Why?

Thanks

BOCP (BOB member since 2007-07-02)

6

There will be blanks where no records exist for that intersection so there is actually nothing there to be not null, it’s simply an empty intersection.

You can get round it by entering 0 in the cell and then use an alerter to display the actual data with a basic bit of logic.

SELECT a1 as Row, b1 as column, 10 as amount
UNION
SELECT a2 as Row, b2 as column, 12 as amount
UNION
SELECT a1 as Row, b2 as column, 14 as amount

If you create the above as a freehand dataprovider, you will get a simple table with three rows in it
a1 | b1 | 10
a2 | b2 | 12
a1 | b2 | 14

Now convert that to a crosstab and you will see that nothing exists at the intersection of a2 and b1 - you cannot replace it with 0 because there is nothing there to replace!

What you need to do is have the cell as 0 and then create the alerter as If Not(IsNull(Amount) Then Amount) (obviously with correct syntax :wink: )

7

That’s right Mark.

But in my case i need to calculate Sum(Amount) foreach(salesoffice), here for few there is no data and when i apply that formula it showing wrong result due to Matrix. (Multiplying twice of each occurrence)

So i thought to divide that formula by Count of each. Now actual problem coming if it has values then it working good but if no values exist it’s failing in that case. So moved to make that empty/null to 0 to add to the context.

Am i doing right thing??

P.S: All values are coming dynamically(OLTP system), I can’t do it in report.

Thanks

BOCP (BOB member since 2007-07-02)

8

A simpler option might be to right click on the object and format the number to have a 0 if undefined…

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

9

I didn’t think that worked in a crosstab.

10

Works fine using efashion…and in a universe that uses SQL Server…

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

11

Good stuff, I don’t think it used to work that way. Might still not do in Deski, I’m not sure.

12

Works in webi and deski in XI 3.x

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