Using SQL Server Reporting Services to Manage Data

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

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 tosetup the sample database (DBScript.sql) is available here (https://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 (https://www.sqlservergeeks.com/files – SSRS_ManageData.rar) and contains one data source and five reports.

Data Source

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

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 ) 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.

• 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”.

• 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.

After entering the data, click on the View Report buttonand 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.

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.

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

Let’s update the country name as INDIA, all in caps. Click on the View Report buttonand 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.

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.

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.

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.

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 (https://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.

Regards

Amit Karkhanis

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook