Geeks With Blogs
Practical .NET

50% of my job is Web Development. The other 50% is managing a 2GB SQL database and web app (with about 1500 users). Part of that 50% is writing SQL Reporting reports. Up until the other day, I was having my users log into the web interface of S/RS and get their reports that way. I was talking to some other developers who use the same system, and they said that you can Proxy into reports using code.

I dug into this a little bit, and in about 2 hours, whipped up a little VB.NET page that did the trick. All it needed was a ReportViewer control, a small helper class, and about 3 more lines of code. Of course, I spruced it up a little bit, entering parameters based on Query String variables, etc. But, here's the gist of what I had put together (with a lot of online help.)

1    Imports System.Collections
2   
Imports System.ComponentModel
3   
Imports System.Data
4   
Imports System.Drawing
5   
Imports System.Web
6   
Imports System.Web.SessionState
7   
Imports System.Web.UI
8   
Imports System.Web.UI.WebControls
9   
Imports System.Web.UI.HtmlControls
10  
Imports Microsoft.Web.UI.WebControls
11  
Imports System.Net
12  
Imports System.Security.Principal
13  
Imports Microsoft.Reporting.WebForms
14  
15  
16  Partial 
Class _Default
17      
Inherits System.Web.UI.Page
18      
Public ReadOnly Property ReportServer() As String
19          Get
20              Return 
"http://reportserver/reportserver"
21          
End Get
22      End Property
23  
24      Public ReadOnly Property 
ReportPath()
25          
Get
26              Return 
"/Proxy"
27          
End Get
28      End Property
29  
30      Protected Sub 
Page_Load(ByVal sender As ObjectByVal As System.EventArgs) Handles Me.Load
31  
32          
If Not ispostback Then
33              
ReportViewer1.ServerReport.ReportServerCredentials = _
34                          
New MyReportServerCredentials()
35              ReportViewer1.ServerReport.ReportPath = ReportPath & 
CStr(Request.QueryString("rpt")).Replace("%20", " ")
36              
Dim As New System.Collections.Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)
37              
38              
'Check to see if the report has a requirement for a person ID.
39              
Dim As Microsoft.Reporting.WebForms.ReportParameterInfoCollection = ReportViewer1.ServerReport.GetParameters()
40              
Dim di As Microsoft.Reporting.WebForms.ReportParameterInfo
41              
For Each di In d
42                  
If di.Name.ToLower = "calendarid" Then
43                      If Not 
IsNothing(Request.QueryString("CalendarId")) Then
44                          
p.Add(New Microsoft.Reporting.WebForms.ReportParameter("CalendarId", Request.QueryString("CalendarId")))
45                      
End If
46                  End If
47              Next
48  
49              
ReportViewer1.ServerReport.SetParameters(p)
50              ReportViewer1.PromptAreaCollapsed = 
False
51          Else
52              
ReportViewer1.PromptAreaCollapsed = True
53          End If
54          
55      End Sub
56  End Class
57  
58  
<Serializable()> _
59  
Public NotInheritable Class MyReportServerCredentials
60      
Implements IReportServerCredentials
61  
62      
Public ReadOnly Property ImpersonationUser() As WindowsIdentity _
63              
Implements IReportServerCredentials.ImpersonationUser
64          
Get
65  
66              
'Use the default windows user.  Credentials will be
67              'provided by the NetworkCredentials property.
68              
Return Nothing
69  
70          End Get
71      End Property
72  
73      Public ReadOnly Property 
NetworkCredentials() As ICredentials _
74              
Implements IReportServerCredentials.NetworkCredentials
75          
Get
76  
77              
'Read the user information from the web.config file.  
78              'By reading the information on demand instead of storing 
79              'it, the credentials will not be stored in session, 
80              'reducing the vulnerable surface area to the web.config 
81              'file, which can be secured with an ACL.
82  
83              'User name
84              
Dim userName As String = _
85                  ConfigurationManager.AppSettings("MyReportViewerUser")
86  
87              
If (String.IsNullOrEmpty(userName)) Then
88                  Throw New Exception
("Missing user name from web.config file")
89              
End If
90  
91              
'Password
92              
Dim password As String = _
93                  ConfigurationManager.AppSettings("MyReportViewerPassword")
94  
95              
If (String.IsNullOrEmpty(password)) Then
96                  Throw New Exception
("Missing password from web.config file")
97              
End If
98  
99              
'Domain
100              
Dim domain As String = _
101                  ConfigurationManager.AppSettings("MyReportViewerDomain")
102  
103              
If (String.IsNullOrEmpty(domain)) Then
104                  Throw New Exception
("Missing domain from web.config file")
105              
End If
106  
107              Return New 
NetworkCredential(userName, password, domain)
108  
109          
End Get
110      End Property
111  
112      Public Function 
GetFormsCredentials(ByRef authCookie As Cookie, _
113                                          
ByRef userName As String, _
114                                          
ByRef password As String, _
115                                          
ByRef authority As String) _
116                                          
As Boolean _
117              
Implements IReportServerCredentials.GetFormsCredentials
118  
119          authCookie = 
Nothing
120          
userName = Nothing
121          
password = Nothing
122          
authority = Nothing
123  
124          
'Not using form credentials
125          
Return False
126  
127      End Function
128  
129  End Class

Posted on Thursday, April 26, 2007 7:11 AM ASP.NET , VB.NET | Back to top


Comments on this post: SQL Reporting Services

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Kyle | Powered by: GeeksWithBlogs.net