Hi Everyone,

I've built a crosstab report in access 2003, with dynamic column headers following solutions9.mdb sample. The report is displaying products as rows, and days as column. Basically for the dates and customer selected, the report is aggregating the total amount per product and per day (cf screenshot).

What i'm trying to do now, is to display 2 extra lines just under each product row:
1st line would aggregate quantity purchased
2nd line the product unit price

In short, each product would have 3 lines: total amount, quantity, price per unit. And at the end of each column and row, the grand total would be displayed (except for price per unit).

The following code is displaying : total amount per product per day and grand total of each row and column. It is also creating dynamic column heading (date range selected) - most of the code is from the solutions9.mdb sample:

Code:
Option Compare Database   'Use database order for string comparisons.
Option Explicit

'  Constant for maximum number of columns CustomerSales query would
'  create plus 1 for a Totals column.
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 Double
Dim lngReportTotal As Double

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
 '  Place values in text boxes and hide unused text boxes.
    
    Dim intX As Integer
    '  Verify that not at end of recordset.
    If Not rstReport.EOF Then
        '  If FormatCount is 1, place 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 detail section.
            For intX = intColumnCount + 2 To conTotalColumns
                Me("Col" + Format(intX)).Visible = False
            Next intX

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

Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
    
    Dim intX As Integer
    Dim lngRowTotal As Double

    '  If PrintCount is 1, initialize rowTotal variable.
    '  Add to column totals.
    If Me.PrintCount = 1 Then
        lngRowTotal = 0
        
        For intX = 2 To intColumnCount
            '  Starting at column 2 (first text box with crosstab value),
            '  compute total for current row in 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
        
        '  Place row total in text box in 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 Detail1_Retreat()

    ' Always back up to previous record when detail section retreats.
    rstReport.MovePrevious

End Sub

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 Sub PageFooter2_Format(Cancel As Integer, FormatCount As Integer)

End Sub

Private Sub PageHeader0_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)) = rstReport(intX - 1).Name
    Next intX

    '  Make next available text box Totals heading.
    Me("Head" + Format(intColumnCount + 1)) = "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_Activate()
   
    '  Hide built-in Print Preview toolbar.
    DoCmd.ShowToolbar "Print Preview", acToolbarNo
   
End Sub

Private Sub Report_Close()
    
    On Error Resume Next

    '  Close recordset.
    rstReport.Close
    
End Sub

Private Sub Report_Deactivate()
    
    '  Show built-in Print Preview toolbar.
    DoCmd.ShowToolbar "Print Preview", acToolbarWhereApprop

End Sub

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
    rstReport.Close
    Cancel = True
End Sub

Private Sub Report_Open(Cancel As Integer)

    '  Create underlying recordset for report using criteria entered in
    '  CustomerSalesDialogBox form.
    
    Dim intX As Integer
    Dim qdf As QueryDef
    Dim frm As Form
            
    '   Don't open report if CustomerSalesDialogBox form isn't loaded.
    If Not (IsLoaded("CustomerSalesDialogBox")) Then
        Cancel = True
        MsgBox "To preview or print this report, you must open " _
        & "CustomerSalesDialogBox in Form view.", vbExclamation, _
        "Must Open Dialog Box"
        Exit Sub
    End If

    '  Set database variable to current database.
    Set dbsReport = CurrentDb
    Set frm = Forms!CustomerSalesDialogBox
    '  Open QueryDef object.
     Set qdf = dbsReport.QueryDefs("Customer Sales Query")
   ' Set parameters for query based on values entered
   ' in CustomerSalesDialogBox form.
   qdf.Parameters("Forms!CustomerSalesDialogBox!BeginningDate") _
     = frm!BeginningDate
   qdf.Parameters("Forms!CustomerSalesDialogBox!EndingDate") _
     = frm!EndingDate
   qdf.Parameters("Forms!CustomerSalesDialogBox!CustomerName") _
        = frm!CustomerName

    '  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 ReportFooter4_Print(Cancel As Integer, PrintCount As Integer)
    
    Dim intX As Integer

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

    '  Place 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 ReportHeader3_Format(Cancel As Integer, FormatCount As Integer)

    '  Move to first record in recordset at beginning of report
    '  or when 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.)
    rstReport.MoveFirst

    'Initialize variables.
    InitVars

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
    Else
        ' Otherwise, return varX.
        xtabCnulls = varX
    End If

End Function
This is the query (Customer Sales Query) :


Code:
PARAMETERS [Forms]![CustomerSalesDialogBox]![BeginningDate] DateTime, [Forms]![CustomerSalesDialogBox]![EndingDate] DateTime, [Forms]![CustomerSalesDialogBox]![CustomerName] Text ( 255 );
TRANSFORM Sum([Order Details Extended].ExtendedPrice) AS [Order Amount]
SELECT [Order Details Extended].bread_name AS [Bread Name]
FROM [Order Details Extended] INNER JOIN TBL_ORDER ON [Order Details Extended].order_id = TBL_ORDER.order_id
WHERE (((TBL_ORDER.order_date) Between [Forms]![CustomerSalesDialogBox]![BeginningDate] And [Forms]![CustomerSalesDialogBox]![EndingDate]) AND ((TBL_ORDER.customer_id)=[Forms]![CustomerSalesDialogBox]![CustomerName]))
GROUP BY [Order Details Extended].bread_name
ORDER BY [Order Details Extended].bread_name, TBL_ORDER.order_date
PIVOT TBL_ORDER.order_date;

The view "Order Details Extended" has the quantity ordered and the unit price.

Please advise on the best way to add total quantity per product and unit price.

Thanks a lot!!
SB