Write back to a database using SSRS!

I bet we were all in this kind of situation: users want you to find a solution for their problem, and they need the solution for “yesterday”…You warn them that the only possible outcome would be “quick & dirty”, but they insist on the quickest (which is most often not the wisest) solution…

Please keep reading this article having in mind the following: if you CAN do something, that doesn’t necessarily mean that you SHOULD! And, full disclaimer here: whenever possible, you should avoid applying this technique that I will show you!

However, as I already said, real-life scenarios are sometimes unpredictable and may force you to ignore the rules and best practices on your way to finding a quick solution to your business needs.

Request

The original request from my user was to enable the following: there is a report (built in SSRS), displaying the list of customers that were still waiting for their items to be shipped. The sales representative would contact the customer, and after the contact was made (usually via phone), that specific customer should be marked as contacted and not displayed in the report anymore!

That means the customer needs to be somehow marked in the underlying database and consequentially excluded from the report. But, how can a sales representative, that has only SSRS report in front of her/him to interact with, “write” data back to a database and mark specific customer?!

By definition, SSRS (SQL Server Reporting Services) is a reporting platform – that being said, it should READ the data from the data warehouse/database, render that data, and finally display it to the report consumer. So, WRITING data back into the database is not what is SSRS intended for.

Solution

For demo purposes, I will use the AdventureWorks2012 sample database. I’ve slightly adjusted the original SalesOrderHeader table so that it now contains rows with no ShipDate (NULL as ShipDate).

Here is the query that pulls the data for the report:

SELECT sales.SalesOrderID
	,sales.OrderDate
	,sales.DueDate
	,sales.ShipDate
	,sales.SalesOrderNumber
	,cus.AccountNumber
	,cus.PersonID
	,p.FirstName + ' ' + p.LastName AS FullName
FROM Sales.SalesOrderHeader AS sales
	INNER JOIN Sales.Customer AS cus on cus.CustomerID = sales.CustomerID
	INNER JOIN Person.Person AS p ON p.BusinessEntityID = cus.PersonID
WHERE sales.ShipDate IS NULL

And, here is how the report looks like in SSRS:

As you can see, this is a basic report, showing all customers whose items still hadn’t been shipped. Now, imagine that sales representative calls, let’s say, Carla Adams (the first customer on the list), and once the call is finished, Carla needs to be marked as contacted and removed from this list!

In order to keep track of the customers who were contacted, we will need a separate table to store the data. Therefore, I’ll create a new table that will store the data about the customer, sales order number, and the person who contacted the customer (in case there is a need to measure how many customers were contacted by a specific sales representative).

Now, if you are wondering: what would happen if we have two customers with exactly the same name…There is an additional column in the report (PersonID), that is the unique identifier of the person. But, this column is hidden when the report renders:

That column will also need to be included in the newly created table. So, let’s go and create a table Sales.SalesNoShipContacted:

CREATE TABLE Sales.SalesNoShipContacted (PersonID INT,SalesOrderNumber VARCHAR(128),FullName VARCHAR(128),ContactedBy VARCHAR(128))

Let the party start…

Now the real party starts! I will insert an image that will be used as a button to trigger the action from the report.

In order for this to work, I need an additional “helper” report, that will insert a specific portion of data into a newly created table.

Now, if you are asking yourselves – how can I insert the data into the database using an SSRS report?! Sure, I can. Basically, whenever you run a report, it will trigger the T-SQL query to execute over the source database. When I say T-SQL query, I mean WHATEVER T-SQL query – of course, assuming that syntax is correct…In 99.99% of cases, this will be some kind of SELECT statement (and it SHOULD be like that), but no one said that you can’t use other operations – namely, INSERT!

Just pay attention to my wording here: you SHOULD use SELECT, but you CAN use INSERT too…

Here is the “helper” report, that contains a few parameters only:

Here, all the parameters are hidden, except ContactedBy, which is Internal and I’m providing here as a default value a global parameter UserID, that will return the domain\username of the user that is currently logged in.

The next critical step is to “tell” this report which query to execute:

