Excel Report Builder in Dynamics GP

Excel Report Builder is an easy to use tool in Dynamics GP that will let you easily report on information in GP.

This video shows a simple example of how to use it.

Setup New Fiscal Year in Dynamics GP

It's summer time in Montana so that means fly fishing and farmers markets. Oh how I love summertime. All two months of it. (It might end up only one and a half months this year as it's barely starting to warm up now).

Usually this time of year also brings a rash of calls regarding Fiscal Year setup in GP. Fiscal year setup is a very simple thing to do but when you only do it once a year it sometimes intimidates the faint of heart.

Here's how to setup a new fiscal year:

1. Navigate to Dynamics GP>>Tools>>setup>>company>>Fiscal Periods
2. Put your cursor in the year field and type in the new fiscal year (I.E. 2010)
3. Press Tab
4. Verify the dates are correct in the date fields
5. Verify the number of periods you want in the year. Typically 12.
6. Choose calculate
7. You can then check off the periods you don't want anyone to post to yet. You can open these whenever you desire.
8. If you already have these periods setup you may just need to uncheck the modules to allow posting.

Couple of notes:

• Make sure the dates don't overlap any other year
• Make sure you have all dates covered. GP doesn't like any days missing from one year to the next

Sales Taxes, Not so Difficult

Most Dynamics GP users I talk with, complain about the sales tax functionality. They say it's difficult to set up and maintain. I don't necessarily agree.

I just updated the new tax rates effective in California as of July 1, 2009. Because I had setup the taxes using Tax Schedules that contain individual Tax Details to represent the different taxing agencies, all I had to do was update the Tax Details that changed. In my case, the one Tax Detail that changed was Los Angeles County. Once I changed the one Tax Detail, it will effect all Tax Schedules that include that Tax Detail; perhaps six.

The task was simplified because the CA Board of Equalization publishes a list of tax rates. There are maybe 130 different taxes on this. An added bonus; this report includes four character codes that can be used for the Tax Detail ID.

When I set up sales tax in GP, I start with the tax return that needs to be filed on a periodic basis, and figure out how much detail I will need to assist in reporting. I then identify the different tax details that need to be created, and then create the Tax Schedules to contain the details. The set up takes some time, but keeping the rates up to date; not so difficult.

I agree that if you have more than a few states, this can become overwhelming. For those cases, use a service like AvaTax or CCH.

Revenue / Expense Deferral in Dynamics GP

This video shows an example of how to use the Revenue / Expense Deferral module in Dynamics GP to defer sales revenue over a 36 month period from the SOP module, using a deferral profile.

The video shows the module setup, a sample deferral profile, and how a default deferral is applied to an invoice transaction in the SOP module.

The reporting for the module provides a simple but effective analysis of deferral transactions at a user specified date.

Quickly Printing Multiple Invoices for a Customer

If you have the need to print invoices for a customer and some of the invoices are in history (posted), there is a way to do this quickly in Microsoft Dynamics GP version 10. Follow these steps:

1. Click on the Sales page button in the lower left corner of your screen
2. A list of inquiry/report options will be shown on the left side of the screen (above the page buttons). Click on "Sales Transactions" within the list.
3. Enter the customer's name in the "Type to filter" field and then click the arrow button to the right of this field.
4. Click on the "Document Type" column header to sort all of the invoices together.
5. Check the box to the left of each invoice.
6. Click "Print Documents" near the top of the screen.

Dynamics GP will determine if the regular invoice is applicable or if the historical invoice is applicable for each invoice and print all of the marked invoices.

Use the FRx Email option to automatically send reports generated to Excel

In the past, I've rarely seen a use for the FRx output Email Option. If you email a report generated to the FRx Drilldown Viewer, the email will have the report in a Drilldown Viewer file attached or it will have a link to the Drilldown Viewer file. Unfortunately, a report recipient would need to have FRx Drilldown Viewer installed and more importantly, a Drilldown Viewer user license, to open the file and view the report. A Drilldown Viewer license is fairly pricey, so many companies do not have Drilldown user licenses for their report recipients. Instead, the reports are generated to Excel files and then those files are emailed to the recipients.

