Slow reports

G

gr

Hi, I made a cross tab report which opened in about 6 sec.
which I think it was quite good since the report record
source is a croostab query of a union query which is the
union of other 4 select queries. This report opens the
Time recording for 4 areas.
I wanted to give the user the option of viewing the report
for only one area. So the source record source will be the
crosstab query of only the select query (no union query
needed). So I expected this report to me much faster. But
it takes around 1 minute to open..

Is there any reason for this?
Here is the example code queries for one area and I also
include the on Open event of the report.

Thank you!

------------------ ALL AREAS --------------------------
---- qryName_Admin----
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRAdmin.Hours) AS SumofAdmin
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

---- qryName_Prj----
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRPrj.PrjHours) AS SumofPrj
FROM tblName INNER JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

--- qryName_Sales----
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRSales.Hours) AS SumofSales
FROM tblName INNER JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

-----qryName_Service-----
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRService.Hours) AS SumofService
FROM tblName INNER JOIN tblTRService ON tblName.NameID =
tblTRService.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

----uqryAllAreas----
SELECT qryName_Admin.NameID, qryName_Admin.Names,
qryName_Admin.Dt, Format(qryName_Admin.
[SumofAdmin], "#0.00") AS [Hours], "Administration" AS
[Area]
FROM qryName_Admin
UNION ALL SELECT qryName_Prj.NameID, qryName_Prj.Names,
qryName_Prj.Dt, Format(qryName_Prj.
[SumofPrj], "#0.00"), "Projects"
FROM qryName_Prj
UNION ALL SELECT qryName_Sales.NameID,
qryName_Sales.Names, qryName_Sales.Dt, Format
(qryName_Sales.[SumofSales], "#0.00"), "Sales"
FROM qryName_Sales
UNION ALL SELECT qryName_Service.NameID,
qryName_Service.Names, qryName_Service.Dt, Format
(qryName_Service.[SumofService], "#0.00"), "Service"
FROM qryName_Service
ORDER BY Dt;

-------- crossqryAllAreas -----
PARAMETERS [Forms]![frmMonthReport].[Form]![txtStart]
DateTime, [Forms]![frmMonthReport].[Form]![txtEnd]
DateTime;
TRANSFORM Format(Sum(uqryAllAreas.Hours),"Fixed") AS
SumOfHours
SELECT uqryAllAreas.Names
FROM uqryAllAreas
WHERE (((uqryAllAreas.Dt) Between [Forms]![frmMonthReport].
[Form]![txtStart] And [Forms]![frmMonthReport].[Form]!
[txtEnd]))
GROUP BY uqryAllAreas.Names
ORDER BY Format([Dt],"mmm-dd")
PIVOT Format([Dt],"mmm-dd");

---- all this was for the one that is openning in 6 sec. --

---- Example of the slow one --------------------------
--- again qryName_Admin ---
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRAdmin.Hours) AS SumofAdmin
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

------ crossqryAdmin---------------------
PARAMETERS [Forms]![frmMonthReport].[Form]![txtStart]
DateTime, [Forms]![frmMonthReport].[Form]![txtEnd]
DateTime;
TRANSFORM Sum(qryName_Admin.SumofAdmin) AS SumOfSumofAdmin
SELECT qryName_Admin.Names
FROM qryName_Admin
WHERE (((qryName_Admin.Dt) Between [Forms]!
[frmMonthReport].[Form]![txtStart] And [Forms]!
[frmMonthReport].[Form]![txtEnd]))
GROUP BY qryName_Admin.Names
ORDER BY Format([Dt],"mmm-dd")
PIVOT Format([Dt],"mmm-dd");

-------- REPORT CODE------------------------------------
Option Compare Database
Option Explicit

Const conTotalColumns = 27
Dim mrstReport As DAO.Recordset
Dim mintColumnCount As Integer
Dim mlngRgColumnTotal(1 To conTotalColumns) As Double
Dim mlngReportTotal As Double

Private Sub Detail1_Format(Cancel As Integer, FormatCount
As Integer)
Dim intX As Integer
If Not mrstReport.EOF Then
If Me.FormatCount = 1 Then
For intX = 1 To mintColumnCount
Me("Col" + Format(intX)) = xtabCnulls(mrstReport(intX - 1))
Next intX
For intX = mintColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX
mrstReport.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 Me.PrintCount = 1 Then
lngRowTotal = 0
For intX = 2 To mintColumnCount
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
mlngRgColumnTotal(intX) = mlngRgColumnTotal(intX) + Me
("Col" + Format(intX))
If Me("Col" + Format(intX)) < 8 Then
Me("Col" + Format(intX)).ForeColor = 255
ElseIf Me("Col" + Format(intX)) = 8 Then
Me("Col" + Format(intX)).ForeColor = 0
ElseIf Me("Col" + Format(intX)) > 8 Then
Me("Col" + Format(intX)).ForeColor = 32768
End If
Next intX
Me("Col" + Format(mintColumnCount + 1)) = lngRowTotal
mlngReportTotal = mlngReportTotal + lngRowTotal
Me("Col" + Format(mintColumnCount + 1)).BackColor =
12632256
Me("Col" + Format(mintColumnCount + 1)).ForeColor = 8388608

End If
End Sub

Private Sub Detail1_Retreat()
mrstReport.MovePrevious
End Sub

Private Sub InitVars()
Dim intX As Integer
mlngReportTotal = 0
For intX = 1 To conTotalColumns
mlngRgColumnTotal(intX) = 0
Next intX
End Sub

Private Sub PageHeader0_Format(Cancel As Integer,
FormatCount As Integer)
Dim intX As Integer
For intX = 1 To mintColumnCount
Me("Head" + Format(intX)) = mrstReport(intX - 1).Name
Next intX
Me("Head" + Format(mintColumnCount + 1)) = "Totals"

For intX = (mintColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX
End Sub

Private Sub Report_Close()
On Error Resume Next
mrstReport.Close
Set mrstReport = Nothing
End Sub

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

Private Sub Report_Open(Cancel As Integer)
If Not (IsLoaded("frmMonthReport")) Then
Cancel = True
MsgBox "To preview this report, you must open form Month
Report", vbExclamation, "Must Open Dialog Box"
Exit Sub
End If
Dim intReportOptions As Integer
Dim intX As Integer
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim frm As Form

intReportOptions = Forms!frmMonthReport!OgReportOptions
Set frm = Forms!frmMonthReport
Set db = CurrentDb()

Select Case intReportOptions
Case 1
Me.RecordSource = "crossqryAdmin"
Set qdf = db.QueryDefs("crossqryAdmin")
Case 2
Me.RecordSource = "crossqryPrj"
Set qdf = db.QueryDefs("crossqryPrj")

Case 3
Me.RecordSource = "crossqrySales"
Set qdf = db.QueryDefs("crossqrySales")
Case 4
Me.RecordSource = "crossqryService"
Set qdf = db.QueryDefs("crossqryService")
Case 5
Me.RecordSource = "crossqryAllAreas"
Set qdf = db.QueryDefs("crossqryAllAreas")
Case Else
MsgBox "Unrecognized Database Error. Cancelling
Report... ", vbCritical, "Warning"
Cancel = True
Exit Sub
End Select
qdf.Parameters("[Forms]![frmMonthReport].[Form]!
[txtStart]") = frm!txtStart
qdf.Parameters("[Forms]![frmMonthReport].[Form]![txtEnd]")
= frm!txtEnd
Set mrstReport = qdf.OpenRecordset
mintColumnCount = mrstReport.Fields.Count
End Sub

Private Sub ReportFooter4_Print(Cancel As Integer,
PrintCount As Integer)
Dim intX As Integer
For intX = 2 To mintColumnCount
Me("Tot" + Format(intX)) = mlngRgColumnTotal(intX)
Next intX
Me("Tot" + Format(mintColumnCount + 1)) = mlngReportTotal
For intX = mintColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX
End Sub

Private Sub ReportHeader3_Format(Cancel As Integer,
FormatCount As Integer)
mrstReport.MoveFirst
Call InitVars
End Sub

Private Function xtabCnulls(varX As Variant) As Variant
'xtabCnulls = Nz(varX, 0)
If varX = "" Then
xtabCnulls = 0
ElseIf IsNull(varX) Then
xtabCnulls = 0
Else
xtabCnulls = varX
End If
End Function

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view
or Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function
 
D

Duane Hookom

I really don't want to try to work through all your sql and code. I did
notice that you are using code in the report that I have found very slow and
inflexible. There are examples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4 that are faster and more
flexible.

--
Duane Hookom
MS Access MVP


gr said:
Hi, I made a cross tab report which opened in about 6 sec.
which I think it was quite good since the report record
source is a croostab query of a union query which is the
union of other 4 select queries. This report opens the
Time recording for 4 areas.
I wanted to give the user the option of viewing the report
for only one area. So the source record source will be the
crosstab query of only the select query (no union query
needed). So I expected this report to me much faster. But
it takes around 1 minute to open..

Is there any reason for this?
Here is the example code queries for one area and I also
include the on Open event of the report.

Thank you!

------------------ ALL AREAS --------------------------
---- qryName_Admin----
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRAdmin.Hours) AS SumofAdmin
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

---- qryName_Prj----
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRPrj.PrjHours) AS SumofPrj
FROM tblName INNER JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

