Microsoft Dynamics GP Excel Dashboards

Over the past two weeks, I have downloaded, installed, and been playing with my latest demo toy: Excel Dashboards for Microsoft Dynamics GP! In all fairness, this is not a new product, but rather a research being conducted by the Microsoft Dynamics GP Product Management team on the subject of Business Intelligence. However and to drive the topic, they have created a really cool dashboard using Microsoft Excel 2013, which I also happen to be running on my machine.

Excel Dashboard

Installation

The first thing to do is to download the dashboard which you can find here. Of course, most of the settings are tailored for my friend and Microsoft Program Manager, Jared Hall's computer, who kindly put this dashboard together for your delight, so some work is needed to make things work.

Once you have downloaded the spreadsheet, you will need to follow each of the steps provided on the Instructions sheet in the workbook, including running an embedded stored procedure against your company databases. This stored procedure compiles some GL ratios and piggy back on a few already pre-written table valued functions created for the SRS KPI reports.

Dashboard Instructions sheet

The additional steps (3 thru 6) explain how to change the data connections to look at the company database in your environment. Very key is, if you ARE NOT running Microsoft Dynamics CRM, you should follow step 6 to disable the data connections. In particular, I did not have CRM on my laptop, so I removed the data connections and remove the rows where data was supposed to be pulled from CRM to have a clean looking spreadsheet.

I have now showed this dashboard to two prospective clients and needless to say they have all drooled over it, since it works with the financial data in real time. This dashboard plus the Microsoft Dynamics GP 2013 Web Client, and Microsoft Excel 2013 have all been a total hit!

NOTE: Microsoft Office 2013 is available via MSDN or you can download the preview from the Microsoft Office site.

Of course, a research won't be complete without feedback, so here's where you come in. The Microsoft Dynamics GP Product Management team is asking you to answer 3 simple questions:

1. What changes would you make to the current dashboard? Of course, to answer this one you effectively need to have downloaded and play with it for a bit.

2. What other dashboards and what information would you incorporate according to your role in your organization?

3. Of course, what do you do? What's your role in the organization and what challenges do you face?

Please feel free to direct your answers to the Inside Microsoft Dynamics GP blog as comments to the original article:

Business Intelligence Investigation

Now, to my esteemed colleagues in the Product Management team, it would be cool if these questions could be added to a short survey on your site (or elsewhere) as it would make people want to actually provide some feedback... just saying!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Codename GP "12" Preliminary Features Series - 1 of 4

Codename GP "12" Preliminary Features - Part 1 

This is article is part 1 of 4 from the series Codename GP "12" Preliminary Features. Some images and content reproduced with express permission from Microsoft Business Solutions, a division of Microsoft Corporation.

DISCLAIMER: These features are subject to change.

---

I am only repeating what I have heard so please don't shoot the messenger. With GP "12" to be released "sometimes next year" - and yes, December 31, 2012 is still within that time frame - there may still be features on the following list that may not make the cut. However, it was fairly clear that the boys and girls on the Microsoft Dynamics GP development team in Fargo are working their rears off to get the feature list to a state where they feel pretty comfortable, meeting the demands on the shopping list.

Straight out of GPUG Summit's closing session, comes some of the top features being worked on, based on the traditional 4 pillar goals:

4 Core Design Pillars

Each pillar allows the engineering team to showcase a list of features that will support the objectives behind pillar.

Simplicity

The PM Reprint Check Remittance, for instance, will allow users to re-print the check remittance without having to generate the check.

Improvements have also been considered for the FA Calendar Setup feature. In this case, the Fixed Assets calendar does not have to match the fiscal calendar. You can now have multiple calendars, for example, having an asset depreciate on a fiscal year for tax purposes, if tax year and fiscal year are different, and depreciate calendar year for financial reporting purposes.
  
The Journal Entry History Inquiry will see enhancements too. In today's world, the existing window only looks at the open tables. The plan for GP "12" is to have it look at both open and/or history.

In the reporting area, you will be delighted to know that you will now be able to choose a printer at print time. This feature was only 20 years in the making, but it's finally here! Hey, I remember like if it was yesterday, when Windows True Type fonts became a standard part of Report Writer reports. Before that, we only had 4 fonts to play with. Challenge: name the 4 fonts available prior to the introduction of True Type fonts.

