Solved

Can SSRS perform a database write back based on specific criteria in the report output?

Posted on 2010-09-08
11
1,727 Views
Last Modified: 2013-12-17
How can I perform an "Update database" action based on specific report output?
0
Comment
Question by:rhservan
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 33632515
Probably but what are you trying to do?  The first basic tenant of a reportng subsystem is not to modify the data.
0
 

Author Comment

by:rhservan
ID: 33632552
1. This is an environment to test 10,000 transactions a day.
2. If the datasource provides incorrect data to the report I need to flag it and send an update with the correct
information.
3. On the larger scale this will also provide debug information to the originating input to the datasource and permanently correct the problem.
0
 
LVL 1

Expert Comment

by:Ashok_Paulose
ID: 33632974
I do not think you can do any CRUD operations thru SSRS even if you are executing a stored procedure that provides the data for you report.  A simple alternate would be to run two reports:  One for the correct data and the other for incorrect data.
0
 

Author Comment

by:rhservan
ID: 33633055
Ashok,
Two reports?  I will already have the one report with the incorrect data, but unless I am able to "write back to the data source I will not have correct data for the correct data report.  PLease advise.
0
 
LVL 1

Expert Comment

by:Ashok_Paulose
ID: 33633138
What exactly do you mean by correct data?  Do you have a business rule that is violated in your SQL or in the report that you want to report as incorrect?  You cannot write back to the datasource when using SSRS.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:rhservan
ID: 33633222
Through some additonal research, although a little vague for me, but it appears the statement below can perform the update.

 based on a  parameter then I could then I could have a conditional update statement

Please advise.
0
 
LVL 1

Expert Comment

by:Ashok_Paulose
ID: 33633346
I think there is a block in the SSRS to prevent any sort of transaction that modifies data.

I may not have understood your specific issue, but in general you can use the reports to identify the incorrect data and may be suggest a fix for the incorrect data, but the reports itself cannot make changes to the source data.
0
 

Author Comment

by:rhservan
ID: 33633622
Ashok,
Thanks for your responses. To clarify and simplify:
1. I have a datasource used in my report.
2. The datasource has provided incorrect data.
3. I flag the error(s) in the report based on conditions not met.
For an example - If data is Less than 100 then okay else error.

My research has shown me that there are two solutions as shown below:

1. you can use parameters to prompt a user for input and, if a user has the necessary permissions in the database, parameters can be passed to an INSERT query expression to insert a new record.

2. Jump to url and aspx page

IIt is not clear to me how to create either of the above.  
0
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 33641906
Hi

I admit I have done the same thing in the past.... several times.... I know I shouldn't :)

The way I did that was:
In scenario one:
-- I added extra column with predefined values and then I used "jump to itself" and passed parameter values
-- for the datasource I had something like that
Update myTable
Set Value = @NewValue
WHERE ID = @MyID <-- I set MyID default value to -1 so the first update (report opened first time) doesn't do anything

and then my standard SQL.
The user (SQL or Windows User) must have update/insert access to the table.
That worked fine for me.

For more complicated data modifications I have:
-- Created URL and embedeed javascript to do pop up and display asp.net page. If you type ssrs javascript window.open in google you will fine more info.

Hope that helps.
Regards
Emil

0
 

Author Comment

by:rhservan
ID: 33699398
itcouple, it has been crazy lately, but I am beginning to work on this problem again.  I want to test your ideas above soon.  Does javascript run by default in SSRS? Any additional info at this point would be great.  I will be working on this over the weekend. Thanks for your efforts.
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33717135
Hi

Javascript is not really supported but you can embed some javascript in SSRS. If you use ReportViewer you can also 'grab' html code and run your javascript. I did that in the past to get nice tool tips ;)

Regards
Emil

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now