Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Connect D365 FO with S...
Finance forum
Answered

Connect D365 FO with Sql Database

Posted on by 451

Hi,

Is there a way to connect D365 FO with Management reporter database.

I have used the standard codes but it does not gets connected with the database.

Is it that we have a different way to connect to Sql database in D365 FO.

Thanks,

VM

  • vicky1234 Profile Picture
    vicky1234 451 on at
    RE: Connect D365 FO with Sql Database

    Thanks Andre

    I suggested the same initially however I am not sure on the structures of MR tables and designs so thought of checking.

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,696 Super User on at
    RE: Connect D365 FO with Sql Database

    Hi Vicky,

    I think the contents in these tables will only change when you change the report setup. I haven't paid attention to these tables. I did some troubleshooting on the data mart integration before.

    If the reports are designed in a development environment first, you can export the contents of these tables and see if you can use this as input for your 3rd party service. Not ideal, but might work.

  • vicky1234 Profile Picture
    vicky1234 451 on at
    RE: Connect D365 FO with Sql Database

    Thanks Andre for the reply.

    Just one thing I would like to understand , since I have access to the dev environment MR tables. I would like to understand whether the data in these 3 tables (ControlRowCriteria ,ControlRowMaster ,ControlRowDetail)  is static or it keeps on changing .

    I mean if the MR database is having only reporting setups in this tables then this should be static and would change only if the MR report design is changed.

    However I am not sure if this will help me in exporting to the prod envt , however just an idea.

    Is there any other solution that you suggest  ?

    Thanks,

    VK

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,696 Super User on at
    RE: Connect D365 FO with Sql Database

    Hi Vicky,

    So, you would like to have some reporting setup also exported. Unfortunately, there is no option to do this. You can export the report blocks, but that is in an unreadable file format. You can't connect the MR database. The MR service is retrieving transactional data from F&O. The report designs are created using a designer app which directly connects to the MR database storage.

  • vicky1234 Profile Picture
    vicky1234 451 on at
    RE: Connect D365 FO with Sql Database

    Thanks once Again Andre.

    The requirement is to join the dimension tables of AX with tables of MR eg. MR is having tables like Reporting.ControlRowMaster ,Reporting.ControlRowCriteria . Reporting is the schema of this tables.

    So it will be like for example MainAccount join Reporting.ControlRowCriteria where MainAccount .fieldA == ControlRowCriteria .fieldB and the data of this query is pushed to a custom table ABC.

    This table will be further used by other applications eg. data lake.

    I checked in D365 but somehow i did not find any reference to  MR tables like ControlRowCriteria ,ControlRowMaster ,ControlRowDetail in Visual studio.

    If I get the data of these 3 tables in AX then I don't need to connect to MR databases.

    Also does these tables store any transaction data , or its just that they store the MR report design data and the data changes only when the MR report design is changed.

    If I get the data then the problem is resolved :)

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,696 Super User on at
    RE: Connect D365 FO with Sql Database

    Hi Vicky,

    Can you be specific about which information should be pulled from the MR database? You can't access it directly, so I would like to think with you what information to retrieve. As mentioned, MR information is retrieved from F&O, so there should be a way to get all information gathered from F&O and put it in your custom table. MR is just storing and aggregating it in a different way to support also other Dynamics solutions.

  • vicky1234 Profile Picture
    vicky1234 451 on at
    RE: Connect D365 FO with Sql Database

    Hi Andre,

    thanks fore replying,

    The requirement is that the client wants to fetch data from some tables of MR database and with some logic push the data in D365 tables .

    this custom D365 table will be used by 3rd party for reporting purpose.

    It will be a batch job that will run EOD.

    Also one more question is that whether we have the database names same as we have in Dev environment or is it different ? I will ask this in a separate question but just in case you know.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,696 Super User on at
    RE: Connect D365 FO with Sql Database

    Hi Vicky,

    All information in the MR database comes from F&O. So, you have the requirement to get data back in F&O which is already stored in F&O? What is the exact business requirement?

  • vicky1234 Profile Picture
    vicky1234 451 on at
    RE: Connect D365 FO with Sql Database

    I want to run a query via the code in MR database and save the results in Axdb tables.

    And the query is a bit complicated

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,696 Super User on at
    RE: Connect D365 FO with Sql Database

    Hi Vicky,

    Can you tell what exactly you want to accomplish with a direct connection to a database?

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans