Thoughts on Dynamic Reporting

[swalsh84]

I have form that has options for a report to be pulled. The goal would be to have one report that does everything so that I don't have to create seperate queries and reports for each and every scenario. I thought about changing the record set based on a query that is built on the options selected on the form, but I'm not sure how to accomplish this. I don't even know if this is the best way to accomplish what I'm trying to do.

Here's the bottom line:

1. User selects a case summary report from three different options. This is the initial filter.
2. User selects parameters from form to further filter or group data.
3. Query is built to pull necessary data.
4. Report is pulled and displayed.
5. User selects parameters from form to further filter or group data.
6. Query is built to pull necessary data.
7. Report is refreshed with updated parameters.

I attached a screen shot of my form to give you a better idea of what I'm trying to accomplish.
TIA

[pbaldy]

As long as you're just talking about allowing the user to filter based on any combination of those items, what you describe is not too difficult. If you want them to be able to change the actual structure of the report (different fields being included, etc), it can get pretty complicated. You can actually build a query, but I'd be more likely to build a wherecondition that can be used with OpenReport to restrict the records.

Rather than the two buttons at the bottom, you'd just have one to open the report. A report in Preview mode is static and can't be refreshed (2007 introduced a new view that can be).

[swalsh84]

I already have one report that does something similar using the filter option of the report. The trouble that I've been experiencing with this particular one is that I need the summaries in crosstab format. The report fields won't change as you suggested above, but whether the user wants average turn times or counts for the days outstanding or number cases in a closed status is where I'm running into issues. It seems that loading a cross tab query and then trying to filter it from the report properties window doesn't yield the results for which I am looking. Here's sample of the query that I'd like to build. I'll add the variables later for grouping and such, but this thing has been wracking my brain for 3 days now.

 
    Select Case Me.fraCommonRpts
        Case 1
 
            sqlSTMT = "TRANSFORM Avg(qry_Closed_Summary.DaysOutstanding) AS AvgOfDaysOutstanding SELECT qry_Closed_Summary.CompanyCode, Avg(qry_Closed_Summary.DaysOutstanding) AS [Total Of DaysOutstanding] FROM qry_Closed_Summary GROUP BY qry_Closed_Summary.CompanyCode PIVOT Format([ClosedDate],'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');"
 
            DoCmd.RunSQL sqlSTMT
        Case 2
        Case 3
    End Select