The Practical Guide to SQL Server Reporting Services (SSRS)

In this lesson, we will install the software required and set up our environment.

• Install SQL Server 2019 Developer Edition

• Install SQL Server Reporting Services

  • Configure the Report Server

• Install SQL Server Management Tools

  • SQL Server Management Studio (SSMS)

• Install SQL Server Data Tools

  • Visual Studio 2019 Community Edition

• Install Microsoft Reporting Services Project Extension in Visual Studio

• Restore the AdventureWorks Database

  • Add new login: ReportingUser

• Install 7-Zip

Here we look at

• The database schema

• Table relations

• Primary keys

• Foreign keys

In my opinion, the most important part of creating reports is having a thorough understanding of the data that you will be using. You will need to know which tables contain the data that you require, how the tables relate to each other and how to use SQL to retrieve the correct data for your reports.

The AdventureWorks database is a sample database from Microsoft for a fictitious bicycle manufacturer, Adventure Works Cycles. You can find a data dictionary at the link below, which has information describing the contents, format and structure of the database and the relationship among the elements.

In this lesson we will,

• Create the report

• Introduce Visual Studio

  • Solutions Explorer

  • Design Window

  • Report Data Pane

  • Toolbox

  • Properties Pane

  • Groups Pane

  • Rearrange Panes

• Create an embedded data source

• Create an embedded dataset using the built-in query designer

• Add a table

• Format the report

  • Resize textbox

  • Vertical align

  • Format numbers

• Add a total row

• Set background color

• Set alternating background colors

• Look at expressions

• Look at the IIf() and RowNumber() functions

• Add a page header and footer

• Add built-in fields to the header and footer

In this lesson we will,

• Reopen our project

• Extract the project from the previous lesson's zip file

• Create a shared data source

• Create a shared dataset

• Use SSMS to build our query

  • Join tables - left outer join, inner join

• Add column aliases

• Reformat our query

• Look at the IsNull() function

• Add a calculated field

• Copy textbox format

• Row group: Add parent groups - header and footer

• Merge cells

• Add subtotals

• Copy format from another report

• Add textbox expression

We will be creating a report with multiple groups and drill-down. The drill-down allows a user to click on a plus or minus sign to expand or collapse a report section to show or hide further levels of detail.

We will also look at repeating column headings and rows on every page.

Topics introduced

• Linking tables in query designer

• SQL Year() function

• SQL CASE statement

• Formatting dates

• Sorting row groups

• Changing text color

• Alternating background colors on grouping rows

• The RunningValue() function

• The scope of a function

• Handling errors

• Repeating column headings on every page

• Keeping column headings visible while scrolling

• Displaying the total number of pages in the page navigation bar

A matrix is similar to a table, except that it shows aggregated data grouped by both columns and rows.

Topics introduced

• Column groups total

• Changing border width

• Alternating background colors for a matrix

• Renaming a textbox

• Checking the value of a textbox

• Copying a textbox from another report

View: A virtual table that is the result set of a stored query. It produces a set of rows, just like a table, but it doesn’t store any data. It simplifies queries by hiding the complexity of multiple table-joins and presents them into a single virtual table.

Topics introduced

• Joining a table/view multiple times

• Difference between an INNER JOIN and a LEFT OUTER JOIN

• Modifying a view

• Using a user-defined function

In the previous lesson, we omitted the Postal Code from the Customer View and the Sales Order View. We will add that field to those views here.

Add missing fields from the SalesOrderHeader table

• SubTotal

• TaxAmt

• Freight

• TotalDue

  
  

So far, the reports we have created were producing the same information each time they were run. Typically, though, a lot of reports will require user input, determining the information that is produced, and this is done by using parameters.

Topics introduced

• Refreshing a dataset

• Hard coding parameter values

• SQL SELECT DISTINCT

• SQL ORDER BY

• Getting available values from a query

• Setting a default value - hard coding

• Sorting a row group by multiple values

• Allowing the selection of multiple values

• Setting default values from a query

A cascading parameter is one whose values depend on the value of another. For example, we can display a parameter with a list of States or Provinces based on the selection of a country in another parameter.

Topics introduced

• Copying a dataset

• Copying a report

A drill though report allows a user to click on a content area of one report to display another report showing more details of the item that was clicked.

Topic introduced

• Creating a tooltip

Stored Procedure: A group of one or more SQL statements that is stored as a named object in the database.

Topics introduced

• Using variables

• Executing a stored procedure

In this example, we will be looking at having one header record, for example, a sales order, and multiple details records, the line items on that order.

Topics introduced

• Creating shared datasets using stored procedures

• Naming convention for stored procedures and datasets

• Using padding

• Using a list - adding items in a free format manner

• Adding an embedded image (importing)

• Formatting placeholders

When passing a multi-value parameter to a stored procedure, additional logic is required to handle this situation. We will look at how this is done.

• The Join() function

A subreport is a report that is displayed inside the body of a main report. To demonstrate this, we will be creating two reports.

Our first report, the subreport, will display products sold for a date range, grouped by subcategories. The main report will show the totals by category.

Topics introduced

• SQL Union

• Common Table Expression (CTE)

• SQL Convert() function

• Rearranging parameters

• Conditional font weight

