By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

Dynamic Crosstab Report only displays 1 row of data

ollyb303
P: 74
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
  1. Option Compare Database
  2.  
  3.    '  Constant for maximum number of columns
  4.    Const conTotalColumns = 9
  5.  
  6.    '  Variables for Database object and Recordset.
  7.    Dim dbsReport As DAO.Database
  8.    Dim rstReport As DAO.Recordset
  9.  
  10.    '  Variables for number of columns and row and report totals.
  11.    Dim intColumnCount As Integer
  12.    Dim lngRgColumnTotal(1 To conTotalColumns) As Long
  13.    Dim lngReportTotal As Long
  14.  
  15. Private Sub InitVars()
  16.  
  17.    Dim intX As Integer
  18.  
  19.    ' Initialize lngReportTotal variable.
  20.    lngReportTotal = 0
  21.  
  22.    ' Initialize array that stores column totals.
  23.    For intX = 1 To conTotalColumns
  24.       lngRgColumnTotal(intX) = 0
  25.    Next intX
  26.  
  27. End Sub
  28.  
  29.  
  30. Private Function xtabCnulls(varX As Variant)
  31.  
  32.    ' Test if a value is null.
  33.    If IsNull(varX) Then
  34.       ' If varX is null, set varX to 0.
  35.       xtabCnulls = 0
  36.    Else
  37.       ' Otherwise, return varX.
  38.       xtabCnulls = varX
  39.    End If
  40.  
  41. End Function
  42.  
  43. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  44.    ' Put values in text boxes and hide unused text boxes.
  45.  
  46.    Dim intX As Integer
  47.    '  Verify that you are not at end of recordset.
  48.    If Not rstReport.EOF Then
  49.       '  If FormatCount is 1, put values from recordset into text boxes
  50.       '  in "Detail" section.
  51.       If Me.FormatCount = 1 Then
  52.          For intX = 1 To intColumnCount
  53.             '  Convert Null values to 0.
  54.             Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
  55.          Next intX
  56.  
  57.          '  Hide unused text boxes in the "Detail" section.
  58.          For intX = intColumnCount + 2 To conTotalColumns
  59.             Me("Col" + Format(intX)).Visible = False
  60.          Next intX
  61.  
  62.          '  Move to next record in recordset.
  63.          rstReport.MoveNext
  64.       End If
  65.    End If
  66.  
  67. End Sub
  68.  
  69. Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  70.  
  71.    Dim intX As Integer
  72.    Dim lngRowTotal As Long
  73.  
  74.    '  If PrintCount is 1, initialize rowTotal variable.
  75.    '  Add to column totals.
  76.    If Me.PrintCount = 1 Then
  77.       lngRowTotal = 0
  78.  
  79.       For intX = 2 To intColumnCount
  80.          '  Starting at column 2 (first text box with crosstab value),
  81.          '  compute total for current row in the "Detail" section.
  82.          lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
  83.  
  84.          '  Add crosstab value to total for current column.
  85.          lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
  86.       Next intX
  87.  
  88.       '  Put row total in text box in the "Detail" section.
  89.       Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
  90.       '  Add row total for current row to grand total.
  91.       lngReportTotal = lngReportTotal + lngRowTotal
  92.    End If
  93. End Sub
  94.  
  95.  
  96. Private Sub Detail_Retreat()
  97.  
  98.    ' Always back up to previous record when "Detail" section retreats.
  99.    rstReport.MovePrevious
  100.  
  101. End Sub
  102.  
  103.  
  104. Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
  105.  
  106.    Dim intX As Integer
  107.  
  108.    '  Put column headings into text boxes in page header.
  109.    For intX = 1 To intColumnCount
  110.       Me("Head" + Format(intX)) = rstReport(intX - 1).Name
  111.    Next intX
  112.  
  113.    '  Make next available text box Totals heading.
  114.    Me("Head" + Format(intColumnCount + 1)) = "Totals"
  115.  
  116.    '  Hide unused text boxes in page header.
  117.    For intX = (intColumnCount + 2) To conTotalColumns
  118.       Me("Head" + Format(intX)).Visible = False
  119.    Next intX
  120.  
  121. End Sub
  122.  
  123.  
  124. Private Sub Report_Close()
  125.  
  126.    On Error Resume Next
  127.  
  128.    '  Close recordset.
  129.    rstReport.Close
  130.  
  131. End Sub
  132.  
  133.  
  134. Private Sub Report_NoData(Cancel As Integer)
  135.  
  136.    MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
  137.    rstReport.Close
  138.    Cancel = True
  139.  
  140. End Sub
  141.  
  142. Private Sub Report_Open(Cancel As Integer)
  143.  
  144.    '  Create underlying recordset for report
  145.  
  146.    Dim intX As Integer
  147.    Dim qdf As QueryDef
  148.    Dim frm As Form
  149.  
  150.    '  Set database variable to current database.
  151.    Set dbsReport = CurrentDb
  152.    Set frm = Forms!SelectDate
  153.    '  Open QueryDef object.
  154.    Set qdf = dbsReport.QueryDefs("Query1")
  155.    ' Set parameters for query based on values entered
  156.  
  157.    qdf.Parameters("Forms!SelectDate!StartDate") = frm!StartDate
  158.    qdf.Parameters("Forms!SelectDate!EndDate") = frm!EndDate
  159.  
  160.    '  Open Recordset object.
  161.    Set rstReport = qdf.OpenRecordset()
  162.  
  163.    '  Set a variable to hold number of columns in crosstab query.
  164.    intColumnCount = rstReport.Fields.Count
  165.  
  166. End Sub
  167. Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
  168.  
  169.    Dim intX As Integer
  170.  
  171.    '  Put column totals in text boxes in report footer.
  172.    '  Start at column 2 (first text box with crosstab value).
  173.    For intX = 2 To intColumnCount
  174.       Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
  175.    Next intX
  176.  
  177.    '  Put grand total in text box in report footer.
  178.    Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal
  179.  
  180.    '  Hide unused text boxes in report footer.
  181.    For intX = intColumnCount + 2 To conTotalColumns
  182.       Me("Tot" + Format(intX)).Visible = False
  183.    Next intX
  184.  
  185. End Sub
  186. Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
  187.  
  188.    '  Move to first record in recordset at the beginning of the report
  189.    '  or when the report is restarted. (A report is restarted when
  190.    '  you print a report from Print Preview window, or when you return
  191.    '  to a previous page while previewing.)
  192.    rstReport.MoveFirst
  193.  
  194.    'Initialize variables.
  195.    InitVars
  196.  
  197. 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
