Reporting Services in SQL Server Data Tools (SSDT)

Applies to: SQL Server 2016 (13.x) Reporting Services and later

SQL Server Data Tools (SSDT) is a Microsoft Visual Studio environment for creating business intelligence solutions. SSDT features the Report Designer authoring environment. You can use Report Designer to open, modify, preview, save, and deploy SQL Server Reporting Services (SSRS) paginated report definitions, shared data sources, shared datasets, and report parts.

This article describes SSDT solutions, projects, project templates, and configurations that you use for SSRS. It also describes the views, menus, toolbars, and shortcuts that you can use in Report Designer.

SSDT isn't included with SQL Server. You need to install SSDT separately. For instructions, see Install SSDT with Visual Studio.

To use Report Designer templates, you also need to install an SSRS extension for Visual Studio. For instructions, see Install extensions for Analysis Services, Integration Services, and Reporting Services.

To get started designing reports, see Design Reporting Services paginated reports with Report Designer (SSRS).

A report project acts as a container for report definitions and resources. Every file in the report project is published to the report server when the project is deployed. When you create a project for the first time, a solution is also created as a container for the project. You can add multiple projects to a single solution.

To create multiple sets of project properties for deployment variations such as enterprise, test, and production report servers, you use the Configuration Manager feature in Visual Studio. For more information, see Deployment and version support in SQL Server Data Tools (SSRS).

When you install the SSRS extension for Visual Studio, as described earlier in SSDT installation, the following project templates are made available in SSDT:

• Report Server Project. When you use the Report Server Project template, Report Designer opens. You can find this business intelligence project template in the Create a new project dialog. For more information, see Create a report server project.

  Report Server project properties apply to all reports and all shared data sources in an SSDT project. These properties include the URL for the report server and the folder names for reports and shared data sources. You can use the Project Property Pages dialog to view the current property values. To open this dialog, you go to the Project menu, and then select Properties.

• Report Server Project Wizard. When you use the Report Server Wizard Project template, a report server project is automatically created, and the report wizard opens. In the wizard, you can create a report by following instructions on each page. The instructions describe how to:

  • Create a connection string to a data source.
  • Set data source credentials.
  • Design a query.
  • Add a table or matrix data region.
  • Specify report data and groups.
  • Pick a font and color style.
  • Publish the report to a report server.
  • Preview the report locally.

  After you create a report with the wizard, you can change the report data and the report design by using Report Designer in the report server project.

Report Designer offers multiple windows and panes to help you design reports and view rendered reports.

The Report Data pane displays data objects that you can use in a report.

To open the Report Data pane, you shift the focus to the design area. Then on the View menu, you select Report Data.

The following types of objects are available on the Report Data pane:

• Built-in fields. These fields contain predefined report information such as a report name or the time a report was processed.
• Data sources. A data source represents the name and connection information for a source of data.
• Datasets. Each dataset includes a query that specifies which data to retrieve from the data source. You can expand the dataset to view the collection of fields that the dataset query specifies. In some query designers for multidimensional datasets, you can specify filters in the Filters pane and indicate whether to create report parameters. If you specify the report parameter option, a special dataset is automatically created to populate the parameter's valid values list. By default, the dataset doesn't appear in the Report Data pane. For more information, see Show hidden datasets for parameter values - multidimensional data.
• Report parameters. You can create report parameters manually or automatically when a dataset query includes query parameters.
• Images. The images in this list are available to include as image report items in a report.

Data sources and datasets in the Report Data pane represent the elements in the report definition. The Report Data pane is a feature that multiple report authoring environments support.

• In Report Builder, it's the only pane that's available for managing data sources and datasets.
• In Report Designer, the Report Data pane works with Solution Explorer, which lists shared data sources and shared datasets as files. Shared data sources and shared datasets in the Report Data pane must point to their corresponding shared data sources and shared datasets in Solution Explorer. The Report Data pane elements then contain a reference to the data files in Solution Explorer. The project properties determine whether the shared data sources and shared datasets are deployed to the report server or SharePoint site. For more information, see Convert data sources (Report Builder and SSRS).

If the Report Data pane is floating, you can anchor it. For more information, see Dock the Report Data pane in Report Designer (SSRS).

You use the Grouping pane to define groups for a tablix data region. You can define row groups and detail groups for tables, and you can define row and column groups for matrices. You can't use the Grouping pane to define groups for charts or other data regions. For more information, see Groups in a Report Builder paginated report.

The Grouping pane has two modes:

