Reporting Services – Chart – Parameter Driving Vertical Axis – Maximum Value

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.

Values

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.

ChartDataValues

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:

VerticalAxisMaxium

Output

Output – Chart on Hours

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

NumberofItems

Output – Chart on Hours

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

percentile

Summary

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

2 thoughts on “Reporting Services – Chart – Parameter Driving Vertical Axis – Maximum Value

Leave a Reply

Please log in using one of these methods to post your comment:

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