Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Assign Value to an Element in BI Publisher
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
-
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
0 -
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,
0 -
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,
0 -
-
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.
0 -
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
0 -
I think its because I am trying to format number afterwards but I don't know how to nest these two tags.
0 -
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’)?>,-
0 -
Thanks, Sven-BIP. Editing the query in the data model helped solve my problem.
0