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:
@SalesTerritory
parameter. Name it ds_rptSalesTerritory
or just anything you want.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
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:
We can now set aside this report.
Now, let's go back to our main report, which is the SalesOrderHeaderReport.
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.
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:
You can use the little arrow button on top of the window to return to the main report:
You can use the browser back arrow to go back to the main report when it's already deployed on the production.
3.146.178.165