SQL Server Reporting Services: Creating Reports

SQL Server Reporting Services gives you two tools for creating reports:

• SQL Server Data Tools (SSDT) A development tool that you use during the development phase. It is tightly integrated with Visual Studio and allows you to develop and test reports before their deployment.

• Report Builder A stand-alone tool that enables you to do ad hoc reporting without knowing anything about the structure of a particular database or how to create queries using SQL.

NOTE This book discusses creating reports using SQL Server Data Tools only. The reason is that both tools deliver almost the same functionality, and therefore the description of one tool suffices to understand the basics of reporting with SSRS. For the description of Report Builder, see Microsoft Docs.

Before I show how you can create a report, I will describe the installation process of SSDT.

1. Installation of SQL Server Data Tools

As mentioned, SQL Server Data Tools is highly integrated with Visual Studio. In this book we use Visual Studio 2019 and SQL Server Data Tools 2017.

NOTE The user interface of Visual Studio 2019 is significantly different in many ways from the user interface of Visual Studio 2017. (The detailed description of the installation for Visual Studio 2019 is given in the section “Creating a New Project Using Visual Studio 2019” in Chapter 23.)

After installation of Visual Studio 2019, you need to install SSDT. To install SSDT, click Extensions in the main menu of Visual Studio 2019, click Manage Extensions, click Install, and select Online | Visual Studio Marketplace in the left navigation pane, as shown in Figure 25-5. The list of the components that you can install is shown in the middle of Figure 25-5.

Choose Microsoft Reporting Services Projects and click Install. The installation process starts when you close all active windows in Visual Studio.

2. Creating Your First Report

To create a report, start Visual Studio and click Create a New Project from the list of tasks. On the Create a New Project page (see Figure 25-6), you will see two project templates related to reports: Report Server Project Wizard and Report Server Project. The Report Server Project Wizard guides you during the creation phase of a new report. The Report Server Project creates an empty report and leaves you alone to do the rest of the work. This section shows you how to use the wizard to create reports, while the subsequent section shows you how to start with an empty report using the Report Server Project template.

NOTE Generally, SSRS reports are built using Report Designer, a collection of graphical query and design tools. It provides the Report Data pane, which you can use to organize data used in your report, and tabbed views for Design and Preview so that you can design a report interactively. Report Designer also includes Query Designer, to help you specify data for retrieval, and the Expression dialog box, to specify report data to use in the report layout. (All graphical and design tools will be explained later in this chapter.)

To create a new report with the wizard’s guidance, select Report Server Project Wizard. Type the name for your project (Project1, in my case). Use the default filename in the Project Location dialog box. Click Create. This leads you to the welcome page of the wizard.

The Report Server Project Wizard welcome page introduces the major steps that it takes you through to create a report:

1. Select a data source from which to retrieve data.
2. Design a query to execute against the data source.
3. Choose the type of report you want to create.
4. Design the data in the table.
5. Choose the table layout.

These steps are described in the upcoming subsections, followed by a quick summary of how to preview the result set. First, though, you will learn how to plan your data sources and datasets.

2.1. Planning Data Sources and Datasets

Before you create a report, you should prepare your data sources for use. You use these sources to create the corresponding tables and/or views, which will be used to retrieve the particular result set. For this reason, the environment in which you prepare data sources is SQL Server Management Studio, with its Transact-SQL capabilities, rather than SSRS.

During the planning phase, you have to work with both data sources and datasets. The differences between these two concepts are described next, followed by a description of how you can use both of them.

Data Sources vs. Datasets The most important difference between data sources and datasets is that data sources are not included in your report. A data source just delivers information for your report. This information can be stored in a database and/or a file. The task of SSRS is to generate datasets from the given data sources using the set of instructions. This set includes, among other things, the information concerning the type of the source, the name of the database (if the data source is stored in a database) or the file path, and optionally the connection information to the source.

During the execution of a report, SSRS uses this information to generate a new format, called a dataset. Therefore, a dataset is just an abstraction of underlying data sources and is used as a direct input for the corresponding report.

Using Data Sources To include data in a report, you must first create data connections (a synonym for data sources). A data connection includes the data source type, connection information, and the login for connecting. (Creation of data connections is described in the next section, “Selecting a Data Source”)

There are two types of data sources: embedded and shared. An embedded data source is defined in the report and used only by that report. In other words, when you want to modify an embedded data source, you have to change the properties of that data source. A shared data source is defined independently from a report and can be used by multiple reports. Shared data sources are useful when you have data sources that are often used.

Using Datasets As you already know, each dataset is an abstraction of corresponding data sources and therefore specifies the fields from the data sources that you plan to use in the report. All datasets that you create for a report definition appear in the Datasets window.

