{"id":84677,"date":"2019-07-05T14:43:06","date_gmt":"2019-07-05T14:43:06","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=84677"},"modified":"2022-04-24T21:17:43","modified_gmt":"2022-04-24T21:17:43","slug":"reporting-services-basics-understanding-data-sources-and-datasets","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/reporting-services-basics-understanding-data-sources-and-datasets\/","title":{"rendered":"Reporting Services Basics: Understanding Data Sources and Datasets"},"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

    If you’ve been following along with this series, you have learned about the architecture of Reporting Services (SSRS) and how to build a report using the wizard. Most of the time, the wizard is not that useful, although I must admit that I used the wizard for creating Matrix reports in SSRS for quite some time. In this article, you\u2019ll begin to learn the basics of building a report from the ground up without the help of the wizard. There are two components, data sources and datasets, that you must understand to be able to build reports, so this article spends quite some time covering them.<\/p>\n

    Start with a Project<\/h2>\n

    To get started, create a new Report Server Project<\/em> in Visual Studio. In the previous article, you started with the Report Server Project Wizard<\/em>. The Report Server Project<\/em> will create the project shell without any objects. It will be up to you to add all the necessary components.<\/p>\n

    Figure 1 shows the New Project<\/em> dialogue. Be sure to fill in a Name<\/em> and click OK<\/em> to create the project.<\/p>\n

    <\/p>\n

    Figure 1: Create a new project<\/p>\n

    Once you create the project, you\u2019ll see the Solution Explorer,<\/em> shown in Figure 2, that is empty except for some folders.<\/p>\n

    <\/p>\n

    Figure 2: The Solution Explorer<\/p>\n

    Shared Data Sources<\/h2>\n

    A data source is like an address for the data. It contains the connection string. This might include the server name and stored credentials to get to a SQL Server database or be something as simple as the location of a text file. You can use multiple data sources in a report if the data will come from more than one place.<\/p>\n

    You can store data sources in two ways: sharing at the project level or embedding in the individual report. My advice is always to share the data sources, especially if you will have dozens or hundreds of reports pointing to the same database. By creating shared data sources, you or the database administrator (DBA) will have fewer of them to manage once the reports are published in the Web Portal.<\/p>\n

    To get started, right-click the Shared Data Sources folder<\/em> and select Add New Data Source, <\/em>as shown in Figure 3.<\/p>\n

    <\/p>\n

    Figure 3: Add New Data Source<\/p>\n

    This brings up the Shared Data Source Properties<\/em> dialogue. Fill in a Name<\/em> for the data source. I suggest using the database name, but your team may have a specific naming convention to use.<\/p>\n

    Choose the Type<\/em> of database source. It\u2019s SQL Server<\/em> by default and what you\u2019ll use in this example, but you might want to review the many sources possible.<\/p>\n

    If you know your Connection String<\/em>, you can fill it in, but it\u2019s easier to click Edit<\/em> to open the Connection Properties<\/em> dialogue shown in Figure 4. You\u2019ll need to fill in your Server name<\/em>, Authentication<\/em> method, and Connect to a database<\/em>. (If you have problems connecting, review the Connecting to Your SQL Server Instance<\/em> section in this article<\/a>. If you are connecting to an instance in your network, check with your DBA for help.) Click OK<\/em> to save the properties.<\/p>\n

    <\/p>\n

    Figure 4: The Connection Properties<\/p>\n

    Once you save the properties, the General<\/em> page should similar to Figure 5. Click OK<\/em> to save the changes.<\/p>\n

    <\/p>\n

    Figure 5: The Shared Data Source Properties<\/p>\n

    You\u2019ll see the new data source in the Solution Explorer<\/em> window shown in Figure 6.<\/p>\n

    <\/p>\n

    Figure 6: The new data source<\/p>\n

    At the time of this writing, there is a bug that prevents saving the Credentials<\/em> settings when first creating the data source. Open the properties again by double-clicking the data source. (Note that if you right-click and choose Properties<\/em>, you\u2019ll be able to see only the file location.) Figure 7 shows the Credentials<\/em> page and the correct setting. Fill in the credentials that you used in Step 5 if you did not use Windows authentication. After changing the setting, click OK<\/em>.<\/p>\n

    <\/p>\n

    Figure 7: The data source credentials<\/p>\n

    That\u2019s all there is to create the data source. In step 3, I pointed out that there are many different types of data sources, and configuring each type is different. For now, stick with SQL Server to get started with SSRS. View this article<\/a> for more information about each type of data source.<\/p>\n

    Now it\u2019s time to discuss datasets.<\/p>\n

    Shared Datasets<\/h2>\n

    A dataset is the query that runs when you view the report. The type of query will depend on the data source. For example, when working with SQL Server databases, the query will be written in T-SQL, or you also have the option of calling stored procedures. Even though there is a Shared Datasets <\/em>folder, most of the time, the dataset should be embedded in the report and not shared. The reason for embedding datasets is that queries are not reused that often. There are exceptions, for example, parameter lists that are reused in many reports. If a dataset is shared, then it will be published when you deploy the project. You probably won\u2019t want to clutter the Web Portal with every dataset for dozens or hundreds of reports.<\/p>\n

    The report must first exist before you can embed a dataset. Instead of showing you how to create a dataset, first learn how to create a new blank report.<\/p>\n

    New Reports<\/h2>\n

    When creating a new blank report, you must take care not to kick off the Report Wizard. Follow these steps to create a new blank report.<\/p>\n

    Right-click the Reports<\/em> folder and select Add New Item\u2026.<\/em> Be sure NOT to select Add New Report,<\/em> because that launches the Report Wizard. Figure 8 shows you the menu item.<\/p>\n

    <\/p>\n

    Figure 8: Add new item, not add new report<\/p>\n

    You\u2019ll then see the Add New Item<\/em> dialogue shown in Figure 9. Select Report<\/em> and give the report a name. When working on reports for your company, be sure to provide the report with a meaningful name that will make sense to the people who run the report.<\/p>\n

    <\/p>\n

    Figure 9: The Add New Item dialogue<\/p>\n

    Click OK<\/em> to create the report. You should now see it in the Solution<\/em> Explorer, <\/em>as shown in Figure 10.<\/p>\n

    <\/p>\n

    Figure 10: View the new report<\/p>\n

    The new report may be open in Design<\/em> view. If not, double-click it to open it. The report canvas will look like Figure 11.<\/p>\n

    <\/p>\n

    Figure11: The report in Design view<\/p>\n

    Now you have a new report. On the left side of the screen, you should see the Report Data<\/em> window. (If you don\u2019t see it, make sure the report canvas is selected and type CTRL+ALT+D<\/em>.) Figure 12 shows you the Report<\/em> Data<\/em> window. Everything in this window is specific to the report you are editing. You\u2019ll learn about the different folders throughout these articles.<\/p>\n

    <\/p>\n

    Figure 12: The Report Data window<\/p>\n

    Right-click the Data<\/em> Sources<\/em> folder in the Report<\/em> Data<\/em> window and select Add Data Source<\/em>. This data source will point to a shared reference, the data source you created earlier. Give it a Name<\/em> and click Use shared data source reference<\/em>. Select the AdventureWorks2017 data source from the list. (Note that you can give them the same name, but for illustration purposes, I\u2019m naming them differently.) Figure 13 shows the properties. Click OK<\/em> to create the data source.<\/p>\n

    <\/p>\n

    Figure 13: The data source properties<\/p>\n

    Once you create the data source, you\u2019ll see it in the window. Notice that it has a little arrow on the icon that designates that it is pointing to a shared data source, as shown in Figure 14.<\/p>\n

    <\/p>\n

    Figure 14: The new data source<\/p>\n

    You now have everything in place to create an embedded dataset.<\/p>\n

    Embedded Datasets<\/h2>\n

    As mentioned earlier, it makes sense to embed most datasets in the reports instead of sharing them. Shared datasets end up published as reusable Report<\/em> Parts<\/em> for creating ad-hoc reports by advanced users. Most datasets are not needed for this and sharing them will clutter up the folder and make it more difficult for end users to find what they need. In the later article about parameters, you\u2019ll learn to create shared datasets when it makes sense.<\/p>\n

    Make sure that your data source pointing to the shared data source is in place and follow these steps to create a dataset.<\/p>\n

    Fill in a Name<\/em> that describes your query. Select Use a dataset embedded in my report<\/em>. A dropdown box for the data source will appear. Select the data source you just created. At this point, the dialogue box will look like Figure 15.<\/p>\n

    <\/p>\n

    Figure 15: The Datasets Properties so far<\/p>\n

    Make sure that the Query type<\/em> is set to Text<\/em> and paste in this query:<\/p>\n

    SELECT Prod.ProductID, Prod.Name AS ProductName, \r\n\tProd.Color, Prod.StandardCost, Prod.ListPrice, \r\n\tSub.Name AS SubCategory, Cat.Name AS Category\r\nFROM Production.Product AS Prod\r\nJOIN Production.ProductSubcategory AS Sub \r\n   ON Prod.ProductSubcategoryID = Sub.ProductSubcategoryID\r\nJOIN Production.ProductCategory AS Cat \r\n   ON Sub.ProductCategoryID = Cat.ProductCategoryID;<\/pre>\n
    There is also a Query Designer<\/em> you can use to build simple queries, or you can import a query from a text file. I recommend writing the query in SSMS or ADS and pasting it here. The Dataset<\/em> Properties<\/em> should look like Figure 16. Click OK<\/em> to create the dataset.<\/p>\n
    <\/p>\n
    Figure 16: Paste in the query<\/p>\n
    Once you have saved the dataset, you\u2019ll see it along with all the fields in the Report<\/em> Data<\/em> window. Click the arrow next to the dataset to expand the fields, as shown in Figure 17.<\/p>\n
    <\/p>\n
    Figure 17: The new dataset<\/p>\n
    If you have made any syntax errors in the query \u2013 and it\u2019s a good practice to make sure it runs first in SSMS \u2013 the fields will not show up. To troubleshoot, you can double-click to open the properties again and fix the query. You may need to click Refresh<\/em> Fields<\/em> to see the change.<\/p>\n
    Adding Fields to the Report<\/h2>\n
    You\u2019ll learn much more about creating reports throughout this series, but to make sure that what you\u2019ve done so far works follow these steps to create a rudimentary report:<\/p>\n
    Right-click on the report canvas and select Insert Table<\/em> as shown in Figure 18. You can also drag objects from the Toolbox<\/em> window to the canvas to accomplish the same thing.<\/p>\n
    <\/p>\n
    Figure 18: Insert a table<\/p>\n
    You\u2019ll see a small grid with a Header<\/em> and Detail<\/em> row shown in Figure 19.<\/p>\n
    <\/p>\n
    Figure 19: The new table<\/p>\n
    There are three ways to add fields to the table. You can drag them over to the Detail<\/em> row from the dataset in the Report<\/em> Data<\/em> window. You can hover over a cell and select from the little popup menu, as shown in Figure 20. You could also type the field with brackets in the detail row, but the header will not be automatically filled in for you. If you try this last method, note that the fields are case-sensitive.<\/p>\n
    <\/p>\n
    Figure 20: How to add a field to the table<\/p>\n
    Now that you have added a few fields, click Preview<\/em> to view the report so far. My report looks like Figure 21.<\/p>\n
    <\/p>\n
    Figure 21: The report so far<\/p>\n
    Of course, there is much more work to do on this report, but this shows you that your dataset is working.<\/p>\n
    Summary<\/h2>\n
    In this article, you learned how to<\/p>\n
      \n
    • Create an SSRS project<\/li>\n
    • Add a shared data source<\/li>\n
    • Add a report without launching the wizard<\/li>\n
    • Point a report data source to a shared data source<\/li>\n
    • Embed a dataset in the report<\/li>\n
    • Add a table to the report<\/li>\n
    • Add some fields to the table<\/li>\n<\/ul>\n
      I suggest that you refer to this article until you become comfortable with data sources and datasets. The concepts are confusing for many students, so having a resource you can follow will make all the difference.<\/p>\n
      The next article will build on what you know about creating reports, and you\u2019ll get to learn about some of the other components like headers and footers that make up a report.<\/p>\n
       <\/p>\n
       <\/p>\n","protected":false},"excerpt":{"rendered":"
      Data sources and datasets are critical components of an SSRS report, but many people new to SSRS struggle to understand them. In this article, Kathi Kellenberger shows you how to create these components and explains when they should be shared vs. embedded. …<\/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":[95509],"coauthors":[11292],"class_list":["post-84677","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-standardize"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84677","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=84677"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84677\/revisions"}],"predecessor-version":[{"id":84980,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84677\/revisions\/84980"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=84677"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=84677"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=84677"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=84677"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}