ARGH!!!! I just attempted to post a detailed response, got an error and lost what I wrote. Forgive any errors and typos:
First, SSRS is not designed for this kind of thing and, as someone who has done this, I would advise you consider an Access Form against a SQL back-end, Sharepoint or an ASP.NET web form. That said, what you are looking for should be pretty simple.
The steps would be:
1. Add a Primary Key to DBO.SHIFTS. We want something like this:
CREATE TABLE [DBO].[SHIFTS]
( [SHIFT_ID] INT IDENTITY PRIMARY KEY,
[WEEK_ENDING] DATETIME NULL
,[FORENAME] VARCHAR(50) NOT NULL
,[SURNAME] VARCHAR(50) NOT NULL
,[EMP_ID] VARCHAR(10) NOT NULL
,[DEPARTMENT] VARCHAR(50) NOT NULL
,[SHIFT] VARCHAR(20) NOT NULL
,[HOURS] INT NULL)
2. In the same DB, create a stored procedure for updating the shifts. We'll call it dbo.rpt_shiftInputOutput.
The query would look something like this:
IF OBJECT_ID('tempdb..#ShiftInfo') IS NOT NULL
DROP TABLE #ShiftInfo;
--create a temp table for old and new values
CREATE TABLE #ShiftInfo (
VAL varchar(20) NOT NULL
,[DEPARTMENT] VARCHAR(50) NOT NULL
,[SHIFT] VARCHAR(20) NOT NULL
,[HOURS] INT NULL)
-- get the previous values
INSERT INTO #ShiftInfo
SELECT 'Previous value', DEPARTMENT, SHIFT, HOURS
FROM DBO.SHIFTS
WHERE SHIFT_ID=@pShiftID;
-- make the update
UPDATE DBO.SHIFTS
SET DEPARTMENT=@pDepartment,
SHIFT=@pShift,
HOURS=@pHours
WHERE SHIFT_ID=@pShiftID;
-- get the new values
INSERT INTO #ShiftInfo
SELECT 'New value', DEPARTMENT, SHIFT, HOURS
FROM DBO.SHIFTS
WHERE SHIFT_ID=@pShiftID;
-- Output
SELECT VAL, DEPARTMENT, SHIFT, HOURS
FROM DBO.SHIFTS
WHERE SHIFT_ID=@pShiftID;
3. Create your SSRS data source and attach it to DBO.SHIFTS
4. Create a dataset named dsShifts
5. In dsShifts create a query that looks something like this:
SELECT SHIFT_ID, SHIFT
FROM DBO.SHIFTS
5. In your report, create the following SSRS parameters:
i.pShiftID
ii.pDepartment
iii. pShift
iv.pHours
5. Open the parameter pShiftID, under available values set the following:
Dataset: dsShifts
value field: SHIFT_ID
label field: SHIFT
6. In your report create a tablix and tie it to dsOutput. This will be used to display the results.
Now you have a report that has a drop down which allows you to select the shift that you want as well as text boxes for
each of the fields you discussed changing. When you click VIEW REPORT your parameters will be passed to dsShifts which
will execute dbo.rpt_shiftInputOutput. The old and new values will appear in your report.
I have not tested the code in this post but I (after suggesting better solutions) have had to do this many times.
My naming was on the fly and there's not any attention here to error handling and usability. This should get you started nonetheless.
-- Itzik Ben-Gan 2001