• Default. You use default mode to display all row and column groups in a hierarchical format that shows the relationships between parent groups, child groups, adjacent groups, and detail groups. A child group appears under and at the next indent level compared to its parent group. An adjacent group appears at the same indent level as its peer or sibling groups.

  You also use default mode to add, edit, or delete groups. For groups based a single dataset field, you drag the field to the Row Groups or Column Groups pane. You can insert the group next to an existing group. To add an adjacent group, you use the shortcut menu of the sibling group. To display which tablix cells belong to a group, you select the group in the Grouping pane.

• Advanced. You use advanced mode to display static and dynamic row and column group members of a selected tablix data region. In advanced mode, you can also set properties that control the visibility of the rows and columns that are associated with a group or group member. These properties determine the rules that renderers use to try to keep groups together on a page. Group members appear on the design surface as cells in the row group and column group areas.

For more information, see Grouping pane.

The Toolbox pane contains report items that you can drag to the design surface.

• Data regions are report items that you use to organize data on the report. Examples of data regions include a table, matrix, list, chart, gauge, data bar, sparkline, and indicator.
• Other report items include a map, text box, rectangle, line, image, and subreport.
• Custom report items might also appear in the Toolbox pane if your system administrator installs and registers them.

The Properties pane is a standard Visual Studio window that shows property names and values for the currently selected report item on the design surface.

To display the Properties pane, you go to the View menu, and then select Properties Window. You can undock this pane and move it to another area of the SSDT window, or display it as a tabbed view on the design surface.

In most cases, property names correspond to elements and attributes in the Report Definition Language (RDL) file. You can set the most commonly used properties by using the Properties dialog for a selected item. To open the Properties dialog for an item, you select the item and then select the Property Pages button on the Properties pane toolbar. Advanced users can set property values directly in the Properties pane.

You can use the Properties pane for the following tasks:

• Set properties for the currently selected item on the design surface. Some properties provide a dropdown list of values. You can also enter the value directly in the cell. Some properties contain a collection of values, indicated by the value (Collection). Most properties can accept an expression. Complex expressions are indicated by the value <expression>. To open the Expression dialog, you select the Expression (fx) button. For more information, see Expression dialog box.
• Use the Properties pane toolbar buttons to change the grid from a category view to an alphabetical view. In the category view, you might need to expand a category to see all the properties under it. To open an item's Properties dialog, you can select the Property Pages button on the toolbar. Or you can right-click the item and select Properties.
• Set properties for the currently selected group member in the Grouping pane. Group member properties help control how static group header and footer rows are repeated for each group instance. For more information, see Display headers and footers with a group in a paginated report (Report Builder).

Solution Explorer is a standard Visual Studio component that displays all the items in your project. For a report server project, this list of items includes folders to organize shared data sources, shared datasets, reports, and resources. Folder items are automatically alphabetized when you open the solution file. To view item properties in the Properties pane, select the item.

The Output window displays processing errors that occur when you preview a report. This window also displays publishing errors that occur when you deploy a report or a shared data source.

You can use the Output window to help debug errors in expressions.

The Document Outline pane displays a hierarchical list of all report items in the report definition. To open the Document Outline pane, you can select View > Other Windows > Document Outline.

The Document Outline pane is useful for identifying text boxes and other report items by name. When you select an item in the Document Outline pane, the item is also selected on the design surface.

You can also use the Document Outline pane to help debug errors in expressions.

The Task List window displays build errors that occur when you import a report from another application. For instance, if you import a report from Microsoft Access and the report contains a feature that SSRS doesn't support, an error is reported in the Task List window.

Report Designer supports two views:

• Design, to define a report's data and layout
• Preview, to display a rendered view of a report

When you create a report server project, Report Designer opens in Design view by default and displays the design surface. By default, the design surface displays the report body and the report background.

The design surface background has a shortcut menu. That menu provides options for adding a page header and page footer. It also contains a View menu that you can use to display the ruler, the Grouping pane, and the Parameters pane.

You can use the zoom control to increase or decrease the magnification of the report.

To design a report, you drag report items from the Toolbox pane to the design surface. Then you configure their properties and alter their arrangement on the report.

In the Preview view, you run the report and view the rendered report in the report viewer. You can also set configuration properties to run the report in debug mode by using a browser.

When you preview a report, Report Designer:

• Connects to the report data sources.
• Runs dataset queries.
• Caches the data on the local computer.
• Processes the report to combine data and layout.
• Renders the report.

There are a few points to keep in mind when you use the Preview view:

• Parameterized reports. When you preview a report, the report is processed automatically if all report parameters have valid default values. If one or more report parameters don't have a valid default value, you must choose a value for each unassigned parameter. On the report toolbar, you then have to select View Report.
• The local data cache. When you preview a report, the report processor runs all the queries for datasets in the report by using the current parameter defaults. It saves the results as a local data cache (.rdl.data) file. You can continue to design your report without incurring the overhead of retrieving this data again if you make no changes to either the report dataset queries or the report parameters.
• Configuration Manager and debug. In SSDT, project properties define how you want to deploy and debug your reports. These properties apply to all reports and shared data sources in the project. To set the project properties, you go to the Project menu and select Properties. In the Property Pages dialog, you then select Configuration Manager. You can use these settings to test your reports and publish them to the report server.
• The Output pane. When you preview a report and the report processor detects a problem, it writes error messages to the Output pane.

