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 ●

MDGP 2013 R2 Feature of the Day: Reprint Outstanding Transaction in Bank Reconciliation

● Ian Grieve ●  ● 0 Comments  ● 

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The eleventh Feature of the Day covered is Reprint Outstanding Transaction in Bank Reconciliation. The Outstanding Transactions Report option has been added in the Bank Posting Journals report list (Financials ¯ Reports ¯ Posting Journals):

Financial Postings Journal

Continue reading → MDGP 2013 R2 Feature of the Day: Reprint Outstanding Transaction in Bank Reconciliation

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

Deactivate All Reports (Except PM EFT Payment Register) In All Companies

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GP includes a large number of reports which will automatically print off when a transaction or batch is posted; and some postings produce many different reports.

When I implement a new client I typically leave them on during training with an instruction to identify which ones they want to use after go-live as I will switch off any I’m not told to leave on.

I used to do it the other way and tell people to let me know which ones they wanted switched off; however, this lead to all reports being left on and then it being mentioned a year or so down the line that they get all these reports printing that they don’t want.

So now, all get switched off unless I am specifically told to leave them on. As I’ve dealt with clients with many companies (I think the largest is 180 companies) this is not something I want to do manually.

Fortunately, the settings for whether the reports should print is stored in SQL table which means a SQL script can be written to switch them off in bulk.

Continue reading → Deactivate All Reports (Except PM EFT Payment Register) In All Companies

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

Microsoft Dynamics GP 2013 Reporting – Review Incoming

● Ian Grieve ●  ● 0 Comments  ● 

I have received a copy of Microsoft Dynamics GP 2013 Reporting to review which I will be doing over the next couple of weeks.

This is an update to the previous edition of the book for Microsoft Dynamics GP 2010, but this one is for the current version:

Microsoft Dynamics GP 2013 Reporting

I’ll be giving it a thorough read and, setting aside my distaste for reporting outside of Management Reporter, I may even follow some of the examples and post a detailed account.

If you don’t want to wait for my review of the book, it can be bought now from Packt Publishing.

● Categories: Dynamics, GP, Management Reporter, Microsoft, Reporting Services Reports, SmartList Builder ● Tags: , , , , , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●