0

I know the we can use custom code in SSRS and I know you have actions on Textbox items in a SSRS report.

Is it possible to "combine" this functionality for the following, which I appreciate is quite out the normal use of SSRS

I wish to produce a list of outstanding requisitions for purchase orders from the SQL tables of our ERP system. In the report against each item would be a Text box with the word "Approve" in The idea is for the requisitions the user wishes to approve they would click on the "Approve" and then use the custom code would fire a SQL procedure, passing in the id of the requisition to the procedure. This procedure would interact with the ERP system to do the approval.

Is this possible?

Some pseudo code for the SSRS report code

Imports System.data.sqlclient
Dim connectionString AS String = "Server=XXXXXXX;Database=db1234;User Id=user_name;Password=my_password"
Dim Command As New SQLCommand("[CHC_AppBuilder].[Testssrscall]", connectionString)
Command.CommandType = CommandType.StoredProcedure
Command.ExecuteNonQuery()

The SQL procedure [CHC_AppBuilder].[Testssrscall] just currently executes dbmail to myself

Problems so far:

  1. Location of the System.data.sqlclient.dll to add the references as per you would in visual studio, so far not been able to test the code.

  2. Trigger the Stored procedure per item.

0

I think this used to be easier when SQL Server included SOAP/web services with SQL 2005/2008. With SOAP you could create a wrapper to a SQL web service that would call a stored procedure with a URL. Microsoft gutted that functionality in future releases of SQL Server 2012 and above. I miss the functionality.

I can think of three ways to handle this but there's probably more:

First create an ASP.NET web service

Here's a tutorial to get you to connecting to a database and accessing the web service with HTML. So basically, you could call your stored procedure from anywhere--not just SSRS.

https://docs.microsoft.com/en-us/aspnet/web-api/overview/older-versions/build-restful-apis-with-aspnet-web-api#Exercise3

And, second, embed custom code with SSRS (here's a few examples I thought were useful):

http://www.davidgiard.com/2012/09/07/EmbeddedCodeInAnSSRSReport.aspx

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cdf290fb-153d-4bc2-8964-8b550df7a087/calling-stored-procedure-from-custom-code?forum=sqlreportingservices

Last, create an assembly to call a custom function (that calls your procedure):

http://davidgiard.com/2012/09/11/CallingAnExternalAssemblyFromSSRS.aspx

  • Thanks I think ASP.NET web service is where I should be trying to go as of the greater functionality – Ian W Jul 18 '18 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.