Light Dark
More...

Now Reading: Automation Tool for Dynamics GP Management Reporter – Row Definition ( Auto Update MR Row Definition )

1
  • 01

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

Light Dark

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

Mahmoud M. AlSaadi - MVPBusiness Central5 years ago24 Views

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;

Normal
0

false
false
false

EN-US
X-NONE
AR-SA

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:8.0pt;
mso-para-margin-left:0in;
line-height:107%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,sans-serif;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:Arial;
mso-bidi-theme-font:minor-bidi;}


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.

Normal
0

false
false
false

EN-US
X-NONE
AR-SA

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:8.0pt;
mso-para-margin-left:0in;
line-height:107%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,sans-serif;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:Arial;
mso-bidi-theme-font:minor-bidi;}

—
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

Original Post https://mahmoudsaadi.blogspot.com/2019/12/auotmation-tool-for-dynamics-gp.html

Upvote0PointsDownvote

0 Votes: 0 Upvotes, 0 Downvotes (0 Points)

0In Love0Happy0Not Sure0OMG0Angry0Sad0LOL0Cry0Silly0Crazy

Leave a reply

Related Posts

Join Us
  • X Network2.1K
  • LinkedIn3.8k
  • Bluesky0.5K
Support The Site
Events
March 2025
MTWTFSS
      1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31       
« Feb   Apr »
Follow
Sign In/Sign Up Sidebar Search
Popular Now
Show More
Scroll to Top
Loading

Signing-in 3 seconds...

Signing-up 3 seconds...