Rose Business Solutions Dynamics GP

Retrieving information from Dynamics GP for reporting purposes is so easy to do because there are several great tools available and there are many sources of information on how to use the tools and specific code to make and build reports.

Victoria Yudin writes a blog in which she generously shares SQL code that allows you to easily create SQL views to retrieve information from Dynamics GP.  Victoria wrote this article this week, and in it includes code for creating a SQL View that retrieves information about payables invoices and associated purchase order line details.  This is fantastic.

You can easily copy the code from her blog and paste it into a new query using SQL Server Management Studio.  Execute the query and it will create the view.  The SQL view can be used in Excel and SmartList Builder to create SmartLists that all users can have access to.

I created this short video that shows how easy it is to do.

I’ve blogged about this topic before but it seems like things were way too easy to find on partnersource (better make sure everyone catches my dry wit) so MBS thought they would spice things up a bit.

I hit up a few MBS folks at WPC and they actually had a few work arounds to my heated complaints. I better give credit to Shane Kvalevog for some of these insights.

1. When searching the knowledge base make sure you limit your search to only include the knowledgebase. (duh)

• Also select the language but leave all countries as many articles are tagged as all countries not just for the US.
• You can save your default settings so you don’t have to keep doing this every time you try to search.

      2. I mentioned I still use the old search tool as I had it booked marked and it was still available. He looked a little offended and said it was going away soon. I pleaded for him to keep it just a little while longer as it seemed like I can find things better with it. It’s still around but maybe it’s just because MBS forgot about it.

      3. My own input here. Google what you are looking for first. I tried Bing a bunch but for some reason Google just finds more out there.

Hopefully that helps with your searching endeavors.

I just finished up my first project using Management Reporter (MR). As I said in this blog, I learn best by getting a project and digging in. It took a while to get used to the new tool but it is very similiar to FRx and it didn’t take that long to feel comfortable.

Here are a few items that tripped me up:

• Why would this setup give you the error: This report contains one or more errors that prevent it from being rendered.

 

Answer: If you have any TOT or CAL row that references a blank space you will error out. (See row 200 being referenced in row 1300’s total.)

• If you don’t see any data coming up even though MR lets you pull accounts from dimension there is a setting in company setup that wasn’t checked. Companies>>company>>source system (choose settings)>>additional settings.

Put the radio button in Dynamics GP Financials if you don’t have analytical accounting data.

Another couple of notes. If you are planning to migrate from FRx to MR you might want to wait until after the next hot fix comes out. It will fix a lot of issues.

Also, if you export out the building block groups (spec set formats) make sure the account formats are the same as the original. The account numbers on the rows will go away.

And one last thing. The errors in MR are terrible. Try looking at the event viewer in administrative tools. That will give you more detail than the generic, non descript errors.

I don’t usually like posting on break/fix type issues. It’s pretty hard making GL_Account_MSTR posts entertaining and most of the usual Dynamics headaches are documented on the knowledge base or someone elses blog. But for some reason I couldn’t find this error anywhere.

So instead of giving you the anwer right off I need to at least tell a story to make this a little more interesting.

Most of the time I work directly with our clients to fix a wide range of issues.  Over the weekend our CEO came in to work but was met with a whole litany of errors when running reports in Dynamics. Dealing with a client is one thing but I always feel added pressure to resolve issues quickly when dealing with the big boss lady.

Hoping to impress I jump into action thinking maybe, just maybe, if I do this right I can secure a pay raise or something. Much to my chagrin not a single soul has posted about this error. I did find posts of similar errors and one was from my most trusted colleague Steve chapman.

After a bit of hemming and hawing I dust off the cobwebs from the weekend and remember our most excellent payroll guru applied the new HIRE Act update for payroll to our own system late last week. Mike from our office explaines the HIRE Act well in this post.

Now, my number one rule for doing any type of update or upgrade is to recreate the reports.dic file.  It never fails (well, at least 50% of the time) Reports.dic gets corrupted. Don’t even say it David Musgrave. I know you are right about shared reports.dic files.

After recreating the reports.dic file everything was peachy keen. In the recreation process I found the 3 reports that had issues were:

• Tax Schedule List
• Trial Balance Detail
• Trial Balance Detail by Period

I’m still waiting to hear about that pay raise.

If your are struggling with installation of the Round 5 Tax Code Update for Microsoft Dynamics GP payroll, there is an additional wrinkle you probably have not realized.  The documentation for the update anticipates that you will have installed the tax code udate prior to any payroll runs in July (July being the start of 3rd quarter reporting).  The documentatiton instructs you to run the report for the 2nd quarter only.  If you had payroll runs in July (3rd quarter) before you install the Round 5 Tax Code you have FICA amounts posted to the general ledger that should be adjusted.

 If you did have payroll runs in July, it is important to run the Payroll Tax Adjustment Report (HIRE) for the third quarter to identify FICA amounts that you will have to back out of your general ledger.  Another word of caution.  Be sure to run the Payroll Tax Report after installing the Round 5 Tax Code, but before you run any more payrolls in July.  The report will list all FICA amounts for the ENTIRE quarter so it is important to understand under what conditions you are running the report.

The HIRE Act actually went into effect on March 19, 2010.  If you had HIRE Act qualified employees for the period 3/19/2010 to 3/31/2010, then you should determine the general ledger adjustments for FICA wages by reviewing historical payroll posting journals, and again make the necessary adjustments to FICA expense and liability. Running the Payroll Tax Adjustment Report will list all FICA wages for HIRE Act qualified employess for the ENTIRE 1st quarter.