When a Report Designer project is active in SSDT, the following menus are added to the main menu.

When you select an item on the design surface, the Format menu contains the following options:

You can use the following options to configure settings that apply to an entire report. The availability and location of these options depend on the version of Visual Studio that you use:

• In earlier versions, you can find these options in the Report menu.
• In later versions, most options are in the shortcut menu of the design surface background.

You can use the following View menu options to show or hide various Report Designer windows and toolbars:

You can use the following Project menu options to manage shared data sources and reports in a project. When you add or remove items from the project, the hierarchical display of project items in Solution Explorer is automatically updated.

Report Designer provides the following specialized toolbars to use when you design reports:

You can use the View menu to show or hide these toolbars. Other Visual Studio toolbars might be unavailable if their functionality doesn't apply to Report Designer features.

SSDT can integrate with source plug-ins. You can use the Source Controls pages in the Options dialog to specify a plug-in and configure properties.

To use custom reports as templates for new reports, you copy them to the ReportProject folder on the computer on which SSDT is installed.

The default location of this folder depends on the version and edition of Visual Studio that you use. For Visual Studio 2022, 2019, and 2017, this folder is in the following location:

%ProgramFiles%\Microsoft Visual Studio\<release-year>\<edition>\Common7\IDE\CommonExtensions\Microsoft\SSRS\ProjectItems\ReportProject

For Visual Studio 2015, the default location is the following folder:

%ProgramFiles%\Microsoft Visual Studio 14.0\Common7\IDE\Private Assemblies\ProjectItems\ReportProject

When you add a new item to the report project, your custom report appears in the Templates pane. You can also add custom styles to the report wizard.

SSDT is based on Visual Studio and the underlying devenv.exe application. This application offers several command-line options that are useful for working with reports.

Before you can use the command-line options that this section describes, you must set valid values for the following two items:

• Project properties for OverwriteDataSources, TargetDataSourceFolder, TargetReportFolder, and TargetServerURL.
• At least one set of configuration properties, for example, Debug or Release.

For more information, see Publishing data sources and reports.

For a report server project, you can specify the following options from the command line:

• /deploy: Deploys reports by using the project properties that the configuration file specifies. For example, the following command deploys the reports that the solution file Reports.sln specifies. It uses the Release configuration settings that are specified in the project properties:

  devenv.exe "C:\Users\<user-name>\source\repos\Reports\Reports.sln" /deploy "Release"
  

• /build: Builds the solution file, but doesn't deploy it. For example, the following command builds the reports that the solution file Reports.sln specifies. It uses the Debug configuration settings that are specified in the project properties:

  devenv.exe "C:\Users\<user-name>\source\repos\Reports\Reports.sln" /build "Debug"
  

• /out: Redirects the output that's generated by building a solution to the specified file. For example, the following command redirects the output from the build in the previous example to a file named mybuildlog.txt.

  devenv.exe "C:\Users\<user-name>\source\repos\Reports\Reports.sln" /build "Debug" /out mybuildlog.txt
  

You can use keyboard shortcuts to:

• Control windows and modes in SSDT:

  Expand table

  Description	Key combination
  Build the selected project	Ctrl+Shift+B
  Display the Properties window	F4
  Display the Report Data window	Ctrl+Alt+D
  Start debugging	F5
  Move from one open window to the next	F6

• Control items on the report design surface:

  Expand table

  Description	Key Combination
  Move the focus from one report item to the next report item	Tab
  Move the selected report item	Arrow keys
  Nudge the selected report item	Ctrl+Arrow keys
  Increase or decrease the size of the selected report item	Ctrl+Shift+Arrow keys
  In a text box, move the cursor to the beginning of the display text that's visible	Ctrl+Home
  In a text box, move the cursor to the end of the display text that's visible	Ctrl+End
  In a text box, select text from the current cursor position to the beginning of the display text that's visible	Shift+Home
  In a text box, select text from the current cursor position to the end of the display text that's visible	Shift+End
  In a text box, select text from the current cursor position to the beginning of the expression	Ctrl+Shift+Home
  In a text box, select text from the current cursor position to the end of the expression	Ctrl+Shift+End
  Open the shortcut menu for the selected report item	Shift+F10

• Solution Explorer
• Reporting Services reports (SSRS)
• Report Definition Language (SSRS)