• Madeira Team

Using Reporting Services as your Software Frontend

Microsoft SQL Server Reporting Services is an excellent and easy-to-use platform for creating, viewing, managing, and subscribing to reports of all shapes and sizes.

Technically, this platform is meant for nothing more than reports. It’s true that reporting is a huge field and you can do all kinds of cool stuff with it, but it’s still just reporting – or in other words: Viewing data.

However, it’s also possible to use this platform for updating data. Whether it’s deleting items, updating them, or even inserting new records. It’s especially good when your organization doesn’t have skilled programmers that can create a pretty-looking graphical user interface (GUI). Or when there are budget and / or time restrictions.

The key to this special usage of Reporting Services is the Data Sets of type “Stored Procedure”. In SQL Server Reporting Services, one form of getting data for your report is executing a stored procedure in your database. However, a stored procedure (as you probably know) can do a lot more than just querying data. Technically, when you use stored procedures in SSRS, all they need to do is:

Receive parameter values.

  1. Do some processing.

  2. Return a set of data.

The second step can be absolutely anything. It can be an insertion of new data, updating or deleting, and even some complex combination of everything.


image

Actually, you don’t really need a stored procedure to do this. You can also use the Text data set and write all your logic directly in the report. But in my opinion it’s not so comfortable and a lot more cumbersome to work like that, especially when you need to debug your logic, or when the same logic is used in several reports.

Also, it’s important to remember that your stored procedure must return a single result set at the end. Don’t forget this is Reporting Services we’re dealing with, and it expects to receive a single result set for the data it needs to display.

Personally, I like implementing this using what I call an “output temporary table”. The idea is that throughout your logic, you may have several steps, and you want to know the outcome of each of these steps (success or failure, number of rows affected etc.),. To do so, I create a temporary table (best if it’s a variable table), and at the success or failure of each step, I insert a record with a message in that table. At the end, I simply SELECT the entire table and display it in the report.

Updating Rows

For example, here is a sample procedure that I wrote which transfers all the employees from one manager to another (in the AdventureWorks database):

Transact-SQL

CREATE PROCEDURE MoveEmployeesFromManagerToManager @SourceManagerID INT, @DestinationManagerID INT AS DECLARE @RCount INT DECLARE @Output AS TABLE ( Msg NVARCHAR(MAX) ) BEGIN TRY INSERT INTO @Output VALUES( N'Moving employees from ManagerID ' + CONVERT(nvarchar(50), @SourceManagerID) + ' to ManagerID ' + CONVERT(nvarchar(50), @DestinationManagerID) + '...' ) BEGIN TRANSACTION UPDATE Employees SET ManagerID = @DestinationManagerID OUTPUT N'Moved EmployeeID ' + CONVERT(nvarchar(50), INSERTED.EmployeeID) INTO @Output FROM HumanResources.Employee AS Employees WHERE ManagerID = @SourceManagerID SET @RCount = @@ROWCOUNT; IF @RCount > 0 BEGIN INSERT INTO @Output VALUES( N'Moved ' + CONVERT(nvarchar(50), @RCount) + ' employee(s).') END ELSE BEGIN INSERT INTO @Output VALUES(N'No employees were updated.') END COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; INSERT INTO @Output VALUES(N'CRITICAL ERROR: ' + ERROR_MESSAGE()) END CATCH SELECT * FROM @Output

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

CREATE PROCEDURE MoveEmployeesFromManagerToManager

@SourceManagerID INT,

@DestinationManagerID INT

AS

DECLARE @RCount INT

DECLARE @Output AS TABLE

( Msg NVARCHAR(MAX) )

BEGIN TRY

INSERT INTO @Output

VALUES(

N'Moving employees from ManagerID '

+ CONVERT(nvarchar(50), @SourceManagerID)

+ ' to ManagerID '

+ CONVERT(nvarchar(50), @DestinationManagerID)

+ '...'

)

BEGIN TRANSACTION

UPDATE Employees

SET

ManagerID = @DestinationManagerID

OUTPUT

N'Moved EmployeeID '

+ CONVERT(nvarchar(50), INSERTED.EmployeeID)

INTO

@Output

FROM

HumanResources.Employee AS Employees

WHERE

ManagerID = @SourceManagerID

SET @RCount = @@ROWCOUNT;

IF @RCount > 0

BEGIN

INSERT INTO @Output

VALUES(

N'Moved '

+ CONVERT(nvarchar(50), @RCount)

+ ' employee(s).')

END

ELSE

BEGIN

INSERT INTO @Out