Dynamics GP 2016 - Power BI Reports and Dashboards Integration

With the release of Dynamics GP 2016, many features have been introduced among which Power BI integration with Dynamics GP, this may be one of the most important features along with the HTML5 and OData Services. 

In this post, I am going to shed a light on how to configure the Power BI in details. 

Power BI Integration Prerequisites:

• Office 365 Account which entitles you to access Power BI
• At least one Power BI site including at least a report / dashboard
• Dynamics GP 2016 desktop client is installed

Power BI Configuration: 

• Go to Microsoft Dynamics GP > Tools > Setup > System > Reporting Tools Setup. 
• There is a new tab for "Power BI" configuration

Reporting Tools Setup

As for the power BI site, login to the Power BI using your office 365 credentials, the Power BI Site URL is highlighted below:

Power BI Site URL

In order to obtain a client ID, follow the steps below:

• Go to Power BI for Developers  >> Link
• Log in to your Power BI Account 
• Fill in the following details
• App Name: Dynamics GP
• App Type: Native App
• Redirect URL: https://login.live.com/oauth20_desktop.srf
• Choose APIs to access, which are categorized as follows
• Data set APIs
• Report and Dashboard APIs
• Other APIs
• Click on "Register your App", and the client ID will appear in the box below

Power BI for Developers - Client ID

Power BI for Developers - Client ID

Now you can get back to Dynamics GP, and fill in both the Power BI Site and Client ID, and click "OK" to proceed. Now on Dynamics GP Home Page, click on "Customize" and make sure to check the Power BI component as shown below:

Customize Home Page

Please note that there is an option to "Show All" power BI selected reports on the home page. If it not checked, then all reports will appear within the same part with an arrows to go through them one by one.

To proceed, click on the "pencil" icon in order to change the details of the Power BI Component, through which you can check/ select which reports to show on Dynamics GP Home Page. It might prompt a login window requesting office 365 credentials in order to access the Power BI site and retrieve the reports and dashboard details:

Customize

Power BI Details

Finally, Dynamics GP Home Page and Power BI reports/ dashboard is presented based on the choices/ selections made above. 

Best Regards, 

Mahmoud M. AlSaadi

Dynamcis GP 2015 - Financial Year End Closing Hangs | Process Failure

While closing the year in Dynamics GP 2015, the system hangs completely at step three just after performing the basic initial validation, It seemed that it was processing, but in fact there was no single process on the SQL Server (SQL Profiler). The following troubleshooting were considered : 

1. Connectivity/ Network Issue (as I was performing the closing on the client side)
2. SQL Server - Corrupted Master or Transnational data 

Connectivity/ Network Issue 
Starting with the first potential cause, I have restored the original backup, and proceeded with closing the year on the SQL server in order to omit any possibility of connectivity/network issue. Although, I have had the exact same results >> The system hangs at step three of the financial closing without proceeding or giving any error on the application level.

SQL Server - Corrupted Master or Transnational data

Following up this case, it was obvious that there is specific issue with the data that is causing the year-end closing to stop resulting with complete hang of the application. 

As I have checked the General Ledger tables, records in GL20000 for the year being closed was there and didn't move to the history tables GL30000, which in turn was empty for this specific year.

Here is the code at which the system stops proceeding any further:

BEGIN

    DECLARE @stored_proc_name CHAR(26);

    DECLARE @retstat INT;

    DECLARE @param23 NUMERIC(19, 5);

    DECLARE @param24 INT;

    DECLARE @param25 INT;

    SET NOCOUNT ON;

    SELECT  @stored_proc_name = 'COE.dbo.glYearEndCloseMain';

    EXEC @retstat = @stored_proc_name 2015, 2016, 1, 'SAR', '', 0, 'AVERAGE',

        'CLOSE', 6137, 'GLTRX00002694', 'DYNSA', '##0560435', '##0650435',

        '##0700435', 94, 570, 1, 6137, 6137, 0, '2015.12.31', '2015.01.01',

        @param23 OUT, @param24 OUT, @param25 OUT;

    SELECT  @retstat ,

            @param23 ,

            @param24 ,

            @param25;

    SET NOCOUNT ON;

END;  

Several parts of the stored procedure above (glYearEndCloseMain) referred to currency translation as shown below:

• MC40000
• glYECCreateBBFTranslationOpen
• glYECCreatePLTranslationOpen

As I have checked the Multi currency setup in Dynamics GP, It was clear that there are several setup lines for the currency transaction as shown below:

Dynamics GP | Currency Translation Setup

I have proceeded by removing the translation setup above completely (after restoring the original backup of the company) and then proceeded with the financial year end closing. The result was just perfect, the closing went so smoothly without hanging for a single second. 

 

Year End Closing - Complete

Best Regards, 

Mahmoud M. AlSaadi

Dynamics GP 2016 is Now Available

It was officially published today that Microsoft Dynamics GP 2016 is available, the download link is provided below:

• Dynamics GP 2016 - Customer Source 
• Dynamics GP 2016 - Partner Source

Upcoming features have been summarized in a previous post - Dynamics GP 2016 Upcoming Features. Among all features, the new HTML5 browser and multi-device support for the web client has been the big announcement. Additionally, further more features and enhancements such as but not limited to: oData services for Power BI, Workflow features and All in one viewers for sales and inventory.

Dynamics GP Essentials will shed a light on each of these features in the upcoming days in order to dig in depth in the 35+ features of this big release.
 

Dynamics GP 2016

From a technical perspective, it is quite essential to take a look at the following two articles; officially published by the Dynamics GP Support Team blog:

• Table and  Column Changes in Microsoft Dynamics GP 2016 
• Upgrading Dynamics GP 2016 Web Client 

Stay Tuned ! 

Best Regards,
Mahmoud M. AlSaadi