Similar to data sources, SSRS supports shared datasets, too. Simply put, a shared dataset is a dataset that allows several reports to share a query to provide a consistent set of data for multiple reports. Generally, you use shared data sources to generate shared datasets.

The query of a shared dataset can include parameters. You can configure a shared dataset to cache query results for specific parameter combinations on first use or by specifying a schedule.

Now we will begin to create a report with the wizard. The first step is selection of a data source, described next.

2.2. Selecting a Data Source

The data source contains information about the connection to the particular database. Click Next on the welcome page of the Report Server Project Wizard to select the data source. On the Select the Data Source page, type the name of the new data source in the Name field.

The Type drop-down list on the Select Data Source page allows you to choose one of the different data source types. SSRS can create reports from different relational databases (SQL Server, Teradata, and Oracle, among others) or multidimensional databases (SQL Server Analysis Services). OLE DB, ODBC, and Azure data sources can be used, too. After you choose a type (Microsoft SQL Server), click Edit. The Connection Properties dialog box appears (see Figure 25-7).

Type the name of your database server instance as the server name. Below that, choose Windows Authentication in the drop-down list. Click the Select or Enter a Database Name radio button and choose from the drop-down list one of the databases as the data source. (For the first report use the sample database.) Before you click OK, click the Test Connection button to test the connection to the database. Clicking OK takes you back to the Select the Data Source page. Click Next to start the next step of the wizard.

2.3. Designing a Query

The next step is to design a query to be executed against the selected data source. On the Design the Query page, you can either type (or paste) an existing query or use the Query Builder component to create a query from scratch.

NOTE Query Builder corresponds to the similar Access component that you can use to design queries even if you have no knowledge of the SQL language. This component is generally known as QBE (query by example).

For this first report, type the query given in Example 25.1 and click Next.

Example 25.1

SELECT dept_name, emp_lname, emp_fname, job, enter_date

FROM department d JOIN employee e ON d.dept_no = e.dept_no

   JOIN works_on w ON w.emp_no = e.emp_no

WHERE YEAR(enter_date) = 2017

ORDER BY dept_name;

The query in Example 25.1 is related to the sample database. It selects data for employees who entered their job in 2017. The result set of the query is then sorted by department names.

NOTE In the Designing a Query step, SSRS checks the names of the tables and columns listed in the query. If the system finds any syntax errors, it displays the corresponding message in the new window.

2.4. Choosing the Report Type

The next step in creating a report is to select the report type. You can choose between two report types:

• Tabular Creates a report in tabular form. Columns of the table correspond to the columns from the SELECT list, while the number of rows in the table depends on the result set of the query.
• Matrix Creates a report in matrix form, which is similar to table form but provides functionality of crosstabs. Unlike the tabular report type, which has a static set of columns, the matrix report type can be dynamic.

NOTE You should use the matrix report type whenever you want to create queries that contain aggregate functions, such as AVG or SUM.

The query in Example 25.1 does not contain any aggregate functions. Therefore, choose the tabular report type and click Next.

2.5. Designing the Data in the Table

The Design the Table page allows you to decide where selected columns will be placed in your report. The Design the Table page contains two groups of fields:

• Available fields
• Displayed fields

The page also has three views:

• Page
• Group
• Details

Available fields are the columns from the SELECT list of your query. Each column can be moved to one of the views. To move a field to the Page, Group, or Details view, select the field and then click the Page, Group, or Details button, respectively. A displayed field is an available field that is assigned to one of the existing views.

Page view lists all columns that appear at the page level, and Group view lists columns that are used to group the result set of the query. Details view is used to display columns that appear in the detail section of the table. In Example 25.1, the dept_name column will appear at the page level, while the job column will be used to group the selected rows. When you have chosen how to group the data in the table, click Next.

NOTE The order of the columns can be important, especially for the Group view. To change the order of the columns, select a column and click the up or down button to the right.

2.6. Choosing the Table Layout

The next step is to specify the layout of your report. The Choose the Table Layout page has these options:

• Stepped
• Block
• Include subtotals
• Enable drilldown

If you choose Stepped, the report will contain one column for each field, with grouping fields appearing in headers to the left of columns from the detail field. In this case, the group footer will not be created. If you include subtotals with this layout type, the subtotal is placed in the group header rows.

The Block option creates a report that contains one column for each field, with group fields appearing in the first detail row for each group. This layout type has group footers only if the Include Subtotals option is activated.

The Enable Drilldown option hides the inner groups of the report and enables a visibility toggle. (You can choose Enable Drilldown only if you select the Stepped option.)

