Empty data value displayed as zero in Cross Table

I have a custom report generator application, that essentially uses the BIRT API to generate .rptdesign files. The .rptdesign files can then be used to generate a report using a currently attached data source (currently an SQLite database). 
I have a problem with empty/NULL data values in cross tables. If a data value is empty (or NULL) in the database, I want the corresponding cell to be empty in the cross table. Whatever I do, the value is always displayed as 0.00. This is misleading to the user, giving the impression that the stored value is zero rather than empty. 
Using standard tables (not cross tables), accessing the same data from the database gives me the desired result i.e. if the value in the database is empty then the cell in the table is also empty. The expression used for the structure in the bound data columns list for the table is....
<pre class="_prettyXprint">
if (dataSetRow["MyData_Empty1"] != null) dataSetRow["MyData_Empty1"]*1.0+0.0
</pre>
I have experimented with the measureExpression for the tabular-measure in the cube, and with the expression in the structure inside the boundDataColumns list property in the grid to no avail. The expression as used above which works fine for a table does not give the required result for a grid.
 
Can I achieve the desired result using some javascript in the <expression> XML field? Or am I approaching this wrong and there is a better way?
I'm hoping I am missing a setting somewhere and it is something really simple that I have overlooked. Does anyone have any ideas? 

Thanks.

Find more posts tagged with

Magellan BI & Reporting

Comments

mwilliams

Could you give an idea of what your data looks like (in your data set) so I can try to reproduce a simple version of your crosstab? Also, what's your BIRT version? Thanks.

pauldouglasromax

Thanks for the response Michael.
 
I'm using BIRT version 4.3.1.
 
The data in the dataset is very simple: the table in the database looks like this (in reality there are a lot more columns; I've reduced it to 3 here for illustration purposes)
<pre class="_prettyXprint _lang-nocode">
MyData
======
Column1 Empty1 Column3

3 3.9e-5
1 12070.45 0.0016
4 17720.6 3.0e-6

</pre>
So in this example, the "Empty1" data in the first row is empty, and this appears as empty in the standard table (correct) but as 0.00 in the cross table (incorrect). All other values are reported correctly.
 
The SQL query used is also very simple:
SELECT MyData.Column1, MyData.Empty1, MyData.Column3 FROM MyData
 
It's probably worth mentioning that the "dataType" property for the structure in the bound data columns is set to "float" for both the table for the standard table and the grid for the cross table.

mwilliams

The reason behind this is the SUM aggregation being used. If your values are already final in the data set and don't require the crosstab to sum them, you could use the FIRST aggregation and this summation wouldn't occur, changing the blank value to 0. I'm guessing you can't just hide the data element when the value is 0 since 0 is probably a valid value in some cases?

pauldouglasromax

Thanks so much Michael - that did the trick. Can't believe it was so simple - doh!