Historical Aged Trail Balance report runs slow

[G S]

I'm the DBA at my company, and I've been asked to research why a
Historical Aged Trail Balance report takes over 1.5 hours to run,
printing to the screen. I'm not familiar with Great Plains at all, and
I was not involved in the installation of our current version, but I
think we use 7.5.

This report runs pretty fast when restrictions are used to filter down
to one or two customers for a short time frame, but our controller likes
to run the report from First to 11/30/2005, and I believe we have at
least three full years of customer/invoice data in this db.

Is it reasonable to expect a report that hits/processes that much data
to run for that long?

Thanks!
Gary

[Robert C]

Hello G.S.

You say you are running a Historical Aged Trial Balance, and later
mention customers. I therefore take it that you a running a Receivables
HATB.

The length taken when unrestricted like you mention, it is conceiveable
for the report to take that long. The reason being that for each invoice
the preprocessing checks whether this given document was applied
within the given date range and that the given apply document was also
entered within the given date range. If it succeeds both tests the
document would not be displayed, but does take up processing time.

It is likely that most documents should have been paid off, and
therefore would not display, but are still undergoing those checks.
Therefore you are making it work excessively for little gain.

If you are not concerned about whether a document was paid off by an
cash receipt recieved or applied outside the date range, use the 'Aged
trial balance' report instead.

[Victoria [MVP]]

Gary,

As Robert says, it's possible that it would take this long, but there may be
some things you could do to speed it up. How many pages are on the report
when it finally prints? (This should give us a better idea of whether it
should really take 1.5 hrs). Have you tried printing the report directly on
the SQL server? Does it still take the same amount of time? It would help
to know the exact version and service pack you're on. Also what printer is
set up in Great Plains when this user prints the report? (Even if it's
being printed to the screen, the report will use the printer driver for the
printer currently set up in GP.)

--
Victoria Yudin
GP MVP

"G S" <bob...@newsgroups.nospam> wrote in message
news:%23a1zMd5%23FHA...@tk2msftngp13.phx.gbl...

[S@discussions.microsoft.com G S]

Thanks for the replies. I'll run this info by our controller and IT manager
to see if the Aged Trial Balance would work for them.

The unrestricted historical aged trial balance report has 149 pages.

This particular client pc has no printers defined, but the controller's pc
has a few (one or two inkjets and one or two lasers).

By "printing the report directly on the sql server," do you mean running the
main stored proc in query analyzer? I have not done this, although I did
view the estimated execution plan in QA, with what i assumed to be the
necessary parameters (end date) and some fudging to get the optimizer to
recognize a few necessary temp tables used by the stored proc...the execution
plan listed table scans on the Constants table, but nothing else seemed
obvious from a query performance standpoint...this seems to be an issue with
the sheer amount of data that's being processed.

Version Information:
Great Plains - 7.50g3
Dexterity - 7.50m003
SmartList - 7.50.3
Database - SQL Server (2000)
System - Windows XP
ODBC Driver Manager - 03.52.0000
ODBC Driver - 03.85.1117

Session Information:
Size - 9617mb

Thanks again!
Gary

[Victoria [MVP]]

Gary,

149 pages is a fairly large report. But I still don't think it should
necessarily take that long. I think you're exactly right in that it's the
amount of the data. What I have seen in the past is that when GP tries to
send a lot of data to a report it is sometimes slowed down significantly by
2 variables: the network connection to the SQL Server and the printer (this
could be the printer itself, the printer driver, the print server, etc., and
could also depend on the network). That's why I was asking about what kind
of printer and trying to run this directly on the server. (And what I meant
by that is doing the same thing that the user is doing in the GP interface,
but directly on the SQL server if the GP application is installed there.)

One of our customers when they first started using GP (version 7.5) used to
crash in the middle of almost every check run. It took us a while to figure
out that it was because their check runs were usually 200+ checks and their
printer couldn't handle it. They upgraded to a new, more powerful printer
and everything has been fine since then.

