Tutorial scenario

In this exercise, you will step into the shoes of an IT developer working at a fictitious bike retailer called Adventure Works. You have been tasked with creating a report for the Internet sales department that will provide information on year-over-year sales trends. After developing the report, you will need to deploy it to the company's existing standalone report server, where it can be accessed by users in the sales department.

Creating a Reporting Services project

Since we are using SQL Server Data Tools (instead of Report Builder) for this exercise, the first step is to create a Reporting Services project:

  1. Open SQL Server Data Tools (SSDT).
  2. From the menu bar across the top, navigate to File | New | Project.
  3. In the New Project window, navigate to Business Intelligence | Reporting Services | Report Server Project.
  4. Enter a name and location for the project, and click on OK to create and open the project.
    Creating a Reporting Services project

    New Project dialog box for creating a new Report Server Project

Creating a report object

After creating the project, you are ready to begin building your Reporting Services report as follows:

  1. If necessary, open the Report Server Project created in the previous section.
  2. In the Solution Explorer window, right-click on the Shared Data Sources folder, choose Add, and then select New Item… to bring up the Add New Item window as shown in the following screenshot.
  3. Select Report.
  4. Name the report Sales Summary, and click on Add to finish creating the report item, then add it to the Report Server Project.
    Creating a report object

    Add New Item dialog box for adding a new report to the Report Server Project

Creating a shared data source

Next, you need to create a data source that contains the connection details to the source system that contains the Internet sales data on which you need to report. As you learned in Chapter 2, SSRS – Standard Reporting, there are two types of data sources: shared data sources and embedded data sources. For this exercise, you will create a shared data source pointing to the Adventure Works Sample Data Warehouse database, as follows:

  1. In the Solution Explorer window, right-click on the Shared Data Sources folder, and choose Add New Data Source.
  2. Name the data source DW_AdventureWorks, and confirm that Microsoft SQL Server is selected as the type of data source.
  3. Click on the Edit button to bring up the Connection Properties dialog box shown in the following screenshot.
  4. For the Server name property, enter the name of the server or SQL Server instance that hosts the source database.
  5. In the Connect to a database section, select the source database from the drop-down list.
  6. Click on the Test Connection button to make sure everything has been entered correctly.
  7. Click on OK to save the changes and return to the Shared Data Source Properties window.
  8. Select the Credentials page on the left to review the credentials used for this data source. Make sure Use Windows Authentication is selected and click on OK to create the shared data source. Using Windows authentication will ensure that only those users who have been granted access to the source database will have access to the data through this data source:
    Creating a shared data source

    Connection Properties dialog box

Adding reference to shared data source

At this point, the Sales Summary report and the DW_AdventureWorks shared data source are still two completely separate objects. In order to actually use the shared data source in the report, you need to create a data source reference in the report and point it to the shared data source in our project.

  1. Open the Sales Summary report.
  2. In the Report Data window, shown in the following screenshot, right-click on the Data Sources folder and select Add Data Source….
    Adding reference to shared data source

    Report Data window in SQL Server Development Tools environment

  3. In the General tab of the Data Source Properties window, as shown in the following screenshot, give the data source a name (for example, sds_DW_AdventureWorks), and select Use shared data source reference, as shown in the following screenshot.
  4. Next, choose the shared data source created in the previous task from the drop-down menu.
  5. Click on the Credentials tab and notice that all options are grayed out. This is because the credentials for shared data sources are controlled at the project level.
  6. Click on OK to add the shared data source reference to the report.
    Adding reference to shared data source

    Data Source Properties dialog box

Creating a dataset

The next step is to create a dataset. As you can recall from the previous chapter, the dataset is the component that holds the data pulled from source system. Follow these steps to create an embedded dataset to hold the Sales Summary data:

  1. In the Report Data window, right-click on the Datasets folder, and select Add Dataset….
    Creating a dataset

    Report Data window in SQL Server Development Tools environment

  2. In the Query tab of the Dataset Properties window, give the dataset a name (for example, ds_SalesSummary), and select the Use a dataset embedded in my report option, which allows us to specify the dataset via a query or stored procedure.
  3. In the Data source section, select the data source reference (for example, sds_DW_AdventureWorks) from the drop-down list of available data sources in the report.
  4. In the Query textbox, enter the following text, and click on OK to finish creating the embedded dataset:
    SELECT    dd_ord.CalendarYear
         ,dd_ord.MonthNumberOfYear
         ,dd_ord.EnglishMonthName AS [Month]
         ,SUM(fis.SalesAmount) AS [TotalSalesAmount]
FROM     dbo.FactInternetSales fis
         INNER JOIN dbo.DimDate dd_ord ON dd_ord.DateKey = fis.OrderDateKey
GROUP BY dd_ord.CalendarYear
        ,dd_ord.MonthNumberOfYear
        ,dd_ord.EnglishMonthName
    Creating a dataset

    Dataset Properties dialog box

Adding a report item

