Rose Business Solutions Dynamics GP

Archive for May, 2010

FRx is an excellent financial reporting tool.  It provides so much flexibility and control over the selection of data and the formatting of the reports.

If you’re new to FRx you may find it a bit difficult getting acquainted with it.  I recommend that you try using the Report Wizard to create your first few reports.  It will give you a good start by creating some basic row formats, column layouts, and reporting trees.

To start the Report Wizard just click on the icon in the top menu bar.

Your experience will be enhanced if you’ve judiciously assigned your accounts to Categories in GP.

I created this short video to show how this works:  Financial Reports with FRx in Dynamics GP

Patrick over at Developing for Dynamics has a great solution for those of you using Dynamics GP 10 and beyond.

I tested this out on my stinky old PC. I downloaded 20,000 rows in about 50 seconds after making the dex.ini switch. Without the switch I quite after a 1 min and 15 seconds and had only downloaded 2000 rows. Extrapolated that would have taken 12.5 mins to do 20,000.

This has been a limiting aspect of smartlist in the past so was pretty syched to see this solution.

What a great find.

Is it me or is this the time of year auditors really become annoying (or maybe I should say more annoying than usual.) Here we are several months past end of year and you get requests like “what were your outstanding checks for 12/31 of last year?” If you haven’t kept all your posting journals in binder (I still see many clients running through this silly process of killing enumerable trees) you may curse the day you installed GP as there is no “Outstanding transactions” report available in the bank module as of a certain date. In fact it is only available to print posting journals when you reconcile your bank account.

Well have no fear fellow Dynamics GP user. Here is the solution to your auditors audacious request.

Method 1 Using Smartlist

1. Open Financial>>bank transactions
2. Add “Cleared Date” column
3. Add search criteria on GL posting date to be “Less Than” the transaction date you desire
4. Add Checkbook ID to search criteria if you have more than one checkbook
5. Export to Excel (Here is where it gets a little cumbersome)
6. Sort by GL posting date and Cleared date
7. Delete any rows that cleared date is equal to or less than the desired date. (Please note that the cleared date on some may be blank or “0/0/0000” if they have not been cleared yet. You will need to still include those transactions. Some may have been voided as well so you will have to exclude those transactions as well. You may also have to add the “Voided” and “Void GL Posting Date” if you have to look at those variables with your data.)
8. Sum up transactions if you desire

This should equal your outstanding transactions for that date in time.

Method 2 Using Report Writer

1. Print to screen Reports>>Financial>>Checkbook>>Checkbook Register
2. Choose modify
3. Add Cleared Date to the checkbook register report as shown below
4. Save report and give security access to modified report
5. Open Reports>>Financial>>Checkbook>>Checkbook Register again
6. Insert restrictions on checkbook ID and Date. I left everything from 00/00/0000 to the date required. You could restrict it a bit if you don’t want to show everything before the beginning of time.
7. Send report to a file
8. Open with Excel.
9. Here’s where it gets kind of cumbersome. You will need to sort and delete out all headers, lines, *’s (voids) etc until you only have the rows of data.
10. The data seems to all go to one column. You will need to use text to column function in Excel (Data ribbon). I would try fixed width without doing anything fancy with fixed width. Seems to do the trick.   
11. Then you can sort again by transaction date and cleared date.
12. Delete all rows that the cleared date is before the date you desire

This should equal your outstanding checks for that period of time.

Whew, I did this approach for a client the other day and it tied out to the penny. I’m not going to say I was totally surprised but I will say the client was. Not only that the approached tied out correctly but that a sophisticated system makes you go through such gyrations.

Looks like I’m heading south to San Diego to visit a few customer sites the week of May 17th to 21st. It will be nice to see old friends, some of which I’ve only met over the phone.

Let me know if you are up for a site visit and I’ll have my people contact your people.