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

• Part 1 - Master Definition 
• Part 2 - Procedures Download 
• Part 3 - Add new Row Code 

Best Regards, 

Mahmoud M. AlSaadi