• 1 reply
  • Latest Post - ‏2013-07-30T22:52:00Z by OpherB
92 Posts

Pinned topic Reporting issue handling null values

‏2013-07-30T03:15:34Z |

Hello Everybody,

Thank you for your time!

I have 4 cross tabs with a metric on each row with month Names on all the four columns. Only The column name for the first crosstab is visible where as other three column tittle name are hidden(box type = none ). So these four cross tab is made to look like single cross tab with four different metrics on each row and months on thecolumns. I have attached a snapshot for example.

My issue is, when I filter a report by year. few of the metrics have data for all the months but few other just have data for only few months.

for example. if there are  meterics A,B,C,D  and filter the report for 2012. A,B,C Metrics have data for all the 12 months and D has data for only jan, march, august, and december.


Now all those four months appear  next to each other like jan, feb , march and april but actually they are Jan, march, august and december. since its column name is hidden by box type = none.


My question is how can I have a 0 or null character for the months which doesnt have any values.

The query for the cross tabs is formed after a outer join with a time data item which has all the 12 months which I thought would help to get all the 12 months irrespective for the months for which the data is available.

What I tried.

- Edited the data format to have a null character like 0 or - . It doesnt work.

- Created a data item like if( [count_a] is null ) then(0) else([count_a]). This works when I View as a tabulated data but when I run as HTML , I don't see the  Null character handling and the measures are just seen for the months available (i.e Jan March august and december).


How Can I always have all the 12 months ,irrespective of data availablity.

Any help wold be greatly appreciated .

 Version : 10.1.1

Database : DB2


Updated on 2013-07-30T03:28:10Z at 2013-07-30T03:28:10Z by venkat_Jey
  • OpherB
    684 Posts

    Re: Reporting issue handling null values


    I would not approach this report using crosstabs, specifically because of the difficulty in handling sparse data.

    I suggest you switch to a "pivot list" and a UNION of the four source queries.

    Create 12 calculated data items, named January - December. In each expression, verify the month of the transaction and then calculate the amount, something like ceiling(ifnull([data item],0),0) should work.

    Good luck,