Hope this helps you.

--
Victoria Yudin
GP MVP

"G S" <G S...@discussions.microsoft.com> wrote in message
news:DA8B12B4-1CD4-40E7...@microsoft.com...

[Zach Morgan]

In the AR/SOP module you can go to Routines/Aging and put in an aging date
(last day of month). Then use smartlists under customers and create your own
aging. Use the columns Ageing Period Amount 1..2..3...4 etc. This of course
will not provide transaction detail, but will give a quick aging that should
tie to the GL and can be exported and saved in soft copy.

[Lisa Koehntop [MSFT]]

Hi Gary,

It looks like you have been getting great help here!

One thing I am wondering is if it would be possible for the
controller to enter a date in the First value instead of leaving it
as First. This would decrease the amount of records the system would
have to sort through. You said you have three years of data. If there
aren't any outstanding invoices for prior years, could the report be
printed from 7/1/2005 to 11/30/2005 for example? Does that increase
the processing of the report for you?

Also, I recommend you review the list of supported printers for Great
Plains 7.5. It is best to print this report with a supported printer
set as the default printer. As Victoria said, this printer driver is
used to print the report to the screen. Here is the Printer
Compatibility List:
https://mbs.microsoft.com/customersource/support/documentation/SystemR
equirements/compatibility_GP7.5_printers.htm.

Thank you, Lisa
Microsoft Online Support Engineer

Get Secure! - www.microsoft.com/security

=================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
Thread-Topic: Historical Aged Trail Balance report runs slow
thread-index: AcX8ME0GKOe0vEJuSxOjiQngH8fsrQ==
X-WBNR-Posting-Host: 12.179.188.5
From: "=?Utf-8?B?WmFjaCBNb3JnYW4=?="
<ZachM...@discussions.microsoft.com>
References: <#a1zMd5#FHA....@tk2msftngp13.phx.gbl>
<OqRxRu##FHA...@TK2MSFTNGP10.phx.gbl>
<DA8B12B4-1CD4-40E7...@microsoft.com>
Subject: Re: Historical Aged Trail Balance report runs slow
Date: Thu, 8 Dec 2005 11:48:03 -0800
Lines: 85
Message-ID: <F3E96792-1ACC-47EC...@microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.greatplains
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.greatplains:20641
X-Tomcat-NG: microsoft.public.greatplains

[G S]

Thank you all for your suggestions and help.

The report is back to running in ~30 minutes since we reindexed the
database, so that's a positive step. I will also check his printer
against the list to see if that could be part of the problem.

Interestingly, speaking of printer drivers, the other night the
controller showed me how his report (not sure which one) was rendering
on his client screen at a rate of ~1 page every 3 minutes. It was
bizarre watching the page counter stay on 13 for 3 minutes, then watch
it change to 14 all of a sudden....... This seems like it could be a
printer issue.

Thanks again!
Gary

[GS]

Well, now I'm not so sure that the printer has anything to do with our
particular problem. Each member in our A/R staff uses one of four
printers. The only one that is on the compatibility list is the HP
Laserjet 1200. I ran the HATB report (no date range or other
restrictions) from this workstation, and it took 50 minutes to complete
(at least that's how long the main stored proc took to run).

Back to the drawing board...

Gary

[Victoria [MVP]]

Gary,

It's not so much if it's on the list or not, I have used dozens of printers
not on the supported list successfully. What is much more important is how
much memory the printer has, what printer driver you're using, etc. I am
not sure I would expect a Laserjet 1200 to have good performance for this
size of a report.

--
Victoria Yudin
GP MVP

"GS" <bob...@nospam.nospam> wrote in message
news:OetOnvmA...@TK2MSFTNGP15.phx.gbl...

[Dukester]

Few questions which the group haven't ask you:

1) How do the users access GP?
2) How many GP servers do you have? If more than one, are all on the same OS?
3) Have you tried printing to File? Does it run on same amount of time
compared to printing to a printer?
--
http://ddelprado.blogspot.com