SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating SQL table using SSRS


Updating SQL table using SSRS

Author
Message
ByronOne
ByronOne
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 457
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
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1786 Visits: 5526
You could treat the parameters of your report as update requests and send these parameters to a stored procedure.

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5233 Visits: 7732
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
keebler96
keebler96
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 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
ByronOne
ByronOne
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 457
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
brrimes
brrimes
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 25
I don't see where you have tied in the SP of dbo.rpt_shiftInputOutput to the report ...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search