Recently, I realized that you can use the Email Output Option to automatically send out a report in an Excel file. For example, if you email to the Board of Directors the Balance Sheet, P&L, and Statement of Cash Flows in Excel files each month, you could set the Output Option on each of the reports to automatically email them to each of the Board Members. The only time this won't work is if you are using a reporting tree and only certain units of the tree are to go to only certain people. There is currently no way to do this. (If anyone has figured out a way to do this, please let me know!)

If you routinely generate FRx reports to Excel files and then email those files out, using the Email Out Option will save you some time.

SQL Magic...post script

In my previous blog I talked about how my SQL wizard (Tom Celvi) was able to help migrate masses of data when Excel and Integration Manger could not step handle it. I asked him if he would elaborate briefly as to his steps. Thanks to Tom for this contribution!

A Custom database was created

"I did this so I could keep the initial import data separate from GP and to provide a central point of access for both the GP production and test companies.

This way, I could use the same data and processes for both testing and production deployment without having to re-import the data for both test and production runs.

Additionally, I created a method for capturing the results of each employee import, so we could review failures and successes. I created a single stored procedure to process the data into a company. This stored procedure was developed in the test company, and then deployed and run in the production company once the data import was acceptable.

By placing this data into a separate database, I was able to use the actual GP table definitions and names, which provides some data quality checking at the time of data import."

A view was built in the custom database

"To grab the employee ID’s of any employees that had received checks in the past 6 months.
That view was then used for filtering employees during transaction processing."

Uploaded the flat file data in the custom database.

"I just uploaded the flat file data to the actual GP table definition, but in the custom database. This gave me confidence that the file data was usable, since the original definition was able to accept it."

The stored procedure used eConnect to insert the data into the production and test databases.

"This provided additional system integrity validation (making sure all codes existed and that all data values sent to GP where actually valid)."

"One item of note, it appears that eConnect requires the Class ID information to be rolled down manually to the employee level."

Tips for Footers on FRx Reports

There may be times when the default placement of footers is not acceptable. For example, if a report is multiple pages, the footer is printer immediately under the last line on the page. This presentation can look sloppy and confusing. Another example is when a report is very short and the footer is way down near the bottom of the page. Some users may want the footer information to print higher, more closely to the report.

To fix the problem of the footer printing too close to the last line of the report, you can add the @BLANK code to one or two of the first lines in the footer. Then enter the actual footer codes and/or text on the next lines of the footer. That way, there will always be a line or two between the report data and the footer data.

To fix the problem of the footer printing too low/too far away from the report, you can increase the bottom margin (on the report catalog under Report Options, Page Setup) which will cause the footer to print much higher on the page. I found that 9.0 was the largest bottom footer FRx would except but that was sufficient for a report that had only 6 lines of data.

Row Format Codes to Create Lines and Boxes on FRx Reports

When you are building an FRx report, don't forget that often the layout/presentation of the report can be very effective in making the report easier to read/understand. One of the layout features you might want to consider is the LNE format code. This feature is helpful for separating sections of a report. If you put this format code on a row in your row format, a thin line will be printed across the page. In addition to a thin line, you have other options if you add text in the Description field on the LNE row as follows:
TYPE=2 will print a bold line across the page
TYPE=3 will print a dotted line across the page
TYPE=4 will print two lines across the page, the top one thick and bottom one thin
TYPE=5 will print two lines across the page, the top one thin and bottom one thick

You can also place a box around a row or rows by using the BXB (box begin) and BXC (box complete) format codes. For example, if you wanted to put a box around the Net Income amount, you would put BXB in the format code of the row above the Net Income row and put BXC in the format code of the line below the net income row. If you don't want the box to be around all the columns, enter the desired columns in Column G. If you want the box to be drawn in something other than a thin line, use one of the TYPE= descriptions noted above on the BXB and BXC rows. In this example, a box with a bold outline will be around columns B and C on the Net Income row.

It Pays to Know a SQL Expert, Especially if you are not!

