Parameters in ADP Reports
Reporting is what matters to the top level management. Without meaningful, easily extensible and modifiable reports, none of your collected data means anything to your organization.
MS Access provides a robust, full featured reporting environment that integrates extremely well with most relational database management systems. I find ADP particularly appealing.
With Access 2002(XP) and above, ADP reports can now bind to stored procedures at run time! This feature makes dynamic parameter passing a breeze. Let me explain this concept with an example:
Let’s say we want to generate a report that takes in ‘BeginDate’ and ‘EndDate’ as parameters.
1. Create your Stored procedure to handle the business logic:
CREATE PROCEDURE [dbo].[Rpt_Employees]
(@BeginDate datetime, @EndDate datetime)
SELECT LastName, FirstName
where Hire_Date between @BeginDate and @EndDate
2. In your ADP, create a simple form with two text boxes (for the begin and end dates) and a button to launch the report.
3. Design a report with the required fields.
Here is a tip.. I always find it more convenient to use the Access report wizard to initially create and position the fields for me. In order to ‘bind’ the fields, create a ‘VIEW’ with the same logic but without the parameters and use that view as the recordsource for your report. Once you are satisfied with the look of the report, get rid of the recordsource and the view!
4. In the report properties window, add code that dynamically builds the recordsource in the ‘Open’ event as shown below:
Private Sub Report_Open(Cancel As Integer)
Dim Str As String
Dim BeginDate As String
Dim EndDate As String
Dim strRecordSource As String
BeginDate = Trim([Forms]![Employee_Form]!txtFromDate)
EndDate = Trim([Forms]![ Employee_Form]!txtToDate)
strRecordSource = “exec [dbo].[ Rpt_Employees] ” & “‘” &amp;amp;amp;amp;amp;amp; BeginDate & “‘” & “,” & “‘” & EndDate & “‘”
Me.RecordSource = strRecordSource
And you are done 🙂
There is a property known as ‘ServerFilter’ which is supposed to do the same thing but I find this method much more flexible and amenable to coding.
This was a very simple example. Let me show you the true power and flexibility of dynamic binding of a stored procedure to a report.
What if you want to pass ‘variable’ parameters? For example, let’s say the user is allowed to select ‘Multiple’ values from a list box.
SQL Server Stored procedures do not have an ‘array’ data structure to aid in this… but there is a clever workaround.
Pay close attention to the following procedure:
Public Function BuildCSVFromList(lst As ListBox) As String
Dim prov As Variant
Dim retVal As String
Dim lstVal As String
For Each prov In lst.ItemsSelected
lstVal = lst.ItemData(prov)
retVal = “‘” & lstVal & “‘” & “,” & retVal
‘if the last char is a , (comma) get rid of it!
retVal = Trim(retVal)
If (Right(retVal, 1) = “,”) Then
retVal = Left(retVal, Len(retVal) – 1)
BuildCSVFromList = retVal
It takes a listbox control as input parameter and generates a comma separated string
Now, when you code your stored procedure, accept this comma separated list and ‘emit’ the final SQL statement to include an ‘IN’ clause like so:
CREATE PROCEDURE dbo.Employee_Params (@ProvList varchar(500)=’’)
declare @sql varchar(1500)
declare @whereList varchar(500)
declare @whereClause varchar(500)
set @sql = ‘SELECT Last_Name, First_Name FROM Employee_Table’
if (@ProvList !=’ ‘)
set @whereList = ‘ (Employee_SSN IN (‘ + @ProvList + ‘))
set @whereClause = ltrim(rtrim(@whereList))
set @sql = @sql + ‘ WHERE ‘ + @whereClause
Now, within your form, you need to build the CSV values
If lstProviders.ItemsSelected.Count > 0 Then
strProviders = BuildCSVFromList(lstProviders)
‘store this in a hidden form text box for use from within the report
txtParamProviders.Value = Chr(34) & strProviders & Chr(34)
In the reports ‘Open’ method, create the exec sp call at runtime and bind it to the reports recordsource property
ProvList = Trim([Forms]![Employee_Report]!txtParamProviders)
If (ProvList “”) Then
strRecordSource = ProvList
strRecordSource = “‘ ‘”
Me.RecordSource = “exec [dbo].[Employee_Params] ” & strRecordSource
And you are done! Exetnsions to this idea are only limited by your imagination.. Happy reporting!