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.
Reblogged this on NEW GENERATION MEDIA TECHNOLOGY.
Thanks Mr. Francis; all the best to you and yours.