Open Forum

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

    Posted Oct 16, 2020 12:02 PM
    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
    ------------------------------


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

    Posted Oct 16, 2020 03:28 PM
    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
    ------------------------------



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

    Posted Oct 19, 2020 08:13 AM
    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
    ------------------------------



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

    Posted Oct 19, 2020 05:43 PM
    Edited by Andy Alldredge Oct 19, 2020 05:45 PM
    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
    ------------------------------



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

    Posted Oct 30, 2020 09:37 AM
    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
    ------------------------------



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

    Posted Nov 04, 2020 09:12 AM
    Thanks Phil. Your solution looks great and appreciate the feedback!

    I was going the route of the page extension in VSC but then was also looking at creating a query object / procedure for other pursuits that may stretch PBI (e.g. it seems that managing complex relationships in PBI is more of a workaround than a feature). Could see that I need a better foundational understanding in VSC / AL and starting to read / work through Mastering Microsoft Dynamics 365 Business Central (Packt Publishing; December 20, 2019; by Stefano Demiliani & Duilio Tacconi). Its proving to be a good study.

    Thanks again for your contribution.

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



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

    TOP CONTRIBUTOR
    Posted Nov 05, 2020 09:12 AM
    Since you are "exposing" pages using Web Services you only have the fields that are in the page available. As you, @Phil York, have noted ​a page extension allows you to include additional fields that you may need. Part of your Planning for PowerBI needs to include assessing what is on the pages that you want to use.

    ------------------------------
    Andrew Good
    President
    Liberty Grove Software
    Oakbrook Terrace IL
    ------------------------------



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

    Posted Nov 05, 2020 06:31 PM
    Is there a way to create a new query object?

    ------------------------------
    Jordan Combs
    Albert Frei & Sons, Inc
    Henderson CO
    ------------------------------



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

    Posted Nov 06, 2020 08:27 AM
    Yes, in VSC, can start by using a code snippet - type tq.
    There are two types of query objects. Have a look here - Query Object - for more info.

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



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