Management Reporter Does Show All Account Descriptions

Microsoft Dynamics GPWe’ve recently been implementing Microsoft Dynamics GP for a manufacturing company in England. When verifying that opening balances had imported correctly, the user reported that not all of the rows in Management Reporter were displaying an account description. A quick check around and I found a blog post from nJevity covering this exact subject.

Typically Management Reporter is run only to show posted transactions, but you can also set it to include unposted transactions. In this testing , we were returning both posted and unposted as we had imported the opening balances, but not yet posted them in case any changes were required.

The rows which included only unposted transactions did not display the account descriptions, but rows with even a single posted transaction did. As soon as the opening balances were verified and posted, all rows did display the account description. As the client would not usually be reporting on unposted activity, this was deemed a non issue.

Management Reporter Upgrade Fails The Users Can’t Connect

Microsoft Dynamics GPI recently did a Microsoft Dynamics GP upgrade for a client which included Management Reporter. During the upgrade of Management Reporter to the latest hotfix, we found that the upgrade of the legacy connector failed every time. We spent quite a bit of time looking into the problem and eventually got it to install correctly.

When legacy connector was first added, it was added using the FQDN of the SQL Server Instance (SQL01.example.com\GP), the upgrade failed every time we tried to use the FQDN, but when we switched to just the machine name (SQL01\GP), the upgrade was successful.

Management Reporter has been used by this client since the release of the 2012 version and it has been upgraded previously without issue, so I am unsure of what had changed in this version.

That was not the end of the story though. When I tested Management Reporter was working fine, it was. However, I’d logged in using the sa account. When users started logging in they were finding that they were unable to connect to the GP company database:

Management Reporter unable to connect

Unable to connect to the 'Example Limited' company.

The connection to the Microsoft Dynamics GP database failed. Contact your system administrator.

I’d seen this error message before and it is because the System DSN used by Dynamics GP was using the FQDN, but now the legacy connector in Management Reporter i using the server name so the password encryption will be different.

I didn’t want to remove the legacy connector and try to install it again as I was pretty sure we’d have the same problem as previously. Instead I decided to have a go at updating the connection used by Management reporter which is stored in the Reporting.ControlCompany table and which I noted I’d done in the previous bog post.

However, I didn’t post the SQL I’d used, so I needed to recreate it. The two highlighted sections are the server name which needed to be changed and to what:

/*
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). */
UPDATE Reporting.ControlCompany SET GLEntityConnectionInformation = REPLACE(CAST(GLEntityConnectionInformation AS VARCHAR(8000)), 'SQL01\GP', 'SQL01.example.com\GP') GO

Obviously, take a backup of the database before running the script and test that it has worked correctly before allowing users back into Management Reporter.

GeneralUser Error When Migrating Management Reporter to a New Server

Microsoft Dynamics GPAcross the years since it was launched I have done a lot of work with Management Reporter, including many migrations to new servers and upgrades which necessitated a migration to a new server or instance of SQL Server, but have never seen this particular error message before.

In this case, I migrated the Management Reporter database to a new server and installed the very latest version of the server software. When I started the database configuration, I received this error message:

Database configuration error

Validation Messages

Database configuration: The connection to the database was successful, but the connection to database 'Management Reporter' failed. Verify that the Management Reporter service account has been added to the General User role in the database.

Continue reading “GeneralUser Error When Migrating Management Reporter to a New Server”

Recent ISC Software Webinar: Automation in Microsoft Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at Automation in Microsoft Dynamics GP. In this webinar, we covered how automation can be used in Microsoft Dynamics GP to improve efficiencies and accuracy of data. If you want to catch up on this, or any other, webinar, you can do so here.

In this blog post, I am going to recap the webinar and cover the highlights of how automation can be used in Microsoft Dynamics GP to improve efficiencies and improve data accuracy:

  1. Introduction
  2. Integration
  3. Scanning
  4. Approval
  5. Posting
  6. Reporting
  7. Conclusion

Introduction ^

Where possible in this webinar I highlighted standard, or Microsoft supplied, features or additional products where they are available. However, in many cases the standard functionality does not allow for full automation. This is an intentional design choice made when Microsoft Dynamics GP was first created back in the md-90s. The company who created Great Plains, the original name of Dynamics GP, was intended from the very beginning to be extensible with the intention that there be a thriving third-party marketplace for add-ons.

