Dynamic Crosstab Report only displays 1 row of data

[ollyb303]

Hi,

I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor.

I have one crosstab query (Query1) which has the following fields:

SPID (supervisor ID), total:group by, as row heading
Date, total:group by, as column heading
Calls handled, total:sum, as value
Date, total:where, criteria between [StartDate] and [EndDate] - this is taken from a form, [SelectDate]

When I run the query (start and end dates entered in the form), it returns the correct data (42 rows).

I have created a report based on this MS article:
http://support.microsoft.com/kb/328320

It has 9 headings in the page header, 9 columns in the detail section and 9 total boxes in the report footer, all set up as suggested in the article.

Here is the VBA behind the report:

Option Compare Database
 
   '  Constant for maximum number of columns
   Const conTotalColumns = 9
 
   '  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
   Else
      ' 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.
         rstReport.MoveNext
      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 = 2 To intColumnCount
         '  Starting at column 2 (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.
   rstReport.MovePrevious
 
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)) = 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_Close()
 
   On Error Resume Next
 
   '  Close recordset.
   rstReport.Close
 
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
 
   Dim intX As Integer
   Dim qdf As QueryDef
   Dim frm As Form
 
   '  Set database variable to current database.
   Set dbsReport = CurrentDb
   Set frm = Forms!SelectDate
   '  Open QueryDef object.
   Set qdf = dbsReport.QueryDefs("Query1")
   ' Set parameters for query based on values entered
 
   qdf.Parameters("Forms!SelectDate!StartDate") = frm!StartDate
   qdf.Parameters("Forms!SelectDate!EndDate") = frm!EndDate
 
   '  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 = 2 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.)
   rstReport.MoveFirst
 
   'Initialize variables.
   InitVars
 
End Sub

As you can see, I have not altered it much from the article.

When I run the report, it displays only the 1st row of data from the query.

I did test the code using northwind following the instructions from the MS article and it worked fine.

Can anyone help with this, I'm tearing my hair out here!!

Cheers,

Olly

[puppydogbuddy]

Olly,
Your crosstab query appears to be a totals query, and you need the details for the report, Make a copy of your query, place it in design view;then click the Greek symbol (Sigma) Off on your toolbar and change your query to a detail crosstab, and use this for your report.

[ollyb303]

Thanks for the reply, however this doesn't work.

Sigma is greyed out in design view for this query - I don't see how it would help anyway as the query is working exactly as I want it to and displays all of the data - it's the report based on the query which is not displaying all of the rows.

Query gives me 42 rows, report is showing only 1!

Anyone else?

[puppydogbuddy]

Thanks for the reply, however this doesn't work.

Sigma is greyed out in design view for this query - I don't see how it would help anyway as the query is working exactly as I want it to and displays all of the data - it's the report based on the query which is not displaying all of the rows.

Query gives me 42 rows, report is showing only 1!

Anyone else?

1. Are you using the same exact query (without modification) as the record source of your report?
2. Are you using the same grouping levels in both, the query and the report?
3. Have you reconciled the difference in total amounts between the query and the report? Can you tie that difference to anything?

[ollyb303]

There is only one query in my database at the moment and that is the one used for the report.

However, all textboxes on the report are unbound and are populated by the VBA which I've posted.

SPID    11/10/2008    12/10/2008    13/10/2008    14/10/2008    15/10/2008    16/10/2008    17/10/2008
    220    374    159    75    52    504    412
900000002                        200    
900000003    86                26    49    31
900000004        16    1            50    
900000006    2720    869    973    421        1881    1567
900000010        36                1    
900000011        163    421    1829    1169    328    
900000012    90        80    670    612    80    
900000015    1024    818    436    1385    1    140    
900000019    2470    276    143        409    1123    802
900000020    5124    2371    1664    801        2091    1608
900000021    2436    1817    1813    216    123    2504    2452
900000022            331    909    541    819    1286
900000025    66    18    879    1303    411    152    12
900000029    2602    1046    1082    653    323    1111    1077
900000033    12    15            65    247    582
900000036    4436    1681    2369    286        2126    1831
900000044            300    1691    991    500    253
900000045    1664    1107    1055    516    531        877
900000046    1960    1169    1472    322        1393    1180
900000049    3096    1777    1808    1503    516    1248    843
900000051    20    4                    
900000068    3524    1814    1889    500    239    980    700
900000069    542    349    179    357    667    251    347
900000070            370    1101    1025    526    369
900000073        287    1191    1568    273    899    591
900000074    4554    1842    2231    272        2196    2596
900000077    2406    668    192    1555    733        151
900000079    4712    1207    1511    301        2049    1700
900000082        67    1            224    104
900000084    124            37            
900000085    5388    2357    2549        353    2521    2288
900000086    6040    2762    2540        364    1783    2424
900000087    4188    2192    1972    1119    999    1559    2332
900000088    5186    3336    2136    1204    1051    3290    3870
900000089    4218    1384    1678    381    342    2577    1994
900000090    3250    2058    2156    1222        1856    1461
900000091    3022    1493    3007            3090    2273
900000092    3272    2505    2770    1977    941    1797    2090
900000093    1236    486    723    972    758    466    389
900000096                4123    3326        
900000098                1424    729        

