Link to home
Create AccountLog in
Avatar of AmadeusC27
AmadeusC27Flag for United States of America

asked on

Grouped Totals in a Dynamic Crosstab Query Report (MS Access 2003)

I have successfully created a dynamic crosstab report using the code provided by Microsoft (my modified code attached below).  The report runs great - but I need to add Group Footer totals and I can't find how to do that dynamically any where.

I'm also attaching a copy of my report as it stands now - I need totals on the location and dept footers.  Can anyone help me out? sample.doc
Option Compare Database

   '  Constant for maximum number of columns EmployeeSales query would
   '  create plus 1 for a Totals column. Here, you have 9 employees.
   Const conTotalColumns = 11

   '  Variables for Database object and Recordset.
   Dim dbsReport As DAO.Database
   Dim rstReport As DAO.Recordset

   '  Variables for number of columns and row and report totals.
   Dim intColumnCount As Integer
   Dim lngRgColumnTotal(1 To conTotalColumns) As Long
   Dim lngReportTotal As Long

Private Sub InitVars()
   Dim intX As Integer

   ' Initialize lngReportTotal variable.
   lngReportTotal = 0
   ' Initialize array that stores column totals.
   For intX = 1 To conTotalColumns
      lngRgColumnTotal(intX) = 0
   Next intX

End Sub

Private Function xtabCnulls(varX As Variant)
   ' Test if a value is null.
   If IsNull(varX) Then
      ' If varX is null, set varX to 0.
      xtabCnulls = 0
      ' Otherwise, return varX.
      xtabCnulls = varX
   End If

End Function

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   ' Put values in text boxes and hide unused text boxes.
   Dim intX As Integer
   '  Verify that you are not at end of recordset.
   If Not rstReport.EOF Then
      '  If FormatCount is 1, put values from recordset into text boxes
      '  in "Detail" section.
      If Me.FormatCount = 1 Then
         For intX = 1 To intColumnCount
            '  Convert Null values to 0.
            Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
         Next intX
         '  Hide unused text boxes in the "Detail" section.
         For intX = intColumnCount + 2 To conTotalColumns
            Me("Col" + Format(intX)).Visible = False
         Next intX

         '  Move to next record in recordset.
      End If
   End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   Dim intX As Integer
   Dim lngRowTotal As Long

   '  If PrintCount is 1, initialize rowTotal variable.
   '  Add to column totals.
   If Me.PrintCount = 1 Then
      lngRowTotal = 0
      For intX = 5 To intColumnCount
         '  Starting at column 5 (first text box with crosstab value),
         '  compute total for current row in the "Detail" section.
         lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

         '  Add crosstab value to total for current column.
         lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
      Next intX
      '  Put row total in text box in the "Detail" section.
      Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
      '  Add row total for current row to grand total.
      lngReportTotal = lngReportTotal + lngRowTotal
   End If
End Sub

Private Sub Detail_Retreat()

   ' Always back up to previous record when "Detail" section retreats.

End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
   Dim intX As Integer
   '  Put column headings into text boxes in page header.
   For intX = 1 To intColumnCount
      Me("Head" + Format(intX)).Caption = rstReport(intX - 1).Name
   Next intX

   '  Make next available text box Totals heading.
   Me("Head" + Format(intColumnCount + 1)).Caption = "Totals"

   '  Hide unused text boxes in page header.
   For intX = (intColumnCount + 2) To conTotalColumns
      Me("Head" + Format(intX)).Visible = False
   Next intX

End Sub

Private Sub Report_Close()
   On Error Resume Next

   '  Close recordset.
End Sub

Private Sub Report_NoData(Cancel As Integer)

   MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
   Cancel = True

End Sub

Private Sub Report_Open(Cancel As Integer)

   '  Create underlying recordset for report using criteria entered in
   '  EmployeeSalesDialogBox form.
   Dim intX As Integer
   Dim qdf As QueryDef
   Dim frm As Form

   '  Set database variable to current database.
   Set dbsReport = CurrentDb
   Set frm = Forms!frmReportChoices
   '  Open QueryDef object.
   Set qdf = dbsReport.QueryDefs("qryPayrollData2_Crosstab")
   ' Set parameters for query based on values entered
   ' in EmployeeSalesDialogBox form.
   qdf.Parameters("Forms!frmReportChoices!txtStartPayDate") _
     = frm!txtStartPayDate
   qdf.Parameters("Forms!frmReportChoices!txtLastPayDate") _
     = frm!txtLastPayDate

   '  Open Recordset object.
   Set rstReport = qdf.OpenRecordset()
   '  Set a variable to hold number of columns in crosstab query.
   intColumnCount = rstReport.Fields.Count
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
   Dim intX As Integer

   '  Put column totals in text boxes in report footer.
   '  Start at column 2 (first text box with crosstab value).
   For intX = 5 To intColumnCount
      Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
   Next intX

   '  Put grand total in text box in report footer.
   Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

   '  Hide unused text boxes in report footer.
   For intX = intColumnCount + 2 To conTotalColumns
      Me("Tot" + Format(intX)).Visible = False
   Next intX

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

   '  Move to first record in recordset at the beginning of the report
   '  or when the report is restarted. (A report is restarted when
   '  you print a report from Print Preview window, or when you return
   '  to a previous page while previewing.)

   'Initialize variables.