--- qryName_Sales----
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRSales.Hours) AS SumofSales
FROM tblName INNER JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

-----qryName_Service-----
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRService.Hours) AS SumofService
FROM tblName INNER JOIN tblTRService ON tblName.NameID =
tblTRService.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

----uqryAllAreas----
SELECT qryName_Admin.NameID, qryName_Admin.Names,
qryName_Admin.Dt, Format(qryName_Admin.
[SumofAdmin], "#0.00") AS [Hours], "Administration" AS
[Area]
FROM qryName_Admin
UNION ALL SELECT qryName_Prj.NameID, qryName_Prj.Names,
qryName_Prj.Dt, Format(qryName_Prj.
[SumofPrj], "#0.00"), "Projects"
FROM qryName_Prj
UNION ALL SELECT qryName_Sales.NameID,
qryName_Sales.Names, qryName_Sales.Dt, Format
(qryName_Sales.[SumofSales], "#0.00"), "Sales"
FROM qryName_Sales
UNION ALL SELECT qryName_Service.NameID,
qryName_Service.Names, qryName_Service.Dt, Format
(qryName_Service.[SumofService], "#0.00"), "Service"
FROM qryName_Service
ORDER BY Dt;

-------- crossqryAllAreas -----
PARAMETERS [Forms]![frmMonthReport].[Form]![txtStart]
DateTime, [Forms]![frmMonthReport].[Form]![txtEnd]
DateTime;
TRANSFORM Format(Sum(uqryAllAreas.Hours),"Fixed") AS
SumOfHours
SELECT uqryAllAreas.Names
FROM uqryAllAreas
WHERE (((uqryAllAreas.Dt) Between [Forms]![frmMonthReport].
[Form]![txtStart] And [Forms]![frmMonthReport].[Form]!
[txtEnd]))
GROUP BY uqryAllAreas.Names
ORDER BY Format([Dt],"mmm-dd")
PIVOT Format([Dt],"mmm-dd");

---- all this was for the one that is openning in 6 sec. --

---- Example of the slow one --------------------------
--- again qryName_Admin ---
SELECT tblName.NameID, tblName.Names, tblName.Dt, Sum
(tblTRAdmin.Hours) AS SumofAdmin
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID
GROUP BY tblName.NameID, tblName.Names, tblName.Dt;

------ crossqryAdmin---------------------
PARAMETERS [Forms]![frmMonthReport].[Form]![txtStart]
DateTime, [Forms]![frmMonthReport].[Form]![txtEnd]
DateTime;
TRANSFORM Sum(qryName_Admin.SumofAdmin) AS SumOfSumofAdmin
SELECT qryName_Admin.Names
FROM qryName_Admin
WHERE (((qryName_Admin.Dt) Between [Forms]!
[frmMonthReport].[Form]![txtStart] And [Forms]!
[frmMonthReport].[Form]![txtEnd]))
GROUP BY qryName_Admin.Names
ORDER BY Format([Dt],"mmm-dd")
PIVOT Format([Dt],"mmm-dd");

-------- REPORT CODE------------------------------------
Option Compare Database
Option Explicit

Const conTotalColumns = 27
Dim mrstReport As DAO.Recordset
Dim mintColumnCount As Integer
Dim mlngRgColumnTotal(1 To conTotalColumns) As Double
Dim mlngReportTotal As Double

