Using SQL Server Reporting Services to Manage Data

Who is online?  210 guests and 0 members
home  »  articles  »  Using SQL Server Reporting Services to Manage Data

Using SQL Server Reporting Services to Manage Data

change text size: A A A
Published: 6/6/2011 9:08:54 AM by  Amit Karkhanis  - Views:  [50017]

         As everyone knows, SQL Server Reporting Services (SSRS) is a reporting tool like Crystal reports, the use of which is to design and deploy various kinds of reports. If I ask you whether I can use SSRS to manage data in a database, your first and immediate reaction could be one of the following,

  • It’s a reporting platform
  • It’s not a visual studio .Net project to develop data entry forms.
  • Ideally it should be used for reporting

… And so on …

Everybody will agree on the above, but just think out of the box and ask yourself whether SSRS can be used as a tool to insert, update or delete data into a database. Still not able to digest the concept…? I am sure, you will, when you go through this article.

What I will be doing here is,

  1. Setup a sample database with one table and procedures to insert, update and delete data.
  2. Create a reporting services prototype project.
  3. Build and deploy the solution.
  4. Demonstration

         Let’s consider a scenario where we have to manage one lookup table “Country” which holds country code and country name. We should be able to add, modify and delete the data in the Country table and all this should happen using some application so that a non technical guy can also manage this lookup information. Let’s see whether we can achieve our goal without using .Net Smile

For this demonstration, I have used SQL Server 2005. The same can be done in SQL Server 2008. Let’s prepare for the demo.

