{"id":84406,"date":"2019-05-30T15:19:48","date_gmt":"2019-05-30T15:19:48","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=84406"},"modified":"2022-04-24T21:35:37","modified_gmt":"2022-04-24T21:35:37","slug":"reporting-services-basics-creating-your-first-report","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/reporting-services-basics-creating-your-first-report\/","title":{"rendered":"Reporting Services Basics: Creating Your First Report"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. Reporting Services Basics: Overview and Installation<\/a><\/li>\n
  2. Reporting Services Basics: Creating Your First Report<\/a><\/li>\n
  3. Reporting Services Basics: Data Sources and Datasets<\/a><\/li>\n
  4. Reporting Services Basics: Parameters<\/a><\/li>\n
  5. Reporting Services Basics: Adding Groups to Reports<\/a><\/li>\n<\/ol>\n\n

    In the previous article in the series, I explained a bit about the history and architecture of SSRS and helped you get a development environment set up. Now it\u2019s time to start building your first report. The reports in this article are based on the AdventureWorks2017 database, which you should have restored if you followed along with the earlier article.<\/p>\n

    Solutions and Projects<\/h2>\n

    For those of you familiar with working in Visual Studio, you will already understand the concept of solutions and projects. A solution contains one or more project, and the projects may be of all the same or different project types. For example, I was working on a business intelligence project a few years ago and had four different types of projects in my solution, one of which was an SSRS project. Figure 1 shows what my solution looked like.<\/p>\n

    <\/p>\n

    Figure 1: My BI project organization<\/p>\n

    In theory, you could have one solution and one project for all the work you do for years developing reports, but that is not very practical. Typically, you might have a project for one topic area, project, or department. It\u2019s up to you (or your manager) how you organize you work.<\/p>\n

    There are two ways to create an SSRS project, either manually or with a wizard. I will show you the wizard method in this article, which sets up the project as you create a report.<\/p>\n

    Using the Report Wizard<\/h2>\n

    The report wizard is not the best way to create a report, but it is a nice way for beginners to SSRS to get started. Generally, the reports you create through the wizard won\u2019t meet any but the most basic development requirements, but do not discount it entirely. It will allow you to get a report up and running with zero code and zero property setting, and the resulting report often makes a good starting point for more complicated reports. Once you\u2019ve created a report with the Report Wizard, you are free to modify it further as you wish, just as for a report you create from scratch, manually.<\/p>\n

    Your very first report project, MyFirstProject<\/em>, demonstrates how to create a report with grouping levels, including one of the dynamic features, collapsing and expanding sections. The result is not perfect, but it is a good start.<\/p>\n

    To create a report project with the wizard, follow these steps:<\/p>\n

      \n
    1. Search for and launch Visual Studio (SSDT). If this is the first time you have run Visual Studio, you\u2019ll be greeted by this screen. You will be prompted to sign in to the online developer services. For now, just click Not now, maybe later<\/em>.<\/li>\n<\/ol>\n

      <\/p>\n

        \n
      1. You can select which color theme you would like to use. Select one and click Start Visual Studio<\/em>.<\/li>\n<\/ol>\n

        <\/p>\n

          \n
        1. When Visual Studio opens, you\u2019ll see a screen that looks like this:<\/li>\n<\/ol>\n

          <\/p>\n

            \n
          1. To create the project, select File New Project\u2026<\/em><\/li>\n<\/ol>\n

            <\/p>\n

              \n
            1. Look for the Reporting Services<\/em> templates in the Business Intelligence<\/em> section and select Report Server Project Wizard<\/em>. (You may have to look around; when I recently installed SSRS, Reporting Services didn\u2019t end up in that section.)<\/li>\n<\/ol>\n

              <\/p>\n

                \n
              1. Fill in a name for the project and location. Notice that the Solution name<\/em> will automatically be the same as the project name. You can override that if you wish.<\/li>\n<\/ol>\n

                <\/p>\n

                  \n
                1. Click OK,<\/em> which will launch the wizard. Click Next<\/em> on the introduction page.<\/li>\n<\/ol>\n

                  <\/p>\n

                    \n
                  1. On the Select the Data Source<\/em> page, you will set up a new data source. You\u2019ll learn more about data sources and datasets in the next article in this series. Click the Edit<\/em> button.<\/li>\n<\/ol>\n

                    <\/p>\n

                      \n
                    1. This brings up the Connection<\/em> Properties<\/em> window. Enter the SQL Server name, and, if local, you can type in localhost. If you have a named instance, you will need to use this syntax: [computername]\\[instancename].<\/li>\n
                    2. Leave the Authentication<\/em> setting at Windows Authentication<\/em>.<\/li>\n
                    3. Click the dropdown to find the AdventureWorks2017<\/em> database.<\/li>\n<\/ol>\n

                      <\/p>\n

                        \n
                      1. Click OK <\/em>to save the connection properties. You\u2019ll now see the Connection string<\/em> in the dialog.<\/li>\n<\/ol>\n

                        <\/p>\n

                          \n
                        1. Click Next<\/em> to move to the Design the Query<\/em> page. There is a Query Builder<\/em> which you can use, or just write your query in SSMS to make sure it runs as expected. In this case, I\u2019m providing a query for you. Paste this query into the Query string<\/em> window:<\/li>\n<\/ol>\n
                          SELECT Ter.[Group] AS WorldRegion, \r\n    CountryRegionCode, \r\n    Name as Territory, \r\n    SalesOrderID, \r\n    OrderDate, \r\n    TotalDue \r\nFROM Sales.SalesOrderHeader AS SOD\r\nJOIN Sales.SalesTerritory AS Ter \r\n    ON SOD.TerritoryID = Ter.TerritoryID;<\/pre>\n
                          <\/p>\n
                            \n
                          1. Click Next<\/em> to move to the next screen where you can select either a Tabular<\/em> or Matrix<\/em> report. A Matrix<\/em> report is like a pivot table in Excel. For now, select Tabular<\/em>.<\/li>\n<\/ol>\n
                            <\/p>\n
                              \n
                            1. Click Next<\/em> to move to the Design the Table<\/em> screen. I\u2019ll tell you what to do here, but when you are creating a real report, you will need to figure out how the grouping levels, if any, need to be arranged before you start working on the report. Select the fields and move them to the proper areas.<\/li>\n<\/ol>\n
                              <\/p>\n
                               <\/p>\n
                                \n
                              1. Click Next<\/em> to move to the Choose the Table Layout<\/em> page. You have the choice of Stepped<\/em> or Block<\/em>. Click each to see how the report layout will look. For this example, choose Stepped<\/em> and also select Include subtotals <\/em>and Enable drilldown<\/em>.<\/li>\n<\/ol>\n
                                <\/p>\n
                                  \n
                                1. Click Next to move to the Completing the Wizard<\/em> page. Here you should add a Report name <\/em>and click Finish<\/em> to build the report.<\/li>\n<\/ol>\n
                                  <\/p>\n
                                  Viewing the Wizard Report<\/h2>\n
                                  Once the wizard creates the report, you\u2019ll see the definition and the report listed in the Solution Explorer.<\/p>\n
                                  <\/p>\n
                                  To view how the report looks when it\u2019s run, click the Preview<\/em> tab at the top.<\/p>\n
                                  <\/p>\n
                                  When previewing, SSRS will combine the report definition with the actual data. The interesting thing about this report is that you selected Enable drilldown,<\/em> so the sections are collapsed.<\/p>\n
                                  <\/p>\n
                                  Click the first plus sign to expand the section. You\u2019ll see that there is another section that can be expanded to see the details.<\/p>\n
                                  <\/p>\n
                                  Cleaning up the Wizard Report<\/h2>\n
                                  The wizard successfully created a report, but there are some things to clean up. For example, wouldn\u2019t it be nice to format the currency fields? You might also want to add some color to the report.<\/p>\n
                                  Here is a list of items that must be cleaned up before the report is useful:<\/p>\n
                                    \n
                                  • Remove totals and subtotals from Sales Order ID<\/em> fields<\/li>\n
                                  • Format date fields<\/li>\n
                                  • Format currency fields<\/li>\n
                                  • Adjust widths<\/li>\n<\/ul>\n
                                    Selecting the cell you wish to modify is one of the trickiest things about formatting reports. You can select the cell, the contents of the cell, or even a portion of contents of the cell. I\u2019ve had good luck by clicking the edge of the cell when I want to select it for formatting. It might take some practice, but don\u2019t give up!<\/p>\n
                                    You need to remove all the total and subtotals from the Sales Order ID<\/em> column. In addition to the header, the only one you should keep is the one in the Detail<\/em> row, which is at the bottom of the report. You may be able to see that the two to be removed are formulas which start with [SUM<\/code>.<\/p>\n
                                    <\/p>\n
                                    Select each cell and click Delete<\/em> to remove the two formulas.<\/p>\n
                                    <\/p>\n
                                    The next item in the list is to format the dates. All the dates in the AdventureWorks database are the old DateTime data type and has 12:00 am for each time. There is no reason to show these times. The easiest way to format is to right-click on the cell so that the context menu pops up. Select Text Box Properties\u2026<\/em><\/p>\n
                                    <\/p>\n
                                    The Text Box Properties dialog has quite a few options. You\u2019ll learn more about them as you read through the series, but for now select Number Date<\/em>.<\/p>\n
                                    <\/p>\n
                                    You\u2019ll see a list of many date formats. Select one of them that displays only the date and click OK<\/em>.<\/p>\n
                                    <\/p>\n
                                    It\u2019s a good idea to preview the report as you go, so take a look now by clicking the Preview<\/em> tab. You\u2019ll need to also expand the plus signs to see the all the changes.<\/p>\n
                                    <\/p>\n
                                    Back in design view, it\u2019s now time to format the three currency cells. The only way to use the Text Box Property<\/em> dialog is to format each of them separately. I\u2019ll show you a trick that will save you some time, especially if you have several of them to format.<\/p>\n
                                    Right-click on one of the Total Due<\/em> cells and select Text Box Properties\u2026<\/em> Navigate to Number Currency<\/em>. If you just click OK <\/em>at this point, it will format the cell to your local currency, but you might want to change the Decimal Places<\/em> to 0 and select Use 1000 separator <\/em>or make some other changes. You\u2019ll see a sample of the format at the top.<\/p>\n
                                    <\/p>\n
                                    Click OK<\/em> to save the format. In order to save time, you will copy the format to the other currency cells. While the cell you formatted is selected, bring up the Properties<\/em> window by clicking F4<\/em>. Note that this is not the same as the Text Box Properties<\/em>. The cell\u2019s name is shown at the top of the window.<\/p>\n
                                    Scroll down until you find the Format<\/em> property. Select the text and copy to the Clipboard.<\/p>\n
                                    <\/p>\n
                                    Now select the other currency cells. You can select multiple cells by holding down the CTRL<\/em> key. You can also select entire columns if that is helpful. In this case, select the Total<\/em> Due<\/em> column by clicking the column handle.<\/p>\n
                                    <\/p>\n
                                    Open the Properties window again and paste the formula into the Format<\/em> property. Since multiple cells are selected, the name is blank.<\/p>\n
                                    <\/p>\n
                                    Preview the report to view the currency cell formatting.<\/p>\n
                                    <\/p>\n
                                    Switch back to design view. The last thing on the list is to adjust widths. You will probably want to expand the Sales Order ID<\/em> column. Select the column and then drag the right side of it over to expand the cell slightly.<\/p>\n
                                    Changing Fonts and Colors<\/h2>\n
                                    It\u2019s simple to change font properties and background colors, and SSRS gives you three ways to do it. You can use the Text Box Property<\/em> dialog box, the Properties<\/em> window, or menu items at above the design window.<\/p>\n
                                    Select the report name text box at the top of the report. Select a different font name and size.<\/p>\n
                                    <\/p>\n
                                    Select the background icon, highlighted above, and choose a different color. Click OK<\/em>. You can also format entire rows or columns by selecting a handle. Select the row handle for the header row and then format with Italics<\/em>. Format any other items as desired and then preview the report.<\/p>\n
                                    <\/p>\n
                                    Try formatting a single cell with the Text Box Properties<\/em> dialog or the Properties<\/em> window. You may find that the menu items are the easiest when it comes to formatting the fonts and colors.<\/p>\n
                                    Print Layout Mode<\/h2>\n
                                    One of the most frustrating things about previewing the report is that you can\u2019t really tell how the report will look when printed or exported to PDF. To solve this issue, you can switch to Print Layout<\/em> mode while viewing the report in preview.<\/p>\n
                                    <\/p>\n
                                    It\u2019s critical that you view the report this way and even scroll to the next page in order to make sure that the report width fits properly on the page, and that it doesn\u2019t leave strange blank pages between populated pages.<\/p>\n
                                    This report fits perfectly on the page, so to see the problem, switch back to design view. Grab the right edge of the report and pull it several inches to the right, leaving quite a bit of white space.<\/p>\n
                                    <\/p>\n
                                    Go back into preview mode. Make sure that Print<\/em> Layout<\/em> is toggled off so that you can expand the DE<\/em> and Germany<\/em> sections. Switch to Print<\/em> Layout<\/em> and scroll a few pages. You\u2019ll see that every other page is blank! This is due to width of the page even though the right side is not populated with any data.<\/p>\n
                                    Another thing that you may need to do from time to time is to change the print layout. If the report is fairly wide, you can change to landscape or even switch to a different size paper, such as legal, if your company uses that size. Switch back to design view and select Report Report Properties<\/em> from the menu. Here you can switch the Orientation<\/em> between Portrait<\/em> and Landscape<\/em>, change the paper size, and modify the report margins.<\/p>\n
                                    <\/p>\n
                                    One more tool at your disposal is the ruler which you can turn on from Report View Ruler<\/em>. Make sure that your report width plus the Left<\/em> and Right<\/em> margins do not exceed the width of your paper.<\/p>\n
                                    It may take some trial and error, but don\u2019t stop working until you are sure that the report will print properly.<\/p>\n
                                    Launching the Wizard from the Solution Explorer<\/h2>\n
                                    Now that you\u2019ve created a report with the wizard, you should be able to see it in the Solution Explorer<\/em>, typically on the right of the screen.<\/p>\n
                                    <\/p>\n
                                    If you close the report definition, you can open it by double-clicking the name here. Another thing you might want to do is to create another report. If you would like to launch the wizard again, all you have to do is to right-click the Reports<\/em> folder and select Add new report<\/em>.<\/p>\n
                                    <\/p>\n
                                    Instead, if you would like to create a report manually, choose Reports Add New Item Report<\/em>. Creating a new report this way will be covered in the next article.<\/p>\n
                                    What if you close the project and want to open it again later? There are several ways to do that.<\/p>\n
                                    Open Visual Studio\u2026<\/p>\n
                                      \n
                                    • Click the name of the project on the Start Page<\/em> under Recent<\/em><\/li>\n<\/ul>\n
                                      <\/p>\n
                                        \n
                                      • From the menu, select File Open<\/em> and then navigate to the solution on your hard drive.<\/li>\n<\/ul>\n
                                        <\/p>\n
                                          \n
                                        • From the menu, select File Recent Projects and Solutions <\/em>and then select it from the list.<\/li>\n<\/ul>\n
                                          <\/p>\n
                                          You could also navigate using the file explorer and double-click the sln<\/em> file which will also launch Visual Studio, but this is more difficult than the other methods.<\/p>\n
                                          <\/p>\n
                                          Be sure to save your solution once you are done working.<\/p>\n
                                          Conclusion<\/h2>\n
                                          In this article, you created a report using the wizard, learned about formatting and how to make sure that your report fits properly on the page when printed. The Report Wizard is a nice way to get started, but you will probably realize that there are many limitations soon enough and not become dependent upon it.<\/p>\n
                                          In the next article, you\u2019ll learn about data sources, datasets, and create a report from scratch without using the wizard.<\/p>\n","protected":false},"excerpt":{"rendered":"
                                          In this article, Kathi Kellenberger demonstrates how to create a project and report using the SSRS Report Wizard. The wizard is fine for getting started, but you\u2019ll soon learn about its many limitations. She also explains how reports fit in projects and solutions.…<\/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":[],"coauthors":[11292],"class_list":["post-84406","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84406","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=84406"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84406\/revisions"}],"predecessor-version":[{"id":84455,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84406\/revisions\/84455"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=84406"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=84406"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=84406"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=84406"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}