4 February 2019

Dynamic control of number of charts in SSRS reports

1. Introduction

It is often required in Business Intelligence projects to report through the SQL Server Reporting Services (SSRS) tool. However, there is a set of rules and data visualization best practices that we must have in mind.

This article addresses one of the best practices of controlled presentation of information through charts, in other words, the dynamic presentation of charts according to business rules.

 

2. The conditions for the development of dynamic charts in SSRS

In order to have clean data visualization it is necessary to ensure the existence of the following items:

2.1. Dataset

This kind of representation makes sense when we want to present amounts (and / or other quantitative metrics) by qualitative categories. As such, it is necessary to ensure that the dataset (query / stored procedure / etc.) contains all the necessary information.

2.2. Charts

The chart should take into account what best represents the information as well the easiest to analyse by the common user. Again, it is important not to forget the best practices of information visualization.

2.3. Code

The secret to this functionality is in the code that needs to be incorporated into the report. When clicking on the options / properties of the report there is the option “Report Code”, where the following code should be inserted:

2.4. Tablix and Groups

In order to obtain N charts through a single one, that is, to obtain the dynamic representation of charts, it is necessary to create a tablix (table) and to insert the chart within one of its cells, as will be presented in the following images.

It is in the tablix that we will define the number of charts that we want to display per line as well as by which category (qualitative field) we intend to disaggregate the information. It will then be necessary to add to tablix:

  • Column Group: the tablix column will be grouped and sorted by the FinancialInstrumentType field.

Relatórios SSRS

  • Row Group: here we use the existing group “Details”, where we input an expression that allows us to determine the number of charts that we want to display per line. In this case, as it is possible to verify in the following images, we want 3 charts to be displayed:

Relatórios SSRS

Note: It is necessary to sort the group by the field we use in the expression option, as it was done in the Column Group.

Relatórios SSRS

 

3. Dynamic charts report

After completion of the previous steps, the final result is obtained as shown in the following image. Here it is possible to verify that, regardless of the size of the report and / or page settings, the number of charts appear according to the number determined in the Row Group “Details” per line, however, it is replicated according to the number of categories which the dataset contains according to the filters applied.

Relatórios SSRS

 

4. Conclusion

It is known that data visualization is highly sought after by companies in Business Intelligence projects. However, seeing that nowadays there are many competing SSRS tools that are essentially used to analyse data and generate reports, it makes sense to question whether this is a tool that is falling into disuse and if so, how to maintain active demand by applying new techniques and practices.

It is in this sense that this article arises since this technique, although simple and easy, is not recurrently used by BI consultants, resulting perhaps, in time wasted configuring and replicating reports, running the risk of not guaranteeing the correct visualization of the information.

 

  

Sofia Margalha
   Consultant
Blog