Creating reports using SQL Server 2016 Reporting Services

[box type="note" align="" class="" width=""]This article is an excerpt from a book authored by Dinesh Priyankara and Robert C. Cain, titled SQL Server 2016 Reporting Services Cookbook.This book will help you create cross-browser and cross-platform reports using SQL Server 2016 Reporting Services.[/box]

In today’s tutorial, we explore steps to create reports on multiple axis charts with SQL Server 2016.

Often you will want to have multiple items plotted on a chart. In this article, we will plot two values over time, in this case, the Total Sales Amount (Excluding Tax) and the Total Tax Amount. As you might expect though, the tax amounts are going to be a small percentage of the sales amounts. By default, this would create a chart with a huge gap in the middle and a Y Axis that is quite large and difficult to pinpoint values on.

To prevent this, Reporting Services allows us to place a second Y Axis on our charts. With this article, we'll explore both adding a second line to our chart as well as having it plotted on a second Y-Axis.

Getting ready

First, we'll create a new Reporting Services project to contain it. Name this new project Chapter03.

Within the new project, create a Shared Data Source that will connect to the WideWorldImportersDW
database. Name the new data source after the database, WideWorldImportersDW.

Next, we'll need data. Our data will come from the sales table, and we will want to sum our totals by year, so we can plot our years across the X-Axis. For the Y-Axis, we'll use the totals of two fields: TotalExcludingTax and
TaxAmount. Here is the query by which we will accomplish this:

SELECT YEAR([Invoice Date Key]) AS InvoiceYear

,SUM([Total Excluding Tax]) AS TotalExcludingTax

,SUM([Tax Amount]) AS TotalTaxAmount FROM [Fact].[Sale]

GROUP BY YEAR([Invoice Date Key])

How to do it…

1. Right-click on the Reports branch in the Solution Explorer.
2. Go to Add | New Item… from the pop-up menu.
3. On the Add New Item dialog, select Report from the choice of templates in the middle (do not select Report wizard).
4. At the bottom, name the report Report 03-01 Multi Axis Charts.rdl and click on Add.
5. Go to the Report Data tool pane. Right-click on Data Sources and then click Add Data Source… from the menu.
6. In the Name: area, enter WideWorldImportersDW.
7. Change the data source option to the Use shared dataset source reference.
8. In the dropdown, select WideWorldImportersDW.
9. Click on OK to close the DataSet Properties window.
10. Right-click on the Datasets branch and select Add Dataset….
11. Name the dataset SalesTotalsOverTime.
12. Select the Use a dataset embedded in my report option.
13. Select WideWorldImportersDW in the Data source dropdown.
14. Paste in the query from the Getting ready area of this article:

1. When your window resembles that of the preceding figure, click on OK.
2. Next, go to the Toolbox pane.
3. Drag and drop a Chart tool onto the report.
4. Select the leftmost Line chart from the Select Chart Type window, and click on OK.
5. Resize the chart to a larger size. (For this demo, the exact size is not important. For your production reports, you can resize as needed using the Properties window, as seen previously.)
6. Click inside the main chart area to make the Chart Data dialog appear to the right of the chart.
7. Click on the + (plus button) to the right of the Values.
8. Select TotalExcludingTax.
9. Click on the plus button again, and now pick TotalTaxAmount.
10. Click on the + (plus button) beside Category Groups, and pick InvoiceYear.
11. Click on Preview. You will note the large gap between the two graphed lines. In addition, the values for the Total Tax Amount are almost impossible to guess, as shown in the following figure:

1. Return to the designer, and again click in the chart area to make the Chart Data dialog appear.
2. In the Chart Data dialog, click on the dropdown beside TotalTaxAmount:

1. Select Series Properties….
2. Click on the Axes and Chart Area page, and for Vertical axis, select Secondary:
3. Click on OK to close the Series Properties window.
4. Right-click on the numbers now appearing on the right in the vertical axis area, and select Secondary Vertical Axis Properties in the menu.
5. In the Axis Options, uncheck Always include zero.
6. Click on the Number page.
7. Under Category, select Currency. Change the Decimal places to 0, and place a check in Use 1000 separator.
8. Click on OK to close this window.
9. Now move to the vertical axis on the left-hand side of the chart, right-click, and pick Vertical Axis Properties.
10. Uncheck Always include zero.
11. On the Number page, pick Currency, set Decimal places to 0, and check Use 1000 separator. Click on OK to close.
12. Click on the Preview tab to see the results:

You can now see a chart with a second axis. The monetary amounts are much easier to read. Further, the plotted lines have a similar rise and fall, indicating the taxes collected matched the sales totals in terms of trending.
SSRS is capable of plotting multiple lines on a chart. Here we've just placed two fields, but you can add as many as you need. But do realize that the more lines included, the harder the chart can become to read.

All that is needed is to put the additional fields into the Values area of the Chart Data window. When these values are of similar scale, for example, sales broken up by state, this works fine.

There are times though when the scale between plotted values is so great that it distorts the entire chart, leaving one value in a slender line at the top and another at the bottom, with a huge gap in the middle. To fix this, SSRS allows a second Y-Axis to be included. This will create a scale for the field (or fields) assigned to that axis in the Series Properties window.

To summarize, we learned how creating reports with multiple axis is much more simpler with SQL Server 2016 Reporting Services.

If you liked our post, check out the book SQL Server 2016 Reporting Services Cookbook to know more about different types of reportings and Power BI integrations.