You may have been in this situation before – you designed a chart based on a particular dataset and everything looks great. However, later the data volume increases, or new categories dynamically show up in your data source, and there is not enough horizontal/vertical space in the chart to show all the data or categories; unless, of course, you applied an approach to dynamically increase the height/width of the chart.
In this recipe, you learn how to create the chart shown in Figure 14-1, which grows and shrinks dynamically in size based on the number of data points in the report. This technique can help accommodating varying volumes of data to visualize, especially in bar or column charts keeping individual datapoints more easily readable.
Product Versions (one of the following):
- SQL Server Reporting Services 2016 or newer
- Report Builder 2016 or newer
What You’ll Need
- Northwind sample database
Difficulty Level: 300
Contributed by Robert Bruckner
The report design uses Report Builder, with a report parameter that determines how much data (chart categories) will be shown, and dynamically adjusts the width of the chart to accommodate more data points.
Figure 14-1. Dynamic sizing of chart depending on number of categories
Designing the query
This example uses the Northwind for SQL Server sample database, which you can download from here: https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs
To design the dataset, use the following steps:
Launch Report Builder and create a new data source for the Northwind sample databases.
Create a simple dataset query to retrieve the customer list, as shown below:
SELECT * from Customers
Designing the report
There are two chart RDL properties to simplify the task of dynamically increasing chart height and width in Reporting Services. The DynamicHeight and DynamicWidth properties allow you to set the height and width of your chart dynamically at runtime.
Both properties can be set to any RDL expression that evaluates to a size string at runtime, for example, =“3 in”. Note the space in the size string between the numeric part and the size unit. If the properties are not specified, then the design time sizes will be applied.
The chart in this recipe dynamically increases its width based on the number of countries shown in the chart (determined by a report parameter value). To accomplish this, the DynamicWidth property of the chart is set to the following expression:
=(1 + Parameters!TopNCountries.Value / 2) & “ in”
In this example, if you choose to show only the top 5 countries, the chart will have a width of 1+5/2 = 3.5 inches. If you select the top 10 countries, the chart has a width of 1+10/2 = 6 inches and therefore more horizontal room to draw additional categories.
The following steps walk you through implementing this recipe step-by-step:
Add a new chart report item, set chart title & font, and delete the chart legend to create more room.
From the Datasets field list, drag the CustomerID field onto a chart data point. Then right-click on the chart data point to edit the chart series settings and change the value field aggregation to Count (instead of Sum).
From the Datasets field list, drag the Country field to the chart’s Category Group drop zone (or onto the category axis).
Select the overall chart, and in the chart’s properties list, set the DynamicWidth property to:
=(1 + Parameters!TopNCountries.Value / 2) & " in"
…as shown in Figure 14-2.
Figure 14-2. Initial chart configuration with DynamicWidth property setting
Click on the chart’s category axis, and then right-click on the Country Category Group created in the previous step. Open the Category Group properties. Set a group TopN filter expression using group count =Count(Fields!CustomerID.Value) and the report parameter runtime value =Parameters!TopNCountries.Value, as shown in Figure 14-3.
Figure 14-3. Displaying only as many chart groups (countries) as specified with the report parameter at runtime.
[Dynamic Sizing – Group Filter.png]
On the same category group properties, set a group descending sort expression based on =Count(Fields!CustomerID.Value), as shown in Figure 14-4.
Figure 14-4. Sorting the chart groups (countries) by descending customer count.
Run the report. Initially it will run with the report parameter default value (5) and display the top 5 countries by customer count. As you change the parameter value, the chart dynamically adjusts its width to accommodate additional data points, as selected by the report parameter. Figure 14-5 illustrates the result.
Figure 14-5. Dynamic sizing of chart depending on number of categories
In this recipe, you learned how to dynamically control the width of a column chart. You can similarly dynamically control the height of a bar chart, or dynamically resize a scatter chart based on data volumes.