Apologies, that doesn't look great!

That's what the query returns, the report shows only the headings, the first row of data and the total row (which is the same as the first row as this is all there is to total). Could it be related to the fact that the first row has no row heading (as these are calls handled by people who are not assigned to a supervisor)?

[puppydogbuddy]

Yes, not having a header row could pre sent some problems if you are grouping the data. Try assigning to a supervisor id = "None" or some other means of grouping the"unassigned" in your query and see what happens with your report.

[ollyb303]

That didn't work either! I ran an update query on the underlying table and updated all null SPIDs to a value (900000023) and ran the report again. Now it still shows only one row (the 900000002 row!)

I don't get this.

[puppydogbuddy]

That didn't work either! I ran an update query on the underlying table and updated all null SPIDs to a value (900000023) and ran the report again. Now it still shows only one row (the 900000002 row!)

I don't get this.

If the query works and the report does not, the problem is the report.
Did you set the required vb library reference to DAO? Does your code compile without error? Are you passing any parameters that are limiting the report to the 90000002 row? Put some breaks in your report code and examine the values of variables at different breakpoints.

[ollyb303]

Ok, so I've made some progress....

(I did have the right object library referenced, yes)

By adding the underlying table as the recordsource for the report (same as the query) I can now see all of the data from the query on the report, and the totals.

But now I have a new problem:

The last row of data is repeated over and over again for 155 pages(!) and then it totals at the bottom.

I don't understand:
a) Why the last row is repeating like this as the code specifys only to populate the text boxes if it is not the end of the recordset - surely if it is the end, it should stop;
b) Why it stops (seemingly arbitrarily) after 155 pages!

Any ideas?

[puppydogbuddy]

By adding the underlying table as the recordsource for the report (same as the query) I can now see all of the data from the query on the report, and the totals.

a) Why the last row is repeating like this as the code specifys only to populate the text boxes if it is not the end of the recordset - surely if it is the end, it should stop;
b) Why it stops (seemingly arbitrarily) after 155 pages!

Any ideas?

I have no idea based on the info you provided. I don't understand what you mean by this statement:

"By adding the underlying table as the recordsource for the report (same as the query) I can now see all of the data from the query on the report, and the totals."

Weren't you already using the query as the record source of your report?

Looking at the data, what is different about the last row, compared to the other rows?

Did you add any code of your own to the report (other than what MS provided you in the kb article)? Does your recordset loop check for EOF?

[ollyb303]

"By adding the underlying table as the recordsource for the report (same as the query) I can now see all of the data from the query on the report, and the totals."

Weren't you already using the query as the record source of your report?

No. Originally there was nothing in the recordsource property for the report - the recordset for the report was (and still is) created in code:

   Set qdf = dbsReport.QueryDefs("Query1")

   Set rstReport = qdf.OpenRecordset()

I was advised by someone else to add the table which the query is based on as the recordsource property for the report. I did this and now I can see all of the data from the query on the report.

Nothing is different about the last row, other than the fact that it is at the bottom! As this is a dynamic crosstab query, the last row of data is not always the same as it changes as the date range I'm using changes - it is not always the same Supervisor ID which comes last depending on which teams were working during the specified date range.

The only things I changed in the code were names of the query, form, controls, etc and the number of columns.

Check for EOF?

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.
         rstReport.MoveNext
 
      End If
   End If
 
End Sub

Seems to!

[puppydogbuddy]

Did you set any breakpoints and look at the values of variables at the breakpoint?

Are all of the following event procedures set for the report?
Report/Section Property Setting
------------------------------------------------------------
Report ................ OnOpen........... [Event Procedure]
Report ................ OnClose........... [Event Procedure]
Report ................ OnNoData......... [Event Procedure]
Report Header...... OnFormat.......... [Event Procedure]
Page Header........ OnFormat...........[Event Procedure]
Detail Section....... OnFormat...........[Event Procedure]
Detail Section....... OnPrint.............. [Event Procedure]
Detail Section....... OnRetreat...........[Event Procedure]
Report footer......... OnPrint...............[Event procedure]


Are you executing the following code? Try commenting it out and see what happens.

Private Sub Detail_Retreat()
 
   ' Always back up to previous record when "Detail" section retreats.
   rstReport.MovePrevious
 
End Sub

[ollyb303]

Not sure how to look at variable values at breakpoints, sorry.

All of those event procedures are covered yes.

I have tried commenting out that code and it doesn't do anything!

[puppydogbuddy]

Olly,
if you are interested in learning about the various debugging techniques available to you, see this link:

http://www.fmsinc.com/TPapers/vbacode/Debug.asp

[ollyb303]

Thanks for this. Very useful : )