Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Sunday, December 8, 2019

Automation Tool for Dynamics GP Management Reporter - Row Definition ( Auto Update MR Row Definition )

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. 


Technical Case Description:

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
Posted by at 29 comments:
Labels:
Subscribe to: Posts (Atom)