Reporting and Data Entering at the same time using Microsoft Reporting Service ReportViewer embedded in an ASP.NET page
A Report is for displaying data not for editing. But what if you want to implement both functions using Microsoft SQL Server Reporting Service (SSRS) Report. In a standalone report web page from SSRS, it’s not possible unless you use report parameters for very simple data editing. But if an SSRS report is embedded in a ReportViewer of an ASP.NET page, you could use events that ReportViewer generates and mimicking data editing. I used BookmarkNavigation event.
1. Install SQL Server Data Tools – Business Intelligence if you haven’t yet. Once installed, you will find SQL Server Data Tools under SQL Server 2012 program group in start menu. ReportBuilder does not show all properties.
2. Edit Bookmark property of a report element (typically a textbox) you want to edit using SQL Server Data Tools. Since I needed three parameters to identify which score (o x in the screen shot) user clicked, I composed bookmark name something like “LTRIM(STR(Fields!ID_Audit.Value))+”:”+LTRIM(STR(Fields!ID_Item.Value))+”:2″ (example score=2). Also set the action property of the textbox to “Go to a Bookmark” as described in the capture below.
3. In the code-behind file of ReportViewer host page, add the following event handler. You have to extract three parameters from the BookmarkNavigationEventArgs and update database.
4. After database updated, refresh the ReportViewer. There will be some flickering between postbacks. (Let them) Deal with it or try some UpdatePanel (I tried without success).
5. If you pop up a modal dialog extender panel for input inside the BookmarkNavigation event handler, you can edit text data fields(comment field in the screen shot, for example) too.
Private Sub ReportViewer1_BookmarkNavigation(sender As Object, e As Microsoft.Reporting.WebForms.BookmarkNavigationEventArgs) Handles ReportViewer1.BookmarkNavigation e.Cancel = True 'We didn't want to navigate actually. We use this event just for communication between ReportViewer & WebServer 'This assumes the BookmarkId is a three-word string delimited by ":" (ID_Audit:ID_Item:Score) Dim tokens() As String = e.BookmarkId.Split(":") If tokens.Length >= 3 Then Dim ID_Audit As Integer = Val(tokens(0)) Dim ID_Item As Integer = Val(tokens(1)) Dim Score As Integer Int32.TryParse(tokens(2), Score) 'Update database ObjectDataSource_AuditDetail.UpdateParameters("ID_Audit").DefaultValue = ID_Audit ObjectDataSource_AuditDetail.UpdateParameters("ID_Item").DefaultValue = ID_Item ObjectDataSource_AuditDetail.UpdateParameters("Score").DefaultValue = Score ObjectDataSource_AuditDetail.Update() 'After database is updated, refresh the ReportViewer End If End Sub
You might ask what’s the point of using SSRS report when you are already developing an ASP.NET web application which can have rich interface for data editing. Well, sometimes you need a very quick one page implementation of data reporting which also needs relatively simple data editing and the Report was already developed (in my case, by other system analyst). Then you may want to take advantage of it.
After completion of this project, I found a similar method described in this link, which was basically using hyperlink action instead of bookmarks. I tried the same method but it bothered me that I had to include web server’s URL in the hyperlinks (with parameters in query string) of the report element because I had to maintain two versions of reports for developing and deploying. Basically it creates one more dependency you have to take care of. Performance seemed to have a slight difference too. The bookmark method seemed just a little bit faster though I am not sure.