D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Multi Level Costing Sheet Source Data

  • 1.  Multi Level Costing Sheet Source Data

    SILVER CONTRIBUTOR
    Posted Sep 02, 2020 09:39 AM
      |   view attached
    Hello everyone, and thanks in advance for any insights anyone may have.  It is very much appreciated.

    This question overlaps with Power BI, but I thought I'd post here first.

    I'm trying to be able to build a Power BI report that is able to show an Item Number's Cost per Cost Component at the lowest BoM level.  Within Dynamics 365, an example of the data I'm trying to get within Power BI is attached and I tried to past it within the body of this dialogue as well.

    The only table that I've been successful in finding within Power BI having to do with Item Cost is the InventItemPrices table.  However it doesn't provide any breakdown at the Cost Component level.

    Any ideas on if there is a source available that I can pull from D365 within Power BI that would provide the same information as in the attached screenshot?



    ------------------------------
    Jeffrey DiOrio
    Vice President of Finance
    The Heico Companies
    Azusa
    ------------------------------
    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: Multi Level Costing Sheet Source Data

    TOP CONTRIBUTOR
    Posted Sep 03, 2020 12:50 PM
    I cannot actually see your screen shot, but the detail costs are held in BOMCalcTrans.  There is a field on InventItemPrice called PriceCalcID that links to BOMCalcTrans.

    ------------------------------
    Kevin McLean
    Strategic Solutions NW
    Beaverton OR
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: Multi Level Costing Sheet Source Data

    SILVER CONTRIBUTOR
    Posted Sep 03, 2020 01:13 PM
    Thanks Kevin, greatly appreciated.  I'm unfortunately not seeing that table as an option within Power BI :(  I'll have to investigate why it's not appearing within Power BI for me.

    ------------------------------
    Jeffrey DiOrio
    Vice President of Finance
    The Heico Companies
    Azusa
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 4.  RE: Multi Level Costing Sheet Source Data

    SILVER CONTRIBUTOR
    Posted Sep 15, 2020 09:27 AM
    Hi everyone.  Just wanted to ping this one to the top.  The table that @Kevin McLean mentioned, unfortunately, I'm not finding within the list of tables I have access to within Power BI.  Any other ideas?​

    ------------------------------
    Jeffrey DiOrio
    Vice President of Finance
    The Heico Companies
    Azusa
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 5.  RE: Multi Level Costing Sheet Source Data

    TOP CONTRIBUTOR
    Posted Sep 16, 2020 10:51 AM
    I believe technically the "tables" that PowerBI allows access to are actually what D365 FnO calls Entities. Not all tables within D365 FnO have corresponding entities. However, you can create custom entities to fill that gap.
    Instructions can be found here: Data entities overview - Finance & Operations | Dynamics 365

    ------------------------------
    Tony Zeigler
    Senior Consultant
    Strategic Solutions NW
    Beaverton OR
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 6.  RE: Multi Level Costing Sheet Source Data

    GOLD CONTRIBUTOR
    Posted Sep 16, 2020 10:55 AM
    Hello,

    I ll just comment real quick on why you don't see this table (BOMCalcTrans) in PowerBI. The tables you see in PowerBI, assuming you are using Odata to connect are not the SQL tables from which the system runs, what you see in PowerBI are data entities that can be related to one table or multiple tables in the SQL database. To be able to see this table BOMCalcTrans you will need a developer to custom create a dataentity set it to public and link it to the data from this SQL table BOMCalcTrans.

    regards,


    ------------------------------
    Juan Sebastian Grijalba
    Berkowitz Pollack Brant
    Miami
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


If you've found this thread useful, dive deeper into User Group community content by role