One of our customers requested to have their report definition of "Management Reporter" gets updated automatically whenever a change on the chart of accounts occurs. This can be simply automated by considering the "All Accounts" function of MR which by default retrieves all accounts to the row definition regardless of any criteria. A link will show up on the home page, on which you click and drill down for details of "account numbers".
Although, the case is different as we have to consider a specific representation of he GL account, each account should have the "Account number" first along with the "Account Description".
The structure includes Dynamics GP and Management Reporter data mart, which runs an integration between Dynamics GP database and MR DataMart, so that data can be structure in an easy and efficient way for reporting purposes.
Management reporter has two main components for every report, row definition and column definition. In our case the row definition includes the list of accounts in a "Static" manner, along with a "Total" row at the bottom. Here is a screen shot of how the "Row Definition" should look like as derived from Fabrikam GP Test Company
 |
| MR Row Definition |
Database Structure
Any row definition in MR is primarily defined in both [ControlRowCriteria] and [ControlRowDetail]. These two tables defines the criteria in which the row definition records are defined ( in addition to many other tables as well )
ControlRowCriteria Table:
- RowLinkID
- RowDetailID
ControlRowDetail Table:
- ID ( Secondary Key for ControlRowCriteria - RowDetailID)
- RowFormatID
- RowNumber
- RowCode
Automation Tool - MR Report Definition
The purpose of the tool is to retrieve any updates from Dynamics GP COA, and reflect the changes on the MR report "row" definition automatically without.
Important Note
- The code above has been tested on a limited data sample, with a specific chart of account segmentation design. Therefore, do not run this on production environment.
- The code below is built for a chart of account with three segment, different COA requires different segmentation
Part 1 - Master Details
---- Log Table, for any new rows added to
MR
CREATE TABLE [ManagementReporter].[Reporting].[GPEssentials_RowCode]
(
[RowCode] [BIGINT] NULL,
[RowFormatID] [UNIQUEIDENTIFIER] NULL,
[MR_TimeStamp] [DATETIME] NULL
) ON [PRIMARY];
GO
--- Staging Master Table, to keep track
of changes on COA
CREATE TABLE TWO.DBO.GPEssentials_NewAccounts
(
AccountIndex [INT] NOT NULL,
AccountNumber VARCHAR(MAX),
SegmentOne VARCHAR(MAX),
SegmentTwo VARCHAR(MAX),
SegmentThree VARCHAR(MAX),
AccountDescription VARCHAR(MAX),
MR_AccountDescription VARCHAR(MAX),
AddedToMR_Index INT,
MR_TimeStamp DATETIME
);
--- Migrate all existing GL Accounts into
the Master Table - Staging
INSERT INTO TWO.DBO.GPEssentials_NewAccounts
SELECT [Account Index],
[Account Number],
Segment1,
Segment2,
Segment3,
[Account Description],
CONCAT(RTRIM(LTRIM([Account Number])), ' ', RTRIM(LTRIM([Account Description]))),
1,
GETDATE()
FROM dbo.Accounts;
Part 2 - Procedures
Every table from the above two tables has a specific stored procedure to insert a record, these procedures are:
CREATE PROCEDURE [Reporting].[GPEssentials_InsertNew_ControlRowCriteria]
@sp_RowLinkID AS [UNIQUEIDENTIFIER],
@sp_RowDetailID AS [UNIQUEIDENTIFIER],
@sp_Low AS
VARCHAR(MAX),
@sp_High AS VARCHAR(MAX),
@sp_DimensionCode AS VARCHAR(MAX)
AS
INSERT INTO [Reporting].[ControlRowCriteria]
(
[RowLinkID],
[RowDetailID],
[DisplayOrder],
[IsSubtracted],
[CriteriaType],
[AccountSetID],
[SegmentNumber],
[DimensionCode],
[SubCriteriaType],
[Low],
[High],
[NamespaceID],
[XbrlUnitID],
[XbrlLinkRoleID],
[XbrlIsNil]
)
VALUES
(@sp_RowLinkID, @sp_RowDetailID, 0, 0, 13, NULL, NULL, @sp_DimensionCode, '', @sp_Low, @sp_High, NULL, NULL, NULL, 0);
GO
CREATE PROCEDURE [Reporting].[GPEssentials_InsertNew_ControlRowDetail]
@sp_RowFormatID AS [UNIQUEIDENTIFIER]
AS
DECLARE @sp_RowLinkID AS [UNIQUEIDENTIFIER];
DECLARE @sp_RowNumber AS INT;
DECLARE @sp_RowCode AS INT;
DECLARE @sp_Description AS VARCHAR(MAX);
SET @sp_RowLinkID = NEWID();
SET @sp_RowNumber =
(
SELECT MAX(RowNumber) + 1
FROM
[Reporting].[ControlRowDetail]
WHERE
RowFormatID = @sp_RowFormatID
);
SET @sp_RowCode =
(
SELECT MAX(RowCode) + 10
FROM
[Reporting].GPEssentials_RowCode
WHERE
RowFormatID = @sp_RowFormatID
);
SET @sp_Description = '';
INSERT INTO [Reporting].GPEssentials_RowCode
VALUES
(@sp_RowCode, @sp_RowFormatID, GETDATE());
INSERT INTO [Reporting].[ControlRowDetail]
(
[ID],
[RowFormatID],
[RowNumber],
[RowCode],
[Description],
[FormatCode],
[RelatedRows],
[FormatOverride],
[NormallyCreditBalance],
[ColumnRange],
[FontStyleID],
[IsNonPrinting],
[UseCurrencySymbol],
[SuppressInAccountDetail],
[SuppressInTransactionDetail],
[SuppressIfZero],
[UseBlanksForZero],
[SuppressRollup],
[SuppressRounding],
[HasFontStyleOverride],
[OverrideFontName],
[OverrideFontSize],
[OverrideIsBold],
[OverrideIsItalic],
[OverrideIsUnderline],
[OverrideIsStrikeThrough],
[OverrideForegroundColor],
[OverrideBackgroundColor],
[OverrideIndent],
[NoteID]
)
VALUES
(@sp_RowLinkID, @sp_RowFormatID, @sp_RowNumber, @sp_RowCode, @sp_Description, 0, '', '', 0, '',
'00000000-0000-0000-0000-000000000000', 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, NULL);
GO
Part 3 - Add new row to MR report row definition
After creating the master tables from Part 1, and the procedures from Part 2, you are ready to run the code below which will update the row definition with any new accounts ( as derived from the new acconts table, which has AddedtoMRIndex as 0 ) only.
Make sure to change the parameters in the code below to reflect the correct RowLinkID and RowFormatID as derived from the specifci row definition you are updating.
---
Make sure to change the @RowFormatID and the @RowLinkID
---
as derived from the row definition you are updating
DECLARE @RowLinkID AS [UNIQUEIDENTIFIER]; --- Row Link ID and ID
DECLARE @RowFormatID AS [UNIQUEIDENTIFIER];
DECLARE @RowNumber AS INT;
DECLARE @RowCode AS INT;
DECLARE @Description AS VARCHAR(MAX);
DECLARE @RowDetailID AS [UNIQUEIDENTIFIER];
DECLARE @SegmentOne AS VARCHAR(MAX);
DECLARE @SegmentTwo VARCHAR(MAX);
DECLARE @SegmentThree AS VARCHAR(MAX);
DECLARE @Low AS VARCHAR(MAX);
DECLARE @High AS VARCHAR(MAX);
DECLARE @DimensionCode VARCHAR(MAX);
DECLARE @AccountIndex VARCHAR(MAX);
DECLARE @AccountNumber VARCHAR(MAX);
DECLARE @AccountDescription VARCHAR(MAX);
DECLARE @MR_AccountDescription VARCHAR(MAX);
---
Get "Total" record RowCode, RowNumber
SET @RowFormatID = 'BF060737-4BC0-4073-A183-841F388F4009';
SET @RowLinkID = '6E1CC8A4-D86F-449B-A719-AA2E9D54084E';
SET @RowNumber =
(
SELECT RowNumber
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = 'Total'
AND
RowFormatID = @RowFormatID
);
SET @RowCode =
(
SELECT RowCode
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = 'Total'
AND
RowFormatID = @RowFormatID
);
---
Remove "Total" record
UPDATE
[Reporting].[ControlRowDetail]
SET [Description] = '',
[FormatCode] = 0,
[RelatedRows] = ''
WHERE [RowFormatID] = @RowFormatID
AND
RowNumber = @RowNumber
AND
RowCode = @RowCode;
DECLARE MR_CurSOR CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT TOP 1
AccountIndex,
RTRIM(LTRIM(AccountNumber)),
RTRIM(LTRIM(SegmentOne)),
RTRIM(LTRIM(SegmentTwo)),
RTRIM(LTRIM(SegmentThree)),
RTRIM(LTRIM(AccountDescription)),
RTRIM(LTRIM(MR_AccountDescription))
FROM TWO.DBO.GPEssentials_NewAccounts
WHERE AddedToMR_Index = 0;
OPEN MR_CurSOR;
FETCH NEXT FROM MR_CurSOR
INTO @AccountIndex,
@AccountNumber,
@SegmentOne,
@SegmentTwo,
@SegmentThree,
@AccountDescription,
@MR_AccountDescription;
WHILE @@FETCH_STATUS = 0
BEGIN
IF
EXISTS
(
SELECT RowNumber
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND RowFormatID = @RowFormatID
)
BEGIN
SET
@RowCode =
(
SELECT MIN(RowCode)
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND RowFormatID = @RowFormatID
);
SET
@RowNumber =
(
SELECT MIN(RowNumber)
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND RowFormatID = @RowFormatID
);
SET
@RowDetailID =
(
SELECT ID
FROM
[Reporting].[ControlRowDetail]
WHERE
RowFormatID = @RowFormatID
AND RowCode = @RowCode
AND RowNumber = @RowNumber
);
UPDATE [Reporting].[ControlRowDetail]
SET
[Description] = @MR_AccountDescription
WHERE
[RowFormatID] = @RowFormatID
AND RowNumber = @RowNumber
AND RowCode = @RowCode
AND ID = @RowDetailID;
-- Segment One
SET
@DimensionCode = 'Entity';
EXECUTE [Reporting].GPEssentials_InsertNew_ControlRowCriteria @sp_RowLinkID = @RowLinkID, -- uniqueidentifier
@sp_RowDetailID = @RowDetailID, -- uniqueidentifier
@sp_Low = @SegmentOne, -- varchar(max)
@sp_High = @SegmentOne, -- varchar(max)
@sp_DimensionCode
= @DimensionCode; -- varchar(max)
-- Segment Two
SET
@DimensionCode = 'Division';
EXECUTE [Reporting].GPEssentials_InsertNew_ControlRowCriteria @sp_RowLinkID = @RowLinkID, -- uniqueidentifier
@sp_RowDetailID = @RowDetailID, -- uniqueidentifier
@sp_Low = @SegmentTwo, -- varchar(max)
@sp_High = @SegmentTwo, -- varchar(max)
@sp_DimensionCode = @DimensionCode; -- varchar(max)
-- Segment Three
SET
@DimensionCode = 'Natural Account';
EXECUTE [Reporting].GPEssentials_InsertNew_ControlRowCriteria @sp_RowLinkID = @RowLinkID, -- uniqueidentifier
@sp_RowDetailID = @RowDetailID, -- uniqueidentifier
@sp_Low = @SegmentThree, -- varchar(max)
@sp_High
= @SegmentThree, -- varchar(max)
@sp_DimensionCode = @DimensionCode; -- varchar(max)
UPDATE TWO.DBO.GPEssentials_NewAccounts
SET
AddedToMR_Index = 1
WHERE
AccountIndex = @AccountIndex;
END;
ELSE
BEGIN
EXECUTE [Reporting].[GPEssentials_InsertNew_ControlRowDetail] @sp_RowFormatID = @RowFormatID;
SET
@RowCode =
(
SELECT MIN(RowCode)
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND RowFormatID = @RowFormatID
);
SET
@RowNumber =
(
SELECT MIN(RowNumber)
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND RowFormatID = @RowFormatID
);
SET
@RowDetailID =
(
SELECT ID
FROM
[Reporting].[ControlRowDetail]
WHERE
RowFormatID = @RowFormatID
AND RowCode = @RowCode
AND RowNumber = @RowNumber
);
UPDATE [Reporting].[ControlRowDetail]
SET
[Description] = @MR_AccountDescription
WHERE
[RowFormatID] = @RowFormatID
AND RowNumber = @RowNumber
AND RowCode = @RowCode
AND ID = @RowDetailID;
-- Segment One
SET
@DimensionCode = 'Entity';
EXECUTE [Reporting].GPEssentials_InsertNew_ControlRowCriteria @sp_RowLinkID = @RowLinkID, -- uniqueidentifier
@sp_RowDetailID = @RowDetailID, -- uniqueidentifier
@sp_Low = @SegmentOne, -- varchar(max)
@sp_High = @SegmentOne, -- varchar(max)
@sp_DimensionCode = @DimensionCode; -- varchar(max)
-- Segment Two
SET
@DimensionCode = 'Division';
EXECUTE [Reporting].GPEssentials_InsertNew_ControlRowCriteria @sp_RowLinkID = @RowLinkID, -- uniqueidentifier
@sp_RowDetailID = @RowDetailID, -- uniqueidentifier
@sp_Low = @SegmentTwo, -- varchar(max)
@sp_High = @SegmentTwo, -- varchar(max)
@sp_DimensionCode = @DimensionCode; -- varchar(max)
-- Segment Three
SET
@DimensionCode = 'Natural Account';
EXECUTE [Reporting].GPEssentials_InsertNew_ControlRowCriteria @sp_RowLinkID = @RowLinkID, -- uniqueidentifier
@sp_RowDetailID = @RowDetailID, -- uniqueidentifier
@sp_Low = @SegmentThree, -- varchar(max)
@sp_High = @SegmentThree, -- varchar(max)
@sp_DimensionCode = @DimensionCode; -- varchar(max)
UPDATE TWO.DBO.GPEssentials_NewAccounts
SET
AddedToMR_Index = 1
WHERE
AccountIndex = @AccountIndex;
END;
FETCH
NEXT FROM MR_CurSOR
INTO
@AccountIndex,
@AccountNumber,
@SegmentOne,
@SegmentTwo,
@SegmentThree,
@AccountDescription,
@MR_AccountDescription;
END;
CLOSE MR_CurSOR;
DEALLOCATE MR_CurSOR;
----
Add "Total" record to MR
IF EXISTS
(
SELECT RowNumber
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND
RowFormatID = @RowFormatID
)
BEGIN
SET
@RowCode =
(
SELECT MIN(RowCode)
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND RowFormatID = @RowFormatID
);
SET
@RowNumber =
(
SELECT MIN(RowNumber)
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND
RowFormatID = @RowFormatID
);
UPDATE [Reporting].[ControlRowDetail]
SET
[Description] = 'Total',
[FormatCode] = 2,
[RelatedRows] = CONCAT('10:', @RowCode - 10)
WHERE
[RowFormatID] = @RowFormatID
AND
RowNumber = @RowNumber
AND
RowCode = @RowCode;
END;
ELSE
BEGIN
EXECUTE [Reporting].[GPEssentials_InsertNew_ControlRowDetail] @sp_RowFormatID = @RowFormatID;
SET
@RowCode =
(
SELECT MIN(RowCode)
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND RowFormatID = @RowFormatID
);
SET
@RowNumber =
(
SELECT MIN(RowNumber)
FROM
[Reporting].[ControlRowDetail]
WHERE
[Description] = ''
AND
RowFormatID = @RowFormatID
);
SET
@RowDetailID =
(
SELECT ID
FROM
[Reporting].[ControlRowDetail]
WHERE
RowFormatID = @RowFormatID
AND RowCode = @RowCode
AND RowNumber = @RowNumber
);
UPDATE [Reporting].[ControlRowDetail]
SET
[Description] = 'Total',
[FormatCode] = 2,
[RelatedRows] = CONCAT('10:', @RowCode - 10)
WHERE
[RowFormatID] = @RowFormatID
AND
RowNumber = @RowNumber
AND
RowCode = @RowCode
AND
ID = @RowDetailID;
END;
Download Links
Best Regards,
Mahmoud M. AlSaadi
we will give it a try and see how it works :)
ReplyDeleteIts working Perfectly ..... Thank You Mahmoud.
ReplyDeleteYou are most welcome Eyad :)
DeleteThe purpose of the tool is to retrieve any updates from Dynamics GP COA, and reflect the changes on the MR report "row" definition automatically without.
ReplyDeleteThis doesn't make sense.
Are you suggesting this as an alternative to rebuilding the datamart?
Not at all, this is specifically built for automating certain manual tasks which are applied on the report definition, not the data.
DeleteBest Regards,
Mahmoud M. AlSaadi
The grammar in the "Automation Tool - MR Report Definition" section doesn't make sense. How about a clear definition of what the tool does.
DeleteHey I’m Martin Reed,if you are ready to get a loan contact.Mr Benjamin via email: lfdsloans@lemeridianfds.com,WhatsApp:+1 989-394-3740 I’m giving credit to Le_Meridian Funding Service .They grant me the sum 2,000,000.00 Euro. within 5 working days. Le_Meridian Funding Service is a group investors into pure loan and debt financing at the returns of 1.9% to pay off your bills or buy a home Or Increase your Business. please I advise everyone out there who are in need of loan and can be reliable, trusted and capable of repaying back at the due time of funds.
ReplyDeleteMr Reporting
ReplyDeleteSales Force Automation
Financial Restoration Through the help of benjamin lee. Email: 247officedept@gmail.com that's his Email and this is his whatsapp number +1-989-394-3740 . I'mLeonardo Hugo a agronist who was able to revive his dying Livestock Feed Manufacturing through the help of a GodSent lender known as Benjamin Briel Lee the Loan Officer. I want you to know that his Service is the right place for you to resolve all your financial problem because am a living testimony and I can't just keep this to myself when others are looking for a way to be financially lifted. I want you all to contact this God sent lender using the details as stated in other to be a partaker of this great opportunity and also they work with good/reputable bank that wire money transfer without delay into my account.
ReplyDelete
ReplyDeleteThat is nice article from you, this is informative stuff. Hope more articles from you . I also want to share some information about Salesforce CPQ Training
BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast
ReplyDeleteyou can be to get the new PROGRAMMED blank ATM card that is capable of
hacking into any ATM machine,anywhere in the world. I got to know about
this BLANK ATM CARD when I was searching for job online about a month
ago..It has really changed my life for good and now I can say I'm rich and
I can never be poor again. The least money I get in a day with it is about
$50,000.(fifty thousand USD) Every now and then I keeping pumping money
into my account. Though is illegal,there is no risk of being caught
,because it has been programmed in such a way that it is not traceable,it
also has a technique that makes it impossible for the CCTVs to detect
you..For details on how to get yours today, email the hackers on : (
atmmachinehackers1@gmail.com ). Tell your
loved once too, and start to live large. That's the simple testimony of how
my life changed for good...Love you all ...the email address again is ;
atmmachinehackers1@gmail.com
Very informative blog with a lot of information. Thank you. The Customize Boxes manufactures stylish packaging for Custom Candle Boxes at wholesale prices and discounts with free shipping services in all across USA.
ReplyDeleteThe need for more legitimate hacking companies are rising each passing day because of the magnitude of scams being run on the Internet. I know what I went through before I was able to contact Osecybersailing to work on my case after getting scammed multiple times and trying to retrieve back my lost investment funds. After I have invested $670k usd with a fraudulent crypto investment company and i lost everything in the space of one month and got scammed again and again trying to find a solution which wasn't easy to come by given the circumstances that brought about the incident, getting scammed multiple times is never a good experience for anyone involved which was what exactly happened to me when I got scammed investing into crypto currency. Many posing on the Internet as hacking companies aren't really what they are because most of them are scammers waiting for a prey to devour, I got referred to reach out to Osecybersailing through a friend and after I investigated about their legitimacy I came across many of their testimony from their satisfied clients that got their lost investment funds retrieved working with this legitimate and evergreen hacking institute which also changed my story helping me retrieve back my lost investment funds. Many of the testimony really were saying great and worthy things about how the company saved them from financial loss, I decided to hire them after many consideration and they didn't disappoint me by making the choice of choosing them after what I have experienced lately trying to get my funds back from the scammers. They proved they can be trusted with the recovery of lost investment funds which is why I wrote this down for other victims of scam who wishes to ever retrieve back their lost investment funds to let Osecybersailing solve their case, they're worthy and has what it takes to restore back your funds and peace of mind. If you also wish to check your partner Whatsapp conversation to know if he/she is cheating then Osecybersailing is the best team for you.
ReplyDeleteIf you find yourself lost in the depths of lost Bitcoin, facebook and Whatsapp hacking to catch your cheating partner, let Osecybersailing's team guide you towards the light of redemption.
Facebook page: Osecybersailing
Email: osecybersailing@cyberservices.com
This comment has been removed by the author.
ReplyDeleteThis article sheds light on the significance of ibm 59h4364, and I must say, it's quite intriguing. It's fascinating to learn about these lesser-known components that play a crucial role in the bigger picture. Thanks for sharing this eye-opening piece!
ReplyDeleteHey there! Just stumbled upon this article and couldn't help but drop a comment. I've been eyeing845628-B21's technology products for a while now, and they never disappoint. From performance to reliability, they've got it all covered. Can't wait to see what they come up with next!
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis article about IBM 59H4364 is so informative! I've been curious about its features and specifications, and your explanation has really cleared things up for me. It's fascinating to see how IBM continues to innovate and push the boundaries of technology. Thanks for sharing this valuable insight!
ReplyDelete