Non Scaled SSRS Line Chart with mulitple series

Question

I am trying to present time series of multiple sensors on a single SSRS (v14) line chart I need to plot N series, with each independently plotting the series data in the space provided by the chart (independent vertical axis)

More about the data

There can be anywhere from ~1-10 series The challenge is that they are different orders of magnitude.

One might be degrees F (~0-212) One might be Carbon ppm (~1-16) One might be Ftlbs Thrust (~10k-100k)

the point is , they have no relation and can be very different

The exact value is not important. I can hide the vertical axis

More about what I am trying to do

The idea is to show the multiple time series, plotted together against time for the 4 hours before and after 'an event'. Its not the necessarily the exact value that is important. the subject matter expert would be looking for something odd (temperature falls, thrust spikes, etc).

Things I have tried

If there were just 2 series, i could easily use the 2nd axis available in the SSRS chart. Thats exactly the idea I am chasing. But in this case, I want N series to plot using its own axis.

I have tried stacking N transparent graphs on top of each other. This would be a really ugly solution, but SSRS even wont let you do it. It unstacks them for you.

I have experimented with the Allow Scale Breaks property on the Vert Axis. This would solve the problem but we don't like the 'double jagged line'

Turning on Logarithmic scale is a possibility. It does do a better job of displaying all the data. but its not really what we want. Its going to change the shape of data that ranges over a couple orders of magnitude.

I tried the sparkline component and am having the same problem.

Alan Schofield Alan Schofield · Accepted Answer · 2020-01-10T11:33:05

This approach is essentially the same a Greg's answer above. I've had to do this same process in the past comparing trends of data even though the units were dissimilar.

I took a very simple approach of adding an additional column to the query that showed each value as a percentage of the maximum value in each series.

As an example (just 2 series here for clarity) I started with data like this in myTable

Series  Month  myValue
A       Jan    4
A       Feb    8
A       Mar    16
B       Jan    200
B       Feb    300
B       Mar    400

My Dataset query would be something like.

SELECT *, myValue / MAX(myValue) OVER(PARTITION BY Series) as myPlotValue FROM myTable

This gives us a final dataset which looks liek this.

Series  Month  myValue myPlotValue
A       Jan    4       0.25
A       Feb    8       0.5
A       Mar    16      1
B       Jan    200     0.5
B       Feb    300     0.75
B       Mar    400     1

As you can see all plot values are now between 0 and 1.

I created that charts using the myPlotValue field and had the option of using the original values from the myValue field as datapoint labels.

Non Scaled SSRS Line Chart with mulitple series

2 Answers