Dynamics GP Trial Balance in Excel using FRx


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:FRx TB row

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:FRx TB column

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: FRx TB display
  • 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:

    Excel TB

  • 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:

FRx DR CR

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):

monthly change

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):

ytd balance

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.

43 Responses to “Dynamics GP Trial Balance in Excel using FRx”

  1. Victoria,

    I have used this a lot with my clients and it works in Management Reporter. One trick I added is a Current Month Trial Balance. In my column definition. I set the first column to BASE-1 and YTD, then the next three columns to BASE and PERIODIC.

    -Mark

    Like

  2. Hi Victoria, How I can get the ending balance for each period month?. thanks a lot for your help.

    Like

  3. This was just what I was looking for! Thanks soooooo much!!

    Like

  4. 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!

    Like

    • 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

      Like

  5. 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

    Like

    • 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

      Like

    • 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

      Like

      • 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

        Like

    • 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

      Like

      • 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

        Like

  6. 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

    Like

    • 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

      Like

  7. 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!

    Like

  8. 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

    Like

    • 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

      Like

  9. 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?

    Like

    • 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

      Like

  10. 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

    Like

    • 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

      Like

      • 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

        Like

  11. 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

    Like

    • 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

      Like

      • 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

        Like

        • 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

          Like

          • 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

            Like

            • 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

              Like

  12. 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

    Like

    • 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

      Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | Dynamics GP Trial Balance in Excel using FRxDynamics GP Trial Balance in Excel using FRx - DynamicAccounting.net - April 25, 2016

    […] on July 20, 2009 by Mark Polino Victoria Yudin has a great new post up covering creating a Dynamics GP Trial balance in FRx and then exporting it to […]

    Like

  2. Interesting Findings & Knowledge Sharing » GL Trial Balance in FRx - February 3, 2012

    […] is an easier way using FRx.  Victoria Yudin has a great article on doing just this here – https://victoriayudin.com/2009/07/17/dynamics-gp-trial-balance-in-excel-using-frx/.   *click image to enlarge Now, to take this one more step, if you need more of the […]

    Like

  3. GL Trial Balance in FRx - gp2themax - GP Technical Blogs - Microsoft Dynamics Community - February 3, 2012

    […] is an easier way using FRx.  Victoria Yudin has a great article on doing just this here – https://victoriayudin.com/2009/07/17/dynamics-gp-trial-balance-in-excel-using-frx/.  *click image to enlargeNow, to take this one more step, if you need more of the […]

    Like

  4. Trial Balance from FRx – Nice approach from Victoria Yudin - DynamicsGP.ie - September 11, 2009

    […] https://victoriayudin.com/2009/07/17/dynamics-gp-trial-balance-in-excel-using-frx/ Published: Tuesday, August 18, 2009, 05:00 PM […]

    Like

  5. Trial Balance from FRx – Nice approach from Victoria Yudin « DynamicsGP.ie - August 18, 2009

    […] Trial Balance from FRx – Nice approach from Victoria Yudin « DynamicsGP.ie […]

    Like

  6. Dynamics GP Trial Balance in Excel using FRx - DynamicAccounting.net - July 20, 2009

    […] GP Trial Balance in Excel using FRx Victoria Yudin has a great new post up covering creating a Dynamics GP Trial balance in FRx and then exporting it to Excel. Published: Monday, July 20, […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: