I have a client that is looking to use SSRS for their reports. I have an
A2K2 Front End connected to an SQL Server back end. I was wondering if
anyone here has used SSRS with Access? What is the best way to accomplish
this, and is there any way that you've used criteria in your Access front
end to apply to the SSRS report? Any help or guidance would be greatly
appreciated.
Hi Rick,
We've called SSRS reports from Access. Basically you build the
required URL with selection parameters and call it using
FollowHyperlink. Here's a code snip to get you started.
Note: We have a utility called AppGlobal that does basic things like
retrieving configuration values. Anywhere you see AppGlobal you can
substitute your own way. Also, this application tracks information
about oil wells, so that's why the word "well" appears all over.
Public Sub OpenSQLReport(strReportName As String, strParameters As
String)
On Error GoTo Error_Handler
'Procedure used to call reports from elsewhere in the application.
'This is an initial pass at this approach and there may be more
effective methods to use.
'Anatomy of a URL call to SQL Reports:
' Sample URL:
http://rack3/ReportServer?/MyApp/We...Date=1/1/1955&rs:Command=Render&rs:Format=pdf
' Server:
http://rack3/ReportServer?
' Folder: /MyApp/
' Report Name: WellProduction
' Parameters:
&WellKey=500&EndDate=6/6/2010&StartDate=1/1/1955
' Command: &rs:Command=Render
' Format (Optional): &rs:Format=pdf
'
'Using the Application.FollowHyperlink method
' The Application.Hyperlink Method uses two parameters to make
the URL
' Address:= is the part of a URL that comes before the ? If
there is a ? (NOTE: you do not need to add the ? it is implied)
' ExtraInfo:= is the part of URL tha comes after the ? if
there is a ?
' Reporting services follows the report server with a ?/
then the folder or report
' Everything appearing after the report name is separated by
a &
Dim strFolder As String
Dim strAddress As String
Dim strExtraInfo As String
strAddress =
AppGlobal.GetSysConfigValue("SysConfig_SQLReporting_BaseURL") 'The URL
for the report server
strFolder =
AppGlobal.GetSysConfigValue("SysConfig_SQLReporting_Folder") 'The
folder name for the specific folder used to store the reports within
the report server website
strExtraInfo = "/" & strReportName & "&" & strParameters
strExtraInfo = "/" & strFolder & strExtraInfo &
"&rs:Command=Render " 'The empty space at the end of the strExtraInfo
is needed for the full url to appear in the browsers address window.
'Debug.Print strAddress & strExtraInfo
If strAddress & "" <> "" Then
Application.FollowHyperlink Address:=strAddress,
addhistory:=False, newwindow:=True, extrainfo:=strExtraInfo
Else
MsgBox prompt:="A SQL Report Base URL has not been configured
in the Customize area of this application.",
TITLE:=AppGlobal.ApplicationName, buttons:=vbInformation + vbOKOnly
End If
Exit_Procedure:
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Sub
Armen Stein
Microsoft Access MVP
www.JStreetTech.com