What we like about SQL Server Reporting Services is not only it can create perfect paginated reports but it can also insert, update and delete records on a database! Yes you read it right a report that can actually manipulate database tables. This makes SSRS a very flexible and the possibilities are endless.
Well you might say SSRS should be a data viewer and is only for reporting purposes only. But no! There are some situations that will require you to do this. Below is the step by step guide on how to insert records using an SSRS Report.
1. First we have to create a sample table on SQL Server. (You can also do this using other databases).
USE DATAJACKAL Create TABLE THE_TRUTH ( ID int IDENTITY (1,1), JACKAL_NAMES varchar(50), COMMENTS varchar(MAX) )
2. For this guide we are going to insert four records on the table called THE_TRUTH.
Insert THE_TRUTH VALUES ('LINE_01', 'Two people are standing by a lake.') ,('LINE_02', 'One says, "That''s a lovely reflection in the water."') ,('LINE_03', 'The other says "I see no reflection, but it''s a fascinating assortment of fish, plants and rocks within the water."') ,('LINE_04', 'Which one is lying?')
3. Once you have created the database table and Insert Statement, Open SSRS Report Builder by creating a new paginated report on the web portal.
4. Create two parameters called LINE_NO and ANSWER on the report builder. This will be used to enter the values need to the table.
5. Add a new dataset on the report builder and choose dataset embedded in my report.
6. Under the parameters on the dataset properties specify the two parameter name LINE_NO and ANSWER this would be used to the insert query.
7. On the Query tab in dataset properties choose the query type as text then put the insert statement then specify your data source clicking new. After clicking new tick on use connection embedded on my report. and click build.
8. Specify the data source, we choose SQL Server for this guide. Then specify the server name, database credentials and database then click test connection once the connection succeeded click ok.
8. The dataset will now be available for this report. Add another dataset, this would be used to create a tablix on the report which will reflect the contents of the database table.
9. Create a table using the second dataset to display the database table. Save the report.
10. Create a copy of the report and save it as ALL_THINGS_ARE_TRUE. Now you have two reports called the THE_TRUTH and ALL_THINGS_ARE_TRUE. On the second report delete all parameters and delete the first dataset. Then insert a new row under the tablix put “ANSWER THE QUESTION CLICK HERE” as its value.
11. Put an action on the “ANSWER THE QUESTION CLICK HERE” textbox and select go to report and put the THE_TRUTH report.
12. On the THE_TRUTH report crate a textbox with a confirmation message and add an action go to report ALL_THINGS_ARE_TRUE. Save both report and make sure they are publish on the reporting portal.
13. Run ALL_THING_ARE_TRUE report. Then click on “ANSWER THE QUESTION”. The report will redirect you on the THE_TRUTH_REPORT and ask you for parameters.
14. Place your answers on the parameters and click view report.
15. The confirmation message that you have created earlier on the TRUTH_REPORT will appear click on that.
16. A new record was now inserted on the database table and should now appear on the table.
The same process also applies for updating and deleting records on a database. A lot of possibilities could be done like calling a custom pop up modal to insert, delete and update records.