End Sub

Open in new window

Avatar of Dezzar82
Flag of Australia image

is there a reason why you are creating the report dynamically?  

Is it a report that will always be needed?  It may just be easier to build and save the report in the format you desire, but depends on your answer to the above.
Avatar of AmadeusC27


The reason I created it dynamically is because it's based on a crosstab query which is driven by user input on a form.  The user enters a start and end date which determines the columns for the report (4 to 6 week end dates).

The data is weekly payroll data and the report will be run monthly.

Does that help?
Perhaps try something like:
a) save the report with the maximum number of columns (6) and populate it with the data the user selects.  Unused columns should just display as blank or zero.

b) or try saving the report, with the groupings and subtotals as you wanted and then try dynamically adjusting the columns using some of the code above
I think maybe my quesiton isn't clear enough.  Everything is working great on my report - I just want to add grouping totals at the Location and Dept level.  

Is this impossible to do??
Avatar of Jeffrey Coachman
"Is this impossible to do??"
With your fancy, schamcy "Dynamic Crosstab Report", probably so...
(or at the very least, this might turn out to be fairly complex)
The issue here is that once you use this complex code, you create a "Report". Then you must be well versed in VBA and report design to modify it.
Perhaps if you posted a sample of your database, I can have a crack at this.
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Delete any objects that do not relate directly to the issue.
5. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
6. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
7. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
8. Compile the code. (From the database window, click: Debug-->Compile)
9. Run the compact/Repair utility.
10. Remove any Passwords and/or security.
11. Post explicit steps to replicate the issue.
12. Test the database before posting.

In other words, a database that we can easily open and immediately see the issue.

On the other hand, if you could get by with a standard report, this is realtively simple
If it were me, I would just create a Standard Grouped report and select "Sum" for "Summary Option" for the fields you want.
This may not be ase "Condensed" as a crosstab query, but it will give you subtotals easily.
(Without all the trouble of creating a fancy schmancy "Dynamic Crosstab Report", that won't give you subtotals anyway.)

What you may also be able to do (If the Fields will always be the same) is to send the crosstab query results to a temp Table, then create a "Standard" report from this temp table.
(select "Sum" for "Summary Option" for the fields you want.)
This will easily produce the subtotals you are requesting.

Sample attached illustrating both approaches

OK...thanks Jeff for taking a look at this for me.  I'm attaching the database - I've stripped the data down to the bare minimum for purposes of giving you a sample.  There would be data for every week normally.   For the start and end date on the form, just enter 11/05/2010 and 11/19/2010.  When you click run report, the code runs and creates the report with the columns designated by the chosed start and end dates (up to six weeks worth with the real data).  My issue is that I want to see the totals for each department and location on this report as well.  The grand total for both locations and all departments is already included.

Thanks again! Sample.mdb
Avatar of Luke Chung
Luke Chung
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Is there really no VBA code to handle what I'm trying to do?
Why would you want to write code when a solution exists that doesn't require any code?
Hi Luke,

Unless I'm missing something, your solution doesn't give me the report I'm looking for - for a couple of reasons:

1.)  My report needs to have group level totals (for location and dept)
2.)  My report needs to have a dynamic number of columns.   If the user selects a date range that includes 6 weeks of data, the report will show the six weeks of data.  If the user selects a date range that includes 4 weeks of data, the report will show the four weeks of data - not 6 columns with two of the columns being blank.

Please let me know if I'm misinterpreting your answer.

1, The group levels is not an issue because that's just formatting the report and is independent of the data source.

2. The dynamic columns is a bit of a difference and just depends on providing the start and end dates. in my example, I think you just provide the starting date and it grabs the next twelve months.The query could be changed to accept two parameters for the start and end dates and grab limit the data to just those dates. The report would still have the additional columns but no data would appear which dhouldn't make much of a difference.
OK, you've almost got me convinced.  I've started reworking my entire project based on your suggestion and I'm stumped at how to translate your mm-yyyy formatted columns into my mm-dd-yyyy formatted columns.

Your formula below assumes data with only a month/year input.  My input is mm-dd-yyyy (weekly data).

Expr1: Year([OrderDate])*12+Format([OrderDate],"mm")-(Year([StartDate])*12+Format([StartDate],"mm"))+1

What should I replace the above formula with to get my crosstab query to have set field names (based on the user start and end dates)?

Nevermind!!  I figured it out!

Thanks, Luke!