0

I need to call a stored procedure during my report to insert values into a table. These values are the result of fields from the dataset added. For example, I have a row of values, and the far right column is "ReportItems!TextBox1.Value + ReportItems!TextBox2.Value ..." This gives the correct total on the report. Now I need to call a procedure using this value as a parameter.

Using a stored procedure as a dataset, I am unable to reference the ReportItems! I am also unable to create additional report parameters (even internal or hidden) which could be a result of a dataset due to the reporting infrastructure we are using.

I know using custom report code, I can call a stored procedure and also reference the ReportItems, but I have been unable to find the correct syntax. I am not familiar with VB.net so please be specific. If i could get an example of how to call: Procedure TEST_INSERT(ReportItems!TextBox1.Value), I would be able to figure out how to implement it.

I am using an oracle backend as my data source. Thanks

eestein
  • 4,914
  • 8
  • 54
  • 93
Aheinlein
  • 480
  • 2
  • 7
  • 19
  • Is your problem with VB? If so, have you tried `ExecuteSQL` method? It would be like `ExecuteSQL("your_proc param1, param2")` – eestein Jan 29 '13 at 18:01
  • Yea i suppose my problem is mostly with VB that I do not know how to call it. Do i need to set up the db connection inside my code, or can i just have a vb function with no parameters which calls ExecuteSQL() like you said? – Aheinlein Jan 29 '13 at 21:38
  • Public Shared Function TestInsert(REQUEST_ID AS INTEGER) As Boolean ExecuteSQL("[PACKAGE_NAME].TEST_INSERT, REQUEST_ID) return true End Function This gave the error "Name 'ExecuteSQL' is not declared" is this the correct way to call it or how do i declare it? also - i took out the real package name for this post Edit- Well I cant get this to format correctly in comment, but it is just a very simple function. – Aheinlein Jan 30 '13 at 13:26
  • no, you're missing the database reference. Here's an example of how to do it: http://support.microsoft.com/kb/146651 – eestein Jan 30 '13 at 15:48
  • Are you proficient in oracle? And can you create an oracle package or procedure? This may be an answer – glh Jan 31 '13 at 09:07

2 Answers2

2

If I've understood you correctly, you want to do an database update using a value calculated with an expression in your report. My answer to this would be threefold.

First up: don't do it!.

Second: seriously, don't do it!! Reporting services is not meant or well suited for this kind of task, you most likely are looking at an XY-Problem.

Third, if you insist on doing it anyways, the easiest way I can think of to accomplish that is by using a seperate report to trigger the update, and pass the value you're after into a parameter for that report. In the main report, you set a click action on the cell with the total that calls the report, with the same value into the parameter.

A similar setup which may work as well, is to create a parameter based on the first dataset with that same "sum" expression you mention, and pass that down to another dataset.

However: don't do it! ;-)

Community
  • 1
  • 1
Jeroen
  • 60,696
  • 40
  • 206
  • 339
  • thank you for your response. basically this insert needs to happen every time the report is run. This report will be run daily and if run on the same day of next year, will get the values inserted from todays report run. I will try the executeSQL function in the report code first and use a second report as kind of a last resort – Aheinlein Jan 29 '13 at 21:41
  • If you need to execute a small update for storing a total value somewhere, isn't a [scheduled job](http://stackoverflow.com/questions/5471080/how-to-schedule-a-job-for-sql-query-to-run-daily) a more sensible solution? – Jeroen Jan 29 '13 at 21:44
  • Well the report has about 10 parameters, and about 12 fields to populate into the database- each are sums of different queries. If there is any way to do this from the report, I think it would make it a bit easier – Aheinlein Jan 30 '13 at 13:26
  • 1
    Why not make a stored procedure to return the dataset, and give that sproc 10 parameters as well? The stored procedure is a decent place (better than SSRS) for doing the calculations and updating the database. Possibly encapsulate the update logic in a seperate db object, and either call that daily in a job, or the first time on a day when the report calls the sproc. Put another way, IMO expressions in SSRS should be used for *presentation purposes*, if you find yourself wanting to save the result you'll need to encode the business logic outside SSRS (e.g. in a sproc). – Jeroen Jan 30 '13 at 13:39
0

I would set up a dataset in the report service that calls an update or insert function in oracle and returns a value/s. This way you can send in the total you need calculate and produce a result telling the user if the update was successful.

There is no special method for doing this just select or enter your procedure name and refresh the fields to update the parameter/s. See Oracle stored procedure in SSRS.

Also using this method you can run the stored procedure in oracle, update the table and display the results.

glh
  • 4,900
  • 3
  • 23
  • 40
  • In response to your comment above, I am semi-proficient in Oracle and yes I can create packages and procedures. One reason I am hesitant to create a new dataset to get the values and to do the insert is time. It takes about 3 minutes to run the report getting all data, calculating sums (these sums are to be inserted) and displaying it. If I create a new procedure, I am afraid that I will have to calculate all of these values again inside the procedure to do the insert. This would nearly double the time the report takes to run – Aheinlein Jan 31 '13 at 13:11
  • @Aheinlein, one of two ways I'd recommend. Either remove the sum and do, this in the package, this may increase your performance, or just use a package to insert your values. – glh Jan 31 '13 at 19:31