SSRS Simplicity

In addition to the printer at print time, one of the most awaited features is the ability to print SSRS reports from right within Microsoft Dynamics GP, this is, you will no longer need to wait for the Internet Explorer browser to load Report Server to display the report and will rather see the report from within GP as if you were looking at a Report Writer report in the report layout window... ah, and before I forget, Report Writer will eventually be phased out as the predominant options to render reports.

I don't know how this plays with all that Word Template functionality released fairly recently, but I am sure a lot of you will jump on one feet in happiness knowing that you no longer will need to suffer through the tortuous process of customizing a report. My instinct tells me, that Report Writer will mainly subsists as a data delivery mechanism for XML files needed for your beloved Word Templates. If history and memory serve me well, Microsoft rarely gets rid of a working function within its products, except it becomes as annoying as the Office Paperclip Assistant. Now that I come to think, Report Writer is got to be up there for a lot of you.

I will continue tomorrow with the Productivity pillar.
Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Report Writer Week!

In this corner: David Musgrave

In this corner: Mark Polino

In case you have missed the heated debates - all within cordiality, though there was even a call for insanity - between David Musgrave and Mark Polino on Report Writer, I now join in to continue fueling the heat by declaring this the official Report Writer week!

To make this an even more attractive proposition for you the reader, The Dynamics GP Blogster is siding  (more like ganging up against Mark :-) ) with Developing for Dynamics GP to deliver some really cool Report Writer articles and to show some advanced techniques for developing reports... yes, with Report Writer.

Until next post!

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

Read More

How to resize logo in Microsoft Dynamics GP Word Templates

Getting used to the new Word Templates functionality can be a bit challenging at times. One of those challenges came in the form or a simple question: "How to resize the logo on the template?". One thing to keep in mind is that most objects on a Microsoft Dynamics GP Word Template document live inside of content controls. With that said, here are the steps to achieve this, taking the SOP Blank Invoice Form Template* as an example:

1. Under Report Template Maintenance, select the SOP Blank Invoice Form template from the More Reports list.

2. Click the New button to create a new template. For this example, we will create the template based on the existing template and we will call it SOP Blank Invoice Form Icon. Click Ok to continue.

3. Highlight the newly created template, then click the Modify button to open Microsoft Word.

4. Click on the Picture content control to add a logo image.

Note: This image must also exist in the Image library under Template Configuration.

5. Select the logo, right-click, and chose Remove Content Control.

6. Highlight the image and resize accordingly. The following is a sample of the resized image with some extra text to go along:

NOTE: The logo is part of the Header section of the document.

7. Save the modified template to a different directory where it can be imported from later on, for example, My Documents. Close Microsoft Word.

8. Back to Report Template Maintenance, click the Add Template button and select the newly modified template from the My Documents folder. Replace the existing one when prompted.

9. Click the Assign button, then mark the company you want to assign the template to. Then, click on Set as Default to make the new template the default template for the company. Click Save to continue. Click Save to continue.

10. Back to the Report Template Maintenance window, highlight the original template and click the Assign button and choose Company. Unmark the company and choose Save.

NOTE: This will ensure that the copied version of the original template prints always.

11. To test, select a SOP invoice and print.

Hope you found these steps simple to follow and that you are enjoying the new Word Templates feature in Microsoft Dynamics GP 2010.

Until next post!

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

Read More

RW - Creating Branching Logic Fields in Report Writer

After a long hiatus from blogging, here I am again with another interesting case straight from one of my clients in Venezuela.

As much as many of you dislike Report Writer, I still have to acknowledge that it still has its benefits, especially when working with tightly integrated Dynamics GP reports. Yeah, yeah, build it in SSRS, build it in Crystal, but it's always good when you don't have to build a complex report from scratch and can leverage the built-in application functionality.

The client calls me up and asks, "how can I create a CASE...END CASE statement in Report Writer?" In other reporting systems and SmartList Builder for example, it is possible to enter simple formulas that support complex conditional branching logic, for example:


CASE X
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
END CASE

