• 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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001