Hi Rene,
I agree that FRx or Management Reporter will give you a nice trial balance, but if you don't have access to that software then it really doesn't matter what it can produce. I will presume that is the case. I'm going to 2nd Harry Lee's advice and add a couple of pointers. If what you need is a summary trial balance for an Open year, then there is a report called the 'Worksheet'. See screenshot below:
This report is seldom, if ever, used so that you can modify the Worksheet report's layout without messing up one of the reports you may still need to print on paper. Bear in mind that if you modify the standard trial balance report, your printout will look like the modified report.
Open the report in Report Writer (RW) and remove everything except the Report Header (RH) and Footer 1 (F1). Don't forget to remove the page numbers and the underlines and even the totals. You'll need to move things around a bit. Do NOT remove any of the hidden or yellow background fields. They don't print or export and the report may need them to render some of the calculated fields. When you create your csv (or tab) file, the fields will be static, there won't be any formulas in the 'Net Difference' or 'Ending Balance' columns. My report layout looks like the image below:
Notice my Net Change and Ending Balance fields (if you can see them) have text in them. What I've done is to type in the Excel formula that will calculate those two fields. The screenshot is a bit difficult to read, so I'll type in the formulas.
The Net Change is =+RC[-2]-RC[-1]
The Ending balance is =+RC[-4]+RC[-1]
The formula I'm using is a relative formula. RC[-2] means two columns to the left, RC[-4] means four columns to the left. RC[2] would mean two columns to the right, and so on.
After you print your report to the .CSV or .TAB file, you need to change the Formula option in Excel to relative before you open the file. To change Excel, go to File - Options, select the Formulas option and check the box next to R1C1 reference style. Here's what it looks like on my machine:
You can change it back after you get the file open.
If you need to export the trial balance in detail or want a view for a summary trial balance, check out Victoria Yudin's web site http://victoriayudin.com/gp-reports/general-ledger-sql-views/
She has tons of views you can use for different information coming from GP. Explore her site while you're there, she provides an uncanny number of SQL views for various GP data. This woman is amazing!
Another thing you can do if you run out of room laying out your fields horizontally in RW is to stack them. Just stack the column headings in the RH section and the fields in the F1 section. This is what we had to do way back when GP didn't run on SQL. My stacked report looks like this:
If your records do not layout vertically in your text file, add the following switch to your Dex.ini file:
ExportOneLineBody=TRUE
Another trick we used in the old days was to change the print driver to one that would be used by a plotter. That way, RW gave us a wider widow layout.
I use this method on other reports that need to be generated from the UI that use temporary tables. Sometimes creating a SQL view and adding a new SmartList object or using SSRS or Management Reporter just isn't practical for the environment at the client.
Well, that was certainly a long answer to a short question, hopefully it will help someone.
Kind regards,
Leslie
