I am currently using MR 2012 for my reports for GP 2018, but I was wondering if anyone knows how they are produced and what SQL Queries are used to create the generate the standard Financial Reports, as I was looking at trying to reproduce these in PowerBi and a mini project for myself
IIRC, there's a bunch of stored procs inside the MR database that are used to run the extracts.
The other thing you can do is run a SQL profiler trace on a non-production environment when the extract is running. That should show the way it's extracting and aggregating the results.
Hi BrianHarry - A lot of that info you want to reproduce is encrypted and in the SQL procs/tables. The GL data comes form the GL Summary or GL Detail tables (work, open and history). I couldn't imagine how to pull that off, but I think I see what you are trying to do. For Power BI financial statements, I would recommend instead looking at using the Account Category as the financial reporting driver, and/or making your own "classes" or groups of accounts within the Power BI model itself.
hey BrianHarry - did this answer your question?
Hi BrianHarry, in addition to David's answer I would recommend to look for some blogs and recordings from MsBelindaAllen for PowerBi financial statements from GP. She has had some great presentations on it and some examples to start with.
BrianHarry MR 2012 queries are almost exclusively using the [AccountTransactions] and [AccountSummary] default views in SQL