How to hide null values from the x-axis of a graph?

[nikhil_sabnis2]

If there are null values in the data and if this data is referred by a Chart in the report as X-axis scale then the null value appears in the chart. The below steps will explain how to suppress/hide the null values from the chart.

1. Suppose we have data as below:

2. Create 2 formulas as below in the report:

Dates:

stringVar DD;
stringVar MM;
stringVar YYYY;

DD:=Replace(Replace(Cstr(Day({Sheet6_.Date})),".00",""),",","");
MM:=Replace(Replace(MonthName(Month({Sheet6_.Date}),True),".00",""),",","");
YYYY:=Replace(Replace(Cstr(Year({Sheet6_.Date})),".00",""),",","");

DD &"-"& MM &"-"& YYYY;

Points:

{Sheet6_.Points}

3. When you Create a chart based off this data and using the formulas created in Step 2 then the chart will look like:

One can easily spot the null value in the Legend and in the chart. It is highlighted in RED in the chart.

Note: I am using "Do Not Summarize" optoin for the "Points" formula, in the Chart Expert.

4. We need to find the maximum date within all the dates in our sample data. This can be easily done in any relational database. Include the maximum date as an additional column and repeat this value for all the rows. Please refer the snap-shot below:

5. Update the dates formula created in Step 2, with the code below:

stringVar DD;
stringVar MM;
stringVar YYYY;
dateTimeVar Dates;

If IsNull({Sheet6_.Date}) Then
    Dates:={Sheet6_.MaxDate}
Else
    Dates:={Sheet6_.Date};

DD:=Replace(Replace(Cstr(Day(Dates)),".00",""),",","");
MM:=Replace(Replace(MonthName(Month(Dates),True),".00",""),",","");
YYYY:=Replace(Replace(Cstr(Year(Dates)),".00",""),",","");

DD &"-"& MM &"-"& YYYY;

6. If you refresh the report now, then NULL value appearing in the chart will disappear. Refer the snap-shot below: