New Article on MSDynamicsWorld: Michael Johnson on "Filtering Salesperson-specific data with SSRS"

"It didn't take long to figure out that the problem wasn't with Business Portal, but rather the way they were trying to use it and NOT using SQL Server Reporting Services (SSRS). Both tools have a place in their environment, but one is not a substitute for another"

My friend Michael Johnson, a.k.a. "The MBS Guru" has finally come out of hiding and has decided to publish a really clever solution for filtering salesperson-specific data in GP using SSRS over at MSDynamicsWorld. The good thing about Michael's solution is, you can apply it to pretty much any report that requires such type of access and filtering capabilities. They don't call this guy the MBS Guru for nothing!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

From the Newsgroups: Multicurrency POP Receipts

Welcome to another weekly edition of the new From the Newsgroups blog entry. This week's topic revolves around Multicurrency POP Receipts and a feature available in GP. As usual, no names will be given out, just the question and the answer.

Q: Functional currency is CAN [Ed: Canadian Dollars]. Item has a receipt that was for qty of 6000 each at .62 each USD currency. Vendor is setup as a US vendor. In the fifo layers it shows a receipt for 5999 at .62 each then another line for a qty of 1 at $12.57. Why is the system showing two lines for this receipt and breaking out the exchange all in one line. The exchange should spread over the qty of 6000 not qty 1.

We know there is an issue with split receipts when the item has more than 2 decimal points or there is landed cost involved. That is not the case here. This appears to be because of the exchange rate.

The response is as follows:

A: Good Day.

Thank you for using Forums.

With your exchange rate, it would appear that you have 6000 qty at an extended cost of $3731.95. Is this correct?

If so, the way GP handles a situation where the qty is not evenly divided into the extended cost is to produce a split receipt. The 'logic' behind it takes as many qty at the 'unit cost' as possible, and then puts the left over amount on the last single qty. This is the way this has worked as long as we've been using split receipts.

This is not so much a matter of putting the 'exchange amount' on one qty, but rather that we can't spread the extended cost over the 6000 qty evenly. $3731.95 divided by 6000 is .6219916666666666666 repeating. We have to use some mechanism to have 6000 qty in our system at total cost of $3731.95. The method we use is a split reciept of 5999 @ .62 and 1 @ 12.57. This is standard GP functionality and has been for many versions.

If you have some suggestions on doing this in a different manner, please feel free to enter a product suggestion so that it can be reviewed for possible future releases.

Thanks!

Some of the features in GP are simply hidden to the naked eye unless you are dealing with a number of situations day in and day out. Multicurrency is one such situation.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

IM - How to group Integration Manager transactions based on transaction date

Just recently, I fielded a question where the user wanted to group a number of customer invoice transactions in a file based on the invoice date. The file happened to contain invoices downloaded from the billing system in a date range, for example 02/01/2010 - 02/05/2010. They wanted the resulting transaction batches in GP to be something like,

AR02012010
AR02022010
AR02032010
AR02042010
AR02052010

This would facilitate analysis by day and to make sure control totals matched those created on a daily basis.

---

The solution involves adding a field script to the batch ID field which in turn reads the transaction date field from the source query and forms the new batch ID with that information:




Very simple, but useful script. Keep in mind that you must enable the option to Add Missing batches for the integration as shown below:



When the integration is executed, it will create as many AR batches in Microsoft Dynamics GP as unique transaction dates there are in the file.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

"Advanced Excel for Microsoft Dynamics GP" Training

My friend, MVP Mark Polino is hosting a 2-day class from March 1 - 2, 2010 at the IBIS, Inc., offices in Norcross, Georgia (20 minutes north of downtown Atlanta). Mark promises some blood after you leave this class as nothing will hold you back anymore from connecting Microsoft Dynamics GP and Microsoft Office Excel -- not my words!

Take advantage of this unique opportunity to get some training from one of the best in the business, so click here for more information.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

See you at Microsoft Dynamics Convergence 2010

Great News! I am going to my first Microsoft Convergence conference as a presenter and will be presenting two concurrent sessions (more on this soon) with my partner in crime, David Musgrave. David and I presented together back in November at the Microsoft Dynamics GP Technical Conference 2009 and it was a great success. So now, at the Microsoft Dynamics Convergence 2010, we get a chance to do it again.

Here are some Microsoft Dynamics Convergence resources to start gearing up for April:

• Microsoft Convergence 2010 Atlanta Registration Site - Click here


• David Musgrave's Convergence 2010 announcement article - Click here for more info.


• If you did not get a chance to see MVP Mark Polino at Convergence 2009 in New Orleans, then no worries. He is back with his "50 Tips in 50 Minutes" session this year in Atlanta - Click here for more info.


