Excel Charts from GP Management Reporter

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