SSRS - show missing axis on column chart where there are no values?

0
votes

I have a column chart in SSRS that looks like the attached. This shows the percentage of calls across the day. I have been asked to also show the hours on the chart where there were zero calls.

I have tried adding this into my dataset in SQL however this turned almost 200 rows into 20000 rows (due to the period of time looked at which can sometimes be years)

I have tried using the lookup function to add a time dimension into my report, and then join the dataset onto this time dimension - I then found out that I can't use SUM in a lookup function so I am a bit stuck. Does anyone have any workarounds for this? Value expression is "=Sum(Fields!Calls.Value) / Sum(Fields!Calls.Value, "UsageSummary")"

What chart looks like now (wrong)

enter image description here

What chart should look like (with all hours included)

enter image description here

1
sql-serverreporting-servicesdax
I would still do this in your dataset query. Even if the raw data is 20,000 rows, you can aggregate it in the dataset query and then only pass those results to the report. If you need more help, edit your question to show some sample raw data and also what the data currently looks like when it gets passed to your report (i.e. your dataset output) – Alan Schofield

1 Answers

0
votes

Have you tried clicking on the horizontal axis properties

On the Axis Options update the Interval from Auto to 1