Showing Two Different Scales on Y Axis SQL Server Reporting Services (SSRS)

Long time, no post…

I was asked to create a graph showing throughput of bags on a conveyor grouped into 5 minute intervals.  One of the requirements was to show two scales on the Y axis, bags / 5 minute interval, and the equivalent value in bags / hour.  I.e. bags / 5 minutes * 12.  I thought that I would be able to use a formula to specify the axis values, however, this isn’t possible in SSRS.

What I ended up with is this:

img 1

And if you’re interested, this is how I did it:

Step 1

You need to set the start and interval for the Y axis at a value that will be suitable for your data:

Right-click on the axis, select “Vertical Axis Properties”

img 1b

I set the minimum at zero and the interval at fifty

img 1c

Step 2

Calculate your corresponding Y axis values to match the interval chosen above

As mine is Value * 12 I ended up with a list like below

50 = 600, 100 = 1200, 150 = 1800, etc.

Ensure that your list goes higher than the maximum possible value buy at least one value.

Step 3

StripLines.  If you don’t know what striplines are, they are basically horizontal lines drawn across your chart at a fixed Y value (I use these for showing targets, etc).  They have a number of properties such as width, colour, title, etc.  We are going to cheat and use these to add our additional values.

To access the striplines you need to click on the Y axis and then use the Properties window, note that striplines are not accessible via Right-clicking on the axis and selecting “Vertical Axis Properties”.

If you can’t see StripLines in the properties ensure you’re in the Chart Axis properties and not the Chart (or other section).

img 2

Click on the … next to the StripLines to open the ChartStripLine Collection Editor

The properties we are interested in are:

IntervalOffset, Title and TitleVerticalAlign

We need to add one StripLine for each value in our list we made earlier

  • IntervalOffset is the value on the Y axis where you want the StripLine to appear*.
  • Title is the equivalent value.
  • And TitleVerticalAlign just stops the Title going out of line with the value

*One thing you’ll notice is that I’ve subtracted one from the IntervalOffset value, if you don’t do this the top StripLine will not be shown, so in my chart at the top the 2400 would be missing.

img 3

As we haven’t set the Colour and thickness values for the StripLine it is invisible which is why you only see the values.

Could be a little long-winded if you had a lot of values, but worked for me!

Advertisements
This entry was posted in Programming and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s