Sometimes there are sizing issues with a SQL Server Reporting Services chart that can have varying amounts of data in a chart.  Robert Bruckner blogged about this some time ago in his “Charts with Dynamic Height or Width Based on Categories/Data” entry.  Using his idea as an inspiration, I approached it just a bit differently.

Sizing Issue

In working with a horizontal stacked bar chart, I found that I was pretty dissatisfied with the height if my # of items in the Category Group changed much.  In the following example, which has 2 data points in the Category Group (Math and Reading), this look good.  Without dynamic sizing, if I only had just one subject area its bar would be tall & ugly; similarly, if I had several more subject areas the bars would be all squished up.

The final output, with the vertical height I wanted:

    

The Design View in BIDS:

    

I handled the dynamic sizing in two steps:  a hidden textbox to count the # of items, in conjunction with the DynamicHeight property of the chart.

Hidden Textbox to Perform Count of Items

First, I created a textbox to count the # of subject areas, which is the field in my Category Group:

=CountDistinct(Fields!SubjectArea.Value, “pGetGroupPerformance”)

The purpose for this textbox is to perform the count of the items contained in my Category Group only once, so I can refer to it multiple times later.

The Name property for this textbox is very cleverly called “HiddenTextBox.”  The Hidden property for this textbox is set to True.  For my personal self-documenting purposes, its BackgroundColor is Yellow – that tells me at a glance it’s hidden to drive something else in the report.  Because the textbox isn’t associated to a specific data region (like a table or a chart), it’s required to specify the scope – my scope is the name of the dataset (pGetGroupPerformance, which corresponds to the underlying stored procedure name).

    

DynamicHeight Property

In the Chart Properties, I added the following to the “DynamicHeight” property:

=Switch(ReportItems("HiddenTextBox").Value<=1,"1.5 in",

ReportItems("HiddenTextBox").Value=2, "1.8 in",

ReportItems("HiddenTextBox").Value=3, "2.2 in",

ReportItems("HiddenTextBox").Value=4, "2.5 in",

ReportItems("HiddenTextBox").Value=5, "3 in",

ReportItems("HiddenTextBox").Value>5,(ReportItems("HiddenTextBox").Value -

                    (ReportItems("HiddenTextBox").Value/1.5)) &" in"

)

Notice that it references the value computed in the “HiddenTextBox” – which is why I wanted to do the count once within the hidden textbox instead of performing the count within this Switch statement.  I intentionally wanted to have a lot of control over the sizing of 1-5 items; after 5 the dynamic nature of the calculation can take over.  Compulsive?  Yup, I know, I know.

Using the ReportItems Collection

Just an few quick words about ReportItems:  this is a really useful way to refer to the value already computed elsewhere in the report.  I tend to use the ReportItems collection to simplify other related calculations.  ReportItems is one of the built-in collections in Reporting Services, as well as:  Fields, Globals, User, Parameters, DataSets, DataSources, and Variables.

Finding More Information

Robert Bruckner’s Blog Entry:  Charts with Dynamic Height or Width Based on Categories/Data

Teo Lachev’s Blog Entry:  DynamicHeight Bug

Microsoft Hot Fix:  DynamicHeight property and DynamicWidth property for a Chart Control

MSDN:  Using the ReportItems Collection References in Expressions