Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports by Paul Turley

Author:Paul Turley
Language: eng
Format: mobi, epub, azw3
ISBN: 9781119258360
Publisher: Wiley
Published: 2017-01-16T23:00:00+00:00

Figure 12.16 Table with a drill-through action on the first column.

The SalesSummaryMonth dataset is the source for this table, and the query script follows. Aside from the actions, the design of this report is no different from an analytic report you would build for production use, using the skills you've learned so far, with grouped T-SQL queries and multi-select parameters.

I will point out that the MonthNumber column in this query is used for two purposes: to sort the Details row group of the table and to pass into the drill-through action as a parameter. The four query parameters referenced in the WHERE clause are no different than parameters used in most any standard reporting and I've included these to make this scenario realistic:

-- Navigation Report (Month):

-- SalesSummaryMonth

SELECT

d.MonthNumber,

d.MonthName,

SUM(SalesAmount) AS SalesAmount,

SUM(OrderQuantity) AS OrderQuantity,

SUM(p.StandardCost) AS StandardCost,

SUM(Freight) AS Freight,

SUM(TaxAmt) AS TaxAmt,

SUM(SimpleProfit) AS SimpleProfit

FROM

[dbo].[vProductOrderSalesProfit] s

INNER JOIN [dbo].[SalesTerritory] t ON s.SalesTerritoryKey = t.[TerritoryKey

INNER JOIN [dbo].[Product] p ON s.[ProductKey] = p.[ProductKey

INNER JOIN Date d ON s.OrderDate = d.TheDate

WHERE

t.TerritoryKey IN( @RegionKeys )

AND

p.ProductCategoryKey IN( @CategoryKeys )

AND

( OrderDate BETWEEN @DateFrom AND @DateTo )

GROUP BY

d.MonthNumber,

d.MonthName

;

The following script is the query for the SalesSummarySubcategory dataset, which provides records for the column chart above the table. Of interest is the @SelectedMonth parameter. This is the parameter used to pass the selected month from the table drill-through action to this query and to filter the dataset for the column chart. By default, the value is –1, which returns data for all months. I designed it this way to easily select one value that would result in effectively clearing the filter and returning all months:

-- Navigation Report (category):

-- SalesSummarySubcategory

SELECT

p.[ProductCategory],

p.[ProductSubcategoryKey],

p.[ProductSubcategory],

SUM(SalesAmount) AS SalesAmount,

SUM(OrderQuantity) AS OrderQuantity

FROM

[dbo].[vProductOrderSalesProfit] s

INNER JOIN [dbo].[SalesTerritory] t ON s.SalesTerritoryKey = t.[TerritoryKey

INNER JOIN [dbo].[Product] p ON s.[ProductKey] = p.[ProductKey

WHERE

t.TerritoryKey IN( @RegionKeys )

AND

p.ProductCategoryKey IN( @CategoryKeys )

AND

OrderDate BETWEEN @DateFrom AND @DateTo

AND

( MONTH( s.OrderDate ) = @SelectedMonth OR @SelectedMonth = -1 )

GROUP BY

p.[ProductCategory],

p.[ProductSubcategoryKey],

p.[ProductSubcategory

;

Figure 12.17 shows the ReportData window for this report in Design view. All of the report parameters were auto-generated from the queries. Applying the patterns you used in Chapters 6 and 7, parameters were modified with appropriate data types, default values, and simple datasets to provide list selections as you normally would in a parameterized report. Please take a close look at the sample reports to get an understanding of how these parameters are set up to work.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.