Oct 17 '08 #1
Share this Question
Share on Google+
14 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 18 '08 #2

ollyb303
P: 74
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?
Oct 18 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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?
Oct 18 '08 #4

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

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 18 '08 #6

ollyb303
P: 74
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.
Oct 21 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 21 '08 #8

ollyb303
P: 74
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?
Oct 21 '08 #9

puppydogbuddy
Expert 100+
P: 1,923
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?
Oct 21 '08 #10

ollyb303
P: 74
"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
  1.    Set qdf = dbsReport.QueryDefs("Query1")
Expand|Select|Wrap|Line Numbers
  1.    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
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.    ' Put values in text boxes and hide unused text boxes.
  3.  
  4.    Dim intX As Integer
  5.    '  Verify that you are not at end of recordset.
  6.    If Not rstReport.EOF Then
  7.       '  If FormatCount is 1, put values from recordset into text boxes
  8.       '  in "Detail" section.
  9.       If Me.FormatCount = 1 Then
  10.          For intX = 1 To intColumnCount
  11.             '  Convert Null values to 0.
  12.             Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
  13.          Next intX
  14.  
  15.          '  Hide unused text boxes in the "Detail" section.
  16.          For intX = intColumnCount + 2 To conTotalColumns
  17.             Me("Col" + Format(intX)).Visible = False
  18.          Next intX
  19.  
  20.          '  Move to next record in recordset.
  21.          rstReport.MoveNext
  22.  
  23.       End If
  24.    End If
  25.  
  26. End Sub
Seems to!
Oct 22 '08 #11

puppydogbuddy
Expert 100+
P: 1,923
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
  1. Private Sub Detail_Retreat()
  2.  
  3.    ' Always back up to previous record when "Detail" section retreats.
  4.    rstReport.MovePrevious
  5.  
  6. End Sub
Oct 22 '08 #12

ollyb303
P: 74
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!
Oct 22 '08 #13

puppydogbuddy
Expert 100+
P: 1,923
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
Oct 22 '08 #14

ollyb303
P: 74
Thanks for this. Very useful : )
Oct 22 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.