Dynamic Sorting and Grouping on a report RRS feed

  • Question

  • I changed this post since I changed the way the report parameters are set up.

    The parameters for my report are a drop-down selection where the user can choose 'Date loggged' or 'Deposit date' (these are separate columns in the input query) and a date range (from date and to date) and a drop-down for Type of Action (also a column in the input query).

    The report is to be grouped by the chosen date and type of action. Sub-totals are needed for each date and type of action.

    I have all of the above working but for only one date choice. I cannot figure out how to make it sort/group for either chosen date.

    Also, here is the tricky part. At the end of the report I want to print totals for each type of action across all dates. Seems like I might need a sub-report. How would I set that up? Should I create a separate query to do the grouping and totalling by type of action?

    Monday, September 21, 2015 10:00 PM

All replies

  • Use calculated fields for grouping and sorting.

    If need more then post your field names and parameter input object names.

    Build a little, test a little

    Tuesday, September 22, 2015 1:05 AM
  • I believe that the first technical challenge does not really involve the report object, but rather setting up VBA code that will result in an sql statement (query) that delivers the correct record set.  Once that is achieved - then the report object finalizes the sorting/grouping/totaling. 2 separate steps.

    Because you have optional fields - one must use VBA logic to determine which sql statement should be used or to build the appropriate sql statement.  This code would probably be located in the user's form at the command button that is to launch the report.  It requires trial & error testing - you'll want to check around for examples of building and running an sql statement from VBA.

    Once you have always the correct record set - then the report object needs to be managed; it really does not lend itself to dynamic grouping.  One can dynamically sort and one can dynamically make visible some fields.  For variable grouping I think one would need to create separate report objects - and then select which is used via vba logic. 

    This is at least my overview sum of the situation.  I'll be interested in other's inputs.

    Tuesday, September 22, 2015 12:40 PM