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 Object, ByVal e 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 p 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 d 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