cancel
Showing results for 
Search instead for 
Did you mean: 

Capture blank/null in IDT

Former Member
0 Kudos

Hi,

My universe in IDT is designed to capture records from seven different dim tables in SQL Server. Unfortunately some of the records or fields are blank and showing up as blank in the webi report. I tried putting ISNULL in webi but did not work. How can I put the ISNULL in IDT? Previously there is an option of inserting 0 for undefined for each object. I don't see that in the IDT, may be I am missing something.

Appreciate your suggestion.

Arun

Accepted Solutions (0)

Answers (3)

Answers (3)

0 Kudos

Hi,

If it's the measure value is null, Create the variable with the following function in WEBI

=if(isnull(measure)) then 0 else measure

Thanks,

Santhosh

jyothirmayee_s
Active Contributor
0 Kudos

Hi,

May be you can try

Ifnull()

Thanks,

Jothi

Former Member
0 Kudos

Did you try inserting query filters on those fields in webi query panel as below for all the objects you want to filter out nulls.

[Object1] Is not Null

AND

[Object2] Is not Null

You can open the report in Modify mode (Right click on report>Modify) and set a custom format on the column to define Undefined as 0, if you wish to handle nulls as zeroes.

Thanks

Mallik

Former Member
0 Kudos

Thanks Mallikarjun,

For each column where there is blank, I inserted isnull formula. But its not reflecting on the webi report.

Where do I set the custom format? I opened the report in modify mode and right clicked the column with blanks. Now which option should I click to enable the undefined option?

Arun

Former Member
0 Kudos

Right Click on Column>Format Number and select the number format you wish to have and click on Custom button.

You would be provided with undefined option, where you can replace undefined with 0.

Thanks

Mallik

Former Member
0 Kudos

Ok. I got it. Whats if the column datatype is string?

Arun

Former Member
0 Kudos

you write an if else logic as below with "0" or any other text as you wish such as "NA", "BLANK".

If(IsNull([Object]);"NA";[Object])

Thanks

Mallik

Former Member
0 Kudos

Thanks. It works if the report has a value.

For example I am trying to bring country name from the universe into webi report. The webi report is connected to a Dashboard. Certain countries are not there in the webi because it is not in the universe, but when we select those countries from Dashboard, it throws an error. Since there are no country in the Webi, I have to put some value like N/A so that dashboard don't throw error.

Is that possible in webi? Or I can do it in Universe? How can I do it if so?

Arun

Former Member
0 Kudos

Yes Arun that very much possible,

We have done the same on webi report with BW data sourcing dashboard naming the value as "UNK". we use variables with logic as below, both for list of values and in the Blocks, so that UNK from dashboard comes and filters the block with same value when variables is given as web service filters

=If [Mode]="#" Then "UNK" Else [Mode]

I suggest you better post this as different question in SAP BusinessObjects Dashboards space.

Thanks

Mallik