How to Build and do Grouping in Ad-hoc Reports

[StudentTeacher]

Hi Programmers,



Users would like to be able to build their own reports using a combination of unbound listboxes on a report-selection form. Yikes! Currently, users can only select client names, an option group, and starting and ending dates, and then click on one of many cmdbuttons to generate one of many pre-made reports (I included example code below).

I'm not sure how to approach this--not sure how to build a WHERE string from so many listboxes, and not sure how to apply it to one or more reports. How could sorting and grouping be established?

If there are 10 different listboxes, is there a way to rank these in order of importance and in order of grouping for a report? For instance, use textboxes next to each listbox in which a user can type a rank such as "1", "2", "3",..."10", and the report will then group these accordingly?

For instance, in addition to my unbound client listbox named lstClients, there could be potentially 9 other listboxes used:
lstDorms
lstCategory
lstStates
lstShifts
lstInterventions
lstBehaviors
lstReasons
lstManagers
lstStaff

In a report, users may just want to look at the number of interventions that occurred during a certain work shift involving a certain manager.
Or, users may want to output an all-inclusive report grouping data by dormitories, category, state, work shift, intervention, etc.

Here's background info: most of the tables have been set up with many-to-many relationships. For instance, 1 client could show >1 behavior in an intervention, and the intervention could last for >1 shift, and the intervention could involve >1 manager and/or >1 staff person.

THANKS

Here's an example of current report-generating code.

 
'Thanks to Browne et al. for the code.
Private Sub cmdViewClientReport_Click()
On Error GoTo Err_cmdViewClientReport_Click
Dim stDocName As String
Dim varItem As Variant
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strField = "txtDatePart"
Dim strClientList As String
Dim strAnd As String
 
For Each varItem In lstClients.ItemsSelected
strClientList = strClientList & "," & lstClients.ItemData(varItem)
Next varItem
 
strClientList = Mid(strClientList, 2)
If Len(strClientList) > 0 Then
strClientList = "ClientID IN (" & strClientList & ")"
End If
 
If Me.optNames = 1 Then
    stDocName = "rptDateDormsandClientsFull"
ElseIf Me.optNames = 2 Then
    stDocName = "rptDateDormsandClients"
End If
 
If IsNull(Me.txtStartDate) Then
   If Not IsNull(Me.txtEndDate) Then
  strWhere = strField & " <= " _
  & Format(Me.txtEndDate, conDateFormat)
   End If
Else
If IsNull(Me.txtEndDate) Then
   strWhere = strField & " >= " _
   & Format(Me.txtStartDate, conDateFormat)
 Else
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
 End If
End If
 
If Len(strWhere) > 0 And Len(strClientList) > 0 Then
   strAnd = " AND "
End If
 
strWhere = strWhere & strAnd & strClientList
 
DoCmd.OpenReport stDocName, acPreview, , strWhere
 
Exit_cmdViewClientReport_Click:
Exit Sub
Err_cmdViewClientReport_Click:
  If Err.Number = 2501 Then
Resume Next
  Else
MsgBox Err.Description
Resume Exit_cmdViewClientReport_Click
  End If
End Sub

Thanks

[NTC]

Well I guess my contribution is not what you seek as this is simply not an approach I would take. What you can do is make a report designed with a record source and all fields; then toggle off visibility of some fields dynamically during the OnFormat event.

If different record sources are needed - one would creat a generic Report as a conceptual 'template' and then copy/make a set of similar but separate reports - - and fire the correct one based on the user's data.

The approach of having a report dynamically defined in code is a complex - and db design should be goal oriented using the least complex approach possible. Perhaps this helps a little.

[StudentTeacher]

Thanks for your reply, NTC.

It helps to have second opinions. I did think of your #1, and then thought that setting the visibility of, say, more than half of the fields would leave too many gaps or white spaces in the reports--unless there was a way to code null controls to collapse to super short widths and moving the non-null fields to the left, basically auto repositioning all of the fields to look nice.

I was thinking more along the lines of your #2. Yes, I could basically limit the number and types of reports to make it easier (this is probably what I'll have to do). Actually, I've never had to build where and order and other sql strings from selections in 1 or more listboxes.

I was just wondering if it would be possible to build the detail section of a report--size, position, and order all the controls--from selections a user makes in list boxes on a report-picker form. Probably hard to do.

Thanks.