We’ve been doing a number of upgrades recently and I encountered an old error when trying to deploy the SQL Server Reporting Services (SSRS) and Excel Reports through the Reporting Tools Setup window ( ).
This particular client is being upgraded from Microsoft Dynamics GP 2013 SP2 to 2016 R2.
The problem is that some of the companies are showing as Not implemented. The companies showing this way are the oldest; TEST2016… is actually the main company renamed for testing.
Mark Polino has a set of Historical Excel Reports available for sale. The reports available are as follows:
- Receivables Management Historical Aged Trial Balance
- Payables Management Historical Aged Trial Balance
- Historical Inventory Trial Balance
- Historic Stock Status Report
Now is the ideal time to buy them, as the price is increasing on 1st April 2017.
Now that we have the first client installed, we can deploy the system database. By default the system database is called DYNAMCIS, but i have chosen to use one called D16R1. To deploy the system database, hit the Windows start button, search for and launch GP Utilities.
While I do try to minimise the use of the sa account as it is the SQL Server system administrator account, the initial implementation of Dynamics GP does require its use. So, on the Welcome to Microsoft Dynamics GP Utilities window enter sa in the User ID field and enter the relevant password.
Once done, click OK:
The previous feature of the day introduced the OData Service installation. This one gives a brief overview of how the service works:
The main features of the OData service are:
- Ability to define end points for the service
- View GP data with any tool that supports OData feeds
Microsoft Dynamics GP 2016 R1 is seeing the introduction of a new complimentary product: an OData service which will allow reports on Dynamics GP data using any reporting tool (such as PoweBI) to be created:
The main features of are:
- Separate install under Additional Products
- Use GP Security to control access to data
- Supports tables, views and stored procs as data sources
In February 2014 Microsoft launched PowerBI for Office 265 and are now bringing it to the Microsoft Dynamics GP 2016 R1 home page (but only the home page of the desktop client):
The main features of are:
- New part on Home Page to display Power BI Reports.
- Click on the report to take you out to your Power BI site.
I’m not entirely sure how to feel about the addition of PowerBI to Dynamics GP; it almost feels like a scatter gun approach is being used for reporting with Dynamics GP. There are standard reports, Metrics, Excel Reports, SSRS Reports (which for most clients replace the Metrics to the extent I had to lookup the name of Metrics), Management Reporter (with an unknown roadmap/future) reports and now PowerBI reports.
As mentioned in the previous post, Microsoft Dynamics GP 2013 SP2 saw the introduction of SmartList Designer and have been gradually improving it since. This new feature allows a user to create a new SmartList from a favourite, instead of just from a SmartList Object:
The main features of are:
- A user can create a new SmartList from a favourite using Designer
- The user doesn’t have to remove all extra columns from the default SmartList
This new feature will ease the creation of a SmartList Object simialr to an existing favourite instead of a copy of the default SmartList Object to which the favourite belongs.
This feature of the day puzzles me somewhat as I would swear this was a feature introduced within the last couple of versions. It is the export of numbers as numbers from a SmartList:
As I mentioned in the introductory paragraph, this is a feature I believe was already introduced, but upon further reflection this may be a slightly different new feature.
The previous feature I am thinking of (but can’t find a specific announcement) exported the numbers to five decimal places and without a currency symbol. The example given in the screenshot, above, has a currency symbol and is to two decimal places.
I’ve recently been working on a project upgrading a client from Microsoft Dynamics GP 9 SP3 to a later version and also from Microsoft SQL Server 2000 to SQL Server 2008 R2. Much of the upgrade has gone through without problems, but we’ve encountered one or two issues with customisations and custom reports.
The following error message was produced during testing when generating one of the old Crystal Reports:
Crystal Report Viewer
Failed to open rowset.
Details: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]The query uses non-ANSI outer join operators("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backwa...
The compatibility level of a database for Microsoft Dynamics GP should NOT be changed back to 80 when this error is encountered. The solution to use is the recommended one from the error message: to rewrite the query.
In this case the client had a set of Crystal Reports written against GP 9 which called a variety of stored procedures. I spent a few hours reviewing and rewriting stored procedures to remove the non-ANSI outer joins and replacing them with ANSI ones.
I produced this view to return vendor email address for a client a while ago; I don’t typically use this view by itself, but instead combine it with GP tables to produce a larger SmartList report using either SmartList Designer or SmartList Builder.
CREATE VIEW uv_AZRCRV_VendorInternetAddresses AS SELECT ['Internet Addresses'].Master_ID AS 'Vendor ID' ,['Internet Addresses'].ADRSCODE AS 'Address Code' ,['Internet Addresses'].EmailToAddress AS 'Email To Address' ,['Internet Addresses'].EmailCcAddress AS 'Email Cc Address' ,['Internet Addresses'].EmailBccAddress AS 'Email Bcc Address' ,['Internet Addresses'].INET1 AS 'Email' FROM SY01200 AS ['Internet Addresses'] WHERE ['Internet Addresses'].Master_Type = 'VEN' GO GRANT SELECT ON uv_AZRCRV_VendorInternetAddresses TO DYNGRP GO