SSRS range bar chart, different color when no data

Question

I am looking to change the color in the "gaps" in the range chart that I have built.

Essentially I am trying to build a visualization for the staff to see the availability on bays. The red markings mean the bay is booked, I wanted green then to be in between the red to highlight free bays.

Occupied bays:

I suspect I need to go back to my dataset and add in some date tables to show "null data" to allow a "switch" to work, but I thought someone might know if I could change this without going this way.

An example of the dataset would be:

Following up

here is a screen shot of my output, i added data labels to see if i was going wrong...HOWEVER I also added shadows to my data and my data is actually still there, just hiding behing my secondary bars......

you are right about my data set, i had to add a series grouping to get my appointments all to appear other wise i was just getting the first appointments on the row only to appear....

Following up 2

A further follow up, I have followed @iamdave 's excellent RangeChart.RDL

I have applied my dataset to it but I am now getting the following output, I know I am probably missing something simple, my gut is telling me the cust_id are seperating all the appointments on to seperate lines yet I have both the primary and secondary axis set to "DrawSideBySide = False"

Here is my set up..

here is my latest output, if I could just group all the rows/bars/lines down to the 6 bays it would be great.

Answer 1

You can do this by adding in another data series based on your original dataset, that will require significantly less processing than filling in all your empty periods as suggested by WEI_DBA.

1. In your dataset, add a new column that returns dense_rank() over (order by BAY) as BayID (order by should be your desired axis label).
   1. This may be best done as a wrapping select statement to preserve the rest of the query logic as is.
2. In your Range Chart, add in another Values item, with the same Category Field as your current one.
3. Set the Top Value to 1 and your Bottom Value to 0.
   1. You could also set this to the min and max time of your dataset or the start and end period of your report to only cover the period the Bay was available.
4. Set it to display on the Secondary Axis for both Vertical and Horizontal axes.
5. Also select the Do not show this series in a legend option.
6. Set up the Secondary Vertical Axis in exactly the same way as your Primary Vertical axis.
7. Set the Secondary Horizontal Axis Minimum and Maximum to the same values as your Bottom and Top above.
8. Hide both the Secondary Axes.
9. Select the new Values item and in the Properties window, expand the CustomAttributes section and change DrawSideBySide to False.
10. If necessary, adjust the order of the Values items in your Chart Data window to make sure the new Range Bar displays underneath your actual data.
11. Select the original Values item and in the properties sidebar navigate to Data > DataPoint:
    1. Change AxisLabel to =Fields!BAY.Value (Your axis label).
    2. Change Values > X to =Fields!BayID.Value (Value from point 1.).

You should now have a range chart that that displays your data on top of a solid bar, that you can set to any colour you require: