Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Assign Value to an Element in BI Publisher

Received Response
635
Views
9
Comments
Zimmer
Zimmer Rank 4 - Community Specialist

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.

Answers

  • Venkat Thota - BIP
    Venkat Thota - BIP Rank 7 - Analytics Coach

    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
    Zimmer Rank 4 - Community Specialist

    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
    Zimmer Rank 4 - Community Specialist

    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,

  • Sketz
    Sketz Rank 4 - Community Specialist

    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
    Sketz Rank 4 - Community Specialist

    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
    Zimmer Rank 4 - Community Specialist

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

  • Zimmer
    Zimmer Rank 4 - Community Specialist

    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
    Zimmer Rank 4 - Community Specialist

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