July 17, 2009
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:
- Create a new Row format with one line encompassing all your accounts:
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:
Hi Victoria, Nice post, just I’m looking for the version that will show the net change for each month individually.
What i need to do for chage the Column to see that.
Thanks for your help!
Julyro,
Follow the example I show for the column I labeled Net Change, but instead of 1 to BASE for the Period Code, enter a month number, like 1. Create 11 additional columns identical to that and change their period codes so that you have 12 columns, one for each month (ie period code).
-Victoria
Victoria,
An FRX question: I have a report that allocates corporate overhead to 3 divisions and prints the calculationat the bottom of their P&Ls. Each divsion is a parent/summary unit with various child tree units.
The allocation calculates and prints for all reporting units in the tree. I only want it to print on the division/parent/summary pages. How can I suppress printing on the child tree units?
Thanks,
Jim
Hi Jim,
It’s pretty difficult to suggest something without seeing your report components. The two things that I typically use to restrict what tree units show and where are the Related Rates/Rows/Unit (column D) in the Row Format and the Reporting Unit line in the Column Layout. I would try playing with those two options to see if they can accomplish what you need.
-Victoria
Hi Victoria,
I am trying to create a report like below, a list of income GL accounts across 12 months.
In the Row Format, I would like to be able to specify a range of accounts, like
1000-00 to 1999-99. I would like it by division on one sheet of paper. Any help you
can offer would be appreciated.
Thanks!
Jim Burke
Income Accounts:
GLA # GLA Description Jan Feb, etc.
1000-10 Fee Income
1100-10 Advance Income
1200-10 Express Income
1300-10 Wire Income
1400-10 Credit Income
Total East Division
1000-20 Fee Income
1100-20 Advance Income
1200-20 Express Income
1300-20 Wire Income
1400-20 Credit Income
Total South Division
Total All Divisions
Jim,
If all accounts in a division have a unique second segment (or range), then you should be able to do something like this on row format:
30 East Division 1000-10 TO 1999-10
60 South Division 1000-20 TO 1999-20
90 Total All Divisions TOT 30 TO 60
That would give you a ‘Summary’ report of one line per division, then if you dump it to Excel, as in my example in this blog post, you would see the detail that makes up each division total.
-Victoria
Victoria,
Another FRx question: I have a two page report. In the footer, I would like to number these pages “Page 5″ and “Page 6″. Anyway to do this?
Thanks,
Jim
Jim,
You mean you want to print a page number that is not the actual page number? Unless you can make that part of your row format (meaning not really a footer), I cannot think of a way to do this in FRx.
-Victoria
Great post! How can I get the entire TB to print in the DrillDown Viewer? (So it looks just like the Excel file.)
Thanks,
Jim
Hi Jim,
Thanks! When you see the report with just the one line and no amounts, double click on that line and you will will see the same results in the DrillDown Viewer as you do in Excel.
-Victoria
Victoria,
Thanks for the reply. When I double click,
the account numbers appear, but no account description. Anyway to get that to show up too in the Viewer?
Jim
Jim,
And they show up correctly in Excel? Is your Column Layout identical to mine in the second screenshot above? Especially column B? If so, what version/build of FRx are you on? (Under Help | About FRx.)
-Victoria
Victoria,
Thanks! A wider column width in Column B
got them to show up. I really appreciate
your help.
Jim
Victoria,
We have a scenario where we are trying to show 2 GL accounts from the same compay side-by-side (e.g.)in Frx. I would like to show revenue in one column and expense in another and then calc gross profit in the 3rd column.
This will help us list various programs in multiple rows and show revenue and expenese like we would in Excel.
Not sure how to print the second column- is this another GL link in the row format-tried this, but could not get it to print..
Thanks!
Ranjit,
I believe you should be able to do this with creating non-printing rows for the accounts, then using TOT columns and specifying what column to show which total in.
-Victoria
TB in FRx
Have you come across something like this before Victoria?
All users can access FRx reports on a specific machine except the TB report. But the same report can be accessed on other machines with FRx. Once the NP option is removed in the Column layout, the report prints. The machine in question is a Windows XP SP3/Office 2003. Another machine (Windows 7/Office 2010) has the same issue. As mentioned, only those machine(s) have a issue in printing the TB. We have never come across something like this before. FRx version for all machines: 6.7 SP10
Benjamin
Hi Benjamin,
I have not run into this particular issue, but there may be a few different things going on here. You mention that you have a Windows 7 machine running FRx 6.7 SP 10 – Windows 7 requires SP 11 of FRx 6.7, so I would recommend upgrading to SP 11. It will probably not fix this issue, since it’s only happening with one report, but just to be safe. It’s possible that the report or some of the building blocks for it got locked/corrupted. If this is for the report I am describing in this blog post, you could delete and recreate it pretty quickly. Maybe start with the column layout, since you’re saying that making a change there makes a difference. If the new report works fine everywhere, then chalk it up to something having gone wrong with that one report. If this starts happening for multiple reports, you may want to try compacting the FRx databases to see if that helps.
-Victoria
Hi Yudin
This is a very helpful topic, and thanks for making it in very detail.
Well, I have a requirement of getting the output in XL, and in a specific number format, can it be possible in any of the settings?
Hello,
If you mean Microsoft Excel, you could either export from FRx and change the formatting as needed or create a report in Excel that goes directly against the SQL data. The second option is a lot more work, but could produce a refreshable Excel report in the format you want that end users can run on their own.
-Victoria
Victoria.
We are new MAS500/Frx users in 2010. We have setup 2010 as our first year. I have entered 2009′s closing balances as Beginning Balances in Mas 500.
I have created report similar to what you have here, however, I keep getting zeros/blanks in Beginning Balance and Beginning Balance journal entry shows up as debits and credits.
Do you have any tips on how to get the BB’s to show up in BB?
Thanks
Jim,
I do not know MAS500 at all, so I don’t know if it would work the same way, but in Dynamics GP there is no ‘Beginning Balance’ entry that a user can enter that would be recognized as such by GP or FRx. From what you’re describing you’ve entered your BB balances on 1/1/2010 and FRx is simply recognizing your BB entry as a January entry, which is what would happen in GP, as well.
The way I would get beginning balances for 2010 into GP is to enter the ending balances on 12/31/2009 and not 1/1/2010 (assuming calendar fiscal year) and then perform a year-end close for 2009. This would automatically roll forward the Balance Sheet account balances to 2010 and close out the Income Statement accounts to Retailed Earnings. FRx then is able to recognize and report on both the 2009 ending balances and the 2010 beginning balances correctly.
Again, I do not know if MAS500 works similarly or, if so, whether you still have the option of re-doing this. Also, to my knowledge, there are different versions of FRx that are specific to each GL software, so FRx may work slightly differently with MAS500 than it does with GP. I would consult with your MAS500 partner or support to determine the best approach for handling this. I would hope this should be a pretty straightforward process, as this is a typical need for most companies starting on a new software package, it’s just that the actual entry method may vary greatly depending on the specific software.
-Victoria
Victoria
Thanks for your response.
FYI
I spent part of today on phone with SAGE and found out the following.
One of our original issues with MAS and Frx was that we wanted to see UNPOSTED transactions, so we could see that the entries were recorded to the correct accounts. (We are coming from QuickBooks and it was easy to change JEs so that the account balances would reconcile). We learned that you could PRINT and JE, and NOT post, and we could get this information.
However, in MAS500, our “Beginning Balance” entries were being treated as regular journal entries
In order for Frx to read as BB, we have to do the Print AND Post.
After testing, we found it will work.
We did not want to post, because we knew that our 2009 books are still under audit, and there is always a possibility of an audit adjustment. (We can also make additional BB entries if there adjustments, and we will have accurate beginning balances.)
Hope this help in the future.
jim
Jim,
Thanks for sharing this. Interesting how different functionality is depending on the GL application you’re using. Glad you were able to finally get this resolved.
-Victoria
Great post, very helpful.
I was wondering if there is a way to create a two dimensional report in FRx. I have a group of companies that roll up into a parent company and I want to display the trial balance first by account and then by company. Using Reporting Trees, I was able to create a Trial Balance by account that can be broken down by company if you drill down into one of the lines. However, I was hoping to have this information all displayed on the same report so it can be printed without drilling down.
Thanks in advance,
Brendan
Brendan,
Yes, this can be done by creating a Column layout that specifies a ‘branch’ of your Tree in the Reporting Unit field. So in your case instead of having one column with a balance, you would have 3 of them. One would have your ‘parent’ branch identified under Reporting Unit, another would have your 1st company, the 3rd column would have your 2nd company.
-Victoria
Victoria,
Thanks for you help. Unfortunately I have about 7 different companies and I’m including “Debit” and “Credit” columns. This would result in more columns than would fit on the report. Is there a way to do this that would display the information for each company in diferrent rows instead of different columns?
Thanks,
Brendan
Brendan,
The way to accomplish that is to repeat the same row as many times as you may need to show it and then use the Related Rates/Rows/Unit column to pick the appropriate branch of the tree. It’s a pain to set up, and there is a limitation on the number of rows you can have (I believe it’s 9000), but that’s how you can see that level of detail on different rows of the same report in FRx.
-Victoria
Victoria,
That’s the same conclusion I came to. I was hoping for an easier way. Is there any way to suppress one row based on the values of another? I have the report setup, but when I suppress all empty rows the corresponding format rows remain in view. Thanks again for your help.
Brendan
Brendan,
You can only suppress rows that are bringing in GL accounts. Not description rows or calculation rows. I don’t believe there is a way to suppress rows with a formula. You may want to try posting the FRx newsgroup (see the link under Newsgroups on the right side of this blog) to see if anyone has other ideas for you.
-Victoria
Great Post!
I was wondering if you knew how to show debit balances and credit balances in separate columns on a Trial Balance in FRx for accounts that could have both debit & credit entries.
Your method appears to give simply the debit & credits in separate columns in the report. Instead, could you format the report to display the account balance in either a debit or credit column, depending ont he balance?
Adam
Adam,
I am not aware of a way to do this in FRx. I have tried a few things, but have not been successful. I know I can do it easily in Excel once the data is there.
Have you tried posting this question in the FRx newsgroup? The FRx team monitors it and will help if they can.
-Victoria