July 10, 2013 at 8:00 am
Hi everyone
I have the following table in sql server:
CREATE TABLE [DBO].[SHIFTS]
(
[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
)
I was wondering whether it was possible to create a front end utility in SSRS that could alter the [DEPARTMENT] ,[SHIFT] and [HOURS] fields back in the base sql table. Ideally I'd like the ability for the user to type in the requisite changes which would then update the table. The report will be viewed by Managers and the purpose of this utility will be for them to check, alter and approve the hours worked by their staff. I know its possible to update tables using SSRS but not sure whether it can be done by direct entry via SSRS.
Thanks in advance,
BO
July 10, 2013 at 9:54 am
You could treat the parameters of your report as update requests and send these parameters to a stored procedure.
July 10, 2013 at 10:00 am
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
July 10, 2013 at 12:09 pm
You could also look at Visual Studio Lightswitch if you need a quick solution to present a form over your data table.
If you have any development background Lightswitch is easy to pick up, and by that I would be surprised if you didn't have a working prototype against your table in as little as 2 hours after getting it installed.
http://msdn.microsoft.com/en-us/library/vstudio/ff851953.aspx
July 11, 2013 at 2:33 am
Guys
This all really very, very helpful and I will have a play around and see what I can come up with.
Thanks in particular to Alan - especially as you had to type everything out twice - much appreciated...
BO
January 22, 2015 at 1:28 pm
I don't see where you have tied in the SP of dbo.rpt_shiftInputOutput to the report ...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply