Report Solution Patterns and Recipes - Professional SQL Server 2005 Reporting Services - page 317

interactive report, you should probably be meeting with the architects and developers of your entire
database solution and carefully consider the best method to implement these features. It might be best to
use an application front end better suited for that type of functionality. Yet, since the topic at hand is to
demonstrate cool stuff you can do in reports, I'm going to show you what you can do. I'll leave it to you
to decide what you should do.

“Data write-back” is a highly touted feature of data mining and data warehouse systems. There are a
variety of situations where it may be beneficial to write back data to source tables. These may include
inventory control, prices changes, or testing “what if” scenarios. Where possible, it's usually a good idea
to write data to a staging or temporary table to preserve the state of important source data. You may
then use join statements to correlate the original records with the modifications before (or instead of)
committing changes to the source.

What you'll need:

❑ Permissions granted for the Report Server service user to modify data.

❑ A SQL stored procedure using a conditional query expression used to perform an update and

return records.

❑ Report parameters used to pass data values into a data set using a stored procedure.

❑ Report item(s) with an action expression used to pass parameter values back to the report.

There are a few different methods to execute SQL script used to modify data. In the first of two exam-
ples, I'll use a stored procedure to perform both the data updates and to return results. After that, I'll
separate these two query operations and use two separate reports to do the same thing.

Stored procedures are an excellent place to manage multiple operations and conditional business logic.
The following script defines a stored procedure that accepts two input parameters used to modify price
values for products matching a specified subcategory. If the @PriceIncrease parameter is zero or the
@ProductSubCategoryID parameter value doesn't match values in the Product table, no modifications
will be performed.

The second section of the procedure script returns a result set for all products and related categories and
subcategories.

CREATE PROCEDURE spProductUpdatePrice
@PriceIncrease Float,
@ProductSubCategoryID Int
AS
-- Update price:
IF @PriceIncrease <> 0
BEGIN
UPDATE Production.Product SET ListPrice =
ListPrice + (ListPrice * @PriceIncrease/100)
WHERE ProductSubCategoryID = @ProductSubCategoryID

END

-- Return products
SELECT Production.ProductSubcategory.Name AS ProductSubCategoryName,