Creating a drillthrough or linked report

Let's create our third report and call it SalesTerritoryReport. Use the template we created in the previous chapter.

Find the SQL script called uspSalesPersonSalesByYear.sql and execute it to create a stored procedure called dbo.uspSalesPersonSalesByYear. This stored procedure has one parameter, that is, @SalesTerritory.

As you might have noticed, the query returns sales total for years 2005, 2006, 2007, and 2008 by territory, using the PIVOT function in T-SQL.

Again, let's keep this simple:

  1. Drag a table object into the body of our report. We only need five columns for this report.

    Note

    The report designer added an ID prefix to our year columns (for example, ID2005) because field names must start with a letter from the alphabet.

  2. Just above the left-hand corner of the table, add a textbox to hold the value of the parameter.

    You should have something like the following:

    Creating a drillthrough or linked report
  3. Let's create a separate dataset for the @SalesTerritory parameter. Name it ds_rptSalesTerritory or just anything you want.
  4. Let's throw in a bonus exercise here: adding the string ALL as an option in the parameter.

    Let's set the parameter dataset with this query:

    Select 'ALL' as Name
UNION ALL
Select Name
From Sales.SalesTerritory

    Note

    As a best practice, always use UNION ALL unless you need the UNION functionality. UNION ALL avoids the overhead of the SORT (Distinct) operator. Read more about the execution plan in SQL Server.

    I'll explain the whole ALL parameter thing in a second. For now, let's focus on our drillthrough or linked report.

    This report now looks like the following illustration after applying some of the things we learned from the previous chapters:

    Creating a drillthrough or linked report

    We can now set aside this report.

    Now, let's go back to our main report, which is the SalesOrderHeaderReport.

  5. Right-click on the [Territory] data field in the first listbox, then select Text Box Properties.
  6. On the Text Box Properties dialog window, select the Action tab and enable the Go to report action.
  7. Specify that we want the SalesTerritotyReport as the report we want to go to.
  8. Then click on the Add button where it says Use these parameters to run the report.

    Our Go to report has only one parameter, which is called SalesTerritory. Let's put that under the Name section. For Value, let's select the data field [Territory] from our main report.

    This is a simple parameter passing; from the main report to the go-to report.

    Creating a drillthrough or linked report
  9. We'd also like to change the color of the font to blue and have it underlined, and make it appear as a hyperlink to signal that this data field is clickable.

    Click on OK when done.

    Now, let's try our report with Sales Order Number 51721.

    You will notice that when you hover your cursor over Territory Northwest, you now have a hand cursor which indicates an action will occur when clicked:

    Creating a drillthrough or linked report
  10. Click on Northwest and that will open the SalesTerritoryReport and pass the territory value Northwest to the Sales Territory parameter:

    You can use the little arrow button on top of the window to return to the main report:

    Creating a drillthrough or linked report

You can use the browser back arrow to go back to the main report when it's already deployed on the production.

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

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