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 [mention:bee131a7d9f34ecb9b60fef317dbeb47:e9ed411860ed4f2ba0265705b8793d05] - did this answer your question?
Hi [mention:bee131a7d9f34ecb9b60fef317dbeb47:e9ed411860ed4f2ba0265705b8793d05], in addition to David’s answer I would recommend to look for some blogs and recordings from [mention:c562b73fe84a49cfa57d05e97a46aba6:e9ed411860ed4f2ba0265705b8793d05] for PowerBi financial statements from GP. She has had some great presentations on it and some examples to start with.
[mention:bee131a7d9f34ecb9b60fef317dbeb47:e9ed411860ed4f2ba0265705b8793d05] MR 2012 queries are almost exclusively using the [AccountTransactions] and [AccountSummary] default views in SQL