Setup Sample Database

            Script to setup the sample database (DBScript.sql) is available here (http://www.sqlservergeeks.com/files - SSRS_ManageData.rar). The script does the following.

  1. Create a database - SSRS_ManageData
  2. Create a table - Country
  3. Create insert procedure – uspInsCountry
  4. Create update procedure - uspUpdCountry
  5. Create delete procedure - uspDelCountry

SSRS Project 

            I assume the audience is familiar with Business Intelligence Development Studio (BIDS) and knows how to create basic reports using a report server project. The sample project is available here (http://www.sqlservergeeks.com/files - SSRS_ManageData.rar) and contains one data source and five reports.

Solution Explorere

Data Source

  • SSRS_ManageData – Connection to the database SSRS_ManageData. (Make the required changes in authentication as per the environment available)

            Data Source

Reports

  • MainMenu – This is the main form in the project or you can call it as a landing page. From this page we can navigate to other pages (reports) in this project. Have a look at the following image. It contains three buttons; (Actually, text boxes Smile) Country, City and Reports. For this demo, only Country button has functionality added. You can try the other ones later. Using this Country button, we can navigate to the “Country Master” report.

             Master Maintenance Form

  • CountryMaster – This is the master screen which provides options to Add, Modify or Delete data in a Country table. Add button opens the “Country Master – Add” screen, Modify button opens the “Country Master – Modify” screen and Delete button opens the “Country Master – Delete” screen. The (<) option in the header label is to go back to the previous page i.e. “Master Maintenance Form”.

            Country Master

  • CountryMasterAdd – This is the report which actually adds a new record in the Country table. The report has one data source dsCountryInsert which executes a procedure uspInsCountry. The procedure accepts two parameters, @CountryCode and @CountryName. These two parameters act as report parameters using which we can add a new country record.

           Country Insert

            After entering the data, click on the View Report button and the data gets inserted into the Country table. The procedure also returns a result set, Country ID, Country Code and Country Name which can be shown in the report after successful insertion of data as shown in the following image.

           Country Master Add

            The (<) option in the header label is to go back to the previous page i.e. “Country Master” form.

            Note: Kindly note that this is just a prototype and it will allow you to add the same data multiple times as I have not created any constraints.

  • CountryMasterModify – This is the report which modifies any existing record in the Country table. The report has one data source dsCountryModify which executes a procedure uspUpdCountry. The procedure accepts two parameters, @CountryID and @CountryName. The procedure modifies the Country Name based on the Country ID selected. To get the Country ID, one more dataset dsCountryList is used which uses the following query to list the Country Code and Country Name.

         select CountryID, CountryCode + ' - ' + CountryName [Country] from Country order by CountryCode

            Use this dataset in a report parameter CountryID to show a drop down list of available records for a user to select from.

            Available Values

         The report parameters will look like the following in the preview mode.

         Modify Country

         Let’s update the country name as INDIA, all in caps. Click on the View Report button and the record gets updated in the Country table. The procedure also returns a result set Country ID, Country Code and Country Name which can be shown in the report after successful modification of data as shown in the following image.

         Country Master Modify

         The (<) option in the header label is to go back to the previous page i.e. “Country Master” form.

  • CountryMasterDelete – This is the report which deletes any selected record in the Country table. The report has one data source dsCountryDelete which executes a procedure uspDelCountry. The procedure accepts one parameter, @CountryID. The procedure deletes the Country record based on the Country ID selected. To get the Country ID, one more dataset dsCountryList is used, same as in the above modify option. The report parameters will look like the following in the preview mode.

         Country Delete   

         Select the record and click on the View Report button. The record gets deleted in the Country table. The procedure also returns the deleted Country Code in a result using the Output clause. This can be used to form a notification message to the user after successful deletion of data as shown in the following image.

        Country Delete Msg

Build and Deploy the Solution 

         Finally build the project and deploy the solution to the report server. Check the project properties TargetServerURL, TargetReportFolder before deploying.

Report Property Pages

         Once deployed, hide all the reports except MainMenu so that users can see only the “Master Maintenance Form”. Through this report, users can navigate to the other reports.

Demonstration

         The complete demonstration video is uploaded here (http://www.sqlservergeeks.com/files - SSRSManageData.mp4) which will run you through the entire project.

Conclusion

         This article has illustrated a way of using reporting services to manage data. This can be further enhanced and customized using the various features available in reporting services especially the .Net integration support.

tags : Manage data using SSRS, SSRS as a data entry tool, Insert Data Using Reporting Services
  To rate this article please  register  or  login

Author

Amit Karkhanis Amit Karkhanis (Member since: 3/14/2011 10:05:08 AM)

Comments (13)

admin
6/15/2011 5:37:30 AM Admin Admin said:

Nice once

by
okaasa
6/17/2011 2:27:11 PM okaasa said:

Okie One

by
James Carter
7/19/2011 11:54:49 AM James Carter said:

In this article the description is very clear and helpful. It describes everything clearly. I enjoyed the article very much.  I also have read a article about this topic here "http://www.techyv.com/article/sql-reporting" which is very helpful also.

by
AmitK
7/21/2011 5:25:14 AM Amit Karkhanis said:
Thanks James
by
Suresh G
9/28/2011 6:20:18 AM Suresh G said:

Can any one can tell me without doing manual data entry. any other processes are available.

 

 

by
As
10/19/2011 9:44:30 AM As said:

Thank you very much! solve my problem! :)

by
NZ
2/15/2012 8:27:07 PM NZ said:

@Amit Karkhanis - Thank you Amit.It is wonderful article & you have literally described it very well.Was just looking for this kind of stuff.Thank you once again

@Suresh G - I dont know whether you got the answer to your question or not as it seems that you asked pretty long back,but the answer is yes.You can create other datasets in same report & assign those to parameters as default values.I dont know whether you were asking the question for same scenario or something else.

 

 

by
AmitK
2/16/2012 7:32:19 AM Amit Karkhanis said:

Thanks NZ for appreciation

by
Robert
3/16/2012 8:35:21 PM Robert said:

Amit, great example and use of SSRS.  This really helped me discontinue support of a tiny PHP app that was becoming troublesome to maintain.

Do you have any thoughts of allowing staff to enter free-form text in a parameter and dealing with the commas that SSRS inserts between "lines"?  I can capture it by adding a text parm and allow multiple values, then upon save it gets collapsed down to a single CSV string.

 

@Suresh:  I'm not sure if you got an answer either, but I am using two datasets in one of the update reports.  The first DS simply loads the existing row from the table (based on a rowid parameter passed in from another report).  Each parameter is then setup using fields from that DS as the default.  Then a second DS is the stored procedure to perform the update.

 

The only issue I've run into is that I need an additional "dummy" parameter at the very end that is *NOT* auto-populated or the SP/report will run automatically prior to staff entering their updates.  This final parameter forces the report to not execute and requires them to choose "Yes" from a dropdown before continuing.

by
AmitK
3/19/2012 8:59:43 AM Amit Karkhanis said:

Hi Robert, are you talking something like confirmation from the user before actually adding / updating the data (means executing the report). In that case, try adding an extra parameter with prompt say "Do you really want to proceed...". Make sure that "Allow null value" and "Allow blank value" checkboxes are not set. In "Available values", select "Non-queried" and add "Yes" in Label and Value column.

When you run the report, you have to select "Yes" and only then the report will execute. The drwaback of this approach is that once you select Yes from the dropdown, you cannot make it as blank again. Best is you can add an additional bit parameter to the update procedure which will accept Yes / No and accordingly do the action.

by
hema
8/1/2013 7:33:48 AM hema said:

this article is very helpful....

by
hema
8/1/2013 7:34:14 AM hema said:

this article is very helpful....

by
Ankita
8/19/2013 10:30:18 AM Ankita said:

Can we insert reportitems as well to the record alongwith the parameters?

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Articles RSS Feed

Most Recent Articles