32,738
Like
Share
ReportOne 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 ASVARCHAR(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]
WHERERowFormatID = @sp_RowFormatID
);
SET @sp_RowCode =
(
SELECT MAX(RowCode) + 10
FROM[Reporting].GPEssentials_RowCode
WHERERowFormatID = @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'
ANDRowFormatID = @RowFormatID
);
SET @RowCode =
(
SELECT RowCode
FROM[Reporting].[ControlRowDetail]
WHERE[Description] = 'Total'
ANDRowFormatID = @RowFormatID
);
--- Remove "Total" record
UPDATE[Reporting].[ControlRowDetail]
SET [Description] = '',
[FormatCode] = 0,
[RelatedRows] = ''
WHERE [RowFormatID] = @RowFormatID
ANDRowNumber = @RowNumber
ANDRowCode = @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
IFEXISTS
(
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]
WHERERowFormatID = @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
SETAddedToMR_Index = 1
WHEREAccountIndex = @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]
WHERERowFormatID = @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
SETAddedToMR_Index = 1
WHEREAccountIndex = @AccountIndex;
END;
FETCHNEXT 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] = ''
ANDRowFormatID = @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] = ''
ANDRowFormatID = @RowFormatID
);
UPDATE [Reporting].[ControlRowDetail]
SET[Description] = 'Total',
[FormatCode] = 2,
[RelatedRows] = CONCAT('10:', @RowCode - 10)
WHERE[RowFormatID] = @RowFormatID
ANDRowNumber = @RowNumber
ANDRowCode = @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] = ''
ANDRowFormatID = @RowFormatID
);
SET@RowDetailID =
(
SELECT ID
FROM[Reporting].[ControlRowDetail]
WHERERowFormatID = @RowFormatID
AND RowCode = @RowCode
AND RowNumber = @RowNumber
);
UPDATE [Reporting].[ControlRowDetail]
SET[Description] = 'Total',
[FormatCode] = 2,
[RelatedRows] = CONCAT('10:', @RowCode - 10)
WHERE[RowFormatID] = @RowFormatID
ANDRowNumber = @RowNumber
ANDRowCode = @RowCode
ANDID = @RowDetailID;
END;
Download Links
Best Regards,
Mahmoud M. AlSaadi
*This post is locked for comments