| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | helpviewer_keywords | ||
|---|---|---|---|---|---|---|---|---|---|---|---|
Reporting Services in SQL Server Data Tools (SSDT) |
See how to use the SQL Server Data Tools Report Designer authoring environment in Microsoft Visual Studio to create solutions for Reporting Services. |
maggiesMSFT |
maggies |
09/25/2024 |
reporting-services |
tools |
conceptual |
|
|
[!INCLUDESSRS applies to] [!INCLUDESSRS applies to 2016 and later]
[!INCLUDESQL Server Data Tools (SSDT)] is a [!INCLUDEMicrosoft] [!INCLUDEVisual 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.
[!INCLUDE SSRS report parts deprecated]
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 [!INCLUDEVisual 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 [!INCLUDEVisual Studio]. For more information, see Deployment and version support in SQL Server Data Tools (SSRS).
When you install the SSRS extension for [!INCLUDEVisual 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.
Note
To switch between default and advanced modes, you can right-click the down arrow to the right of the Column Groups icon.
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 [!INCLUDEVisual 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 [!INCLUDEVisual 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 [!INCLUDEMicrosoft] 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:
| Option | Purpose |
|---|---|
| Foreground Color | Select a text color. Black is the default text color. |
| Background Color | Select a background color for your text boxes and data regions. |
| Font | Specify whether the text is bold, italic, or underlined. |
| Justify | Specify whether the text is right-aligned, centered, or left-aligned. |
| Align | Specify how the selected objects are aligned in relation to one another within the report. |
| Make Same Size | Adjust the size of the selected objects within the report. |
| Horizontal Spacing | Adjust the horizontal spacing between the selected objects within the report. |
| Vertical Spacing | Adjust the vertical spacing between the selected objects within the report. |
| Center in Form | Center the selected object vertically and horizontally in relation to the Report Designer window. |
| Order | Move selected objects into the background or foreground. |
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 [!INCLUDEVisual 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.
| Option | Purpose |
|---|---|
| Report Properties | Open the Report Properties dialog to assign general report properties, such as the author name, grid spacing, the number of columns, and the page size. You can also configure settings for custom code, references to assemblies and classes, and the names of data output elements, data transforms, and data schemas. |
| View | Switch between the two Report Designer tabs: Design and Preview. Show or hide the ruler, the Grouping pane, or the Parameters pane. |
| Add Page Header | Add a page header to the report or delete one. When you delete a page header, all items in the page header are deleted. |
| Add Page Footer | Add a page footer to the report or delete one. When you delete a page footer, all items in the page footer are deleted. |
| Publish Report Parts | Select report parts to publish. |
You can use the following View menu options to show or hide various Report Designer windows and toolbars:
| Option | Component to show or hide |
|---|---|
| Error List | Errors that are detected when your publish or preview a report. |
| Output | Errors that are detected when you publish or process a report. Also, detailed information about expression errors when a report displays the text "#Error." |
| Properties Window | Property values for the currently selected report item on the design surface. You can use this option to see properties for nested report items. But you must select a report item multiple times to cycle through its hierarchy and nested members. To see which report item's properties are displayed, you can check the item name at the top of the Properties pane. |
| Toolbox | The toolbox. |
| Other Windows > Document Outline | A hierarchical view of report items and their collections of text boxes in a report. |
| Toolbars | Various toolbars, including Report Borders and Report Formatting. For more information, see Report Designer toolbars. |
| Report Data | The Report Data pane, where you can add report parameters, data sources, datasets, and images. |
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.
| Option | Purpose |
|---|---|
| Add New Item | Add a new shared data source or new report to the project. |
| Add Existing Item | Add an existing shared data source or an existing report to the project. |
| Import Reports | Import reports from another application, for example, Microsoft Access. |
| Exclude from Project | Exclude items from the project. This option doesn't delete excluded items from your file system. |
| Show All Files | Show all files in a project. |
| Refresh Project Toolbox Items | Refresh the toolbox cache when you install new custom report items in your project. |
| Properties | Open the Property Pages dialog for this project. For more information, see Project Property Pages dialog box. |
Report Designer provides the following specialized toolbars to use when you design reports:
| Toolbar | Purpose |
|---|---|
| Report | Add a page header or page footer. Set report properties. Show or hide the ruler or Grouping pane. Use the zoom control to change your view of a report. |
| Report Borders | Set the color, style, and width for all selected lines and the borders of all selected report items. |
| Report Formatting | Set the format of selected report items. For text boxes, you can use the toolbar to change the following types of formatting: font properties, text color, background color, and text justification. |
| Layout | Set the drawing order of report items and merging cells within a data region. |
| Standard | Open or save projects, display windows, and select the Debug configuration. |
You can use the View menu to show or hide these toolbars. Other [!INCLUDEVisual 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 [!INCLUDEVisual Studio] that you use. For [!INCLUDEVisual 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 [!INCLUDEVisual 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 [!INCLUDEVisual 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, andTargetServerURL. - 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:
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:
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