This is the current situation; the core Dynamics GP system has strong core financials and distribution modules, but wider functionality is provided by third party (Independent Software Vendors (ISVs) who have a variety of add-ons and complimentary products which provide the functionality required or automating processes. In each of the areas, there are usually a number of products available from several vendors, but I have selected one in each area. usually an add-on which I have used with several clients across a number of years and which has received positive reviews.

Before implementing one of the solutions, I’d recommend reviewing the functionality it includes, the functionality of competing products and making your own decision about which will best fit your requirements.

Continue reading “Recent ISC Software Webinar: Automation in Microsoft Dynamics GP”

SQL Script to Get Assigned Building Block Groups for Companies in Management Reporter

Microsoft Dynamics GPI recently did a Microsoft Dynamics GP and Management reporter upgrade for a client which incuded migrating the databases to a new server. After performing the upgrade, users were unable, in some companies, to see the reports in Management Reporter. When we looked into it, some of the companies had reverted to the Default building block group.

To easily identify the companies which had reverted, I created the below script which coud be rn on both the original and upgraded Management reporter databases; it lists all the companies and the assigned building block group:

/*
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 ['Case Control'].Code AS 'Company ID' ,['Case Control'].Name AS 'Company Name' ,['Control Specification Set'].Name AS 'Building Block ID' ,['Control Specification Set'].Description AS 'Building Block Name' FROM Reporting.ControlCompany AS ['Case Control'] INNER JOIN Reporting.ControlSpecificationSet AS ['Control Specification Set'] ON ['Control Specification Set'].ID = ['Case Control'].SpecificationSetID ORDER BY ['Case Control'].Code

Management Reporter Currency Does Not Exist Error

Microsoft Dynamics GPI was doing some training on Management Reporter for a client the other week and had an issue to follow up on. The issue was around multicurrency conversion which wasn’t working correctly. When doing some testing around the issue, I was able to reproduce the same currency error the client had encountered:

Currency could not be found error message

Currency XXXX does not exist for company XXXX. No values will be returned.

The currency did exist and on most reports was working fine. It took me a little investigation and research to find that this was an issue with the Data Mart connector; the reports using the legacy connector were working correctly.

This issue is covered in Microsoft KB Article 3058400; when using the Data Mart connector, currency lookups are done using the ISO Code of the Currency rather than the Currency Code itself.

Management Reporter: The operation could not be completed due to a failure on the server

Microsoft Dynamics GPThe issue dates back a while, but may still be relevant to people using Management Reporter. A client, unbeknown to me, decided to move Management Reporter to a new server; they’d been having HDD capacity problems on the existing SQL Server.

They’d migrated the databases and installed the services, correctly configuring the Legacy Connector, but were receiving an error:

Error message

The operation could not be completed due to a failure on the server

The problem is that there are a few tasks which need to be undertaken when moving Management Reporter to a new server, including an encryption key which needs to be set. These are covered in this Microsoft Support article which includes, amongst other things, a script to run.

Move Management Reporter To A New Domain

Microsoft Dynamics GPA client recently ran into problems with Management Reporter whereby they had a large number of building blocks accidentally deleted from one building block group. While the easiest solution would be to restore the Management Reporter database to before the blocks were deleted, this was not possible.

The client in question has a lot of users across different businesses each it their own building block groups and were in the middle of year end. LOsing all the management accounts so far produced ad interrupting all business units to fix an issue in one of them was not possible. To make things worse, their test system was being rebuilt after some year end testing so we couldn’t use this to restore the database and export the building blocks.

The solution was therefore a little more long winded and took several steps:

  1. Copy a backup of the Management Reporter database prior to the deletion and restore to my test system.
  2. Run the copy the Management Reporter 2012 database to a new server script from Microsoft
  3. Use the after you make changes to your domain, you no longer have permission to access Management Reporter; this script was needed as the domain on my demo system is totally different to the originating one.
  4. Export required building blocks.
  5. Import exported building blocks back into client system.

The above took a while due to the size of the database which had to be copied to my test system and restored, but was actually quite straightforward.

Security Views For Use In SmartList Designer: Group Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This, the second Management Reporter security script, shows security for users as granted by their Group membership. the previous post, on Friday, showed the user based company access.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterGroupBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
		,['Security Group Principal'].Name AS 'Group Name'
		,['Security Group Principal'].Description AS 'Group Description'
		,['Control Company'].Code AS 'INTERID'
		,['Control Company'].Name AS 'Company Name'
 FROM 
	Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	Reporting.SecurityGroupUser AS ['Security Group User'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security Group User'].UserID
LEFT JOIN
	Reporting.SecurityPrincipal AS ['Security Group Principal']  WITH (NOLOCK)
		ON
			 ['Security Group User'].GroupID = ['Security Group Principal'].ID
LEFT JOIN
	Reporting.SecurityCompanyPermission AS ['Security Company Group Permission'] WITH (NOLOCK)
		ON
			['Security Group Principal'].ID = ['Security Company Group Permission'].PrincipalID
LEFT JOIN
	Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Group Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterGroupBasedSecurity TO DYNGRP
GO

Security Views For Use In SmartList Designer: User Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This first script returns the security based on how the user is configured; the view I will post on Monday shows Group based security.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterUserBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity 
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
	,['Control Company'].Code AS 'INTERID'
	,['Control Company'].Name AS 'Company Name'
FROM 
	ManagementReporter.Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	ManagementReporter.Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	ManagementReporter.Reporting.SecurityCompanyPermission AS ['Security Company Permission'] WITH (NOLOCK)
		ON
			['Security User Principal'].ID = ['Security Company Permission'].PrincipalID
LEFT JOIN
	ManagementReporter.Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterUserBasedSecurity TO DYNGRP
GO