{"id":1932,"date":"2015-01-06T00:00:00","date_gmt":"2015-01-06T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-reporting-services-basics-deploying-reports\/"},"modified":"2021-05-17T18:33:15","modified_gmt":"2021-05-17T18:33:15","slug":"sql-server-reporting-services-basics-deploying-reports","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sql-server-reporting-services-basics-deploying-reports\/","title":{"rendered":"SQL Server Reporting Services Basics: Deploying Reports"},"content":{"rendered":"<div class=\"article-content\">\n<p> \tThis is the last installment of a four-part series of articles on the basics of creating and publishing reports using SQL Server Reporting Services: <\/p>\n<ul>\n<li><a href=\"https:\/\/www.simple-talk.com\/sql\/reporting-services\/sql-server-reporting-services-basics-building-ssrs-reports\/?utm_source=simpletalk&amp;utm_medium=weblink&amp;utm_content-ssrsbasics4\">Part 1<\/a>, provided a step-by-step guide to basic report creation <\/li>\n<li><a href=\"https:\/\/www.simple-talk.com\/sql\/reporting-services\/sql-server-reporting-services-basics-customizing-ssrs-reports\/?utm_source=simpletalk&amp;utm_medium=weblink&amp;utm_content-ssrsbasics4\">Part 2<\/a> took a tour of some of the core SSRS features and functions that you&#8217;ll need to develop dynamic reports <\/li>\n<li><a href=\"https:\/\/www.simple-talk.com\/sql\/reporting-services\/sql-server-reporting-services-basics-the-visual-controls\/?utm_source=simpletalk&amp;utm_medium=weblink&amp;utm_content-ssrsbasics4\">Part 3<\/a> focused entirely on the visual controls<\/li>\n<\/ul>\n<p> \tPart 4 focuses on the Report Definition Language (RDL), publishing reports, and Report Builder 3.0. First, you&#8217;ll examine the component parts of a typical RDL file and learn how to use your knowledge of RDL to refine and customize your reports, if necessary. <\/p>\n<p> \tYou&#8217;ll then learn about deploying reports to the built-in site for SSRS, Report Manager. There are other ways to publish reports, such as within SharePoint, but this article will focus on the most basic mechanism. <\/p>\n<p> \tFinally, you&#8217;ll take a look at Report Builder 3.0 Tool, the ad-hoc reporting tool that ships with SQL Server Reporting Services 2008 R2 and later. This report-building tool allows end-users to build custom reports based on report parts and shared datasets. Confused? Fear not, all will become clear! <\/p>\n<h2>Getting Started<\/h2>\n<p> \tIn order to follow the examples, you will need to have the SQL Server database engine, SQL Server Reporting Services, and SQL Server Data Tools &#8211; BI (SSDT-BI) correctly installed and configured. If you need some help with this, please refer back to the links in Part 1 of this series. Next, download the code file for this article (see the <strong>Code Download<\/strong> link above). The code file contains two sample Visual Studio projects and a SQL Script for creating the <code>ReportDemo<\/code> database. If you&#8217;ve not done so already, go ahead and create the database using the <code>ReportingDemoDatabaseScript.sql<\/code>. You will not be creating any new reports from scratch in this article but instead using reports that were created in the Projects from Parts 1 and 3, all of which are included in the code download file. <\/p>\n<h2>Report Definition Language<\/h2>\n<p> \tIf you really wanted to, you could develop SSRS reports in a vanilla text editor like Notepad. I don&#8217;t recommend it, though. Microsoft provides great tools that make report development easy with a drag-and-drop interface. However, every once in a while, there is a reason to take a look at the actual code and maybe even tweak it a bit. To do that, it helps to understand what you are looking at when you do take a peek at the code. <\/p>\n<p> \tThe <a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms155062.aspx\">definition of RDL<\/a> provided by MSDN is succinct and hard to better, so I&#8217;ll use it here: <\/p>\n<p> \t\t&#8220;A report definition contains data retrieval and layout information for a report. Report Definition Language (RDL) is an XML representation of this report definition.&#8221; \t<\/p>\n<p> \tReport Definition Language (RDL) is an XML-based schema for defining reports, and the reports that SSRS generates from the SSDT-BI report designer are basically just XML. Each report has a body and will have a header and footer if defined. The schema defines: <\/p>\n<ul>\n<li>Report layout- the body of the RDL file defines all of the objects that will be displayed in the report, including fields, images and tables <\/li>\n<li>Each dataset, the data source for each dataset and database connection information (where no data source is used) <\/li>\n<li>A set of fields in each dataset that can be populated with data <\/li>\n<li>Any parameters that are used in the report<\/li>\n<\/ul>\n<p> \tYou can read the full specification for RDL at: <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/dd297486(v=SQL.100).aspx\">http:\/\/technet.microsoft.com\/en-us\/library\/dd297486(v=SQL.100).aspx<\/a>. <\/p>\n<h3>Inside a typical RDL file<\/h3>\n<p> \tYou don&#8217;t have to be an XML guru to understand the RDL file, and the easiest way to get familiar with the basics of RDL is to dive right in and take a look at the component part of a typical RDL file, in this case the RDL for the <code>ExpressionReport.rdl<\/code> report that you developed back in Part 2, and which is included in the download project. <\/p>\n<p> \tIn SQL Server Data Tools &#8211; BI, open the <strong>Chart<\/strong><strong>Project<\/strong> report solution, from the code download, navigate to the sample report, <strong>MyChart<\/strong><strong>.rdl<\/strong>, in the solution explorer, right-click and select the <strong>View Code<\/strong> option. This opens the report in XML mode. The full XML for this file can be found in the code download bundle, but Figure 1 shows contents of the file with all nodes collapsed. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-aea554f7-f6d7-49af-97f7-a120af7c4927.png\" alt=\"2122-1-aea554f7-f6d7-49af-97f7-a120af7c4\" \/><\/p>\n<p class=\"caption\"> \tFigure 1 <\/p>\n<p> \tAfter the document element, the RDL files breaks down into the following major sections: <\/p>\n<ul>\n<li><code>Document Element<\/code> &#8211; defines the name of the report and the schema to which our RDL must conform<\/li>\n<li><code>Body<\/code> &#8211; defines all of the report items<\/li>\n<li><code>Page<\/code> &#8211; page headers and footers, if used<\/li>\n<li><code>DataSources<\/code> &#8211; defines all dedicated and shared data sources<\/li>\n<li><code>DataSets<\/code> &#8211; defines each data set used in the report<\/li>\n<li><code>Report<\/code><code>Parameters<\/code> &#8211; all parameters defined for the report<\/li>\n<li><code>Code<\/code><code><\/code>&#8211; any custom code, such as custom functions<\/li>\n<\/ul>\n<p> \tAt the very top of the code is the <code>Document<\/code> element, as shown in Figure 2. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-4e220969-846b-4af4-b29c-074e22216288.png\" alt=\"2122-1-4e220969-846b-4af4-b29c-074e22216\" \/><\/p>\n<p class=\"caption\"> \tFigure 2 <\/p>\n<p> \tThe document element is called <code>Report<\/code> &#8211; no surprise there. It references two XML namespaces: <\/p>\n<ul>\n<li>http:\/\/schemas.microsoft.com\/sqlserver\/reporting\/2008\/01\/reportdefinition<\/li>\n<li>http:\/\/schemas.microsoft.com\/SQLServer\/reporting\/reportdesigner<\/li>\n<\/ul>\n<p> \tThese namespaces explicitly define what is allowed inside the RDL document from an XML standpoint. To see this in action, I added an invalid tag to the report and received the error message shown in Figure 3. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-ed0ade49-618d-4e9d-b8b0-bd59ccf6ae1b.png\" alt=\"2122-ed0ade49-618d-4e9d-b8b0-bd59ccf6ae1\" \/><\/p>\n<p class=\"caption\"> \tFigure 3 <\/p>\n<p> \tSince the change I made did not conform to the published schema, the change was immediately flagged as an error because the report definition is now invalid. <\/p>\n<div class=\"tips indented\">\n<p> \t\tNOTE: It is possible to create custom report items with a <strong>.NET<\/strong> language. See this MSDN <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms345231.aspx\">article<\/a> for more details. \t<\/p>\n<\/p><\/div>\n<p> \tYou can expand the XML to review the code of any of the sections of the report. You&#8217;ll see all the properties that you are familiar with as well as some that may be new to you. Figure 4 shows a nice example from the <code>DataSets<\/code> section of the <code>MyChart<\/code> RDL file, displaying the actual query for the dataset. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-d632750c-9ed5-499a-8855-b1c3145a65df.png\" alt=\"2122-1-d632750c-9ed5-499a-8855-b1c3145a6\" \/><\/p>\n<p class=\"caption\"> \tFigure 4 <\/p>\n<h3>Writing your own RDL<\/h3>\n<p> \tNow you have a good idea of the RDL that is written behind the scenes when the SQL Report Designer is doing its thing. This understanding of the report designer gives you the ability to manually tweak a report when necessary. I&#8217;ve only had to do this once or twice in over 10 years. One example I remember quite well involved a sub-report that, on the Report Manager site, was located in a different folder than the main report. The only way I could embed the sub-report was to manually change the link inside the XML. However, knowing RDL empowers you in other ways too. You are not restricted to using the Visual Studio Report designer, because SSRS Reports are not in some proprietary format. If you wish, you can roll your own report builder tool or RDL generator. <\/p>\n<h2>Configuring Reports for Deployment<\/h2>\n<p> \tDeveloping reports on your own computer can be quite fun and sometimes challenging, but the reports are not useful until they are published to a location that is accessible to the people who need to see the reports. There are many ways to publish reports, including to SharePoint or within custom applications. However, the easiest way to publish and manage reports is with the built-in <strong>Report Manager<\/strong>. This is also called <strong>Native Mode<\/strong> deployment. Report Manager is a small web site that can run on a separate web server or on the SQL Server that is hosting the SSRS databases. Starting with 2008, SSRS is not dependent on Internet Information Services (IIS) which makes DBAs more comfortable deploying it on the same server as the database engine. <\/p>\n<h3>Viewing the Reporting Services Configuration<\/h3>\n<p> \tThe first article in the series walked through installing and configuring Report Manager and so if you have followed along you probably have Report Manager running on your computer right now. It&#8217;s a great tool for learning how to publish reports to your own computer, before presenting them to the real world. <\/p>\n<p> \tTo make sure Report Manager is configured and running, launch <strong>SQL Server Reporting Services Configuration Manager<\/strong> and connect to the Reporting Server instance, which in my case is a SQL Server running as a default instance, so <strong>MSSQLSERVER<\/strong> is the correct instance name. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-0c1c687c-7677-4320-972d-34f102b3ab83.png\" alt=\"2122-1-0c1c687c-7677-4320-972d-34f102b3a\" \/><\/p>\n<p class=\"caption\"> \tFigure 5 <\/p>\n<p> \tAfter clicking <strong>Connect<\/strong>, you can review the various properties and settings of the Report Server. Click <strong>Web Service URL<\/strong> to find the URL of the report server to which you will deploy your reports. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-fea7aba8-be45-40ec-83de-a8f564ce49a6.png\" alt=\"2122-fea7aba8-be45-40ec-83de-a8f564ce49a\" \/><\/p>\n<p class=\"caption\"> \tFigure 6 <\/p>\n<p> \tClick <strong>Report Manager URL<\/strong> to see the URL for viewing and managing reports, as shown in Figure 7. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-e3d3d41d-d516-4689-a326-06acc96326fc.png\" alt=\"2122-e3d3d41d-d516-4689-a326-06acc96326f\" \/><\/p>\n<p class=\"caption\"> \tFigure 7 <\/p>\n<p> \tIf SSRS and Report Manager are not configured, go back to the first article in the series to configure them before continuing to the next section. <\/p>\n<h3>Configuring a Report Project for Publication<\/h3>\n<p> \tThe process of publishing, or deploying, an SSRS project copies the reports, data sources, and datasets to Report Manager. Once the reports are published, they can be viewed by anyone with the correct permissions. In this section, you&#8217;ll configure deployment of your reports to Report Manager, running on your local computer. The steps for deploying to a site in your enterprise network are the same; you&#8217;ll just have to modify the location. <\/p>\n<p> \tIn SQL Server Data Tools &#8211; BI, open <strong>ChartProject<\/strong> from the download. To deploy the <strong>ChartProject<\/strong> reports, you must configure the location of Report Manager in the project properties. Within the Solution Explorer, right-click on <strong>ChartProject<\/strong>, the project name, and select <strong>Properties<\/strong>. In the <strong>TargetServerURL<\/strong> property, fill in the <strong>Web Service URL<\/strong> you found earlier (see Figure 6). When deploying to a network location in your company, you may need to get the URL value from your DBA. Figure 8 shows the properties I am using. I have replaced my computer name with &#8220;localhost&#8221;. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-2ed63f4b-09e1-4f71-aab3-3a1574dd96b0.png\" alt=\"2122-2ed63f4b-09e1-4f71-aab3-3a1574dd96b\" \/><\/p>\n<p class=\"caption\"> \tFigure 8 <\/p>\n<p> \tIn Figure8, you&#8217;ll see that there are several other target folders. For example, the <strong>TargetDatas<\/strong><strong>ource<\/strong><strong>Folder<\/strong> property shows where all of the shared data sources will end up in Report Manager. By default, the <strong>TargetReportFolder<\/strong> has the same name as the project but you can change this value if required. <\/p>\n<p> \tTake a look at the <strong>OverwriteDatasets<\/strong> property, which is set to <strong>False<\/strong>. In many cases, you will create reports against a development or QA server instead of production. When this value is False, it will not let you overwrite an existing datasource with the same name. This will prevent your development settings from changing the production settings. <\/p>\n<p> \tClick <strong>OK<\/strong> to accept the change to the URL. <\/p>\n<h3>Local Security Issues<\/h3>\n<p> \tWhen working on Windows 7 or 8, you may try to perform tasks that need Administrator permissions. Even if your account is an administrator account, those operations are blocked by default. This is not a bug, but a security feature to prevent malicious code from operating on your system. <\/p>\n<p> \tThis security feature will prevent you from publishing and viewing reports on Report Manager locally. One way to get around this is to launch SSDT-BI by holding down the shift key and right-clicking on the link, and then select <strong>Run as adm<\/strong><strong>inistrator<\/strong>. You will then be able to publish reports locally. When launching your favorite web browser to view Report Manager, you will need to do the same thing. <\/p>\n<p> \tThere is a way to set permissions for your account in SSRS to avoid this issue. Follow the directions in this MSDN <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/bb630430.aspx\">article<\/a> to learn more. <\/p>\n<h2>Deploying Reports<\/h2>\n<p> \tNow that the <strong>TargetServerURL<\/strong> is configured in your project, and you have addressed the local security issue, you are ready to publish some reports! Right-click the project name inside the <strong>Solution Explorer<\/strong> and select <strong>Deploy<\/strong>. The <strong>Output <\/strong>window should pop up to show you the status. Once my reports were deployed, I saw the messages in Figure 9. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-2ccb6f2c-609a-4160-a31c-d6d2dd42f1e3.png\" alt=\"2122-1-2ccb6f2c-609a-4160-a31c-d6d2dd42f\" \/><\/p>\n<p class=\"caption\"> \tFigure 9 <\/p>\n<p> \tIf your reports did not deploy, you will have to review the error messages and troubleshoot. Most of the time, the problems stem from an incorrect or missing <strong>TargetServerURL<\/strong> or from permissions issues. <\/p>\n<h3>Viewing the Published Reports<\/h3>\n<p> \tNow that you have published the reports, you will want to view them on the Report Manager site. Launch your favorite web browser and navigate to the Report Manager URL (see Figure 7). If you receive an error message about permissions, review the previous &#8220;Local Security Issues&#8221; section. <\/p>\n<p> \tIf everything worked as expected, Report Manger should display the contents of the <strong>Home<\/strong> folder (the top-level folder in the Report Server folder hierarchy). In this case, you should see within it two sub-folders, as shown in Figure 10. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-57c16eea-cde6-47aa-a9bb-606b19704ea6.png\" alt=\"2122-1-57c16eea-cde6-47aa-a9bb-606b19704\" \/><\/p>\n<p class=\"caption\"> \tFigure 10 <\/p>\n<p> \tClick the <strong>ChartProject<\/strong> folder. You will see all of the reports from the project. Click one of the reports to run it. At this point, if you have errors connecting to the report, you can navigate to the data source set up in the <strong>Data Sources<\/strong> folder to troubleshoot the connection. <\/p>\n<p> \tIf the <strong>ReportDemo<\/strong> database and the <strong>Report Manager<\/strong> site are all located on the same server, such as your laptop, your credentials should work to run the report. If they are located on separate servers, your network account credentials from the <strong>Report Manager<\/strong> will not pass through to the database. This is called the &#8220;double-hop&#8221; problem. There are two ways around this issue. The easiest, but least secure way, is to use SQL Server security instead of AD security in the report. The other method is to configure <a href=\"http:\/\/blogs.technet.com\/b\/rob\/archive\/2011\/11\/23\/enabling-kerberos-authentication-for-reporting-services.aspx\">Kerberos Authentication<\/a>. Talk to your network administrators. When learning how to develop reports, I recommend running everything on one computer. <\/p>\n<p> \tFigure 11 shows the <strong>Indicators <\/strong>report and the breadcrumb trail at the top for navigating back to any of the folders. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-0762794c-3efa-40d1-93a0-4ec8ff0e16ca.png\" alt=\"2122-1-0762794c-3efa-40d1-93a0-4ec8ff0e1\" \/><\/p>\n<p class=\"caption\"> \tFigure 11 <\/p>\n<p> \tNone of these reports have parameters, but if they did, the parameter controls would be found right above the report. <\/p>\n<p> \tYou have deployed the entire project, but you can deploy just one report if you wish. To do so, just right-click the report and select Deploy. You can also redeploy the project or a report, after making changes. If you redeploy, the reports will be overwritten without a prompt. <\/p>\n<h3>Securing Reports<\/h3>\n<p> \tYou have seen how easy it is to publish reports to Report Manager directly from SSDT-BI. However, it is very important that only the correct eyes see the reports. On your own computer, you will not need to worry about security, but you may need to configure it on your enterprise wide SSRS deployment. In many companies, the person developing reports is not the same person securing them. However, it is useful to understand how the security works, even if it is not your job, just in case you are called in to help troubleshoot an issue. <\/p>\n<p> \tYou can set up permissions at the folder or report level. I strongly recommend that permissions are set up only at the folder level. You allow users to view reports in a given folder by assigning the appropriate roles, either to individual users or to an Active Directory group of which the user is a member. I recommend giving permissions at the AD level, not to individuals. <\/p>\n<p> \tBy default, folders and reports inherit permissions from folders higher in the hierarchy. For example, if the <strong>Everyone<\/strong> network group has permission to run reports at the <strong>Home<\/strong> folder level, then <strong>Everyone<\/strong> also has permission to view reports in all sub-folders, unless you enforce security by overriding the default permissions on each sub-folder, so that only the appropriate users can view the reports in each one. <\/p>\n<p> \tThere are many ways to do this but, for example, within the Home folder you might create folders for specific departments, and then assign permissions to the AD group for the departments. Inside the department folders you could have a <strong>Managers<\/strong> folder with reports only for managers. Figure 12 shows how this might look for the IT department. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-089f1362-25e2-43db-b484-32fad76d042b.png\" alt=\"2122-1-089f1362-25e2-43db-b484-32fad76d0\" \/><\/p>\n<p class=\"caption\"> \tFigure 12 <\/p>\n<p> \tOnce the folders are in place, hover older a folder name to bring up a dropdown list of tasks and select <strong>Security<\/strong> (or open the appropriate folder, select <strong>Folder Settings<\/strong> from the top menu, and then open the <strong>Security<\/strong> page). Figure 13 shows the Security page, though note that you will only see groups such as the <strong>Everyone<\/strong> group if you are running in a network with Active Directory. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-649e8fbc-68d7-461e-9d60-b8ad1d17bce9.png\" alt=\"2122-649e8fbc-68d7-461e-9d60-b8ad1d17bce\" \/><\/p>\n<p class=\"caption\"> \tFigure 13 <\/p>\n<p> \tClick <strong>Edit Item Security<\/strong> and you will be prompted with a message telling you that you will break the inheritance from the parent folder. Click <strong>OK<\/strong>. Click <strong>New Role Assignment<\/strong>. This opens a page where you can assign roles to a named user or AD group, as shown in Figure 14. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-947798ed-cfe6-4afb-9250-6f33106bcc97.png\" alt=\"2122-1-947798ed-cfe6-4afb-9250-6f33106bc\" \/><\/p>\n<p class=\"caption\"> \tFigure 14 <\/p>\n<p> \tTo allow a user or group to view reports only, assign the <strong>Browser<\/strong> role. Remember that when assigning permission to a folder, all the reports in the folder will inherit those permissions. Always assign permissions to folders, not reports, to keep security manageable. <\/p>\n<p> \tThere are also two roles at the instance level: <strong>System Administrator<\/strong> and <strong>System User<\/strong>. To manage these permissions, click the <strong>Site Settings<\/strong> link at the top right of the page. The <strong>System User<\/strong> site role and the folder <strong>Report Builder<\/strong> role are needed by users who need to run Report Builder. <\/p>\n<h3>Managing Reports<\/h3>\n<p> \tThere are so many factors that you can control in SSRS, that it is impossible to cover all of them in this article. Hover over a report name so that the drop-down list of tasks appears. Select <strong>Manage<\/strong>. Eight topic areas appear that you can manage, as shown in Figure 15. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-bbd8a360-4f55-4861-ba39-cadbfc7be4bb.png\" alt=\"2122-1-bbd8a360-4f55-4861-ba39-cadbfc7be\" \/><\/p>\n<p class=\"caption\"> \tFigure 15 <\/p>\n<p> \tYou can set up subscriptions for automatically delivered reports, cache reports that run frequently with data that doesn&#8217;t often change, and much more. <\/p>\n<h2>Using Report Builder 3.0<\/h2>\n<p> \tMost of the time, a developer creates a report based on a set of requirements and then publishes the report where the person requesting it can run it. However, you may also have heard the term &#8220;self-service BI&#8221;, which means to give the end users the ability to create their own reports and dashboards. Report Builder 3.0 is a self-service BI tool. <\/p>\n<p> \tReport Builder 3.0 enables the user to select from pre-defined datasets and report parts or to design reports from scratch. The user can use the simple drag-and-drop interface to build custom reports, wizards, or design a report with the same techniques that you use. <\/p>\n<p> \tStarting in 2005, Report Builder has been a component of SSRS. The original versions of Report Builder required Data Models, basically a view of the data that could be used. All previous versions of Report Builder, along with Data Models, have been deprecated. Unlike the previous versions, Report Builder 3.0 looks and works almost exactly like the developer tool, SSDT-BI. <\/p>\n<p> \tReport Builder is a great way to let end-users who know little or no T-SQL build their own reports. You can provide predefined parameter lists, data sources, datasets, and even tables and visual elements that they can use to build custom reports. <\/p>\n<p> \tHere are the differences between Report Builder 3.0 and SSDT-BI: <\/p>\n<ul>\n<li>SSDT-BI allows you to work with projects of multiple reports while you can work with only one report at a time with Report Builder 3.0.<\/li>\n<li>SSDT-BI has an interface that is familiar to developers while Report Builder 3.0 looks more like Office.<\/li>\n<li>You can publish Report Parts with SSDT-BI. You can utilize Report Parts with Report Builder 3.0.<\/li>\n<li>SSDT-BI can integrate with source control programs while Report Builder 3.0 cannot.<\/li>\n<li>Report Builder 3.0 has more wizards than SSDT-BI.<\/li>\n<\/ul>\n<h3>Publishing Report Parts<\/h3>\n<p> \tIn this exercise, you are going publish some report parts that will allow the end-users to create and customize their own reports. If you have followed along in this article, the <strong>ChartProject<\/strong> project should be configured for deployment. You will also need to configure the <strong>First<\/strong><strong>Project<\/strong>. Go back to the &#8220;Viewing the Reporting Services Configuration&#8221; and &#8220;Configuring the Project for Publishing&#8221; sections to learn how to set up the properties. <\/p>\n<p> \tReports are composed of objects such as tables, and charts. Not only can you publish entire reports, you can also publish the individual objects so that they can be reused within Report Builder 3.0. You have seen that data sources are automatically deployed when you deploy a project. If the project has datasets, they are also deployed automatically when deploying the project. <\/p>\n<p> \tYou must mark each part of the report that you want to publish. Open up <strong>First<\/strong><strong>Project<\/strong> from the downloaded code. Double-click the <strong>ParameterReport<\/strong> report to open it in the <strong>Design<\/strong> tab. From the menu, select <strong>Report<\/strong> | <strong>Publish Report Parts<\/strong>. This dialog allows you to choose which components of the report to publish as individual items and to rename them. Select <strong>StateCD<\/strong> and <strong>Tablix1<\/strong>. Click the word <strong>Tablix1<\/strong> and change the name to <strong>Customers<\/strong>, as shown in Figure 16 <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-00a716ae-0c95-4a57-b80d-1dc9c3848f84.png\" alt=\"2122-1-00a716ae-0c95-4a57-b80d-1dc9c3848\" \/><\/p>\n<p class=\"caption\"> \tFigure 16 <\/p>\n<p> \tClick <strong>OK<\/strong> to dismiss the dialog box. Right-click the project name and choose <strong>Deploy<\/strong>. If the deployment is successful, open <strong>Report Manager<\/strong> where you should see a new folder called <strong>Report Parts<\/strong> and one called <strong>FirstProject<\/strong>, as shown in Figure 17. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-ead2e483-d774-4d0e-a5d2-038bcbc565ac.png\" alt=\"2122-1-ead2e483-d774-4d0e-a5d2-038bcbc56\" \/><\/p>\n<p class=\"caption\"> \tFigure 17 <\/p>\n<p> \tThe <strong>FirstProject<\/strong> folder should contain all the reports from the project. The <strong>Report Parts<\/strong> folder should contain those two parts you marked for publishing, as shown in Figure 18. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-bcd47d7f-0447-414e-99f6-8e943b33a8bd.png\" alt=\"2122-1-bcd47d7f-0447-414e-99f6-8e943b33a\" \/><\/p>\n<p class=\"caption\"> \tFigure 18 <\/p>\n<p> \tNow open up the <strong>ChartProject<\/strong>. Double-click the <strong>MyChart<\/strong> report to open it in design view. Open the <strong>Publish Report Parts<\/strong> dialog found in the <strong>Report <\/strong>menu. Select <strong>Chart1<\/strong>, but change the name to <strong>SalesChart<\/strong>. Figure 19 shows how the dialog should look. Click <strong>OK<\/strong>. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-58449046-c158-49bf-b877-6050c4ed5433.png\" alt=\"2122-1-58449046-c158-49bf-b877-6050c4ed5\" \/><\/p>\n<p class=\"caption\"> \tFigure 19 <\/p>\n<p> \tRepeat the process with the <strong>Gauge<\/strong> report. Publish the gauge and name it <strong>SalesGauge<\/strong>. Also repeat the process with the <strong>Map<\/strong> report. Publish the map and name it <strong>SalesMap<\/strong>. Deploy the project. <\/p>\n<h3>Create a Custom Report with Report Builder<\/h3>\n<p> \tReport Builder 3.0 installs on first launch after clicking the <strong>Report Builder<\/strong> link in <strong>Report Manager<\/strong>. After Report Builder 3.0 launches, you have many choices, including three wizards or a blank report. You can also create a new dataset or open an existing report to edit. Figure 20 shows the <strong>Getting Started<\/strong> dialog. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-c7a24d5f-e410-4cfa-aa68-cac737437024.png\" alt=\"2122-1-c7a24d5f-e410-4cfa-aa68-cac737437\" \/><\/p>\n<p class=\"caption\"> \tFigure 20 <\/p>\n<p> \tFor now, select <strong>Blank Report<\/strong>. The Report Builder interface is similar to the one found in SSDT-BI, but ribbons have replaced menus and some windows. You still have the <strong>Report Data<\/strong> window, but the toolbox items are found on the <strong>Insert<\/strong> tab. There is no need for a <strong>Solution Explorer<\/strong> because you can work on only one report at a time. Figure 21 shows how the blank report and the layout look. <\/p>\n<p> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-3fdf2943-ca7d-4fdc-aa35-39ed9bac7ed6.png\" alt=\"2122-1-3fdf2943-ca7d-4fdc-aa35-39ed9bac7\" \/><\/p>\n<p class=\"caption\"> \tFigure 21 <\/p>\n<p> \tOn the left side of the <strong>Insert<\/strong> ribbon, click <strong>Report Parts<\/strong>. This opens a <strong>Report Part Gallery<\/strong> on the right. Click the search button, a magnifying glass, to show all of the available published parts. You can also use the search box to filter the list. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-2bb7e97e-89c4-4cd5-9b31-71a80d8f0111.png\" alt=\"2122-1-2bb7e97e-89c4-4cd5-9b31-71a80d8f0\" \/><\/p>\n<p class=\"caption\"> \tFigure 22 <\/p>\n<p> \tThe person creating the report can drag whichever items they need to see on the report, for example, to build a dashboard. If they have the skills, they can also create data sources and datasets. <\/p>\n<p> \tTo build the first report, drag in the <strong>SalesChart<\/strong> and <strong>SalesGauge<\/strong>. When you do, the data source and datasets are automatically added to the report. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-c1b2d975-3432-4b3d-b092-b1cfcd059c87.png\" alt=\"2122-1-c1b2d975-3432-4b3d-b092-b1cfcd059\" \/><\/p>\n<p class=\"caption\">  Figure 23 <\/p>\n<p> \tOn the <strong>Home<\/strong> ribbon, click <strong>Run<\/strong> to view the report. To publish the report, click the <strong>Save<\/strong> button. The <strong>Save as Report<\/strong> dialog box allows you to publish the report or save the report file locally. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-d974080c-e5f9-4fd9-8352-7b44dddc619e.png\" alt=\"2122-1-d974080c-e5f9-4fd9-8352-7b44dddc6\" \/><\/p>\n<p class=\"caption\"> \tFigure 24 <\/p>\n<p> \tPublish the report by naming it RB1 and clicking Save. Now go back to Report Manager to confirm that the report you created shows up, as shown in Figure 25. You may need to refresh the page to see the report. <\/p>\n<p class=\"illustration\"> \t<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2122-1-20bc73ba-765c-4788-81b7-1ef59996f951.png\" alt=\"2122-1-20bc73ba-765c-4788-81b7-1ef59996f\" \/><\/p>\n<p class=\"caption\"> \tFigure 25 <\/p>\n<p> \tClose Report Builder and launch it again. Select <strong>Blank Report<\/strong>. This time add the <strong>Customers<\/strong> table to the report. Look at the <strong>Parameters<\/strong> section of the <strong>Report Data<\/strong> window. The parameter was automatically added for you. Run the report to see how it looks and then publish it. <\/p>\n<p> \tTo really get the most benefit from Report Builder 3.0, you will have to do some planning and work. Are there several parameter lists that would be beneficial for multiple reports? Are there several visual items that can be filtered by the same parameters so that they can create an interactive dashboard? By working with the business users and understanding the data, you can create a valuable resource for your company. <\/p>\n<h2>Conclusion<\/h2>\n<p> \tI hope this tutorial series has given you some insight into the capabilities of SQL Server Reporting Services. There are many more features for you to discover. Just to name a few, I suggest you look into click-through reports, report caching and report subscriptions. <\/p>\n<p> \tHappy reporting&#8230;! <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Having designed and tested our reports, it&#8217;s time to deploy them to the Report Server, so that our users can access them. Kathi Kellenberger demonstrates how to configure reports for native-mode deployment using Report Manager, and then deploy them from within SSDT-BI. She also explains how to use the Report Builder to provide &#8220;self-service&#8221; reporting to end users, allowing them to build custom reports based on report parts and shared datasets.&hellip;<\/p>\n","protected":false},"author":110218,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[4242,4178,4651,4649,4379,4150,4151],"coauthors":[],"class_list":["post-1932","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-basics","tag-bi","tag-report-builder","tag-report-definition-language","tag-reporting-services","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1932","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110218"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1932"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1932\/revisions"}],"predecessor-version":[{"id":91013,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1932\/revisions\/91013"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1932"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1932"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1932"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1932"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}