nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Can BIRT make Joint Data Set nulls appear as zeroes?

talva

My report has a joint data set that uses a left outer join. When there isn't a matching record in the data set being joined to, the value returns as null. Is there a way to display the nulls as zeroes?

Find more posts tagged with

Comments

Tubal

There's a few ways you could do this:

1. Set up a computed column in your joint dataset based on your column that contains nulls, and just say if x is null, then 0, else x. Then handle your data based on the computed column rather than the column with the null values.

2. You could handle the null value in your table's data binding using the same method. In your data binding, just say if x is null then 0 else x.

kclark

You could create a computed column in your dataset. Assuming the field returning NULL is called someData you could do something like this. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if(row["someData"] == "NULL") {
this.text = "0";
}else{
this.text = row["someData"].getValue();
}
</pre>
 
Then use the computed column in your table instead of row["someData"] -- this will either display 0 or the data.

talva

Thanks Tubal and kclark. 
 
I am still having trouble with this. Here is what I am doing, please let me know if it is flawed: 
 
1. I edit the joint data set I want to compute the column for. 
2. I select Computed Columns 
3. I select New. 
4. I fill in the Column Name (does this need to match the column name in the table?) 
5. I attempt to build an expression. 
 
This is where it gets questionable: 
 
6. I select the Available Data Sets in the Category, then Select the Sub-Category which just has the single joint data set, but the columns do not display in the "Double Click to insert" section. 
 
7. I ignore the fact that the column names don't show up, and I add this expression to the expression editor: if(row["CXL Volumes::volume"] == "NULL") { this.text = "0";}else{ this.text = row["CXL Volumes::volume"].getValue();} 
 
8. I attempt to preview the report and I get the following error: 
 
Table (id = 1511): 
+ Fail to compute value for computed column "Volumes". 
A BIRT exception occurred: There are errors evaluating script "if(row["CXL Volumes::volume"] == "NULL") { this.text = "0";}else{ this.text = row["CXL Volumes::volume"].getValue();}": 
TypeError: Cannot find function getValue in object 0. (<inline>#1). See next exception for more information. 
There are errors evaluating script "if(row["CXL Volumes::volume"] == "NULL") { this.text = "0";}else{ this.text = row["CXL Volumes::volume"].getValue();}": 
TypeError: Cannot find function getValue in object 0. (<inline>#1) 
 
Note: I don't know if it is relvant, but the joint data set is combining Data Sets from two Data Sources.

Tubal

<blockquote class='ipsBlockquote' data-author="'talva'" data-cid="110860" data-time="1351008484" data-date="23 October 2012 - 09:08 AM">
 
1. I edit the joint data set I want to compute the column for. 
Ok 
2. I select Computed Columns 
Ok 
3. I select New. 
Ok 
4. I fill in the Column Name (does this need to match the column name in the table?) 
Ok (Name doesn't matter) 
5. I attempt to build an expression. 
 
This is where it gets questionable: 
 
6. I select the Available Data Sets in the Category, then Select the Sub-Category which just has the single joint data set, but the columns do not display in the "Double Click to insert" section. 
 
If the columns do not display, then there is a problem with the way your joint dataset is set up. You should have a list of every column from both datasets in here. This is where the probem is. 
 
7. I ignore the fact that the column names don't show up, and I add this expression to the expression editor: if(row["CXL Volumes::volume"] == "NULL") { this.text = "0";}else{ this.text = row["CXL Volumes::volume"].getValue();} 
 
8. I attempt to preview the report and I get the following error: 
 
Table (id = 1511): 
+ Fail to compute value for computed column "Volumes". 
A BIRT exception occurred: There are errors evaluating script "if(row["CXL Volumes::volume"] == "NULL") { this.text = "0";}else{ this.text = row["CXL Volumes::volume"].getValue();}": 
TypeError: Cannot find function getValue in object 0. (<inline>#1). See next exception for more information. 
There are errors evaluating script "if(row["CXL Volumes::volume"] == "NULL") { this.text = "0";}else{ this.text = row["CXL Volumes::volume"].getValue();}": 
TypeError: Cannot find function getValue in object 0. (<inline>#1) 
 
Note: I don't know if it is relvant, but the joint data set is combining Data Sets from two Data Sources. 
Not relevant. I do this often. </blockquote>

talva

Thank you for the response, Tubal. I've deleted and re-created the joint data set, and still have this issue. The odd thing is that if I select the table in the report layout, then attempt to add a binding, then build an expression, the column names do show up in the "Double Click to insert" section. Should I be adding the computed column on the joint data set, or on the individual data set? (My attempts have been on the joint set.)

Tubal

I'm not sure if it's a bug in BIRT or what, but it appears that some Joint Data Sets make those available, and some don't. I'm not sure what the difference is. I tried to make a few after you said that it's not creating them for you, and some list them and some don't. I tried a join on a text field between two data sets from two different data sources, and it worked fine. Then I tried two different data sets joined on a text field, and it didn't. 
 
Regardless, you should also be able to set this to 0 in your data binding to your table rather than your data set. 
 
Depending on how you created your table, your bindings may already be made. Select your table that's bound to your joint data set, click on the Binding tab in the property editor of the table, and find the field from the dataset you want to work with. If it's not there already, create a binding for it. 
 
Then in the expression of the binding, it will say something along the lines of: 
<pre class='_prettyXprint _lang-auto _linenums:0'>dataSetRow["whatever"]</pre>
 
Change that to: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>if(dataSetRow["whatever"]==null){
0;
}else{
dataSetRow["whatever"];
}</pre>

talva

That works perfectly, thank you Tubal!