reporting-serviceschartsstacked-chartstackedbarseries

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


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?


Solution

  • 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.

    enter image description here

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

    enter image description here

    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.. enter image description here

    Hopefully that will give you enough to go on.