While I work with SQL and Crystal Reports every day, there are some reports that are just easier created elsewhere. One example is a GL Trial Balance that you want to export to Excel. There are many ways of handling this requirement, I like using FRx for it. Below are steps by step instructions on how to set this up, from start to finish it should take no more than 10 minutes:
If you have any unit accounts, make sure to exclude them unless you want to see them on the Trial Balance.
- Create a Column format to show the columns you want, here is an example showing the beginning balance, net change and ending balance:
You can do a lot of different things here, for example, I sometimes use a version that will show the net change for each month individually.
- Create a new Catalog and change the following default settings:
- Set Detail level to Financial & Account
- On the Report Options tab check:
- Display rows with no amounts
- Display reports with no active rows
- Generate the report, you will get something like the following in the DrillDown Viewer:
- Now to get this into Excel:
- Go to File > Export > Worksheet File > Formatted Excel
- Select a file name and destination
- On the Export Selection window uncheck Financial report and check Detail (supporting) report
- Click OK and FRx will automatically open Excel with your detailed Trial Balance:
- You can also set this up to print to Excel automatically (skipping the steps above to have to Export from the DrillDown Viewer). To do this:
- Go to the Output tab, then the Output Options tab
- Change the drop-down option to Formatted Excel and pick a destination and the other settings as desired
Update on August 27, 2009:
I have been asked a few times recently how to show debits and credits in separate columns on a Trial Balance in FRx. To accomplish that, change the column layout I suggested above to look like the following:
Update on May 9, 2013:
A few more variations I have seen requested:
For a monthly Net Change, you can set up your columns to look like the following (click on the image to see it bigger):
The P <=B will on the Print Control row cause future months not to print. For example, if you are printing your report for May, 2013, this will only print the first 5 monthly columns and suppress June through December. If you always want to see all 12 months, you can clear the Print Control row.
For a monthly Year to Date balance, set up your columns to look like the following (click on the image to see it bigger):
Again, you can clear the Print Control row if you always want to see all 12 months, otherwise this will only show the months through your report date.