Assign Value to an Element in BI Publisher

Zimmer

Summary

Assign Value to an Element in BI Publisher

Content

I am new in using BI Publisher and I am wondering how to assign a value to an element of BI Publisher Data Model if it's null. For example in my .rtf template I have <?AMOUNT?>  which is a variable. If it's null in the database, I would like to define a default value based on If...else conditions. How can I do for example <?AMOUNT=3?>

Thanks.

Venkat Thota - BIP

if its null value in database then you can use database functions like create one column using function case when element is null then 'put here your value' end column_name. you can use at RTF level :

required value

or edit data model , go to column properties , there you will find ifnull function , just replace own value if value is null http://www.oraappdata.com/2017/09/if-then-else-statement-in-bi-xml-publisher-reports.html#.WwLO24gvxRY

Zimmer

Thanks, Venkat-BIP. But I am wondering if this <?param@begin...?> works with a parameter already present in a data model too. I don't want to declare a new parameter.

Regards,

Zimmer

Thanks, Venkat-BIP. Actually I am summing some variables at the end like sum(AMOUNT1+AMOUNT2+AMOUNT3) which is the reason why I want to assign a default value to these as summing variables if they are null lead to error.

This idea of setting default value in the data models is great but I am trying to put it in code.

Regards,

Venkat Thota - BIP

References : https://docs.oracle.com/middleware/12212/bip/BIPRD/GUID-C5A6173D-9D9A-47BD-9F8E-45A72B5E08A4.htm#BIPRD2532

Sketz

You should do that in the data model.

Just use NVL in your query. It'll perform much quicker than it does inside the template.

Sketz

Do you recieve any error or is the output not like you've expected?

Without that info it'll be hard to help you...

But I'll take a guess to speed things up...

Even if your code won't lead to a error the output should be something like

12345  12 345 when amount1 would be 12345.

You've got two different and separate statements.

The first one will replace amount1 with '0' if it is 'null'

The second one will format amount1 with the underlaying format mask. That statement will raise an error when the input could not be formatted. 'null' can't be formatted  because 'null' isn't a number.

The xdofx:nvl-function can't replace any values in the data itself. It works just inside the tags...

So you'll need to nest the nvl inside the formatting function.

Unfortunately it doesn't work that way, you can't use xdofx-functions inside of 'normal' BI-functions.

But there is a workaround

Use the nvl() in your data model sql query where it would be something like that:

select [...],       nvl(amount1, 0),       [...]from   [...]

I hope this will help you

Zimmer

I think its because I am trying to format number afterwards but I don't know how to nest these two tags.

Zimmer

Thanks Sven-BIP. I used NVL but for some reason it's not working. I am unable to figure out why. Here's my code:

<?xdofx: nvl(AMOUNT1,0)?><?format-number(AMOUNT1,'## ##0',’Yen’)?>,-

Zimmer

Thanks, Sven-BIP. Editing the query in the data model helped solve my problem.