Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Check criteria of Mana...
Finance | Project Operations, Human Resources, ...
Answered

Check criteria of Management Reporter reports

(0) ShareShare
ReportReport
Posted on by Jason Yeung 181

Hi,

I'm currently creating a Power BI report based off the AccountTransaction view of GP. To compare whether the numbers are correct, I would compare the numbers of this report against a report in Management Reporter. The numbers are slightly off. I was wondering is there a way to check for the query or queries that grab data for a Management Reporter report?

I looked around and some people mentioned the data used by Management Reporter is derived from the AccountTransaction and AccountSummary views. If this is the case, is there a way to check for the criteria used? The SQL script I wrote for Power BI only checks whether the AccountTransactions have a specific account number (e.g. "select * from AccountTransaction where [Account Number] in (...) ). There is no other clause I added to the SQL script.

Jason

Categories:
Microsoft Dynamics GP
  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Check criteria of Management Reporter reports

    Jason,

    I'd suspect that the slight variance you see could come from the way MR queries the GL tables based possibly on a different date field than the AccountTranscations views.. You'd have to take appart the SQL view to see which dates are queried.

    Besides that, what field are you using when querying the data to filter out the data ? I mean when using the AccountTransactions view, you likely limit the data by using a date filter .. which one ?

  • Jason Yeung Profile Picture
    Jason Yeung 181 on at
    RE: Check criteria of Management Reporter reports

    Hi Beat,

    It's mainly to compare the results in the Power BI and MR reports. I wrote an SSIS package which grabs data from the AccountTransactions view in GP and puts it in the data warehouse, where the Power BI report obtains it. My company's accountant has an MR report which displays the transactions and we were comparing the contents between the 2.

    They are very similar, but the numbers are slightly off. It's assumed that the MR report is correct, so I wanted to check what data is the MR based on and to see if there's a difference between the queries (e.g. the clauses especially). I thought MR data could be derived from the AccountTransactions view as well (e.g. it may access the view when the DataMart is generated).

    Sincerely,

    Jason

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Check criteria of Management Reporter reports

    That is likely what I'd do too...

    However, you have to keep in mind that if your MR 2012 is configured to use the DataMart connector instead of the Legacy connector, it's not going to hit the GP company DB's, but the DM DB.. .in which case the whole query will be useless as the table structure is very confusing in the MR DM DB.

    You could install the Legacy connector temporarily and see how the trace results look like..

    However, I'm questioning all the efforts of getting down to this, since PowerBI isn't really a tool meant to build Financial Reports... why not just look at some other tools available from the market.. There are quite a few good ones to replace MR2012.

    IMHO PowerBI is good to create some C-Level dashboards to show Sales numbers, like top 15 customers of the  months, or Inventory movement, or even other type of transactions.. You might get some global data on the Financials of GP, but I don't think that it should be used as a pure Financial Reporting tool.

    Microsoft provides a free PowerBI Dashboard for GP, but it uses the ODATA services, and those by nature are finicky to get working properly.

  • Jason Yeung Profile Picture
    Jason Yeung 181 on at
    RE: Check criteria of Management Reporter reports

    Hi Beat,

    Thanks for your help! Yeah, I did some investigation on various Dynamics forums and also talked to some co-workers and getting the SQL query of an MR report is not very straight forward.

    I reached out to Microsoft support and they mentioned the MR code dynamically creates SQL queries. He recommends using SQL query profiler while generating the MR report to see if I could obtain the query. He recommends the following steps:

    -Start SQL Server Profiler and log in as an admin.

    -Start a new trace and do the following under the Event Selection Tab.

    -Mark the "Show all events" checkbox.

    -Select the items you are looking to trace. TSQL would probably be the most helpful for what you are looking for.

    I'll try these steps when I get a chance to see if it works.

    Jason

  • Verified answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Check criteria of Management Reporter reports

    Hi Jason,

    It's hard to tell where the variances could come from between the AcctTrx SQL view and your MR report... MR pulls the data from the GL transactions (Open & History) and uses some filters to include or exclude specific GL accounts or ranges of accounts..

    Transactions dates could play in the game as well, depending in some of the transactions are across some periods end / start and that the SQL views queries the transactions differently than MR.

    Usually the MR reports are pretty close on spot with TB reports run inside of GP for the financial.

    Hopefully someone from Microsoft with insights on MR could respond here.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans