Is there a way I can export a trial report to excel without having to reformat the report once imported? I use the comma delimited format export file format.
*This post is locked for comments
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
You can modify the Trial Balance Report to remove header information, and align the rows so they are more compatible with Excel, and set the report format to Landscape mode, but this is not likely the best approach to this problem.
If you want to push data you would find on a trial balance report to Excel, use the Account Transaction SmartList as a starting point, make sure you get all the columns you require added, then define a search to meet your reporting criteria.
If you have not set up reports in Management reporter the smartlist view would be your best bet. One of the canned reports could do it or just add a few additional columns as needed and export directly into Excel. Once you get MR set up it will give you a better formatted report.
Hi,
Have you try smartlist or Management Report ? MR can create robust Trial Balance report for you.
Thanks
Sandip
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
