How to create horizontal bar chart with x-axis as time similar to a Gantt style chart ...

Abrams, Dan 86 Reputation points
2022-10-26T20:10:49.057+00:00

Hi All,

I am trying to create a chart that looks like this:

254397-image.png

Each bar represents an object over time with each color representing a state change. For example , when the segment is green = good, yellow = warning, red = bad, etc. The segment color is sized based on the amount of time along the x-axis when the object was in that state.

Is it possible to create a chart of this style in SSRS? How?

Thanks for your help.

--Dan

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,849 questions
0 comments No comments
{count} votes

Accepted answer
  1. Joyzhao-MSFT 15,571 Reputation points
    2022-10-27T03:16:57.903+00:00

    Hi @Abrams, Dan ,
    The object that is best for this task is Range Bar Chart. First you needed to precalculate "segment", "from time" and "to time". Then put time on X axis and "segment" as series, where range top and bottom value were from and to times. It made kind of gant chart that rearranged to what you needed by setting DrawSideBySide option. Colors were made by setting expression for series filling, like:

    =SWITCH(Fields!Segment.Value="Good","Green",Fields!Segment.Value="Warning","Yellow",Fields!Segment.Value="Bad","Red")  
    

    Best Regards,
    Joy


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Abrams, Dan 86 Reputation points
    2022-10-28T14:09:37.11+00:00

    Hi Joy,

    Success!

    I have figured out how to get this to work.

    It has to do with the "Series Groups" value. This value must be set to the "Start" value, i.e., the Start Date of the range.

    Originally, I had it set to the Status value, because I wanted the Status to show up in the Legend. I have since disabled the legend, and created a separate table with a "Legend".

    So, using my sample data from above, the steps are:

    1. Select Range-Bar chart
    2. Select Chart Data
    3. Set Values to Top Value = Start
    4. Set Value to Bottom Value = End
    5. Set Category Groups to Tag
    6. Set Series Group to Start
    7. Set Series Group Sorting to Start
    8. In Fill Properties set appropriate Color for each Tag
    9. In Series Properties Custom Attributes set DrawSideBySide = False
    10. Set X-Axis interval to Hours, Days, etc. as formatting requires
    11. Done!

    --Dan

    1 person found this answer helpful.
    0 comments No comments

  2. Abrams, Dan 86 Reputation points
    2022-10-27T13:58:12.993+00:00

    Hi Joy,

    Thanks for getting back to me.

    I tried this, but it doesn't work. I set DrawSideBySide=False and used "Start" and "End" as my "Top" and "Bottom" values. "Start" and "End" are SQL Server datetime datatypes.

    I am finding that the segment does not repeat itself once it has been drawn.

    The example below shows my "Desired" outcome vs. what the Range-Bar chart is producing ("Actual")

    254788-image.png

    As you can see, The "Actual" segment is not repeating the "Good" or "Warning" segments after the first time they are painted. It is properly positioning the "Bad" segment in the correct starting location, and the length of the segment is correct.

    The problem is that segments don't repeat after the first time they are encountered.

    Any help you can provide would be most appreciated.

    --Dan

    0 comments No comments