Now that the report contains a data source and a dataset, you are ready to create a report item to display the data in a meaningful way on the report. In this exercise, you will create a line chart to display year-over-year sales trends.

  1. Right-click in a blank area of the report body, and navigate to Insert | Chart.
  2. Select the first option in the Line section, and click on OK to create a Line Chart report item, as shown in the following screenshot:
    Adding a report item
  3. Click on the chart item that was created on the report body, move it to the upper-left corner of the report canvas, and then drag out the bottom-right corner to make the chart larger.
  4. Now, double-click on the chart item that was created on the report canvas; notice the Chart Data configuration box that appears to the right. Most report items, such as data bars, spark lines, and gauges, have configuration boxes like the one shown in the following screenshot:
    Adding a report item
  5. In the Values section of the Chart Data configuration box, click on the green button (shaped like a plus sign), and select the TotalSalesAmount data field from the options listed.
  6. In the Category Groups section of the Chart Data configuration box, click on the drop-down arrow on the right-side of the (Details) item, and select the Month data field.
  7. Click on the same drop-down arrow from the previous step, and select the Category Group Properties… option to bring up the Category Group Properties window as shown in the following screenshot:
    Adding a report item

    Category Group Properties dialog box

  8. Select the Sorting tab, change the Sort by column from [Month] to [MonthNumberOfYear], and click on OK. This will keep the months on the x axis sorted in the correct order; otherwise, they will be sorted alphabetically and data for August will appear before February, which is incorrect.
  9. In the Series Groups section of the Chart Data configuration box, click on the green button (shaped like a plus sign), and select the CalendarYear data field from the options listed. This will separate the lines on the chart by year allowing the business users to compare year-over-year trends.
  10. Right-click on the y axis label and uncheck the Show Axis Title option to remove it from the display.
  11. Do the same with the x axis label.
  12. Click on the chart title and change the title from Chart Title to Year over Year – Sales Amount.
  13. The report should now resemble the one in the following screenshot:
    Adding a report item
  14. Right-click one of the numbers along the y axis, and select Vertical Axis Properties… to bring up the Vertical Axis Properties window.
  15. In the Number tab, change the Category value from Default to Currency.
  16. Change the number of decimal places from 2 to 0, and select the checkbox Use 1000 separator (,), as shown in the following screenshot:
    Adding a report item

    Number page of Vertical Axis Properties dialog box

  17. Click on OK to save the changes.
  18. Click on one of the months along the x axis, and select Horizontal Axis Properties… to bring up the Horizontal Axis Properties window.
  19. In the Axis Options tab, change the value for Interval from Auto to 1 and click on OK. This will force every month value to be displayed on the x axis.
  20. Right-click on a blank space in the chart and select Chart Properties… to bring up the Chart Properties window.
  21. In the Border tab, click on the button above None in the Presets section and click on OK to remove the border from the chart.
  22. At the top of the report canvas, click on the Preview tab (to the right of the Design tab) to run the report within SQL Server Data Tools. Your report should resemble the one in the following screenshot:
    Adding a report item

Deploying a report project

Now that the report has been developed and you are satisfied with the results in SSDT, it is time to deploy the report project. In this exercise, you will be deploying the report to a standalone report server installed in the Native mode. The process for deploying a report to a report server installed in SharePoint integrated mode is nearly identical; the only difference is the URLs used in the deployment configuration section of the Report Server Project.

  1. In the Solution Explorer window (on the far right-hand side of the screen), right-click on the name of the project (for example, Sales Report Project), and select Properties to bring up the Sales Report Project Property Pages window, as shown in the following screenshot:
    Deploying a report project

    Sales Report Property Pages dialog box to configure deployment properties

  2. The deployment section (shown in the preceding screenshot) contains several properties that control the deployment destination of the objects in the project. These properties are listed along with a brief description as follows:
    • OverwriteDatasets: This property controls whether or not to overwrite the one already deployed if the project contains a shared dataset with the same name as one already deployed to the report server.
    • OverwriteDataSources: This property controls whether or not to overwrite the one already deployed if the project contains a shared data source with the same name as one already deployed to the report server.
    • TargetDatasetFolder: This property controls the name of the folder where shared datasets contained in project will be deployed.
    • TargetDataSourceFolder: This property controls the name of the folder where shared data sources contained in project will be deployed.
    • TargetReportFolder: This property controls the name of the folder where report files contained in project will be deployed.
    • TargetReportPartFolder: This property controls the name of the folder where shared report parts contained in project will be deployed.

      Note

      For all of the previous properties defining folder locations, if the folder does not already exist, a new one will be created.

    • TargetServerURL: This property contains the URL of the report server and servers as the base path for all of the *Folder properties listed earlier. While deploying to a report server installed in the SharePoint Integrated mode, this URL will point to the SharePoint site.
    • TargetServerVersion: This property contains the version of the report server to which the project will be deployed. While deploying to SQL Server Reporting Services 2012, leave this value at SQL Server 2008 R2 or later.
  3. Enter the appropriate value for the TargetServerURL property and leave all other deployment properties set to the defaults. In my case, with a default report server installation on the same system that I have used to develop this report, I can simply use the URL http://localhost/ReportServer.
  4. After clicking on OK to save the changes to the project properties, right-click on the name of the project (for example, Sales Report Project) in the Solution Explorer window, and select Deploy to deploy all objects in the project to the report server.
  5. Now business users can open up a browser and navigate to the report server, where they should see the items that were just deployed (shown in the following screenshot). The shared data source can be found in the Data Sources folder, while the report, Sales Summary, can be found in the Sales Report Project folder.
    Deploying a report project

    Report Server showing new folders created during the deployment process

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.133.157.142