I then created the second new report called EmployeeUpdate that accepted a single hidden parameter (ID). The second report had a single data set that passed the input parameter to a stored procedure with SQL like this:
CREATE PROCEDURE [dbo].[ReportEmployees_Update]
@ID INT
AS
DECLARE @Employee INT
SET @Employee = (SELECT ID FROM [dbo].[ReportEmployees] WHERE ID = @ID)
IF @Employee = @ID
BEGIN
DELETE FROM [dbo].[ReportEmployees] WHERE ID = @ID
END
ELSE
BEGIN
INSERT INTO [dbo].[ReportEmployees] VALUES(@ID)
END
The second report had generic output that said something to the effect that the report users had been updated.I also offered navigation back to the original report or the EmployeeList report. Finally, I went back to the original report and offered an administration link to the EmployeeList Report and I was done! Everything was in place and the manager could administer his own report.
Well, not quite. As I began to test my new admin tool, I discovered that report navigation did not necessarily cause a report to refresh it’s content. The result of this for the user would have been confusing. While the stored proc was doing it’s job of either adding or removing the ID from the table, my ReportList administrative report did not update to reflect the change when I clicked to go back to it. I could have told the manager that they had to click the refresh button to see the most current state, but I knew it would be confusing and if I wanted to use the solution again for a broader audience, it would need to be simpler to use.