While it's not as apparent in Microsoft Dynamics GP Report Writer, it is possible to support this type of conditional structures by using cascading conditional fields. To illustrate cascading conditional fields, we will work with this simple example: say your company just acquired another firm with customers in Arizona, New Mexico, and Utha and you have to send out invoices this month incorporating a greeting message for customers in those states. The message says something along these lines "XYZ welcomes its new customers in [State here]". Let's see how we can accomplish this in Report Writer.

1. First, we create inner condition -- WHEN 3 THEN.



2. Second, we create the condition leading to the inner condition



3. Finally, we create the top level condition



4. With the calculated fields in place, we may now drag the 3 of them onto the Report Layout window, then change their Visibility property to Hide When Empty. That's it! Now you have created branching logic using cascading conditional fields. May not be pretty, but sure gets the job done.



A couple things to note:

a) you can create conditional branching on different fields as well using the same technique in Report Writer, for example:


CASE X
WHEN 1 THEN
CASE Y
WHEN 'A' THEN 'Z'
WHEN 'B' THEN 'Y'
...
END CASE
WHEN 2 THEN
'Something Else'
END CASE


Needless to say, the more complex your branching logic, you may be more inclined to use something like VBA, or.. ehem.. Crystal or SSRS.

Until next post!

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

Read More

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

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

Parsing Long String fields in Extender for using with Report Writer

A few days aback I came across a Partner Forum question where the partner was attempting to add an Extender field to a Report Writer report, not without his share set of challenges.

In their infinite wisdom, the folks at eOne added a trigger to the rw_TableHeaderString Report Writer function which allows them to expose data to Report Writer without having to create alternate versions of a report in their application. In turn, with a few steps outlined in the Extender manual, users can invoke the rw_TableHeaderString as a user-defined function in a string calculated field to retrieve the piece of data needed from an Extender table by passing in the Window ID, the key fields, and the position of the field to retrieve on the Extender window. This is an example from such call:

Calculated Field: EXTENDER_KEY
Expression Type: Calculated
Result Type: String
Expression: STRIP( SOP_HDR_WORK.SOP Number )

Calculated Field: (C) AdditionalShippingInfo
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 )

This is all good! But here comes the issue... Extender Long String fields are 255 characters long and Report Writer string calculated fields support up to 80 characters. The partner tried to use the rw_ParseString Report Writer function to parse the Extender string in various lines as follows:

Calculated Field: (C) AdditionalShippingInfo_Line1
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_ParseString FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 ) 50 1)

Calculated Field: (C) AdditionalShippingInfo_Line2
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_ParseString FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 ) 50 2)

Of course, when the report was executed it threw an "Error in Equation" error as Report Writer does not support nesting of user-defined function scripts.

At this point, the only option available in order to be able to retrieve a long string and print it on the report is VBA, ADO, and a SQL Server view. The following is the process with references to articles that will help you with each step:

1. Create an Extender view of your data. You may start by reviewing Creating SQL Views of Extender Data over at Developing for Dynamics GP to get an understanding of this process. David Musgrave also outlines a sample view to get you started.

2. Create string calculated fields on your report that will be used to parse the Extender Long String field.

3. Add your report to VBA and add the string calculated fields created in step 2 to the VBA project. Also, add any key fields on the report needed to retrieve the data, i.e., SOP Number.

4. Use ADO to query the view for the information stored and store the data in the calculated fields. You may want to review Using ADO with VBA with Report Writer over at Developing for Dynamics GP for samples on the technique.

While the workaround might seem a bit lengthy, the results will speak for themselves, so don't give up on Report Writer just yet :).

Until next post!

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

Read More

New article on MSDynamicsWorld: MVP Mark Polino and his Nostradamus predictions for 2010

"2010 will be a milestone year for Microsoft Dynamics GP..."

Microsoft Dynamics GP has a new Nostradamus in MVP Mark Polino. Mark looks into his crystal ball and predicts the fortune of Microsoft Dynamics GP 11, BI, Management Reporter and FRx, and the Microsoft Dynamics GP Community as a whole in his latest MSDynamicsWorld article... heck, the guy even talks politics!

Don't miss out on this great article.

Until next post!

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

Read More