I don’t want to sound unkind, but there must be an easier way to encourage employers to hire new employees.

This stuff is hilarious and a nuisance.  Here are some examples of the spam we’re getting at this blog:

“I dont know what to say. It is definitely among the superior blogs Ive understand. Youre so insightful, have so much genuine things to bring for the table. I hope that a lot more people read this and get what I got from it: chills. Great occupation and fantastic web site. I cant wait to examine more, preserve them comin!”

“I dont know what to say. This can be definitely among the greater blogs Ive go through. Youre so insightful, have much real things to bring on the table. I wish that additional men and women read this and get what I got from it: chills. Excellent career and great website. I cant wait to examine far more, maintain them comin!”

“Numerous of my buddies mentioned to me that numerous content articles about the weblog could be of misleading towards the readers as numerous from the composing are as well subjective. I just agreed with them but I do believe that you will find nevertheless some blogs which can bring up great worth towards the reader. One of the most essential point is we as reader require to study after which reflecting ourselve without having accept every thing that the writer composing about. Therefore, I usually maintain this principle for all of the weblog post and I believe you might believe the exact same way as I believe.”

Frank Hamelly on his blog GP2themax recently showed an example of how to use Field Level Security to add a security restriction to a standard GP form.

His article is here.

I created a short video to show this in action.

It’s very easy to do.  You should try it out.

I must admit I haven’t been the biggest Bill Clinton fan in the past…but I was still intrigued to listen to former president Clinton. He certainly knew the crowd he was preaching to. Great jokes for the you to middle aged IT crowd. We got there early and found the right portal to go down when the lines opened. We ended up 3 rows back right down on ice level. Seats would have been perfect if it was a Capitals game.

Main focus of the speech was where does he see the world going and how does technology play into that. Some take aways are listed below:

1. We know WHAT needs to be done as a whole. We still need to figure out HOW to accomplish things

2. Technology has increased capabilities for humanitarian efforts. No way his foundation could do as much good as they have done without the aid of technology.

3. Improved technology is directly related to efficiency to increase return on land production. For example on a quarter acre of land return was $60 and now it’s increased to $400 in South Africa. Another one is price of medication for aids has dramatically decreased in the past couple of years.

4. Social networking is being used to keep donors constantly in touch with foundation activities as well as keeping donor opportunities open throughout the year

5. “Old gray hairs” is now using twitter which he never thought would

6. Pres. Clinton is looking for solutions about providing educational facilities and opportunities for children. He asked for suggestions and volunteers. He had one person say to him “I’m retired, please send me somewhere I can be of use to you”.

7. Standing ovation was in order. He really seemed down to earth and sincere. Has spent a lot of time (since 2001) helping people in need. Mentioned Haiti quite a bit.

Great stuff. Glad I got there early to get good seats. In person he is quite witty and charming and had the crowd in the palm of his hands. Look at me, I have a crush. I better stop before I change political parties.

I’m hanging out at our booth at WPC in D.C. and I thought I’d write down my thoughts so far regarding the conference.

1. I’ve heard the phrase cloud computing innumerable times in the last 2 days

2. Convergence is really different than WPC. It’s kind of funny to have to explain what Dynamics is

3. WPC is way more internationally based than Convergence. Of course Convergence has it’s international attendees but I think I’ve been on 3 different bus rides and was the only one speaking conversational English. Kind of cool being surrounded by such a wide range of people

4. The traffic we’ve had at our myGPcloud booth has been at some times overwhelming. Lot’s of interest.

5. D.C. is hot and humid. I love it.

6. People love T-shirts. Glad we don’t have to bring 4 boxes home.

7. I love meeting and talking with people. Nice to get out of my cage once and a while.

8. 5 for 1 exam passes are a great deal.

9. New competency announced around the cloud offering. Not quite sure of the details but it relates to BPOS partners.

10. Waxing legs doesn’t really help if you are a male booth babe.

Any fellow Blogger blog follower at WPC? I’m at the myGPcloud booth on the 100 row. I’d love to meet anyone that follows my random thoughts.

More to come.

The Word template forms in GP2010 have some nice advantages to Report Writer forms such as increase graphics capabilities and the ability to assign customer specific sales forms. Modifying these Word templates can be tricky and there is little documentation to date. The first thing to know is that, when installing a GP 2010 client, you need to install the “Microsoft GP Add-in for Microsoft Word” found under additional products in the installation media. This Add-in will add access to Great Plains fields from within Word. Once installed, you will find a “Field List” icon on the Developer tab of Word.

  

If you don’t see your Developer tab, you can enable the Developer tab in Word Options – Popular. In Office 2007, it looks like:

The following are instructions for modifying Word Templates, including adding a GP field to a Word template found under Reports – Template Maintenance:

 

1. Select report name drop down, navigate to the report that you want modified, and select that report.
2. Click the NEW button, choose existing template button, select your chosen template, and give it a new name.
3. Then choose the modify button. Word will open for modification.
4. In order to add a field to an empty space on the Word document, you need to first insert a table.
5. Navigate to the designer tab in Word, select your field, then drag field into the new table
6. Save the Word document
7. In GP template maintenance, select your modified template, choose the plus button, and then navigate to your modified Word document and select your modified Word document. This step updates your GP template form with the changes made to the Word document.
8. Choose the Assign button and assign your modified template as the default form for a company or customer.