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

Management Reporter Fact Check

Microsoft Dynamics GPFollowing the announcement by Microsoft back in June last year of a partnership with Jet Reports, the future of Management Reporter has been a topic of conversation amongst the community with rumours constantly swirling that Management Reporter would disappear from the Microsoft Dynamics GP space.

On the Inside Microsoft Dynamics GP team blog, Pam Misialek has posted a Management Reporter Fact Check:

  • Management Reporter is available and fully supported by Microsoft. It is still included in the Starter Pack and will continue to be.
  • Management Reporter will ship with GP Next later this calendar year and will ship in future releases as well.
  • Microsoft are looking for any feature enhancements ideas which can be added to the product.
  • HTTP support was added for Microsoft Dynamics GP 2016 R2
  • Management Reporter will not go fully to the cloud in a multi-tenant environment. It can be used in the cloud, with a RDP connection.

Management Reporter is technically a separate product because it works across all Dynamics products. So what? Who cares? It works great at creating financial statements.

Management Reporter 2012 CU16 Now Available

Microsoft Dynamics GPThe Dynamics CPM Team has recently announced the release of Cumulative Update 16 for Management Reporter 2012.

The big new feature in this CU, is HTTPS support which was announced as a new feature alongside Microsoft Dynamics GP 2016 R2.

Vaidy Mohan has posted an article on the functionality for Microsoft Dynamics GP.

Management Reporter Error: “The operation could not be completed due to a problem in the data provider framework”

Microsoft Dynamics GPA bit like Integration Manager, Management Reporter often presents highly generic meaningless error messages to users. The error message below was provided to me by a client a while ago (but I have just stumbled across the screenshots again), which I was then able to reproduce just be ruunning the report:

Management Reporter: The operation could not be completed due to a problem in the data provider framework

Management Reporter

The operation could not be completed due to a problem in the data provider framework.

Continue reading "Management Reporter Error: “The operation could not be completed due to a problem in the data provider framework”"

MDGP 2016 R2 Feature of the Day: HTTPS Support for Management Reporter

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 nineteenth Feature of the Day is support in Management Reporter 2012 for https access to the web viewer:

This adds the ability to deploy Management Reporter for report access over secure https communication.

This feature is well overdue; it should have been introduced at the same time as the web viewer itself.

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