Report very slow to show

[mauricionava]

HI, is there a trick to open reports faster?

My query uses 2 more queries inside. One of them uses Sum and Last and grouping.

My report has one gruping for details. Has one picture inserted.

Opening my query alone also takes a litle while. Takes about 2 mins.

How can I improve its speed?

Thanks.

 

[ThomasLafferty]

This will depend on any number of things, not the least of which is the structure of any query feeding the report.

It would probably be helpful if you posted the SQL statements which generate your queries. I may not have time to look at it, but I am sure one of the gurus here will be more than glad to take a peek.

Tom

Born once die twice; born twice die once.

 

[mauricionava]

This is the SLQ code. It is using two queries inside. I would like to eliminate those but I realy don't see how.

Thanks.

SELECT DISTINCT [Holdback Income Report New].DEALER, [Holdback Income Report New].BUYER, [Holdback Income Report New].ACCT, Sum([Holdback Income Report New].CURDUE) AS SumOfCURDUE, Sum([Holdback Income Report New].AMTPAID) AS SumOfAMTPAID, Sum([Holdback Income Report New].LATEFEE) AS SumOfLATEFEE, Sum([Holdback Income Report New].PRINPAID) AS SumOfPRINPAID, Sum([Holdback Income Report New].INTPAID) AS SumOfINTPAID, Sum([Holdback Income Report New].MISCPAID) AS SumOfMISCPAID, [Holdback Income Report New].RES_PCT, Sum([Holdback Income Report New].Hpay) AS SumOfHpay, Sum([Holdback Income Report New].GDoc) AS SumOfGDoc, Now()-[TRDATA.LASTPAID] AS DAYSAFTERPYMT, IIf(IsNull(([trdata.principal]-[trdata.gp_amt]-[trdata.docfees])*([trdata.res_pct])/100-[HBKPaid.hpay]),0,(([trdata.principal]-[trdata.gp_amt]-[trdata.docfees])*([trdata.res_pct])/100-[HBKPaid.hpay])) AS REMHB, Dealers.Name, Last([Holdback Income Report New].BALANCE) AS LastOfBALANCE, IIf([TRDATA.CURRENTDUE]<0,0,[TRDATA.CURRENTDUE]) AS CURRENTDUE, Last([Holdback Income Report New].DATEPAID) AS LastOfDATEPAID
FROM HBKPaid INNER JOIN (Dealers INNER JOIN (([Holdback Income Report New] INNER JOIN TRDATA ON [Holdback Income Report New].ACCT = TRDATA.ACCT) LEFT JOIN paytypelookup ON [Holdback Income Report New].PAYTYPE = paytypelookup.Paytype) ON Dealers.Short = [Holdback Income Report New].DEALER) ON HBKPaid.ACCT = [Holdback Income Report New].ACCT
GROUP BY [Holdback Income Report New].DEALER, [Holdback Income Report New].BUYER, [Holdback Income Report New].ACCT, [Holdback Income Report New].RES_PCT, Now()-[TRDATA.LASTPAID], IIf(IsNull(([trdata.principal]-[trdata.gp_amt]-[trdata.docfees])*([trdata.res_pct])/100-[HBKPaid.hpay]),0,(([trdata.principal]-[trdata.gp_amt]-[trdata.docfees])*([trdata.res_pct])/100-[HBKPaid.hpay])), Dealers.Name, IIf([TRDATA.CURRENTDUE]<0,0,[TRDATA.CURRENTDUE])
ORDER BY [Holdback Income Report New].RES_PCT;