Parameters in ADP Reports

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)
AS
SELECT LastName, FirstName
FROM dbo.Employee_Table
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] ” & “‘” & BeginDate & “‘” & “,” & “‘” & EndDate & “‘”

Me.RecordSource = strRecordSource
End Sub

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
Next
‘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)
End If
BuildCSVFromList = retVal
End Function

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)=’’)
AS
begin

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))

if (@whereClause!=”)
begin
set @sql = @sql + ‘ WHERE ‘ + @whereClause
end
exec( @sql)
end

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)
End If

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
Else
strRecordSource = “‘ ‘”
End If

Me.RecordSource = “exec [dbo].[Employee_Params] ” & strRecordSource

And you are done! Exetnsions to this idea are only limited by your imagination.. Happy reporting!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s