Background

As we delved a bit more into Charting in Reporting Services, I thought it best to allow the user to choose which specific column the Y-Axis should be charted on.

Guide

Code

SQL Server Stored Procedure

In Transact SQL code, we pass in a variable.

In our case, we pass in @columnValue and return @attributeValue based on the contents of @columnValue.

    select
               itvfB.*

            , [attributeValue]
                = case (@columnValue)

                    when 'NumberofItems' then itvfB.[TotalBookings]

                    when 'ScheduledHours' then itvfB.[ScheduledHours]
                    when 'ScheduledPercentile' then itvfB.[ScheduledPercentile]

                    when 'UsedHours' then itvfB.[UsedHours]
                    when 'UsedPercentile' then itvfb.UsedPercentile

                    else itvfb.UsedPercentile

                end 

    from   [report].[itvf_Trend]
                (
                     @dateStart
                   , @dateEnd
                   , @cityID
                   , @tvpBuilding
                   , @tvpDate
                   , @interval
                ) itvfB

Chart

Chart Data

In the Chart Data Property Sheet, we chose the “attributeValue” as the column to use for Values.

Vertical Axis Properties

In the Vertical Axis Properties sheet, we set the “Maximum” attribute based on whether we are charting based on Percent or not.

If we are charting on Percent, we want it top out at 100%.

On the other hand, if we are not charting on Percent, we will set at Nothing, and let the system adjust accordingly.

Expression:

=iif(instr(Parameters!reportTrendColumnValue.Value,"Percent")> 0,100, Nothing)

Image:

Output

Output – Chart on Hours

Here we are charting on Hours, and thus allow the system to choose the maximum Y axis.

Output – Chart on Hours

When we chart on percent, the system sets 100%.

Summary

I am really impressed that SSRS gracefully handles setting Y Axis via such a simple expression.