Private Sub Detail1_Format(Cancel As Integer, FormatCount
As Integer)
Dim intX As Integer
If Not mrstReport.EOF Then
If Me.FormatCount = 1 Then
For intX = 1 To mintColumnCount
Me("Col" + Format(intX)) = xtabCnulls(mrstReport(intX - 1))
Next intX
For intX = mintColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX
mrstReport.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 Me.PrintCount = 1 Then
lngRowTotal = 0
For intX = 2 To mintColumnCount
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
mlngRgColumnTotal(intX) = mlngRgColumnTotal(intX) + Me
("Col" + Format(intX))
If Me("Col" + Format(intX)) < 8 Then
Me("Col" + Format(intX)).ForeColor = 255
ElseIf Me("Col" + Format(intX)) = 8 Then
Me("Col" + Format(intX)).ForeColor = 0
ElseIf Me("Col" + Format(intX)) > 8 Then
Me("Col" + Format(intX)).ForeColor = 32768
End If
Next intX
Me("Col" + Format(mintColumnCount + 1)) = lngRowTotal
mlngReportTotal = mlngReportTotal + lngRowTotal
Me("Col" + Format(mintColumnCount + 1)).BackColor =
12632256
Me("Col" + Format(mintColumnCount + 1)).ForeColor = 8388608

End If
End Sub

Private Sub Detail1_Retreat()
mrstReport.MovePrevious
End Sub

Private Sub InitVars()
Dim intX As Integer
mlngReportTotal = 0
For intX = 1 To conTotalColumns
mlngRgColumnTotal(intX) = 0
Next intX
End Sub

Private Sub PageHeader0_Format(Cancel As Integer,
FormatCount As Integer)
Dim intX As Integer
For intX = 1 To mintColumnCount
Me("Head" + Format(intX)) = mrstReport(intX - 1).Name
Next intX
Me("Head" + Format(mintColumnCount + 1)) = "Totals"

For intX = (mintColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX
End Sub

Private Sub Report_Close()
On Error Resume Next
mrstReport.Close
Set mrstReport = Nothing
End Sub

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

Private Sub Report_Open(Cancel As Integer)
If Not (IsLoaded("frmMonthReport")) Then
Cancel = True
MsgBox "To preview this report, you must open form Month
Report", vbExclamation, "Must Open Dialog Box"
Exit Sub
End If
Dim intReportOptions As Integer
Dim intX As Integer
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim frm As Form

intReportOptions = Forms!frmMonthReport!OgReportOptions
Set frm = Forms!frmMonthReport
Set db = CurrentDb()

Select Case intReportOptions
Case 1
Me.RecordSource = "crossqryAdmin"
Set qdf = db.QueryDefs("crossqryAdmin")
Case 2
Me.RecordSource = "crossqryPrj"
Set qdf = db.QueryDefs("crossqryPrj")

Case 3
Me.RecordSource = "crossqrySales"
Set qdf = db.QueryDefs("crossqrySales")
Case 4
Me.RecordSource = "crossqryService"
Set qdf = db.QueryDefs("crossqryService")
Case 5
Me.RecordSource = "crossqryAllAreas"
Set qdf = db.QueryDefs("crossqryAllAreas")
Case Else
MsgBox "Unrecognized Database Error. Cancelling
Report... ", vbCritical, "Warning"
Cancel = True
Exit Sub
End Select
qdf.Parameters("[Forms]![frmMonthReport].[Form]!
[txtStart]") = frm!txtStart
qdf.Parameters("[Forms]![frmMonthReport].[Form]![txtEnd]")
= frm!txtEnd
Set mrstReport = qdf.OpenRecordset
mintColumnCount = mrstReport.Fields.Count
End Sub

Private Sub ReportFooter4_Print(Cancel As Integer,
PrintCount As Integer)
Dim intX As Integer
For intX = 2 To mintColumnCount
Me("Tot" + Format(intX)) = mlngRgColumnTotal(intX)
Next intX
Me("Tot" + Format(mintColumnCount + 1)) = mlngReportTotal
For intX = mintColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX
End Sub

Private Sub ReportHeader3_Format(Cancel As Integer,
FormatCount As Integer)
mrstReport.MoveFirst
Call InitVars
End Sub

Private Function xtabCnulls(varX As Variant) As Variant
'xtabCnulls = Nz(varX, 0)
If varX = "" Then
xtabCnulls = 0
ElseIf IsNull(varX) Then
xtabCnulls = 0
Else
xtabCnulls = varX
End If
End Function

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view
or Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Row and Column Averages in Dynamic Crosstab Report? 3
Dynamic Heading in cross tab qry report 3
Error: Microsoft jet database engine does not recognize " as a va 9
Format and Print Events in a Report 8
dynamic crosstab report with groupfooter totals 1
Last record of crosstab repeats in report 11
Dynamic crosstab report - need help!! 2
Crosstab report repeats last record for all values 10

Top