Search code examples
reporting-servicesssrs-2008ssrs-2012

Have zero values appear on chart

I have a chart (below), which I would like to show all the months across the x axis even if the value is zero.

enter image description here

The values are created by a count

I have tried 'Empty Point Value = Zero' in Custom Attributes

I have tried changing the axis type to Scalar and interval to 1

I have also tried on the value field =iif(isnothing(Count(Fields!Incident.Value)),0,count(Fields!Incident.value))

Solution

  • Your dataset needs to be altered to include those dates where nothing occurred.

    Setting the interval to 1 only ensures that none of the available values for the x-axis are skipped, it will not insert any values that are not there. The chart has no idea that you want 2017-02 displayed, because that value does not exist in your dataset.

    Depending on how your dataset is structured, you could either add an outer join on some sort of date table such that all of the months you want are included but any months without actual data are filled in with NULLs. Or, you could add another dataset containing just the months, which will be the main dataset used for the chart, and then use a Lookup to get the values for those months from the first dataset.