Creating one report to meet multiple business requirements can save a lot of work
down the road. A common scenario is for different users to ask for similar reporting
metrics that may just be variations of existing reports. Using SQL Server Reporting
Services, you can achieve this goal with a little planning and creative report design.
The following technique in Reporting Services for SQL Server 2008 uses a parameterized
expression to modify a dataset so that it returns a different set of columns. Conditional
logic in the report is used to include only columns in a table which correspond to
the columns returned by the query. The same technique will work in earlier versions
of Reporting Services but the designer user interface will be a little different.
This example uses a parameter to change the stored procedure that will be executed
to return the report data. Conditional query logic could also be applied to generate
a dynamic SQL or MDX statement.
To begin, use SQL Server Management Studio to connect to the AdventureWorksDW2008
sample database and then execute the following script to create two new stored procedures:
create proc spResellerSales1
@DateFrom int,
@DateTo int
as
select
d.CalendarYear, d.CalendarQuarter, d.EnglishMonthName
as Month
, sum(fs.OrderQuantity) as OrderQuantity
, sum(fs.SalesAmount) as SalesAmount
from
FactResellerSales fs inner join DimDate
d on fs.OrderDateKey = d.DateKey
inner join DimProduct p on fs.ProductKey
= p.ProductKey
inner join DimProductSubcategory ps
on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
inner join DimProductCategory pc
on ps.ProductCategoryKey = pc.ProductCategoryKey
where d.DateKey between @DateFrom and @DateTo
group by d.CalendarYear, d.CalendarQuarter, d.EnglishMonthName
, d.MonthNumberOfYear
order by d.CalendarYear, d.CalendarQuarter, d.MonthNumberOfYear
;
go
----------------------------
create proc spResellerSales2
@DateFrom int,
@DateTo int
as
select
d.CalendarYear, d.EnglishMonthName as Month
, sum(fs.OrderQuantity) as OrderQuantity
, sum(fs.SalesAmount) as SalesAmount
from
FactResellerSales fs inner join DimDate
d on fs.OrderDateKey = d.DateKey
inner join DimProduct p on fs.ProductKey
= p.ProductKey
inner join DimProductSubcategory ps
on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
inner join DimProductCategory pc
on ps.ProductCategoryKey = pc.ProductCategoryKey
where d.DateKey between @DateFrom and @DateTo
group by d.CalendarYear, d.EnglishMonthName, d.MonthNumberOfYear
order by d.CalendarYear, d.MonthNumberOfYear
;
go
Note that the only significant difference between the spResellerSales1 and spResellerSales2 stored
procedures is that the first one returns the CalendarQuarter column
and the second one does not. Remember that this is a simple example for the sake of
demonstration. You could have as many different procedures or query variations as
you like as long as one of them returns all possible columns and the others return
a subset of columns in the same order.
The report contains parameters, two that will be automatically generated from the DateFrom and DateTo parameters
in the procedure, and another parameter named DataSource that I added
myself. Note that I've provided default values for the DateFrom and DateTo parameters
since they're really not part of the demonstration scenario.
To populate the dataset Fields collection, configure the dataset to use a stored procedure
and select the first procedure (the one that returns all of the columns) as you normally
would without using an expression. The DataSource parameter (not
to be confused with a report data source) is use to pass and swap the stored procedure
names to the report's main dataset. Manually add this parameter to the report:
The available values for this parameter include the two stored procedure names:
This parameter is referenced in the report's main dataset using the expression: =Parameters!DataSource.Value
A table is added to the report and each dataset field is added as a column to the
table:
Since the CalendarQuarter column may not be available when the spResellerSales2 stored
procedure is used in the parameterized dataset, this column must be hidden when the
column is not returned in the result set. This is accomplished by changing the Hidden
property of the table column. Right-click the column header for the Calendar
Quarter column and choose Column Visibility… from the menu.
Choose Show or hide based on an expression and then click the expression
button to use the Expression Builder to create the following expression for this property: =Fields!CalendarQuarter.IsMissing.
Remember that there is a difference between a report dataset and the query that
it references. When a query is written, (or in this case, when a stored procedure
is selected) field definitions are added to the dataset object and will be there whether
the query returns a column or not. If that query doesn't return a corresponding column
for the field, the field's IsMissing property returns True.
You're all done!
Preview the report and choose the first stored procedure in the parameter list. The
report returns columns for all fields, including the Calendar Quarter:
Change the Data Source parameter to use the second stored procedure and click the
View report button on the toolbar. Now the report returns columns for all fields except
the Calendar Quarter:
A copy of this sample SSRS 2008 report is included for download.
Columns.rdl (351.00 bytes)
Weblog by Paul Turley and SQL Server BI Blog.
