Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating SQL table using SSRS Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 8:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:59 PM
Points: 143, Visits: 371
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

Post #1472163
Posted Wednesday, July 10, 2013 9:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 404, Visits: 2,386
You could treat the parameters of your report as update requests and send these parameters to a stored procedure.
Post #1472225
Posted Wednesday, July 10, 2013 10:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:18 AM
Points: 562, Visits: 2,617
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1472228
Posted Wednesday, July 10, 2013 12:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:35 PM
Points: 22, Visits: 147
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
Post #1472286
Posted Thursday, July 11, 2013 2:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:59 PM
Points: 143, Visits: 371
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
Post #1472467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse