How to display Filter Strings in Report Header

Jabba007 · July 12th, 2007, 02:33 PM

Need help figuring out how to display filter string criteria in a report header. Right now, I have the filter string displaying foreign key id's instead of the actual descriptive text.

For example, the report header shows the following:

function_id IN (4,5) AND status_id IN (2,4)

Instead of showing the format above, the end-users are requesting the following format:

Function IN (Customer Service, Billing) AND Status IN (Open, In Progress)

Any help is greatly appreciated!

richard_peers · July 14th, 2007, 02:15 AM

This looks like a classic application for the DLookup function. This allows you to access any table and return data, as long as your criteria will specify a single row in the table. So in your case, you want something like

="Function IN (" & DLookup("[DepartmentName]", "Department", "DepartmentId = " & FirstDepartmentId) & ", " & _
DLookup("[DepartmentName]", "Department", "DepartmentId = " & SecondDepartmentId) & ") AND Status IN (" & _
DLookup("[StatusName]", "Status", "StatusId = " & FirstStatusId) & ", " & _
DLookup("[StatusName]", "Status", "StatusId = " & SecondStatusId) & ")"

as the data source for the text box on the report. Of course, you will need to substitute the correct table names, column names and variables in this code, but I hope it gives you the idea...

Jabba007 · July 16th, 2007, 11:15 AM

Thanks for your reply! Ok, so replacing the placeholders in your example with my real tables, columns, and controls, I have the following:

Code:

="Function IN (" & DLookup("[function]", "tbl_functions", "id = " & function_id) & ", " & _
DLookup("[function]", "tbl_functions", "id = " & function_id) & ") AND Status IN (" & _
DLookup("[status]", "tbl_business_reqs_status", "id = " & status_id) & ", " & _
DLookup("[status]", "tbl_business_reqs_status", "id = " & status_id) & ")"

Your response provides some valuable insight, but I'm still not sure how to apply this code. I should mention that the report is being launched from a filter form that has 4 filter options. Users can filter the report based on Requirement ID, Function, Process, and Status. Each of these fields has a multi-select listbox control on the filter form (e.g. ListRequirementId, ListFunction, ListProcess, ListStatus) that passes criteria to the report fields (e.g. id, function_id, process_id, status_id). The struggle will be trying to figure out how to leverage the DLookup function on a filter string that is generated dynamically.

If it is of any help, here is the code that is behind the filter form that launches the report:

Code:

' Create a filter and open the requirements report
Private Sub cmdViewReport_Click()
    Dim FilterString As String

    ' Add the controls to our report filter criteria
    AddFilterCriteria FilterString, ListRequirementID, "id"
    AddFilterCriteria FilterString, ListFunction, "function_id"
    AddFilterCriteria FilterString, ListProcess, "process_id"
    AddFilterCriteria FilterString, ListStatus, "status_id"

    ' Open the report
    DoCmd.OpenReport "rpt_business_reqs_with_configuration_details", acViewPreview, , FilterString
    If FilterString <> "" Then
        With Reports![rpt_business_reqs_with_configuration_details]
            .Filter = FilterString
            .FilterOn = True
            .lblFilterCriteria.Visible = True
            .txtFilterCriteria.Visible = True
            .txtFilterCriteria.Value = FilterString
        End With
    Else
        With Reports![rpt_business_reqs_with_configuration_details]
            'hide the filter text box label if you filter form criteria was selected when launching the report
            .lblFilterCriteria.Visible = False
        End With
    End If
End Sub

FYI, in case you are wondering what the AddFilterCriteria function looks like, here is the piece of code that applies to List boxes:

Code:

' This handles all of the AND statements so that any person can brainlessly pass controls to this
' function and expect them to add the correct criteria to a report filter.
Public Sub AddFilterCriteria(FilterString As String, ctrl As Control, ReportField As String)
    If FilterString = "" Then
        FilterString = ConvertToSqlFragment(ctrl, ReportField)
    ElseIf ConvertToSqlFragment(ctrl, ReportField) <> "" Then
        FilterString = FilterString & " AND " & ConvertToSqlFragment(ctrl, ReportField)
    End If
End Sub


' You pass in a report item, and you get a SQL fragment that you
' can use for report filters. For example, if you pass a listBox with
' multiple selections, it creates the SQL for all the selections.
Public Function ConvertToSqlFragment(ctrl As Control, ReportField As String)
    Dim FieldSql As String
    ' Figure out what control we're converting into a SQL fragment
    Select Case ctrl.ControlType
        Case acListBox:
        ' List boxes...a bit more complicated
            Dim SqlFragment As String
            Dim ListItem As Variant
            ' This will make an IN clause for each item in the control
            For Each ListItem In ctrl.ItemsSelected
                If SqlFragment = "" Then
                    SqlFragment = ReportField & " IN (" & QuoteIfText(ctrl.ItemData(ListItem))
                Else
                    SqlFragment = SqlFragment & ", " & QuoteIfText(ctrl.ItemData(ListItem))
                End If
            Next
            ' Close off our IN statement if there's a selected value
            If SqlFragment <> "" Then
                SqlFragment = SqlFragment & ")"
            End If
            ConvertToSqlFragment = SqlFragment

richard_peers · July 16th, 2007, 02:58 PM

Well, I'm assuming you want the filter criteria displayed on the report, most likely in a label, rather than a text box. In that case, you can assemble the string above, and then assign that to the label:

strFilterCriteria = ="Function IN (" & DLookup("[function]", "tbl_functions", "id = " & function_id) & ", " & _
DLookup("[function]", "tbl_functions", "id = " & function_id) & ") AND Status IN (" & _
DLookup("[status]", "tbl_business_reqs_status", "id = " & status_id) & ", " & _
DLookup("[status]", "tbl_business_reqs_status", "id = " & status_id) & ")"
YourReport.lblFilterCriteria.Caption = strFilterCriteria

Was that the missing piece of the jigsaw?

Richard

Jabba007 · July 16th, 2007, 11:11 PM

Hmmm, the missing piece of the jigsaw puzzle is more around the dynamic build of the filter string. In the example that I provided, there were two Functions and two Statuses selected from the filter form. However, users will be selecting additional and/or fewer Functions and/or Statuses, and they may also choose to select Requirement IDs and/or Processes in addition to or in lieu of Functions and/or Statuses.

Right now, the filter string that's passed to the report is generated dynamically using the custom AddFilterCriteria function. I lack the programming savvy to continue to leverage this dynamic build of the SQL criteria while also using the DLookup function that you've suggested. This is my dilemma...not knowing how to code the string substitution.

richard_peers · July 17th, 2007, 01:49 AM

Looks to me like you're pretty much there. All you need is an equivalent function to ConvertToSqlFragment called something like ConvertToDisplayableCriterion, and build up strDisplayableFilterCriteria in the same way as you do for FilterString (which might be better named strFilterCriteria, but that's just being picky!). So each time a control is passed to ConvertToSqlFragment, also pass it to ConvertToDisplayableCriterion to add itself in displayable format to the displayable criteria string.

Now, you *do* need to work out the correct table and column to query (via DLookup) for each control to make this work. If there is a direct correlation between each control's name and the table, that would help (you could always change things to make this true, if it's not too much impact elsewhere); the second piece of wizardry would be to put the relevant column name in the Tag property of each control. Then you can build your DLookup on the fly, using something like:

DLookup("[" & ctrl.Tag & "]", "tbl_" & Lower(Mid(ctrl.Name, 4, 99)), "id = " & ctrl.Value)

I think this will work, assuming that your controls are all named in a standard way, e.g. cmbFunctions linked to tbl_functions.

One problem is that you are somehow allowing for multiple selections (i.e. and IN() clause in the SQL), and that may be tricky to manage. You might have to build arrays for each control/table, and only build the string at the end, so you can surround the criteria with the "IN(" and ")" for each one.

Best I can do for you - have a play, you have demonstrated plenty of confidence and competence in the code you have posted, and I'm sure you can crack it using the ideas I've suggested.

Best of luck!

Richard

Jabba007 · July 17th, 2007, 11:57 AM

Dude, you're a genius! I've got it to work exactly how I wanted. Here are the new functions that I wrote to make it happen.

Code:

' this function converts the selected filter criterion into a user-friendly text string
' that can be displayed on reports for informational purposes (e.g. users want to see
' what criteria was selected for the generated report
Public Function ConvertToDisplayableCriterion(ctrl As Control, ReportField As String)
    ' Figure out what control we're converting into descriptive text
    Select Case ctrl.ControlType
        Case acListBox:
        ' List box control type
            Dim strDisplayableCriterion As String
            Dim ListItem As Variant
            ' This will make an IN clause for each item in the control
            For Each ListItem In ctrl.ItemsSelected
                If strDisplayableCriterion = "" Then
                    'strDisplayableCriterion = ReportField & " IN (" & QuoteIfText(ctrl.ItemData(ListItem))
                    strDisplayableCriterion = ReportField & " IN (" & DLookup("[" & ctrl.Tag & "]", "tbl_" & Mid(ctrl.Name, 5), "id = " & QuoteIfText(ctrl.ItemData(ListItem)))
                Else
                    strDisplayableCriterion = strDisplayableCriterion & ", " & DLookup("[" & ctrl.Tag & "]", "tbl_" & Mid(ctrl.Name, 5), "id = " & QuoteIfText(ctrl.ItemData(ListItem)))
                End If
            Next
            ' Close off our IN statement if there's a selected value
            If strDisplayableCriterion <> "" Then
                strDisplayableCriterion = strDisplayableCriterion & ")"
            End If
            ConvertToDisplayableCriterion = strDisplayableCriterion
    End Select
End Function

' This handles all of the AND statements so that any person can brainlessly pass controls to this
' function and expect them to convert the selected report filter criteria in a user-friendly
' format that can be displayed on reports for informational purposes
Public Sub AddDisplayableFilterCriteria(strDisplayableFilterString As String, ctrl As Control, ReportField As String)
    If strDisplayableFilterString = "" Then
        strDisplayableFilterString = ConvertToDisplayableCriterion(ctrl, ReportField)
    ElseIf ConvertToDisplayableCriterion(ctrl, ReportField) <> "" Then
        strDisplayableFilterString = strDisplayableFilterString & " AND " & ConvertToDisplayableCriterion(ctrl, ReportField)
    End If
End Sub

After writing those two new functions, I call the second one, AddDisplayableFilterCriteria, from the report filter form under the On Click event of the View Report command button. Here is the call:

Code:

Dim strDisplayableFilterString As String
AddDisplayableFilterCriteria strDisplayableFilterString, ListBusiness_reqs, "Business Requirement Number"
AddDisplayableFilterCriteria strDisplayableFilterString, ListFunctions, "Function"
AddDisplayableFilterCriteria strDisplayableFilterString, ListProcesses, "Process"
AddDisplayableFilterCriteria strDisplayableFilterString, ListBusiness_reqs_status, "Status"

Lastly, I set the Value property of the text box (I decided to use a text box instead of a label) on the report to strDisplayableFilterString after opening the report:

Code:

With Reports![rpt_business_reqs_with_configuration_details]
    .txtFilterCriteria.Visible = True
    .txtFilterCriteria.Value = strDisplayableFilterString
End With

Many thanks for all your insight!!

Jabba007 · July 17th, 2007, 12:07 PM

Richard, do you have any insight on another issue I'm having regarding how to pass filter criteria to a sub report? See http://p2p.wrox.com/topic.asp?TOPIC_ID=62883