We will create a report with two tables, one grouped by category and the other grouped by subcategory. We will then add a parameter show one table and hide the other.

We will achieve the same effect as the report from the previous lesson, displaying grouping by category or subcategory, but this time using only one table. We shall dynamically change the grouping based on the parameter.

We will create a matrix report that implements drilling down on both the row and column groups.

A document map provides a set of navigational links to report items in a rendered report. When you view a report that includes a document map, a separate side pane appears next to the report. A user can click links in the document map to jump to the report page that displays that item.

Document maps create a separate windows pane with a set of hyperlinks which can be used to navigate around a report, a virtual table of contents.

We will create a product catalog to demonstrate this.

Topics introduced

• Inserting row inside/outside group above/below

• Adding an image from a database

• Keeping a row group together across pages

Charts and graphs are visual representation of data which can highlight categories and trends in a more meaningful and concise manner.

Topics introduced

• Adding a column chart

• Showing axis labels

• Modifying a chart title

• Showing series data labels

• Using custom number format

• Adding a line chart

In a Lesson 8, we created a report that drilled through to another by way of a textbox, now we will demonstrate drilling through using charts.

We will create a Monthly Sales Report that we will navigate to from the Annual Sales Report.

Data bars are small, simple charts that convey a lot of information in a little space. They are often used in table and matrices.

Topics introduced

• Showing data labels

• Fill style - gradient

• The Switch() function

  • Setting a default value (else)

• Sorting in descending order

Indicators are small gauges that convey the state of a single data value at a glance and are most commonly used in tables or matrices to visualize data in rows or columns.

There are times when users would like to print or save a copy of a report that is displayed. We can achieve this by exporting to PDF.

Topics introduced

• Downloading and installing Adobe Acrobat Reader

• Changing report properties

  • Orientation

  • Margins

• Keeping row group together

• Hiding a column on a report

• Page breaks

• Reports with drill down

• Reports will document map

Naming sheets, page breaks

Report with a document map

Report with drill down

Exporting to CSV

Topics

• Matching exported data with the original report

• How drill down items are represented in CSV

• Removing columns from export

• Renaming column headings on export

Exporting to XML

Topics

• Matching exported data with the original report

• Data elements and attributes

• Exporting a matrix

• Removing chart data from export

We will look at deploying reports, managing them on the server and giving users access to them.

Topics

• Creating a user on Windows 10, local account

• Switching between users

• Creating an output folder on the reports server for the application reports

• Setting the target server URL

• Deploying a report with an embedded dataset and data source

• Deploying a report with shared datasets

• Deploying a shared dataset

• Deploying a shared data source

• Deploying all data sources, datasets and reports together

• The behavior of parameters on target drill through reports

• Hiding folders on the reports server

• Viewing hidden items

• Setting user permissions

• Giving users access to the reports

• Copying reports shortcut to a user's desktop

• Examining why users get an error when trying to run reports they have permissions to

• Hiding sub reports that users will not interact with directly

• Removing the default value for a parameter after deployment

A Reporting Services subscription is a configuration that allows us to set up a schedule for a report to be run. The report can be delivered either by email or to a Windows file share.

There are two types of subscriptions, standard and data-driven. The standard subscription sets up a schedule on an individual report, while the data-driven subscription retrieves the configuration for one or more reports by querying a table.

We will look at the standard subscription.

Topics

• Starting the SQL Server Agent service

  • Setting the service to run automatically

• Configuring the reports server to send emails

• Setting a password for a Windows user account

• Restarting the reporting service

• Setting up an email subscription

• Managing subscriptions

• Setting up a Windows file share subscription

• Setting a Uniform Naming Convention (UNC) path

• Using the Windows admin share

The data-driven subscription retrieves the configuration for one or more reports by querying a table.

When passing a multi-value parameter to a data-driven subscription, an error occurs and the report is not generated.

The log files report this error, Default value or value provided for the report parameter 'Salesperson' is not a valid value. This is a known issue applying multi-valued parameters to data-driven subscriptions.

This lesson demonstrates how to work around this problem.

We are going to create a report with a summary and a detail table and have the summary table showing on every page.

We may have a situation where we are creating reports for multiple business units for the same database. Each set of reports would be in their own project, and it would be useful to be able to share the data sources and datasets.

To demonstrate this, we will create another project that uses the data source and a dataset from the one we have been working with.

We will modify a copy of an existing report to implement tabbed controls.

Sometimes when we have a report with one parameter with few values, we can create a tab for each value and use those instead of the parameter. We will do so using a matrix.

This is useful for PDF exported reports to be able to print drill down items expanded or collapsed.

We will implement how to change the color scheme for reports.

• How to filter a parameter with a very long list of values.

• Addressing a potential problem with passing a multi-value parameter to a stored procedure.

From time to time, we create reports with start and end date parameters. We will look at some commonly used functions and expressions to set the default values for such dates.

We will calculate

• Current date

• Tomorrow and yesterday's dates

• The start and end of a week

• Any day of the week

• Start and end of the current, previous and next months

• Start and end of the year

• Start and end of the current, previous and next quarters

Additionally

• How to display values other than true/false for a boolean parameter

Where do we go from here?

• Recommendations

• Tips

• Best practices