{"id":1799,"date":"2014-04-25T00:00:00","date_gmt":"2014-04-25T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-reporting-services-basics-building-ssrs-reports\/"},"modified":"2021-05-17T18:33:16","modified_gmt":"2021-05-17T18:33:16","slug":"sql-server-reporting-services-basics-building-ssrs-reports","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sql-server-reporting-services-basics-building-ssrs-reports\/","title":{"rendered":"SQL Server Reporting Services Basics: Building SSRS Reports"},"content":{"rendered":"<div class=\"article-content\">\n<h1>SQL Server Reporting Services Basics: Building SSRS Reports<\/h1>\n<p>SQL Server Reporting Services (SSRS) is a server-based reporting platform that allows us to create and manage a wide variety of different types of report, and deliver them in a range of formats. We can create basic reports containing tables and graphs, or more complex data visualizations, using charts, maps and sparklines. Our reports can draw their data from SQL Server databases, but also from other relational database such as Oracle, and other types of multi-dimensional or XML-based data sources such as SQL Server Analysis Services, Teradata, or Parallel Data Warehouse.<\/p>\n<p>We can present our finished reports directly from the Reporting Services website, called <strong>Report Manager<\/strong>, or users can view them directly within their web- or Windows-based applications. End users can run reports on demand or schedule them as subscriptions. We can also install SSRS so that the reports run from SharePoint. This article is the first in a series that will provide in-depth coverage of the basics of report development with SSRS:<\/p>\n<ul>\n<li><strong>Building SSRS Reports<\/strong> (this article) &#8211; covers the basics of SSRS, demonstrates how to build quick, simple reports, and will familiarize you with the basic Report Designer environment<\/li>\n<li><strong><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=ssrsbasics1\">Customizing SSRS Reports<\/a><\/strong> (Part 2) &#8211; covers use of custom and aggregate functions, sub-reporting, the matrix control, drill-downs, and sorting.<\/li>\n<li><strong><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=ssrsbasics1\">SSRS Visual Controls<\/a><\/strong> (Part 3) will take an in-depth look at the SSRS visual controls, notably the chart control.<\/li>\n<li><strong><a href=\"https:\/\/www.simple-talk.com\/sql\/reporting-services\/sql-server-reporting-services-basics-deploying-reports\/?utm_source=simpletalk&amp;utm_medium=weblink&amp;utm_content=ssrsbasics1\">Deploying SSRS Reports<\/a><\/strong> (Part 4) walks you through deploying reports and the Report Builder tool<\/li>\n<\/ul>\n<p>The report development examples in this series will work for SSRS 2008 R2 through 2012. Most of the examples will also work for 2008, and any I&#8217;ll call out any exceptions. If you are still using SQL Server 2005, please refer to <a href=\"https:\/\/www.simple-talk.com\/sql\/reporting-services\/beginning-sql-server-reporting-services-part-4\/\">Steve Joubert&#8217;s original SSRS series<\/a>, upon which the example in this series are based.<\/p>\n<h1>SSRS through the ages<\/h1>\n<p>SSRS has been around for over 10 years, first shipping as an &#8220;add-on&#8221; for SQL Server 2000, and then fully integrated into SQL Server 2005. I first saw it demonstrated at the 2003 PASS Summit, and had the Beta version installed and running within a couple of days of getting back to work. Back in 2003, SSRS caused great excitement as it made available, for the first time, built-in reporting capabilities for SQL Server. If you owned SQL Server, you owned SSRS.<\/p>\n<p>Since its integration into SQL Server 2005, Microsoft has made a number of improvements and added many new features. It had a big overhaul in 2008 and acquired a bunch of new features with 2008 R2 including:<\/p>\n<ul>\n<li>The tablix data region which allows us to convert a report area from a table to a matrix and back again<\/li>\n<li>Enhanced chart, and new gauge, data regions (thanks to the Dundas acquisition)<\/li>\n<li>Complete redesign of the Report Designer including a new Grouping Pane<\/li>\n<li>Formatting of text elements, not just at the textbox level<\/li>\n<li>Performance enhancements<\/li>\n<li>2008 R2: KPIs, maps, sparklines, new functions, published Report Parts, and more<\/li>\n<\/ul>\n<p>Since then, the basic features of the standard SSRS reporting tool haven&#8217;t changed. However, with SSRS 2012, Microsoft added <a href=\"http:\/\/office.microsoft.com\/en-us\/excel-help\/power-view-explore-visualize-and-present-your-data-HA102835634.aspx\">Power View<\/a>, providing a new way to create reports and geared towards business users. Power View is part of SSRS when installed in SharePoint integrated mode or as an add-in for Excel 2013. SQL Server 2014 was recently released, and there were no developer feature enhancements with this release.<\/p>\n<h1>SSRS Architecture<\/h1>\n<p>This series of articles focuses on the development of reports. Keep in mind that SSRS can be deployed to SharePoint, but this series of article only covers the native deployment architecture.<\/p>\n<p>An SSRS deployment must be associated with a SQL Server instance. On the instance will be two databases, by default:<\/p>\n<ul>\n<li><code>ReportServer<\/code> &#8211; contains the report definitions, configuration, history, security of deployed reports and more<\/li>\n<li><code>ReportServerTempdb<\/code> &#8211; much like <code>tempdb<\/code>, it is used as a workspace for building reports and doesn&#8217;t maintain any objects permanently.<\/li>\n<\/ul>\n<p>We will also need a location for the <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms152787.aspx\">Report Server Web Service<\/a>, which can be on the same server as the databases, as in the simple deployment architecture shown in Figure 1, or on a different server. On whichever server we choose, we will have access to a <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms157147.aspx\">Report Manager<\/a> website that allows us to deploy and manage the reports. End users can run reports from Report Manager, create subscriptions, and publish their own reports if they have permission.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-1a32f2e5-7fce-4e3c-8c4d-7249004a69bb.png\" alt=\"1979-1-1a32f2e5-7fce-4e3c-8c4d-7249004a6\" \/><\/p>\n<p class=\"caption\">Figure 1<\/p>\n<p>The end user sends an HTTP request for a report, providing any required parameters. The SSRS server finds the metadata of the report and sends a request for data to the data sources. The data returned by the data sources is merged with the report definition into a report. As the report is generated, it is returned to the client.<\/p>\n<h1>Installing and Configuring Reporting Services<\/h1>\n<p>There are many ways to design a Reporting Services deployment for your department or company, with SharePoint integrated mode gaining popularity. However, since this series is focused on report development, it will cover native mode only, and just enough about installation and configuration to help you get things set up on your development computer.<\/p>\n<p>To follow along with this series of articles, you will need to install the development tool and set up the SSRS services. If the SQL Server media is not available, you can download an <a href=\"http:\/\/technet.microsoft.com\/en-us\/evalcenter\/dn205290.aspx\">evaluation copy of SQL Server 2014<\/a>.<\/p>\n<h2>SSRS installation<\/h2>\n<p>When running the SSRS installation, you will need to install the Database Engine and SQL Server Reporting Services in Native mode. It is easier to install both services at one time, because you can choose to have the installation process configure the SSRS services for you. If SSRS is installed later, or if you select not to configure it at installation, you will have to configure it manually. During installation, be sure to select these features:<\/p>\n<ul>\n<li>Database Engine<\/li>\n<li>SQL Server Reporting Services &#8211; Native (if installing 2008 R2, you&#8217;ll be asked on a subsequent screen to select the mode)<\/li>\n<li>Management Tools &#8211; Basic<\/li>\n<li>Management Tools &#8211; Complete<\/li>\n<li>Business Intelligence Development Tools or SQL Server Data Tools if available with the media you are using, which is dependent on the version<\/li>\n<\/ul>\n<p>If given the choice, select <strong>Install and Configure<\/strong>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-cadd8d34-343a-4635-8145-c6ee2b685dc3.png\" alt=\"1979-1-cadd8d34-343a-4635-8145-c6ee2b685\" \/><\/p>\n<p class=\"caption\">Figure 2<\/p>\n<h2>SSRS Development Tools<\/h2>\n<p>Early versions of SQL Server used <strong>Business Intelligence Development Studio<\/strong> (BIDS) as the SSRS development tool. However, starting with SQL Server 2012, you will use <strong>SQL Server Data Tools &#8211; Business Intelligence<\/strong> (SSDT-BI), which may or may not be on the SQL Server media, depending on the version. Each tool runs as an add-in for Visual Studio. If you do not have Visual Studio in place, the BIDS or SSDT-BI installation will install a Visual Studio shell.<\/p>\n<div class=\"pullout\">\n<h4>Naming confusion?<\/h4>\n<p>Just to add a little confusion, there is also an add-in called <a href=\"http:\/\/msdn.microsoft.com\/en-us\/jj650015\">SQL Server Data Tools for Visual Studio 2012<\/a>. This is for database projects, not the BI tools that will allow you to create SSRS projects. It is <em>not<\/em> the option you want for SSRS development. You will also often see SSDT-BI abbreviated simply as SSDT.<\/p>\n<\/div>\n<p>The following table should help you navigate the options, understand the differences, and find the correct process for installing the development tool for your version of SQL Server.<\/p>\n<p>&nbsp;<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td>\n<p class=\"style1\">SQL Server Version<\/p>\n<\/td>\n<td>\n<p class=\"style1\">Development Tool<\/p>\n<\/td>\n<td>\n<p class=\"style1\">Location of Media<\/p>\n<\/td>\n<td>\n<p class=\"style1\">Documentation<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"style1\">2008 R2<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\">BIDS<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\">SQL Server installation media<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\"><a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/bb522791(v=sql.105).aspx\"> http:\/\/technet.microsoft.com\/en-us\/library\/bb522791(v=sql.105).aspx<\/a>.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"style1\">2012 option 1<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\">SSDT-BI and VS 2010<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\">SQL Server Installation media<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\"><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms143711(v=sql.110).aspx\"> http:\/\/msdn.microsoft.com\/en-us\/library\/ms143711(v=sql.110).aspx<\/a><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"style1\">2012 option 2<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\">SSDT-BI and VS2012<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\">Download here <a href=\"http:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=36843\"> http:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=36843<\/a><\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\"><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms143711(v=sql.110).aspx\"> http:\/\/msdn.microsoft.com\/en-us\/library\/ms143711(v=sql.110).aspx<\/a><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p class=\"style1\">2014<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\">SSDT-BI and VS2012<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\">Download here <a href=\"http:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=36843\"> http:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=36843<\/a><\/p>\n<p class=\"MsoNormal\">\u00a0<\/p>\n<\/td>\n<td>\n<p class=\"MsoNormal\"><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms143711(v=sql.120).aspx\"> http:\/\/msdn.microsoft.com\/en-us\/library\/ms143711(v=sql.120).aspx<\/a><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>SSRS configuration<\/h2>\n<p>If you were unable to let the installation process configure SSRS for you, or are installing SSRS after the initial engine installation, or maybe you decided to configure SSRS on your own for fun, you will need to launch the <strong>Reporting Services Configuration Manager<\/strong>.<\/p>\n<p>As discussed, we need to associate SSRS with a SQL Server instance, where it can create the reporting databases (<code>ReportServer<\/code> and <code>ReportSereverTempdb<\/code>). When prompted, select the instance name and click <strong>Connect<\/strong>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-be17ff04-a36d-4785-a774-8b8d3bcb3526.png\" alt=\"1979-1-be17ff04-a36d-4785-a774-8b8d3bcb3\" \/><\/p>\n<p class=\"caption\">Figure 3<\/p>\n<p>On the next screen, select the <strong>Database<\/strong> page on the left menu and click <strong>Change Database<\/strong>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-e810429d-70ea-4bf4-8a1b-cf529d0f59bd.png\" alt=\"1979-1-e810429d-70ea-4bf4-8a1b-cf529d0f5\" \/><\/p>\n<p class=\"caption\">Figure 4<\/p>\n<p>Select <strong>Create<\/strong><strong> a new report server database<\/strong> and click <strong>Next<\/strong>. Enter the name of the SQL Server instance where you wish to create the reporting databases and click <strong>Next<\/strong>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-187717fb-d0e2-4030-a5dd-2fc99f54a638.png\" alt=\"1979-1-187717fb-d0e2-4030-a5dd-2fc99f54a\" \/><\/p>\n<p class=\"caption\">Figure 5<\/p>\n<p>On the following screen, we configure the report databases. We can choose a different name from the default of <code>ReportServer<\/code>, if desired, but only do so if you have a good reason such as multiple instances of Reporting Services on the same server.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-e3b8554f-eefb-40ff-b2b4-84e70b7bfa85.png\" alt=\"1979-1-e3b8554f-eefb-40ff-b2b4-84e70b7bf\" \/><\/p>\n<p class=\"caption\">Figure 6<\/p>\n<p>Continue clicking though the wizard and complete it. Once done, we need to configure the Report Server Web Service, so click the <strong>Web Service URL<\/strong> link.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-b26d3829-d6ce-4f03-8f23-001e7bba2140.png\" alt=\"1979-1-b26d3829-d6ce-4f03-8f23-001e7bba2\" \/><\/p>\n<p class=\"caption\">Figure 7<\/p>\n<p>Accept the defaults and click <strong>Apply<\/strong>. Finally, we need to configure Report Manager, so click the <strong>Report Manager URL<\/strong> link. Again, accept the defaults and click <strong>Apply<\/strong>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-c6f2eb69-ccc2-4ffa-a23c-4b8d1b1df6b2.png\" alt=\"1979-1-c6f2eb69-ccc2-4ffa-a23c-4b8d1b1df\" \/><\/p>\n<p class=\"caption\">Figure 8<\/p>\n<p>Click <strong>Exit <\/strong>to dismiss the Reporting Services Configuration Manager.<\/p>\n<h1>Creating Basic Reports<\/h1>\n<p>Now that we&#8217;re all set up, it&#8217;s time to start building our first report. We&#8217;re going to build a report based on a <code>ReportingDemo<\/code> database. If you want to follow along, building the example report from scratch then you&#8217;ll need to create the database using the <code>ReportingDemoDatabaseScript.sql<\/code> script or, alternatively, by restoring the <code>ReportingDemo.bak<\/code> file, both of which you will find in the code file for this article (see the <strong>Code Download<\/strong>\u00a0<span style=\"color: #373737; font-family: Arial, sans-serif;\">link at the end of the article<\/span>).<\/p>\n<p>Alternatively, the download bundle also contains a copy of the completed SSRS project, <strong>FirstProject<\/strong>.<\/p>\n<h2>Using the Report Wizard<\/h2>\n<p>There are two ways to create SSRS projects within SSDT-BI. We can build the project manually, or we can get a kick start with the Report Wizard. Within the project we can create one or more reports. For example, we might have one project for all of the reports against a particular database, but that is not a requirement.<\/p>\n<p>With the wizard, we can create only a relatively simple report, with one table or matrix data region containing text only.<\/p>\n<p>Generally, the reports we create through the wizard won&#8217;t meet any but the most basic development requirements, but do not discount it entirely. It will allow us to get a report up and running with zero code and zero property setting, and the resulting report often makes a starting point for more complicated reports. Once we&#8217;ve created a report with the Report Wizard, we are free to modify it further as we wish, just as for a report we create from scratch, manually.<\/p>\n<p>Our very first report project, <strong>FirstProject<\/strong>, demonstrates how to create a report with grouping levels including one of the dynamic features, collapsing and expanding sections. The report is essentially just a list of customers that we&#8217;ll group by state. The end result is not perfect, but it is a good start.<\/p>\n<p>Open SSDT-BI and create a new project. Select the <strong>Report Server Project Wizard<\/strong> type. This will open up the <strong>New Project<\/strong> dialog. Create a new <strong>Reporting Services<\/strong> project called <strong>FirstProject<\/strong>. Click <strong>OK<\/strong> to start up the new report wizard. We can also kick off the wizard from within an existing project by right-clicking the <strong>Reports<\/strong> folder in <strong>Solution Explorer<\/strong> and selecting <strong>Add New Report<\/strong>.<\/p>\n<p>The first dialog of note is <strong>Select the Data Source<\/strong>. Since this is our first data source, our only option is to create a new one.<\/p>\n<div class=\"pullout\">\n<h4>Embedded versus Shared Data Sources<\/h4>\n<p>For the sake of this example, we will just create a new, embedded data source, which will be available only to the report in which it is embedded. However, a shared database source is available to all reports within a project, and once deployed, to any deployed report, from any project. If an existing shared data source exists, we should use it, and we have the option on this screen to make the current data source a shared data source. We&#8217;re going to set up a shared data source later in this article.<\/p>\n<\/div>\n<p>Select the <strong>New data source<\/strong> radio button and give the data source a name, usually referring to the database name, so in this case <code>ReportingDemo<\/code>. Leave the data source type as the default (<strong>Microsoft SQL Server<\/strong>).<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-b12c9df6-bd49-4cc2-8fd7-bac30ca80b35.png\" alt=\"1979-1-b12c9df6-bd49-4cc2-8fd7-bac30ca80\" \/><\/p>\n<p class=\"caption\">Figure 9<\/p>\n<p>Click the <strong>Edit<\/strong> button to bring up the <strong>Connection Properties<\/strong> dialog. Enter the name of the SQL Server instance hosting the data source, in this case the <code>ReportingDemo<\/code> database. The default option is to log on using Windows Authentication. If you are using SQL Server Authentication, choose that setting, and enter the username and password. Finally, select <code>ReportingDemo<\/code> and make sure you test the connection before you click <strong>OK<\/strong>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-9696c484-79fa-4339-b195-0e65f0ebe1be.png\" alt=\"1979-1-9696c484-79fa-4339-b195-0e65f0ebe\" \/><\/p>\n<p class=\"caption\">Figure 10<\/p>\n<p>Next up is the <strong>Design the Query<\/strong> screen. We can use the Query Builder by clicking the button at the top left but, in this case, simply enter <code>SELECT * FROM Customer;<\/code> into the Query string text box and click <strong>Next<\/strong>.<\/p>\n<p>On the <strong>Select the Report Type<\/strong> dialog, we can choose between a tabular or matrix report. A tabular report is a traditional grid with column headings and rows of data, and it might contain grouping sections at the row level. A matrix report is like a pivot table. It can have column headings that expand across the top of the report. It can have grouping sections at the row and column levels. We&#8217;re going to start with a simple tabular report so choose <strong>Tabular<\/strong> and hit <strong>Next<\/strong>.<\/p>\n<p>This brings up the <strong>Design the Table<\/strong> screen, where we specify what customer data we wish to include in our report, and how we wish to group it. Essentially, we need to specify how we will use each field (column) in the report. We might display some fields at the top of each page, use others for grouping, and others will form the detail level of the report.<\/p>\n<p>In this case, we simply want to group the customer data by state, so select <code>State<\/code> in the <strong>Available <\/strong><strong>fields<\/strong> box and click the <strong>Group<\/strong> button. If you group by more than one field, then make sure the fields are ordered (use the Up and Down arrows) to reflect the grouping level hierarchy you wish to see in the report. For example, State would be higher than City.<\/p>\n<p>Add the other fields to the <strong>Details<\/strong> box.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-23571361-c394-4afd-987f-844412dda522.png\" alt=\"1979-1-23571361-c394-4afd-987f-844412dda\" \/><\/p>\n<p class=\"caption\">Figure 11<\/p>\n<p>On the <strong>Choose the Table Layout<\/strong> page, we have the choice of <strong>Stepped<\/strong> or <strong>Block<\/strong>. I don&#8217;t see a lot of difference between these layouts. After some experimentation, I found that the Stepped report had a row dedicated to the group label and that row was formatted with a background color. The <strong>Block<\/strong> report shows the group label on the first row of the detail and no special background. With the Block report, we do not have the option for drilldowns.<\/p>\n<p>Select the <strong>Stepped<\/strong> option and check the <strong>Enable Drilldown<\/strong> checkbox, which will allow us to collapse and expand the data by state (the grouping column)<\/p>\n<p>On the next screen, choose a style for your report (I chose Ocean).<\/p>\n<p>The <strong>Choose the Deployment Location<\/strong> screen allows us to specify the Report Server to which we wish to deploy the reports in the project. For now, we will be working within SSDT-BI and not deploying the reports to the server, so we can just accept the defaults. When we are ready to deploy the reports, we can revisit these settings by right-clicking the project name and selecting <strong>Properties<\/strong>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-37caa1d4-43a5-43f0-8d41-4957fd6feb85.png\" alt=\"1979-1-37caa1d4-43a5-43f0-8d41-4957fd6fe\" \/><\/p>\n<p class=\"caption\">Figure 12<\/p>\n<p>This brings up the final screen, <strong>Completing<\/strong><strong> the Wizard<\/strong>, which simply summarizes our chosen report options, and lets us name the report and preview it. Name the report <strong>ReportWZ<\/strong> and click <strong>Finish<\/strong> to end the wizard.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-87c5051f-28f3-4bbf-9402-a2ddf6b53188.png\" alt=\"1979-1-87c5051f-28f3-4bbf-9402-a2ddf6b53\" \/><\/p>\n<p class=\"caption\">Figure 13<\/p>\n<p>After the wizard completes, we will see the report in the <strong>Design<\/strong> tab of the standard Report Designer.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-f1015700-5271-49ae-b96f-6f8665b288a0.png\" alt=\"1979-1-f1015700-5271-49ae-b96f-6f8665b28\" \/><\/p>\n<p class=\"caption\">Figure 14<\/p>\n<p>Click on the <strong>Preview<\/strong> tab to view the report. The <strong>Preview<\/strong> tab allows us to run the report from SSDT-BI, without having to publish it first to the Report Server. If the report takes parameters, the Preview tab will ask us to fill them out before it runs the report. Since this report doesn&#8217;t take any arguments, it will display immediately.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-f9ec9b91-5b4c-4df5-9ff2-d17a390e2097.png\" alt=\"1979-1-f9ec9b91-5b4c-4df5-9ff2-d17a390e2\" \/><\/p>\n<p class=\"caption\">Figure 15<\/p>\n<p>Use the +\/- symbols next to the <strong>State<\/strong> abbreviations to expand and collapse the grouping of the report.<\/p>\n<h2>Manual report creation<\/h2>\n<p>Whilst the <strong>FirstReportWZ<\/strong> report is simplistic, it does demonstrate how quickly we can generate reports using the Wizard, which is often useful for prototyping.<\/p>\n<p>In this section, we are going to create another simple, but more realistic, report from scratch. It will demonstrate how to create and use a shared data source object, stored procedures, and how to format the report, set report properties and use report parameters.<\/p>\n<h3>Create a shared data source<\/h3>\n<p>A shared data source is a data source that is common to, and can be used by, all of the reports in the project. Once the shared data source is published to the report server, any published reports can use it.<\/p>\n<p>In the previous section, we created a <code>Report<\/code><code>ing<\/code><code>Demo<\/code> data source that is embedded in the <code>ReportWZ<\/code> report, and so is available only to that report. However, generally, it is a bad practice to use embedded data sources. Let&#8217;s say we have 20 reports that access the <code>ReportingDemo<\/code> database, but a few months later some aspect of the connection information changes (such as the login credentials, or server name). We&#8217;ll have to open all twenty reports and edit the connection information appropriately. It is much better to define a single shared data source that all reports that need the <code>ReportingDemo<\/code> database can use.<\/p>\n<p>Likewise, let&#8217;s say that our organization has deployed three SSRS sites: <strong>Development<\/strong>, <strong>Test<\/strong> and <strong>Production<\/strong>. If we embed the connection information in the report, we will have to change the data source properties each time we publish the report to Development, Test or Production. If we use a shared data source, the data source will be configured appropriately on each of three sites. We can configure a data source on each site with the same name but pointing to the appropriate servers. We can simply publish the reports to each site, and the reports will automatically use the connection information associated with that environment.<\/p>\n<p>We can find in SSDT-BI&#8217;s Solution Explorer all the reports and other project-level objects. To add a new shared data source to the project, right-click on the <strong>Shared Data Source<\/strong><strong>s<\/strong> folder and select <strong>Add New Data Source<\/strong> and then simply create a <code>ReportingDemo<\/code> data source exactly as described in the previous section. It&#8217;s not possible to edit the existing embedded data source to make it a shared one. We have to define it as a shared data source upon creation.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-e2b9dc40-f552-45ee-972e-a8e3a84a47a6.png\" alt=\"1979-1-e2b9dc40-f552-45ee-972e-a8e3a84a4\" \/><\/p>\n<p class=\"caption\">Figure 16<\/p>\n<h3>Add a new report<\/h3>\n<p>We&#8217;re now going to create another report to return customer information. In the previous example, we used a simple <code>SELECT *<\/code> query to define the data set for the report. Here, we&#8217;ll use a stored procedure. In this first example, we&#8217;ll use a basic stored procedure with no parameters, but later we&#8217;ll refine it to add parameters, and allow end users to filter the report data by parameter value (e.g. to view customers only from a particular state).<\/p>\n<p>When creating a new report manually, we must always follow this sequence of steps before we can begin to add data-connected objects:<\/p>\n<ol>\n<li>Add the report<\/li>\n<li>Create a data source in the report, preferably pointing to a shared data source<\/li>\n<li>Create a dataset that points to the data source and contains your query<\/li>\n<\/ol>\n<p>First we add the report to the project. From the menu select <strong>Project<\/strong> | <strong>Add New Item<\/strong> (or right-click on <code>FirstProject<\/code> in solution explorer). Select <strong>Report<\/strong>, name it <code>FirstR<\/code><code>eportMan.rdl<\/code> and click <strong>Add<\/strong>. The new report will open up in <strong>Report Designer<\/strong> tool, at the <strong>Design<\/strong> tab.<\/p>\n<p>Second, we define the database for the <code>FirstReportMan<\/code> report. On the left, you will see the <strong>Report Data<\/strong> window, used to manage the data sources, datasets and parameters of our report. At the top of the Report Data window, choose <strong>New<\/strong> | <strong>Data Source&#8230;<\/strong> from the dropdown list, which will open the <strong>Data Source Properties<\/strong> window, where we can either create a new embedded data source or point to a shared data source. We&#8217;ll change the name of the data source to <code>ReportingDemo<\/code> and select the <strong>Use shared data source reference<\/strong> option, selecting the <code>ReportingDemo<\/code> shared data source from the dropdown list.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-9790be4d-41e9-4249-9b20-aee197f7c9a1.png\" alt=\"1979-1-9790be4d-41e9-4249-9b20-aee197f7c\" \/><\/p>\n<p class=\"caption\">Figure 17<\/p>\n<p>Third, we create the <strong>Dataset<\/strong>. While the data source is the connection string, the dataset is the query definition. In this example, our dataset is a very simple, parameter-less stored procedure called <code>spr_CustomerSelectAll<\/code> and all it does is select all of the columns from the <code>Customer<\/code> table. The <code>ReportingDemoDatabaseScript.sql<\/code> script, in the code download, includes the code to create the stored procedure.<\/p>\n<p>From the <strong>Report Data<\/strong> menu, choose <strong>New<\/strong> | <strong>Data Set&#8230;<\/strong> to open the <strong>Dataset Properties<\/strong> dialog. We can create a shared dataset or a dataset embedded in the report. Most of the time, the dataset will be embedded in the report. There are some exceptions to this, for example, when we have a parameter list we use in multiple reports. Name the dataset <strong>Customers<\/strong> and choose to embed the dataset in the report. Select the <strong>ReportinDemo<\/strong> data source from the dropdown. Select <strong>StoredProcedure<\/strong> as the command type, and choose <code>spr_CustomerSelectAll<\/code> in the stored procedure list.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-c512cf27-e14f-47a2-9964-6e9f72ee4e33.png\" alt=\"1979-1-c512cf27-e14f-47a2-9964-6e9f72ee4\" \/><\/p>\n<p class=\"caption\">Figure 18<\/p>\n<p>We can repeat these steps to add multiple datasets to the report, but for now we only need the one.<\/p>\n<h3>Designing the report<\/h3>\n<p>As we&#8217;ve noted previously, at the top of the Report Designer window are two tabs: <strong>Design<\/strong> and <strong>Preview<\/strong>. The <strong>Design<\/strong> tab is the physical Report Designer where we design the report i.e. set up the data presentation of the report itself, plus any header and footer for the report pages.<\/p>\n<h4>Design the Report table<\/h4>\n<p>Let&#8217;s start by designing the basic report table. Click to Design tab of our <code>FirstReportMan<\/code> report. We&#8217;re going to build a tabular report, so we start by dragging a <strong>Table<\/strong> control from the <strong>Toolbox<\/strong> window, onto the body of the report. By default, the table shows a header row at the top, and a data (or detail) row at the bottom.<\/p>\n<p>In the Report Data window, expand the <strong>Customers<\/strong> dataset, select <strong>FirstName<\/strong> and drag it to the first cell of the detail row of the table, then drag <strong>LastName<\/strong> into the second column, and <strong>CustomerStatus<\/strong> into the third. When we drag each column into the data row of the table, SSRS make a guess as to what to call the row in the corresponding header. We can expand and shrink the size of the columns by highlighting and dragging.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-5abe8bf3-7310-4850-a425-bd0479dbad11.png\" alt=\"1979-1-5abe8bf3-7310-4850-a425-bd0479dba\" \/><\/p>\n<p class=\"caption\">Figure 19<\/p>\n<p>We can add additional columns to the table by right-clicking on one of the columns and selecting one of the two <strong>Insert Column<\/strong> options. We&#8217;ll add the <code>DateOfBirth<\/code> column to the report, so add a new column to the right of <code>CustomerStatus<\/code>, and then drag the <code>DateOfBirth<\/code> field from the dataset to the detail row of the new column. Alternatively, we can hover over the cell to see a popup list of fields from the dataset, and select <code>D<\/code><code>ateOfBirth<\/code><\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-4f3bb791-8fec-4f4f-9c82-3ec8c1d545f0.png\" alt=\"1979-1-4f3bb791-8fec-4f4f-9c82-3ec8c1d54\" \/><\/p>\n<p><strong>Figure 20<\/strong><\/p>\n<p>At this stage, we can click on the Preview tab to view the report.<\/p>\n<h4>Format the table header<\/h4>\n<p>Now we have a basic, very plain, tabular report, so let&#8217;s add some formatting to make it look better. We have lots of options such as adding background colors, changing the font or text color, adding italics or bolding, and more. In this example, we will change the background color of the header row.<\/p>\n<p>To format all the cells in the header in the same way, click one cell to make the table handles appear. Then select the entire row by selecting the handle:<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-f180ac30-0d7a-4e1d-bab4-ecabe6eae464.png\" alt=\"1979-1-f180ac30-0d7a-4e1d-bab4-ecabe6eae\" \/><\/p>\n<p class=\"caption\">Figure 21<\/p>\n<p>There are two ways to modify the properties. The first is to open the Properties window (F4), where we can set background color, font, and so on. The other method is to use the formatting toolbar. Whichever way you choose, set the background color for the headers to your preferred color and choose the <strong>bold<\/strong> font style.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-f4195573-78b5-4168-963f-5f07f2a7282e.png\" alt=\"1979-1-f4195573-78b5-4168-963f-5f07f2a72\" \/><\/p>\n<p class=\"caption\">Figure 22<\/p>\n<p>Having formatted the header row, we can once again click on the Preview tab to view the report.<\/p>\n<h4>Add a Page header<\/h4>\n<p>We&#8217;ve worked on the table header, but the report page itself can also have a header and footer. Report headers and footers appear on every page of the report. To add one, move to Design mode and select <strong>Report<\/strong> | <strong>Add Page Header<\/strong> from the menu. A new band appears on the report above the body. From the toolbox, drag a text box onto the header, click on it and enter your header, &#8220;My First Report&#8221;. Select the textbox and modify the font and text style, as desired. Again, we can check our handiwork in Preview mode.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-09b8ead6-a0f8-43ef-a946-6f070d70f876.png\" alt=\"1979-1-09b8ead6-a0f8-43ef-a946-6f070d70f\" \/><\/p>\n<p class=\"caption\">Figure 23<\/p>\n<p>Starting with the 2008 release, as well as applying formatting to a textbox, we can also apply multiple formats to the text within a textbox. Clicking the edge of the textbox selects the textbox. Clicking inside the textbox selects the text. To try this, go back to the design view and select just part of the text in the &#8220;My First Report&#8221; textbox and apply different formatting, such as a different font. Now there are two formats within one textbox.<\/p>\n<h4>Field Formatting<\/h4>\n<p>We can apply formatting to each detail field in the report table. Let&#8217;s start by beautifying that ugly <code>DateOf<\/code><code>Birth<\/code> field. The time information is superfluous, so let&#8217;s format the date properly. Right-click on the cell that contains the date of birth, choose <strong>Text Box Properties<\/strong>. Next, navigate <strong>Number<\/strong> | <strong>Date<\/strong> and then set the preferred format (I used <strong>yyyy<\/strong><strong>-mm-<\/strong><strong>dd<\/strong>).<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-7f3787af-5ca3-4e3a-b690-c33052f34de3.png\" alt=\"1979-1-7f3787af-5ca3-4e3a-b690-c33052f34\" \/><\/p>\n<p class=\"caption\">Figure 24<\/p>\n<h4>Setting report level properties<\/h4>\n<p>When on the Design tab of the Report Designer a top menu item, <strong>Report<\/strong>, activates, from where we can:<\/p>\n<ul>\n<li><strong>Turn on and off the header and footer<\/strong> sections for the report pages<\/li>\n<li>Open the <strong>Report Properties<\/strong> dialog and set various report properties, such as the print format<\/li>\n<li><strong>Publish report parts<\/strong> &#8211; starting with 2008 R2, you can publish individual part of the report that can be used to build dashboards (we won&#8217;t cover this till later in the series)<\/li>\n<li><strong>View the grouping section and ruler<\/strong>. For example, if the ruler is not visible, turn it on from <strong>Report<\/strong> | <strong>View<\/strong> | <strong>Ruler<\/strong>.<\/li>\n<\/ul>\n<p>Navigate <strong>Report<\/strong> | <strong>Report Properties<\/strong> to open the Report Properties dialog. Some of the report properties are also available in the Properties (F4) window as well, but you may find that working in the Report Properties dialog is more convenient.<\/p>\n<p>Setting up the report print settings is not intuitive, compared to other products. In the report Design tab you see your report with a ruler across the top. Since I am in the US, my ruler is set up in inches by default. A standard sheet of paper is 8.5 x 11 inches. The default setting in SSRS is to have a 1-inch (or 2 cm) margin all the way around the content area. So, at 8.5 inches wide with a 1-inch margin on the left and another on the right, we have 6.5 inches for content. If the report page is wider than 6.5 inches, it will spill over onto a second sheet of paper when printed. If there is just blank space in the extra area, you will get alternating blank sheets of paper when printed.<\/p>\n<p>On the <strong>Page Setup<\/strong> tab of the Report Properties dialog, we can change the report to Landscape if we wish, switch to centimeters, modify the paper size or the margins. To avoid spill-over when printing, make sure that the width of the report plus the right margin plus the left margin is less than the width of the paper size.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-1cf07fc4-cfb2-4a37-bc5c-d0d88ee3c415.png\" alt=\"1979-1-1cf07fc4-cfb2-4a37-bc5c-d0d88ee3c\" \/><\/p>\n<p class=\"caption\">Figure 25<\/p>\n<h1>Adding parameters to a report<\/h1>\n<p>There are two ways to add parameters to our reports. The first is simply to define parameters in the queries in the dataset, and then these will automatically be available as report parameters. The second way is to manually add report parameters through the <strong>Parameters<\/strong> section of the Report Data window.<\/p>\n<p>We can use parameters to filter the data at the source, or for other reasons like giving the end-user control over one of the properties. We could also have a parameter that is used for both functions.<\/p>\n<h2>Parameters created by the dataset query<\/h2>\n<p>First, we will set up parameters using a dataset query. You can either continue working with the same report or create a new one that is a copy of the original.<\/p>\n<p>To create a copy, right-click on <strong>FirstReportMan<\/strong> in the Solution Explorer and select <strong>C<\/strong><strong>opy<\/strong><strong>. <\/strong>Then right-click on the project name and select <strong>Paste<\/strong>. Rename the new report <strong>ParameterReport<\/strong>. Double-click the new report in Solution Explorer to open it in the designer. In the Report Data window, navigate to the <strong>Customers<\/strong> dataset and double-click it to bring up the properties. The Dataset dialog box opens and allows us to edit the query for the dataset. Change the stored procedure from <code>spr_CustomerSelectAll<\/code> to <code>spr_CustomerSelectByState<\/code>. A parameter called <code>@<\/code><code>StateCD<\/code> should appear in the <strong>Parameters<\/strong> folder. This parameter allows us to filter the data from the <code>Customer<\/code> table by <code>State<\/code>. Again, the <code>ReportingDemoDatabaseScript.sql<\/code> script in the code download includes the creation script for <code>spr_CustomerSelectByState<\/code>.<\/p>\n<p>Switch to the Preview tab to view the report. Instead of the report just running and displaying all the data from the data source, there should be a place for us to enter a value for the <code>@<\/code><code>StateCD<\/code> parameter. Enter &#8220;CA&#8221; and click the <strong>View Report<\/strong> button to see only Californian customers.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-3e2a7621-f0e8-41ed-a0df-94930b37b212.png\" alt=\"1979-1-3e2a7621-f0e8-41ed-a0df-94930b37b\" \/><\/p>\n<p class=\"caption\">Figure 26<\/p>\n<p>To edit existing parameters, or to add new ones, switch to the Design tab of the Report Designer and expand the <strong>Parameters<\/strong> folder. Double-click the <code>StateCD<\/code> parameter to bring up the properties. First, we are going to make the prompt a little more friendly. On the <strong>General<\/strong> page, change the <strong>Prompt<\/strong> value to <strong>State<\/strong>.<\/p>\n<p>We can also provide a list of possible values for the parameter. Select the <strong>Available Values<\/strong> page and from here we can provide either a hard-coded list, or connect the parameter to a dataset. The second method is the generally the best so that we can avoid the need to maintain the list manually.<\/p>\n<p>Close the parameter properties. To connect a parameter to a dataset, we first need to create a new dataset. Using the skills you have learned so far, add a dataset to the report called <strong>StateList<\/strong> based on this query: <code>SELECT DISTINCT State FROM Customer ORDER BY State<\/code><code>;<\/code><em>.<\/em><\/p>\n<p>Open the parameter properties once again and select <strong>Available Values<\/strong>. Choose <strong>Get values from a query.<\/strong> Under <strong>Dataset<\/strong>, select <strong>StateList<\/strong>. For the <strong>Value<\/strong> and <strong>Label<\/strong> fields, select <strong>State<\/strong>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-2abb2eea-5417-45fc-8b5a-66e5e5b31d04.png\" alt=\"1979-1-2abb2eea-5417-45fc-8b5a-66e5e5b31\" \/><\/p>\n<p class=\"caption\">Figure 27<\/p>\n<p>In this case, the <strong>Value<\/strong> and <strong>Label<\/strong> fields are the same. The <strong>Value<\/strong> field is the field that the database needs for the query. The <strong>Label<\/strong> field is the field that the end user should see. For example, if we have a list that has an ID and a description, the ID would be the <strong>Value<\/strong> field while the description would be the <strong>L<\/strong><strong>abel<\/strong> field.<\/p>\n<h2>Manually created parameters<\/h2>\n<div class=\"pullout\">\n<h4>Tip: Shared data sources, embedded datasets<\/h4>\n<p>Generally, data sources are best shared and datasets are best embedded. However, queries to populate parameter lists are good examples of datasets you may want to share instead, since they can often be used for several reports.<\/p>\n<\/div>\n<p>Now let&#8217;s add a parameter that is not defined in a query and, therefore, must be created manually. In the Report Data window, click <strong>Add<\/strong> | <strong>New Parameter<\/strong>. Give the new parameter the name <code>ReportTitle<\/code>, and enter &#8220;Report Title&#8221; for the <strong>Prompt<\/strong>. Its data type should be <strong>Text<\/strong>. Click <strong>OK<\/strong>.<\/p>\n<p>Back on the Design tab, right-click the report header textbox and choose the <strong>Expression<\/strong> option. Remove the original &#8220;My First Report&#8221; text and select <strong>Parameters<\/strong> under <strong>Categories<\/strong>. Double-click on the <code>ReportTitle<\/code> parameter. This text box will now display the value we pass to the <code>Report<\/code><code>Title<\/code> parameter. Click <strong>OK<\/strong>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-1-720587d0-ea9a-4c64-a205-725c627257ba.png\" alt=\"1979-1-720587d0-ea9a-4c64-a205-725c62725\" \/><\/p>\n<p class=\"caption\">Figure 28<\/p>\n<p>Switch to the Preview tab and try it out.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1979-RP-54cfd9bf-ee38-4f27-844e-5652764a58c6.png\" alt=\"1979-RP-54cfd9bf-ee38-4f27-844e-5652764a\" \/><\/p>\n<p class=\"caption\">Figure 29<\/p>\n<h1>Wrap up<\/h1>\n<p>Part 1 covered everything we need to build a basic SSRS report. In the next articles, we&#8217;ll get into more advanced options such as grouping, drill-downs, sorting and custom functions. Stay tuned.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server Reporting Services is one of the most popular components of SQL Server, but it has always been surprisingly difficult to get from one place  all the basic facts you need to get up and running from scratch, to the point of producing reports. Here Kathi Kellenberger quickly describes the basics of SSRS before showing how to build quick, simple reports.&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,4379,4150,4151],"coauthors":[11292],"class_list":["post-1799","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-basics","tag-reporting-services","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1799","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=1799"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1799\/revisions"}],"predecessor-version":[{"id":91015,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1799\/revisions\/91015"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1799"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1799"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1799"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1799"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}