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:

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

Set qdf = dbsReport.QueryDefs("Query1")

Expand|Select|Wrap|Line Numbers

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?

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 
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 : )

Dynamic Crosstab Report only displays 1 row of data

Post your reply

Similar topics

Question stats

	Ask Question
Home Questions Articles Browse Topics Latest Top Members FAQ