SSRS: Range Bar Chart not displaying repeated status based on Date Range

Question

I have a query that gives me the daily status of an item based on desired date range (parameters). This status will vary and can repeat. For example, it can chronologically be: Operational, Repair, Inspection, Operational, Inspection. As you can see Operational & Inspection are represented twice but at different times. While this is true, I cannot seem to get this represented graphically in a Range Bar Chart because it will only display one instance of each status (as shown in the picture). The picture indicates where the remaining status should be presented.

Range bar Chart Error

The data set I am working with is as follows:

Range bar Chart Error Data

As you can see, the chart should represent 4 status in the following order: Inspection, Operational, Repair, Operational but does not display the 2nd Operational status.

Can anyone assist me to overcome this particular hurdle or is this not possible?

Alan Schofield Alan Schofield · Accepted Answer · 2018-01-16T16:25:08

This should point you in the right direction... This is how I would probably approach it.

You need to add an additional column to your dataset. I replicated your dataset into a table variable and then used the following to get the additional column

-- this just replicates your data..    
DECLARE @t TABLE(StatusDate DATE, StatType varchar(20), statStart DateTime, statEnd DateTime, StatusDays int)

    INSERT INTO @t VALUES
    ('2017-02-16', 'Inspection', '2017-01-30 12:49:14', '2017-02-21 12:49:14', 22),
    ...
    ...
    ('2017-03-14', 'Operational', '2017-03-01 11:49:11', '2017-04-19 15:19:48', 49)

-- the important bit    
    SELECT 
        *
        , DENSE_RANK() OVER(ORDER BY statStart) as Sort
     FROM @t

This gives us the following output.

We can use this extra field in SSRS to group and sort the data correctly.

I then used the following expression for the chart series color property to make sure that the colours are consistent with the statType

=SWITCH(
    Fields!StatType.Value = "Inspection", "Tan",
    Fields!StatType.Value = "Operational", "Green",
    Fields!StatType.Value = "Repair", "Red",
    True, "Blue"
    )

(the Blue line is there in case we are missing a type, it'll show on the chart n blue.)

This gives us..

Hopefully that will give you enough to go on.

SSRS: Range Bar Chart not displaying repeated status based on Date Range

1 Answers