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:
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