INSERT INTO Sales.SalesNoShipContacted (PersonID,SalesOrderNumber,FullName,ContactedBy)
SELECT @PersonID PersonID
	,@SalesOrderNumber SalesOrderNumber
	,@FullName FullName
	,@ContactedBy ContactedBy

select @FullName as FullName, @SalesOrderNumber SalesOrderNumber

So, once the report is being run, it will trigger the query you see above. And, this query will insert the values provided within the parameters to our new table. Finally, we are retrieving some of the data to display to our user, as he needs to be aware of what has just happened.

The next step is to hide this “helper” report from the users’ view, since they don’t need to know how the “magic” happens in reality:)

Final touches

And here comes the most important part: how will the “helper” report “know” which values to insert into the new table? Or, simply said, which customer did the user click on, to mark it as contacted?

Let’s go back to our main report, and apply following steps on our image:

As you see, we’ll need to write some code, combining some basic stuff from Javascript and Visual Basic:

="javascript:var check=confirm('Are you sure you want to mark as Contacted: 
"& Fields!FullName.Value & "?'); if (check == true) {void(window.open('http://<your report server>/ReportServer/Pages/ReportViewer.aspx?
"& Globals!ReportFolder & "/NoShipDateContactedInsert&rs:Command=Render&PersonID=" & Fields!PersonID.Value & "&SalesOrderNumber=" &Fields!SalesOrderNumber.Value & "&FullName=" & Replace(Fields!FullName.Value, "#", "")  
 & "','_blank','width=800,height=500,top=0,left=0,toolbar=0,menubar=0,location=0,status=1,scrollbars=0,resizable=0'))}"

This code will enable the dialog box to pop up and ask a user to confirm the action (to confirm if she/he wants to mark a specified customer/order number).

Once the action is confirmed, the URL will be built using the values from the report fields, and this will basically fire the “helper” report in the new window and forward selected values as parameters to a “helper” report!

First, let me confirm that my new table is empty:

Now, let me show you how this would look like, once the user opens the main report and select to mark the first record as contacted:

As you can notice, once I click on the image next to a specific row, I’m prompted to confirm the action. And, after I do that, the “helper” report fires up, executes the INSERT statement, and finally, I’m being informed that my action was successfully completed. So, let’s check if that’s true, and take a look at our table again:

Woohoo, the record is really there!

The final touch, in order to fulfill the main business request, is to remove this record from the original report. To be able to do this, we will adjust our original T-SQL code:

SELECT sales.SalesOrderID
	,sales.OrderDate
	,sales.DueDate
	,sales.ShipDate
	,sales.SalesOrderNumber
	,cus.AccountNumber
	,cus.PersonID
	,p.FirstName + ' ' + p.LastName AS FullName
FROM Sales.SalesOrderHeader AS sales
	INNER JOIN Sales.Customer AS cus on cus.CustomerID = sales.CustomerID
	INNER JOIN Person.Person AS p ON p.BusinessEntityID = cus.PersonID
	LEFT OUTER JOIN Sales.SalesNoShipContacted AS con ON con.PersonID = cus.PersonID AND con.SalesOrderNumber = sales.SalesOrderNumber
WHERE sales.ShipDate IS NULL
	AND con.PersonID IS NULL

Let me shortly explain what we are doing here: with left joining a new table, we want to display only those records that don’t exist in the SalesNoShipContacted (records that were not marked).

We had to include both customer’s name and sales order number in joining condition, because one customer can potentially have multiple orders, and it can happen that not all of them would be without a ship date.

If I now refresh the report, I will see that my marked customer is not being displayed anymore:

Conclusion

As you may notice, by using some non-standard techniques, we significantly extended the capabilities of our report, and enabled users to write back to a database directly from the report itself!

I’ve said multiple times, and I will repeat once again, to conclude: a fact that you CAN do something, doesn’t mean that you SHOULD do it! The solution I’ve shown here is by no means the best practice, and requests like this should be handled with a completely different approach.

However, if we learned something from the real-life experience, that is the fact that there are some edge cases when we are forced to apply some non-recommended solutions.

Thanks for reading!

Last Updated on December 9, 2020 by Nikola