null dates in crosstabs

Having a heck of a time figuring out how to handle this...hope someone can shine some light on it. Given an example dataset that only contains a date-type column and only two rows - one row that has a valid date value and the other that is null. If I group by the date column in my crosstab and output the count by that column, I'd expect to see two output rows in the crosstab; one with an empty or null date and count of 1 and the other with the specified date and count of 1. Instead, the null date is treated as a "0" date and displayed as "01/01/0001" and the count is 0. The other row correctly has the date and count of 1.

How do I tell it a) treat null as a valid value for the purpose of a count aggregation and b) display a null date as ""

Find more posts tagged with

Comments

rextency

Maybe it would be easier to do column mapping as a String.

Do a binding map to display Null as "", and in your dynamic text expression do whatever you need to with the date.

For aggregate doing a count should still work...

Though I couldn't get mapping to work yesterday when I tried

jsherriff

I don't think those types of controls work in a crosstab in general. I did find that problem "a" (not-counting-of-null-values-in-aggregates) isn't particular to dates but is the same for all types. I'd like to find a way to control this - and, of course, a way to display a null date in a better way.

mwilliams

Hi jsherriff,<br />
<br />
You should be able to use mapping in the crosstab to get the null date dimension headers to display as an empty string. For the count, I'm not sure of a way around it besides creating a computed column that keeps a count of each date for you using PGV's to keep track of individual dates. Then, using a "LAST" aggregation on that column in your crosstab as the measure rather than letting the crosstab count them.<br />
<br />
You may log this as a bug at <a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/reportabug.php'>BIRT: Reporting Bugs and Requesting Enhancements</a>. You would think it would count the null values just the same as it counts non-null values.

jsherriff

Michael,
Thanks, I'll do that soon as I finish up. For now, I bit the bullet and wrapped all the possible nulls in my query in nvl()s. And I did get the map to work as you suggested. Thanks.

mwilliams

Great! Let me know if you have any other questions.