© 2018 by PDG Consultants, Inc. 

Tel: 1-727-510-4558

Office: Clearwater, Florida
Please reload

Recent Posts

Excel Charts from GP Management Reporter

December 23, 2015

Yes, Management Reporter has some charting capability from the Viewer. It is very limited however, as you can test for yourself.

 

By using the Tree tool in Analytical Accounting (comes with all standard Dynamics GP), the Account Transaction View provided by Microsoft, some custom SQL (which I will show you), and Excel Refresh-able Reports; you can achieve very detailed pivot tables and charts. Once this is built, you can slice and dice in Excel. (Note: Many labels are blurred to mask reference to the actual company.)

 

MR to Excel Pivot Tables and Charts

 

 

 

 

 

 

 

Charts and Pivot Tables are nice, but even better when the numbers are correct. Here the Excel Profit total matches the same number in MR.

 

The GP Account Transactions View is pivoted to show multiple levels. You need to make a tree in order to achieve multiple levels, because the GP “Financial Category” is only one level. You could create Account Groups, but using a tree, is more WYSIWYG for the user.

 You use Analytical Accounting to make your tree. (It’s available. Ask your GP Partner to install it.)

Once you have the MR Report, the matching tree you made, and the custom SQL query (which organizes the GL Account Transactions by account number and tree level) you can reference that SQL query in an Excel Data Connection.

Once you have the MR Report, the matching tree you made, and the custom SQL query (which organizes the GL Account Transactions by account number and tree level) you can reference that SQL query in an Excel Data Connection.

 

Copy your Excel file into the standard location for the GP Excel Reports, and you’ll be able to access the same Refresh-able report forever.

 

 

Here is the query. It uses the Cross Apply function to match the children of each node to its parent. I couldn’t get the tree effect using an ordinary join.

——————————————————————————————————————-

CREATE VIEW pdg_acct_summary_by_tree_node
AS
/*
SS 20151212 Create a view of all GL transactions linked to the
AA Tree matching the Revenue section of the MR Report for P&L Detail.
Sort the tree nodes according to the MR report for P&L Detail
*/
–Revenue
— Get aalevel 2
WITH T1 AS
(
SELECT  L1.aaNode AS LEVEL1, L2.aaNodeID, L2.aaNode, L2.aaLevel, L2.aaParentNodeID, L2.aaOrder
FROM          AAG00601 AS L1
CROSS APPLY
(SELECT TOP 1000 aanode,aalevel,aaParentNodeID, aaOrder, aaNodeID
FROM AAG00601 AS L1A
WHERE  L1.aanode <> ‘Expenses’ and aaLevel=2
and L1A.aaParentNodeID=L1.aaNodeID ORDER BY aaOrder) AS L2
),
–Get Level 3
— Name the Parent Level (aanode)
T2 AS
(
SELECT  L1.aaNode AS LEVEL1, L2.aaNodeID, L2.aaNode, L2.aaLevel, L2.aaParentNodeID, L2.aaOrder
FROM          AAG00601 AS L1
CROSS APPLY
(SELECT TOP 100 aanode,aalevel,aaParentNodeID, aaOrder, aaNodeID
FROM AAG00601 AS L1A
WHERE  aaLevel=3 AND
L1A.aaParentNodeID=L1.aaNodeID ORDER BY aaOrder) AS L2
),
T3
AS
(
–Cross Apply T2 over T1, to join the 3rd level to its mate on the 2nd level (Concessions)
SELECT ‘Revenue’ AS LEVEL1, T1.aaNode AS LEVEL2, ISNULL(L2.aaNode,t1.aaNode)as LEVEL3,
T1.aaOrder
FROM          T1
OUTER APPLY
(SELECT TOP 1000 aanode,aalevel,aaParentNodeID, aaOrder, aaNodeID
FROM T2
WHERE T2.aaParentNodeID=T1.aaNodeID
ORDER BY aaOrder) AS L2
),
— Sort the lines so they are in the exact order of the MR P&L
T4 AS
(
SELECT LEVEL1, LEVEL2, LEVEL3,AAORDER,
ROW_NUMBER() OVER (ORDER BY AAORDER) AS ROW
FROM T3
),
— Link the GL account number to the AA level (tree node) using
— a GP view. Do a left join to gather all rows with linked accounts.
T5 AS
(
SELECT TOP 1000 LEVEL1, LEVEL2, LEVEL3, ROW, L3.strAccountNumber AS ACCOUNT
FROM T4 LEFT JOIN AAG00200FL L3
ON T4.LEVEL3 = L3.AANODE
WHERE L3.aaNode<>’account number’ and ACTIVE =1
order by ROW, straccountnumber
),
— link Account Transactions view to the MR structure (AA Tree) above
T6
AS
(
–Account Transactions matched to the account number from the AA Tree
SELECT    LEVEL1, LEVEL2,
CASE
WHEN LEVEL3 =  LEVEL2 THEN ”
ELSE LEVEL3
END AS LEVEL3
, ROW, b.[Journal Entry],b.[TRX Date] TRX_DATE, YEAR([TRX Date]) AS YR,
MONTH([TRX DATE]) AS MM, ACCOUNT, b.[Credit Amount] AS CREDIT,b.[Debit Amount] AS DEBIT,
b.[Account Category Number] AS CATEGORY
FROM         T5 INNER JOIN
AccountTransactions b ON T5.ACCOUNT = b.[Account Number]
)
SELECT ROW,LEVEL2, LEVEL3,TRX_DATE, YR, MM, ACCOUNT,
CREDIT – DEBIT AS REVENUE FROM T6

 

—————————————————————————————————————–

 

This is for Revenue only. To get all Expenses by level, then copy the same query above, replace the word “Revenue” with “Expenses”, and recreate it into a second SQL view. Remember to subtract the Credits from the Debits, when you do the Expenses, else you will end up with bar graphs going southerly.

 

Sorry it was so technical, but it does work and you have the query to give to your I/T guy (gal). All you need to do is make a Tree in AA which matches the levels of your MR P&L, then assign GL accounts to each lowest level according to your MR Row Definitions. This will also work the same for FRx, if you haven’t upgraded to MR.

It really delivers BI to the bosses!!

Share on Facebook
Share on Twitter
Please reload

Follow Us
Please reload

Search By Tags
Please reload

Archive
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square