SQL View to Return Budgets By Month

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPThe budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

Continue reading → SQL View to Return Budgets By Month

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

How To Find Out What SQL Features Are Installed

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft SQL ServerNo day when you learn something new is a wasted day. I was onsite with a client recently to do some work with them on Microsoft Dynamics GP and got chatting to Den in their IT department.

It turns out that SQL Server has a report you can run which will show which features are installed.

The report is run from the SQL Server Installation Center which is available from the Windows Start menu. Click on Tools and then on Installed SQL Server features discovery report:

SQL Server Installation Center

Continue reading → How To Find Out What SQL Features Are Installed

● Categories: Microsoft, SQL Server ● Tags: , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R2 Feature of the Day: New Project Accounting Timesheet Status Report

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here.

The seventeenth Feature of the Day is a new Project Accounting Timesheet Status report.

This feature provides a report for PTE Timesheets which gives visibility into all timesheets including missing timesheets and can be printed by status or approver:

Project Accounting Timesheet Status Report

Click to show/hide the MDGP 2016 R2 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, Project Accounting ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL View To Allow Customer/Item Link In SmartList Builder

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPOn this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.

As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):


CREATE VIEW uv_AZRCRV_CustomerItemLink AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
	SELECT
		SOP102.SOPNUMBE
		,SOP102.SOPTYPE
		,SOP102.LNITMSEQ
		,SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
	FROM
		SOP10200 AS SOP102 WITH (NOLOCK)
	INNER JOIN
		SOP10100 AS SOP101 WITH (NOLOCK)
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
	INNER JOIN
		SOP60300 AS SOP603 WITH (NOLOCK)
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

General Ledger Summary Trial Balance Returning Incorrect Data

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPWe are currently in the process of upgrading a large 20 site client from Microsoft Dynamics GP 9 to a later version (unfortunately not 2015 due to third party add-ons which are not supported on this version). The client previously had standalone implementations at each site, but we are aggregating them into one implementation for ease of maintenance and also to reduce licensing costs.

The first few we centralised went fine, but we then ran into problems when we did the head office companies. When they ran the GL Summary TB on the old and new systems there was a mismatch between them.

After investigating, the support team established that the problem was down to some corruption in the Account Index Master table (GL00105) where some of the accounts had segments separated with dots instead of dashes as defined in Account Format Setup (Administration area page ¯ Setup ¯ Company ¯ Account Format).

The solution they followed was fairly simple, and in three steps:

  1. Run a script to clear the Account Index Master table:
    DELETE FROM GL00105
  2. Run Check Links (Microsoft Dynamics GP menu ¯ Maintenance ¯ Check Links) on Account Master.
  3. Run the General Ledger Reconcile utility (Financials area page ¯ Utilities ¯ Reconcile) on all years starting with the oldest and working forward.

After this was done, the report produced from the upgraded system matched the old one.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

Install SQL Server 2014: SSRS Configuration for Microsoft Dynamics GP

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPIn the previous two posts, I stepped through the installation and configuration of SSRS. To deploy the Microsoft Dynamics GP SSRS reports to the SSRS server there is one last piece of configuration which needs to be completed.

There is a setting in the web.config file which needs to be added for the reports to deploy successfully. I have previously blogged about that setting here. The only change in the path will be the version number in the folder name will be higher.

Once you have completed that step you can then move onto deploying the SSRS reports in Dynamics GP; this post was written on Dynamics GP 2010 R2, but the process for deploying them is the same in Dynamics GP 2015.

Click to show/hide the Install SQL Server 2014 Series Index

Install SQL Server 2014
SQL Server Installation
SSRS Installation
SSRS Configuration
SSRS Configuration for Microsoft Dynamics GP
● Categories: Dynamics, GP, Microsoft, SQL Server, SQL Server 2014, SSRS 2014 ● Tags: , , , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Error In SmartList Excluding One Category

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPA client reported an error to our service desk recently when they were working with an Account Transactions SmartList which they were trying to filter to exclude a particular Account Category. When they entered the search criteria they received a string of error messages.

First:

Microsoft Dynamics GP - GPS Error: 58

Microsoft Dynamics GP

GPS Error: 58

Continue reading → Error In SmartList Excluding One Category

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Sample Company Deployment

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts o how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here. I am assuming you already have your server built with Windows and SQL Server for this series.

In the last post I deployed the system database; in this one I’m going to take a look at deploying the sample company. While this isn’t something generally done with a new system, I am in the process of building a new demo/test environment.

We’ll pick up where the previous post left off on the Additional Tasks step of GP Utilities. If you had closed GP Utilities log back in and progress through to this step and in the combo box, in the middle of the screen, make sure it is set to Add sample company data and click Process:

Additional Tasks

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Sample Company Deployment

● Categories: Dynamics, GP, GP Utilities, Microsoft ● Tags: , , , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 Feature of the Day: Management Reporter Integration Setup

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

The twenty fifth Feature of the Day is Management Reporter Integration Setup. This feature created a new navigation list for accessing Management Reporter reports from within the Financial and Administration Navigation buttons.

The Management Reporter navigation list becomes enabled once the location of the Management Reporter service is defined in the Reporting Tools Setup window:

Reporting Tools Setup

The location of the Report Designer Application is an optional field and when defined, will enable users to launch the designer from the Management Reporter navigation list if they have permissions to application.

The navigation list displays a list of reports the current user has security access to and enables them to view the reports in the Management Reporter Viewer with just a click of a button.

The roles targeted by this feature are:

  • All

Click to show/hide MDGP 2015 Feature of the Day Series Index

● Categories: Dynamics, GP, Management Reporter, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 Feature of the Day: Management Reporter Integration

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

The twenty fourth Feature of the Day is Management Reporter Integration. This feature created a new navigation list for accessing Management Reporter reports from within the Financial and Administration Navigation Lists.

The Management Reporter navigation list becomes enabled once the location of the Management Reporter service is defined in the Reporting Tools Setup window.

The navigation list displays a list of reports the current user has security access to and enables them to view the reports in the Management Reporter Viewer with just a click of a button:

Management Reporter Reports

The roles targeted by this feature are:

  • All

Click to show/hide MDGP 2015 Feature of the Day Series Index

● Categories: Dynamics, GP, Management Reporter, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●