To continue, choose Stepped and Include Subtotals and click Next. After that, you will be prompted to complete the wizard.

Finally, you complete the wizard’s work by providing a name for the report. (This report has the name Report1.) Also, you can take a look at the report summary, where all your previous steps during the creation of the report are documented. Click Finish to finish the wizard.

2.7. Previewing the Result Set

When you finish the creation of your report using the wizard, the Report Designer pane automatically appears. (If the Report Designer pane is not visible, click View | Designer.) In the pane, there are two tabs, Design and Preview, which you can use to view the created report in different forms.

The Design tab allows you to view and modify the layout of your report. The Design mode consists of the following sections: body, page, header, and page footer. You can use the Toolbox and Properties windows to manipulate items in the report. To view these windows, select Toolbox or Properties Window in the View menu. Use the Toolbox window to select items to place them in one of the sections. Each item on the report design surface contains properties that can be managed using the Properties window.

To preview the report, click the Preview tab (see Figure 25-8). The report runs automatically, using already specified properties. To finish the report, select File | Save All. That way you save your project and all files in relation to the created report.

3. Creating a Parameterized Report

A parameterized report is one that uses input parameters to complete report processing. The parameters are then used to execute a query that selects specific data for the report. If you design or deploy a parameterized report, you need to understand how parameter selections affect the report.

Parameters in SQL Server Reporting Services are used to filter data. They are specified using the standard syntax for variables (@year, for instance). If a parameter is specified in a query, a value must be provided to complete the SELECT statement or stored procedure that retrieves data for a report.

You can define a default value for a parameter. If all parameters have default values, the report will immediately display data when the report is executed. If at least one parameter does not have a default value, the report will display data after the user enters all needed parameter values.

When the report is run in a browser, the parameter is displayed in a box at the top of the report. When the report is run in Preview mode, the value of the parameter is displayed in the corresponding box.

An example will be used to show you how to create a parameterized report. This example describes only those steps that are different from the steps already discussed in relation to Example 25.1.

To create a new report, go to Solution Explorer in Visual Studio, click Projectl, and then right-click Reports and select Add New Report. The Report Wizard appears. Click Next.

The selection of a new source is identical to the specification of the first report’s source, except that you choose the AdventureWorksDW database instead of the sample database.

The query shown in Example 25.2 will be used to select data from the data source. It retrieves data from the AdventureWorksDW database. If you take a closer look at the SELECT statement, you will see that it calculates the number and the sum of unit product prices and groups the rows according to the list of column names in the GROUP BY clause, in that order. Again, type the query from Example 25.2 in the Design the Query page.

Example 25.2

SELECT t.MonthNumberOfYear AS month, t.CalendarYear AS year,

p.ProductKey AS product_id, SUM(f.UnitPrice) AS sum_of_sales,

COUNT(f.UnitPrice) AS total_sales

FROM DimDate t, DimProduct p, FactInternetSales f

WHERE t.DateKey = f.OrderDateKey AND p.ProductKey = f.ProductKey

AND CalendarYear = @year

GROUP BY t.CalendarYear, t.MonthNumberOfYear, p.ProductKey

ORDER BY 1;

The expression

CalendarYear = @year

in the WHERE clause of the example specifies that the input parameter @year in this query is related to the calendar year for which you want to query data. Therefore, this value will be used later to generate the report’s preview.

While the SELECT list of the query in Example 25.2 contains aggregate functions (COUNT and SUM), this report is a typical example of a matrix-type report. For this reason, choose Matrix on the Choose the Report Type page. On the Design the Matrix page, shown in Figure 25-9, values of the year column will be assigned to the Page view, values of the month column to the Columns view, and values of the product_id column to the Rows view. The Details view displays the aggregate values of sum_of_sales and total_sales. All remaining steps for the creation of this report are identical to the corresponding steps described in the section “Creating Your First Report.”

To start the report in the Preview mode, type the value of the CalendarYear parameter and click the Preview tab. (I used the value 2013 to generate the report in Figure 25-10.)

4. Creating a Report Using a Chart

The output of both reports in the previous sections is given in tabular or matrix form. Today, almost all users prefer to see a report’s output in graphical form. Charts are a popular method to present large quantities of data, because everyone can understand such an output. This section shows you different tools that are provided by Reporting Services to graphically present your data.

In this section we will first create a blank report and a shared data source using the AdventureWorksDW database. After that, we’ll create a shared data set with the query from Example 25.2, where the value for the year parameter will be set to 2013 in advance.

NOTE The main difference between the creation of reports in the previous section and the current section is that this section demonstrates how to create blank reports using the Report Service Project template instead of using the wizard.

