<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

SSRS can be also used for user inputs!

Published on
34,011 Points
24,711 Views
8 Endorsements
Last Modified:
Jason Yousef, MS
There's the plan, then there's what actually happens.
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028

Of course it’s not the best way to use SSRS as it’s a presentation layer and so limited in doing that using Parameters and sub-Reports which will fire a code or a stored procedure to update or delete a record…but IT COULD BE DONE!!

And that’s what I was bargaining about…

1-I started by creating a new project in VS 2008, “Report Server Project” type.
 
 1
2-I’m using SQL 2008 R2 as my testing platform…I’ve created a test DB called “Admin”

CREATE DATABASE [Admin]

3-I created a test table called “Employees”  contains only 3 columns.

Create TABLE Employees
    (
    ID int IDENTITY (1,1),
    Name varchar(50),
    Comments varchar(MAX)
    )



4- Inserted 4 test records…
   
Insert Employees VALUES('Jason', 'Lazy Employee')
    ,('Yousef', NULL)
    ,('John', 'Anything Goes')
    ,('Smith', NULL)

 
 
 2
5-back to VS, Created a Shared Data Source to my Admin DB
 
 3

6-created 3 Blank reports, using the shared Data Source.

1-      To display the records which in the table.
2-      A user summary and confirm screen of which record will be updated and with what.
3-      A done screen, which will call a stored procedure or just have a simple inline T-SQL query.


 4


That’s the trick in the second report, you need to hide the ID and keep the Comments Parameter visible and allow it to accept NULL Value, so it display the record before you need to enter the value.  And also if you need an empty comment, such as to clear the comments field or whatever.

7-The design…

Report1: 1ReportsToDB.rdl

Just a basic table, drag the 3 fields to the table, added an extra field with an expression of  =CHR(0254) and Font of: Wingdings  Just to display a shape without using an image.
Please refer to my article for shapes in

SSRS http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html

Also I added an action to that text field to go to report 2 and pass the ID.
 
 6

 5
Report2: 2ReportsToDB-Confirm.rdl

Again a simple table with the basic 3 fields and added another field for the new Comments value which is using an expression of:  =Parameters!Comments.Value and a field for a confirmation symbol, yes again using my favorite shapes font WINGDINGS , with action to run the 3rd report and pass the ID and the new comments.
 
7
 8

Report3: 3ReportsToDB-Done.rdl

I added 3 text fields, but it’s all optional (for fun).
 
Just with an action to go back to the first report and the summary or what was done!!

 9
Lets’ run and test it….

 test1
I’ll click next to my name on the check mark to update my comments…
That’s what you get at first because we allowed the NULL, that’s the trick to see the table before the parameter gets initialized.
 
 test2
Remove the NULL check mark and enter the new comments….
 
 test3
You’ll get the confirmation above…
Now click on the check mark to confirm the change,  yes you guessed it right…it’s the wingdings shapes again ¿
And that’s what you get..
 
 test4
I’ll click on my GO Back, to the first report to check the data.
Done.. my record was updated in the DB.
 
 test5
Again that’s for the purpose of showing that SSRS is more than a presentation layer and that was the simplest part, it could get more complicated and done more professionally using Custom .NET code and functions.
Hope that helps someone…

The project can be downloaded from
http://www.box.com/s/sskhxfppful1l7sbytzh

References:
http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html
http://www.purplehell.com/riddletools/wingdingschart.htm

Thanks
Jason Yousef
8
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +4
15 Comments
 

Expert Comment

by:HeroOfSparta
HI huslayer ,

I am using SQL server 2008 for this and I am wondering if I can write UPDATE statemnt for the 3rd report above using SSRS 2008?. It was giving some message when I am tried to write UPDATE statemnt.

Deserialization failed: The element 'TableCells' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has incomplete content. List of possible elements expected: 'TableCell' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'. Line 38, position 16. (Microsoft Visual Studio)
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
Hi HeroOfSparta,

Yes sure, 2008 is exactly the same as 2008R2 minus some features, I never seen the error above before, there's a connect issue but has been closed as non-reproducible !

http://connect.microsoft.com/VisualStudio/feedback/details/335511/the-element-tablecells-in-namespace-http-schemas-microsoft-com-sqlserver-reporting-2005-01-reportdefinition-has-incomplete-content

When do you get that error, when you run the report or when you click to update?
0
 

Expert Comment

by:HeroOfSparta
I get this error when creating the 3rd report. I entered the query in query buuilder clicked next ,next and when clicked finish I got this message.

UPDATE EMPLOYEES
SET Comments=@comments
where id=@id
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 

Expert Comment

by:HeroOfSparta
Hi huslayer,

I got it working somehow. But, when  I am in the second report after executing first report, after entring new comment do I have to hit 'View reoprt ' first?? Instead of pressing 'Click to confirm ' button??. Because when I clicked click to confirm after entering new comment im getting 'Comment' parameter is missing message.

Is it suppose to be work like this>?? OR Is there anyway, to press 'click to confirm ' direclty after entering new comments in the parameter box and get it working?
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
Yes you need to click to view the report, SSRS doesn't refresh the parameters automatically.
Look at screen shot 'TEST 3'
0
 

Expert Comment

by:HeroOfSparta
Right!! that is what initially I got confused. But It would have been more smooth if the parameter refreshes automatically.

But anyhow,, this article helps a lot thanks Huslayer. :)
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
Then click on "Was this article helpful?"   ...great that it helped :) feel free to contact me if you need more help.
0
 
LVL 37

Expert Comment

by:ValentinoV
Hey Jason,

Interesting approach, I clicked "yes" :)

Best regards,
Valentino.
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
Hey Valentino,

Thanks,  that's one of my craziest moments to poke around with SQL :)

Regards,
Jason
0
 
LVL 37

Expert Comment

by:ValentinoV
That type of moments are the best! ;)
0
 
LVL 21

Expert Comment

by:Alpesh Patel
New to learn (Out of window)
0
 

Expert Comment

by:HSI_guelph
I've tried adapting this to a report we have where we want to allow people to put comments in but I am getting an error on the last page, the confirmation page:
An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset 'InsertComment'.
Must declare the scalar variable "@ClientID".

My insert query is
INSERT INTO Comments
                      (CommentId, ClientID, CommentDate, Comments, Author)
VALUES     (REPLACE(CONVERT(VARCHAR(12), GETDATE(), 114), ':', ''),@ClientID, GETDATE(),@Comments, REPLACE(SYSTEM_USER, '<domain>\', ''))

Open in new window


I'm using the date for the primary key because I messed up the table and can't get it to auto-assign the PK.  I have to add a ClientID parameter to the first page of the report or the parameter does not show up on the second page, very annoying this is.  

Any help you can give to resolve this issue would be greatly appreciated.
0
 

Expert Comment

by:Guy Davidovich
Best article ever! I enjoyed doing this task :)
0
 

Expert Comment

by:Zina Pettitt
can you do the same for SSRS 2010?
0
 

Expert Comment

by:Zina Pettitt
How do you do it in SSRS 2010
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month