• If you are interested in volunteering opportunities at Convergence you need to watch the following video for more information.
  
  

http://www.youtube.com/watch?v=9gWgaw_CXZM

See you in my backyard.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

Microsoft Dynamics GP 2010 - International Enhancements

After spending a week in Costa Rica enjoying the spectacular beaches and the fabulous weather in San Jose (although my purpose in San Jose was to deliver Microsoft Dexterity training), I thought it was just appropriate to blog about the international features sported by Microsoft Dynamics GP 2010. The bottom line, there are quite a bit of enhancements for the international community, so let's get right to them:

1. VAT Daybook. You will be pleased to know that the VAT daybook has become a feature available for installation to all countries. Since value added taxes are very common across the globe with most countries requiring pretty much the same tracking and reporting capabilities, the good folks at MS have decided to make it available to everyone.

For you folks up in Canada, the VAT Daybook can be used to generate the Canadian GST/HST return in electronic format. You can set up the reporting periods for the return as specified by the tax Canada Revenue Agency. You can assign the tax detail IDs to consider for each line of the return. You can set up the default claim percentage for input tax credit for each tax detail ID. You can change this percentage for each transaction before you generate the return. You can resubmit the return after making changes, if required.

Vous serez heureux de savoir que le fil de la journée de la TVA est devenue une caractéristique disponible pour l'installation de tous les pays. Depuis la valeur ajoutée les impôts sont très courants dans le monde entier avec la plupart des pays ayant besoin d'à peu près le même suivi et de reporting, les braves gens qui habitaient les États membres ont décidé de le rendre accessible à tous.

Pour vous, les amis au Canada, le fil de la journée la TVA peut être utilisé pour générer la TPS canadienne / TVH en format électronique. Vous pouvez configurer les périodes de déclaration pour le retour tel que spécifié par l'impôt du revenu du Canada Agence. Vous pouvez attribuer le détail impôt identifiants à considérer pour chaque ligne de la déclaration. Vous pouvez configurer le pourcentage par défaut de réclamation de crédit d'impôt d'entrée de chaque détail d'identification fiscale. Vous pouvez changer ce pourcentage pour chaque transaction avant que vous produisez la déclaration. Vous pouvez renvoyer le retour en cas de modifications, si nécessaire.

2. Fixed Assets Enhancements. For you folks in Australia and New Zeland you will be pleased to know that Fixed Assets Enhancements is a tool that allows you to create a depreciation method to calculate depreciation for low-cost and low-value assets. This product is automatically installed when you choose to install the Fixed Assets Management product for Australia or New Zealand installations.

3. Export Financial Data. Export Financial Data is a tool that allows you to export the ACCON account balance report for the users in Belgium and the ETAFI account balance report for the users in France. These reports are exported to a format that can be imported by the respective legal authorities. This product is available for selection only for Belgian or French installations of Microsoft Dynamics GP.

Données Financières de L'Exportation est un outil qui vous permet d'exporter les soldes des comptes rapport Accone en Belgique, et le rapport solde du compte ETAFI pour les utilisateurs en France. Ces rapports sont exportés vers un format qui peut être importé par les autorités judiciaires.. Ce produit est disponible pour la sélection uniquement pour les installations belge ou française de Microsoft Dynamics GP.

4. "Principles of Data Access and Verifiability of Digital Documents". GDPDU is a tool that allows you to extract tax related data from Microsoft Dynamics GP based German legal requirements. You can send this data to the auditor for analysis before submitting the final report to the tax authorities.

You can choose to install this feature from the ...\DvdImage\Interntl\Products\ folder located within your Microsoft Dynamics GP installation package.

GDPDU - Grundsätze zum Datenzugriff und zur Prüfbarkeit digitaler Unterlagen -ist ein werkzeug, dass sie die steuerrelevanten daten aus Microsoft Dynamics GP lebenden deutschen gesetzlichen anforderungen zu extrahieren. Sie können diese daten an den abschlussprüfer für die Analyse zu schicken, bevor die vorlage des abschlussberichts an den fiskus.

Sie können wählen, um diese funktion aus dem ... \DvdImage\Interntl\Products\ in ihrem Microsoft Dynamics GP installationspaket befindet.

Now, you will want to excuse my French and German, but I did the best I could... now, go enjoy your new features!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Read More

New article on MSDynamicsWorld: MVP Victoria Yudin on "Taking charge of your own support"

"When I first started using Microsoft Dynamics GP almost twenty years ago... there were limited options for getting help"

MVP Victoria Yudin gives an account of her 20-year career history with Microsoft Dynamics GP and how the support landscape has changed over those years. She also gives out some important pointers on how to request help on newsgroups, forums, and even from Microsoft Dynamics GP Support. Be sure to check out her article over at MSDynamicsWorld.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Read More