Open Forum

Expand all | Collapse all

Business Central Web services -> publish Object ID 112 Sales Invoice Header -> Power BI

  • 1.  Business Central Web services -> publish Object ID 112 Sales Invoice Header -> Power BI

    Posted 14 days ago
    In Business Central Web services, I am attempting to publish Object ID 112 Sales Invoice Header and then pull the data into Power BI. I feel that I am missing some fundamental understanding of how to approach the Web Services part; I can retrieve other published data in Power BI. My attempts have included (with images):

    1. Create a new web service by searching on the ID, provide a Service Name (Sales_Invoice_Header) and then select Publish.

    2. After doing so, the Object Name changes to Inventory Posting Groups and a search also now shows 112 Inventory Posting Groups.


    3. In Power BI, I can see the Service Name (Sales_Invoice_Header), and am also shown that This table is empty.

    4. To further investigate, I open the Jet Reports Browser and find the Sales Invoice Header table (number 112).

    5. I can drop that into Excel and retrieve the entire data set.

    6. If I attempt to change the Object Type from Page to Query in Web services, it cannot find the object.

    The expectation is to be able to publish the Web Service Object ID 112 Sales Invoice Header and then pull the data into Power BI.

    Any insight into what I may be missing in this approach?
    #Technical #BIandReporting #ArchitectureandDevelopment #BC/NAVPlus
    ​​​​​

    ------------------------------
    Andy Alldredge
    Alum-I-Tank
    Harvard IL
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Business Central Web services -> publish Object ID 112 Sales Invoice Header -> Power BI

    Posted 13 days ago
    A correct answer to this was provided in Dynamics 365 Business Central Forum

    Much thanks to Mohana Yadav for the quick feedback!

    ------------------------------
    Andy Alldredge
    Alum-I-Tank
    Harvard IL
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Business Central Web services -> publish Object ID 112 Sales Invoice Header -> Power BI

    Posted 11 days ago
    Andy, now that you have the posted sales invoices table exposed, have you figured out how to expose all fields in that table?

    It appears the web service only exposes some of the fields, we are in this situation looking for "Order Date" to publish in PBI and the only relevant date exposed is "Document Date".  Even when attempting to edit the data set, I see some fields are not selected to be exposed, however, some are just not there at all?


    ------------------------------
    Phil York
    Radius Global Supply
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Business Central Web services -> publish Object ID 112 Sales Invoice Header -> Power BI

    Posted 10 days ago
    Edited by Andy Alldredge 10 days ago
    Hi Phil, I am running into the same issue. "Order Date" is obviously there, but missing. I can download the entire table, with all 152 fields from the Configuration Packages page.

    And have also tried to edit the Wed Services -> Create Data Set. I can see "Order Date" in the filter selection, but selecting it does not show it.

    I am moving on to looking at Retrieving Date Data in Queries,  as they have an example there that shows the "Order Date" in the Sales Header table. So, think if that works, should be able to use the Posted Sales Invoices as well. Perhaps then create a page extension? If that is the route, it seems an extremely awkward approach. Perhaps someone else will be able to provide more directed feedback.

    ------------------------------
    Andy Alldredge
    Alum-I-Tank
    Harvard IL
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Business Central Web services -> publish Object ID 112 Sales Invoice Header -> Power BI

    Posted 2 hours ago
    Andy, just to update you I solved this issue.  If the page doesn't show the field you need in the PBI report but exists in the table you need to expose it on the page first and then you can edit your data set to include.

    You have 2 options to expose the field on the page.
    • Use Visual Studio Code and create a page extension using AL.  the example below to expose the order data within the posted sales invoice page.
    pageextension 50149 PageExtensionOrderDate extends "Posted Sales Invoice" { layout { addafter("Posting Date") { field("Order Date49919"; "Order Date") { ApplicationArea = All; } } } }​
    • Use BC Sandbox designer and add the field to page and download package.  You will have to use visual studio code to build the .app package and import it into your production environment as an extension.


    ------------------------------
    Phil York
    Radius Global Supply
    ------------------------------

    Academy - Online Interactive Learning from Experts


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