System Availability Charts in SSRS

Introduction

We are currently working on some dashboard development to present system availability data to our clients. SQL Server Reporting Service(SSRS) is one of the dashboard engines we use to power our system. It is not very intuitive to use the traditional bar charts or line charts to depict the precise idea of system availability (up/down/degraded) in the last 24 hours. A more user-friendly approach will be a color coded view of system availability timeline. We can have two types of basic display models as below. The first one is a continuous timeline of last 24 hours. The second one is the timeline breakdown by hours. We use red to represent the system down time, yellow to represent system degraded, and green to represent system healthy. After some researching and tweaking, I have come up with a fairly easy solution to create this type of chart in SSRS. Here are the steps.

Using the Code

1. Look at your source data. Our system monitoring data looks like the following in the database. Status 1 represents healthy, 2 represents degraded, and 3 represents downtime. Period is an indicator of how often data is updated. 120 means 120 seconds, as you can see from the timestamp column.
   
2. Massage your data. Using the following simple query will help you to reformat your data to what we need in SSRS to build our dashboard chart. I will explain each column in the next step.
   
   SQL

    SELECT serviceame
    , time_stamp
    , DATENAME(HOUR, time_stamp) + ':' +  right('0' + DATENAME(mi, time_stamp), 2) as time
    , status
    , 1 as dummy
   FROM  monitoring_data
   

   

3. Build first SSRS availability chart. Just like all the SSRS reports, you have to create your data source first and then use the query above to create your data set. After creating your dataset, insert a column chart into the report.

   

   I normally delete the chart legend, title, x-axis data mark, and x-axis title just to make the timeline look cleaner. Drag “dummy” into values pane, and “time” into category group pane. You need to sort category group by time_stamp column, which is the actual datetime column in order to get the correct time sequence. As you can see, “Dummy” is a constant number, which is 1 in my report. This column is used to display the value so we can force all the columns in this chart to have the same height, and our timeline will look like a straight even stripe.

   

   Now if we run the results, we can actually see a very odd chart like what we have here. We are getting closer, now we just need to tweak some chart settings.

   

4. Tweak your chart. There are three places you need to change on your current chart in order to make it look like what I showed in the beginning of this article.

   The first thing is to remove all the white spaces and make it look like a continuous timeline. Click on the column series. Go to the “General” section of the properties. Expand “Custom Attributes”. Change “PointWidth” property to 1. SSRS considers the combination of one column and the white space on the right of the column as one total width unit. PointWidth property is to set the percentage of column width in terms of total width unit. If this value is set to 1, column will occupy all the space, so we can eliminate the white space.

   

   The second thing we need to change is the color of the columns. Click on the column series, and go to the “Appearance” property section. Change the color setting to use expressions. Type the following formula. Of course, this formula is based on our data; the essence of this part is to define the color of each individual column for a color-coded visualization.

   

   The last change I want to make is to make sure all the time marks are displayed in one-hour intervals. In my situation, we have one column every 2 minutes, which makes 30 columns per hour. Click on chart x-axis. Go to “Labels” property section. Change the “LabelInterval” attribute to 30.

   

   Now you can run the report. VOILÀ! Your system availability timeline will show up as follows. You can tailor this approach to fit your needs and your data structures.

   

Now let's take a step further. I had two availability visualizations in the beginning of this article. The first one is explained above. The second one is actually very close to the first one. I am not going to go through the step-by-step details. But here are some differences you need to adjust from the previous process.

1. Change your chart type from Column Chart to Stacked Column Chart.
2. Since this is broken down by hour, you need to have hours and minutes parsed out separately in the query. Modify your query to:
   
   SQL

   SELECT
       serviceame
     , time_stamp
     , DATENAME(HOUR, time_stamp) + ':' +  right('0' + DATENAME(mi, time_stamp), 2) as time
     , status
     , 1 as dummy 
            , DATENAME(HOUR, time_stamp) as hours
            , right('0' + DATENAME(mi, time_stamp), 2) as minutes
   
     FROM  monitoring_data 

3. Add "hours" to Category Groups pane and "minutes" to Series Groups pane.

   

   Now run your chart, you will see the following results. Every column is an individual hour and minutes ascend from the bottom.

Ultimately, you will be able to change the column chart in SSRS report and use it as a continuous timeline to visually represent system availability. Hopefully, this is helpful to provide some baseline to build your own availability charts.