Steve Simon

How to use Expressions within SQL Server Reporting Services to create efficient reports

December 9, 2016 by

Introduction

As many of you by now know, I am a fan of utilizing expressions within Reporting Services reports to add additional flexibility. Recently I received a client request to create a stacked bar report which in turn would provide access to the underlying data the made up the bars on the chart. My client sells two articles: “Whigs” and “Tories”. Many people like to “buy” them.The idea is to display the aggregated data as may be seen below (gray black and turquoise) and depending on which bar and color is selected( clicked upon), to drill down and to display the underlying detailed data (see below).

In today’s “fire side chat”, we are going discuss how the report may be constructed in an efficient and effective manner.

Let’s get started!

Getting started

We begin our journey by having a quick look at the raw data which has been loaded into our SQLShack database.

The contents of the Whig table may be seen above.

The contents of the Tories table may be seen above.

Now that we have had a quick look at the data behind our report, it is now time to begin constructing our client’s report.

Creating our client’s report

Opening Visual Studio 2015 or SQL Server Data Tools 2010 or greater, we create a new Reporting Services project.