As consultants, we all have aspects of our work that are more frustrating than others. Migration of data is one of those tasks that I prefer to do alone...I tend to swear a lot and it isn't pretty! Last week I had a payroll migration that was one of the most hairy to date and I have been doing this for years.

It started out very vanilla. After all, it doesn't matter the quantity of data...it is all about the quality, right? I only needed current year employee payroll transactions. My methodology is very tried and true. I Download the data into Excel, clean it up, save it, map it to Integration Manager and after a few hit and miss tries...the data has been successfully integrated. I can move on to something more exciting.

My client runs about 2,500 payroll checks a week and there is a massive amount of employee turnover. In the legacy system (which is housed in a separate facility from the new implementation) employees were not inactivated and, after several years, there were literally hundreds of thousands of employee master records.

As in Dynamics GP, the data that I needed was in several different tables, and had to be exported. No problem I think, and dump out the historical payroll transactions via SQL table export. I will do the same thing with the entire employee master record table, Do a VLook-up to parse out the employees that haven't been paid this year...and there you go... I have what I need to import into my new GP database.

Who knew there was a limit to how much data Excel could handle. Yes, I know, there is a max. amount of rows - and Excel 2007 can hold a lot! Apparently, not enough for my employee master and state tax file. After several attempts (and a lot of swearing) I have the data in separate workbooks. Now to get rid of the employees I do not need. Not so easy. The calculation speed was painfully slow and while I could import data to all 1M rows, Excel couldn't run the formula due to memory constraints (so I am told.) 12 hours later I am completely frustrated and at my witts end.

This is where the story gets good. My fairy godmother (a.k.a. my boss) tells me to quit pulling my hair out and call Tom Celvi. He is a SQL wizard! I am a bean-counter and application consultant. I have always thought of SQL as something to be respected and a bit feared. After all, it is the "house" for my GP data.

Silly me! There is a whole other world out there that I owe myself to learn more about. Tom has shown me that SQL is a very powerful tool and can be used for other things besides housing my precious GP databases.

Tom created a database within the clients SQL 2008 environment, imported all my raw, ugly data and through a series events that I can only explain as pure alchemy...he managed to move that cleaned up data into my GP database and I had my data in all the correct payroll tables along with only the 12,000 current employees!

This took Tom 10 hours in total and I bet he didn't swear once! I was then able to successfully integrate the current year payroll transactions via Integration Manager and my my client is live and processing payroll.

Under lessons learned:

1. SQL doesn't have to be feared, it can be your friend (but should be respected)


2. Quality over Quantity is over rated - strive for both because Excel does have its limits.


3. hang out with a SQL guru, it will save on hair dye

Hosting model for Dynamics GP

As you may know Rose Business Solutions has a sister company called Rose ASP. This is a company that hosts all things Dynamics GP and SL and products that tie into those systems. The company works with Microsoft Partners to provide a solution for those customers that don't want to have the huge outlay in buying servers, finding IT resources to maintain the GP install etc. (For a good overview of hosting here's a demo from our own Rock/Movie Star Linda Rose.)



Hosting is a pretty big buzz word now a days. While at Convergence this year I heard several people say "We host" or "We'd like to host". I heard several approaches as to how people host as well.

Here are some of the wide ranging approaches to hosting that I've heard:

• "We provide the hardware, the partner does everything else"
  
• "We only host for our GP clients"
  
• "We can host anything you want. Any module, any application"
  
• "We only host Dynamics products no 3rd party products"
  
• "We offer SPLA pricing"

It will be interesting to see where hosting Dynamics evolves to in the long term. I personally think things are going to get easier with GP and hosting. Right now there are particular challenges with hosting an application that is not necessarily built for an in the cloud delivery model. I'm assuming MBS will catch up with technology and make it easier to host the application.

The advantages of hosting are great for most customers. As my good buddy Jason Young of RoseASP put it:


"What you really get out of hosting is an infrastructure and connectivity that is out of reach for most companies. It's like buying an airplane ticket. Owning a 747 is waaaay beyond most people. But buying one seat and using its facilities is well within reach."


I see hosting or more specific how Dynamics is deployed in the future effecting many Dynamics partners and customers.

Where do you thing hosting is going for the Dynamics product line?