To create a new report graphically, run Visual Studio. On the Start page, click Create a New Project to create a new project. Select Report Server Project (as shown earlier in Figure 25-6). Type the name of the project (Project2). The Location field should point to the default value. Click Create to create the new project.

In Solution Explorer, right-click the Shared Data Sources folder of the Project2 project. Select Add New Data Source, and type the name of the data source (Source3). Click Build. The Connection Properties dialog box appears. In the Server Name text box, choose your database server instance that hosts the AdventureWorksDW database.

For the Authentication field, select Windows Authentication. In the Select or Enter a Database Name field, select or type the database name (AdventureWorksDW). Click Test Connection. If the connection succeeds, click OK in the Connection Test dialog box, and click OK again to exit the Connection Properties dialog box. Finally, click OK to exit the Shared Data Source Properties dialog box. A new data source, called Source3.rds, is stored in the Shared Data Sources folder of the Project2 project.

Now, add the blank report in the following way: In Solution Explorer, under the Project2 project, right-click the Reports folder, choose Add in the drop-down menu, and select the New Item submenu. The Add New Item – Project2 dialog box appears (see Figure 25-11). Select the Report item. Enter Report3.rdl in the Name field in the lower part of the window. Click Add. The blank report, Report3.rdl, is created in the Project2 project. Now, we have to load the data and visualize it.

In the Report Data pane, click the New drop-down menu. Click Data Source from the submenu. The Data Source Properties dialog box appears. Enter DataSource3 for the name and check the Use Shared Data Source Reference radio button. Select Source3 from the corresponding drop-down list. Click OK.

NOTE If you cannot open the Report Data pane, press ctrl-alt-d to open it.

In the Report Data pane again, right-click the entry for the DataSource3 data source and select Add Dataset from the context menu. The Dataset Properties window appears with the Query page displayed. Enter Sales_2013 in the Name Field. In the Query pane, type the content of the query from Example 25.3 (see Figure 25-12). Click OK.

Example 25.3

SELECT t.MonthNumberOfYear AS month, t.CalendarYear AS year,

p.ProductKey AS product_id, SUM(f.UnitPrice) AS sum_of_sales,

COUNT(f.UnitPrice) AS total_sales

FROM DimDate t, DimProduct p, FactInternetSales f

WHERE t.DateKey=f.OrderDateKey AND p.ProductKey=f.ProductKey

AND CalendarYear = 2013

GROUP BY t.CalendarYear,t.MonthNumberOfYear,p.ProductKey

ORDER BY 1;

Drag the edges of the design area so that this surface fills the available space on the screen. After that, click View, click Toolbox, and select Chart. The Select Chart Type dialog box appears. Choose the first chart report item on the left side under Column and click OK to exit the Select Chart Type dialog box. You will see a representation of that chart type in the design area.

Double-click anywhere on the chart. The Chart Data window with field areas appears to the right of the chart. The areas are Values, Category Groups, and Series Groups. (The Chart Data window disappears when you click somewhere on the Design tab that is not covered by the chart.) Now, click the Refresh Fields button on the Dataset Properties dialog to refresh all values.

Before you drag and drop particular fields in one of the three Chart Data window areas, check out the purpose of each:

• Value The fields you select for this area provide the values for the data on the chart. In other words, when you add the field to the Values area, the text of the data field appears in the legend, and the data from this numeric field will be aggregated into one value. (The Values area in the chart is identical to the Data area in the matrix form.) Therefore, for our visualization, drag the sum_of_sales field from the folder and drop it in this field.

• Category Groups The fields you select in this area provide the labels for the horizontal axis of the chart. These category fields also group the rows from the dataset into multiple categories. (The Groups area in the chart is identical to the Columns group in the matrix form.) Therefore, for our visualization, drag the month column.
• Series Groups If you add a field to this area, the number of series depends on the data that is contained in that field. In other words, the number of values in the field will determine how many series will appear on the chart. For instance, if the field contains three values, the chart will display three series for every field in the Values area. For our visualization, we won’t drop a field in this area because we don’t have any series. (The Series Groups area in the chart is identical to the Rows group in the matrix form.)

Click Preview to run the report. Figure 25-13 shows what the bar chart looks like. Click Save All on the toolbar to save your report.

NOTE As you can see from Figure 25-13, the name of the chart has not been changed. To change the chart title above the chart area of a bar chart, switch to the Design view, right-click the chart title at the top of the chart, and click Title Properties. Replace the Title text field with the new text and click anywhere outside the text. In the same way you can change the name of the vertical and horizontal axis titles.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.