Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Reporting Sales Orders...
Small and medium business | Business Central, N...
Suggested answer

Reporting Sales Orders by Date before/after Posting to Sales Invoice

(1) ShareShare
ReportReport
Posted on by jkop 5

Hi! 

Question: How can Power BI query the value of all Sales Orders by Date (historically) for both Sales Orders that are open and for those that have been Posted to Invoice?

Context:

Sales Orders from my e-commerce platform are paid in full at the moment of the transaction on the website.

Sales Orders are then fulfilled (delivered) at a later date at which time, once completed, the Sales Order is Posted to Invoice. 

From a BI point of view, I need to know the dollar amount and count of Sales Orders per date (whether still as Sales Order or Posted Invoice).

I wish to capture the Sales Order value (in $ or quantity or any other measure) by Date for reporting purposes. 

Example:

e.g. Sales Orders on Black Friday 2021 (November 26, 2021) vs. Sales Orders on Black Friday 2020 (November 27, 2021)

Assume all Sales Orders from Black Friday 2020 have been Posted to Invoice on varying dates (different posting dates / delivery dates)

Assume *some* Sales Orders from Black Friday 2021 remain unfulfilled (still a Sales Order) and some have been Posted to Invoice on varying dates (different posting dates / delivery dates)

Thank you so much to the community,

Jeremy

Categories:
Business Central
  • Suggested answer
    MahGah Profile Picture
    MahGah 15,428 on at
    RE: Reporting Sales Orders by Date before/after Posting to Sales Invoice

    Hi jkop 

    For Power BI you can only publish pages and not table. Page only included some "visible" fields. If field is visible in page (you can see it in page or add it through personalization) then you can do the following : in Web Service page select Action->New Document -> Create Data Set. Then either decide to modify existing published web service or create a new one. For sales page my recommendation is to select Create New from Existing. Then you can select more fields to publish. 

    Back to Power BI design: 

    I do not have much experience there. But usually I try to pull the data such as Item info and Customer info from Master Card (Item/Customer) this way if any change happen on those cards my report is update by itself. 

    Also, I usually use the table with most data as a main table and create a relationship with other tables. In you case if SO Line has the majority of info then you can use it as main table and pull the data from other tables to it. I believe SO Line has many to one relationship with SO header.  

    The other method is to ask developer to create Query in BC for you that has all data. Then publish the query and use that.

    I hope this help 

  • jkop Profile Picture
    jkop 5 on at
    RE: Reporting Sales Orders by Date before/after Posting to Sales Invoice

    Hey MahGah,

    Hoping you could shed some light.

    1) Following your feedback on the page inspector, the following holds true.

    Sales Orders

    Page: Sales Order List (9305, List)

    Table: Sales Header (36)

    Posted Sales Invoices

    Page: Posted Sales Invoices (143, List)

    Table: Sales Invoice Header (112)

    In Web Services, (1) I can only retrieve the pages (9305 and 143) but not the tables and most importantly, when comparing the pages (9305 and 143), they do not correspond to the tables.

    In other words, Sales Order List (9305, List) set as a page in Web Services does not have all of the columns of the table Sales Header (36).

    2) Data Model

    In creating my data model within PBI, I need data from both the 'Sales Orders' and 'Sales Orders by Line' tables. That said, when creating the relationship between both tables using the document no. as the key identifier, many-to-many cardinality applies.

    What is the best way to set up the relationship between these tables?

    Should I only use the Sales Orders by Line table and transform it (within PBI) to bring in the data from 'Sales Orders' that I need?

    Which one should serve as a lookup table vs. a fact table? Which one should have a relationship to other tables (e.g. Items, Customers, etc.)

    Thanks so much

  • Suggested answer
    MahGah Profile Picture
    MahGah 15,428 on at
    RE: Reporting Sales Orders by Date before/after Posting to Sales Invoice

    Hi

    1,3) If you go to any page (such as Posted Sales Invoice) then use Alt + Ctrl + F1 (page inspector) you will see Page and Table ID. For example "Posted Sales Invoice (132, Document)" is the page id for Posted Sales Invoice. You always need Page ID for Web Services. Then go to Web Services in BC and see if page (such as 132 ) has been published. if not then click New then select page and in Object ID enter page number (132 for example) and then select name (no space) and publish. This should show up under Web service section in Power BI. 

    pastedimage1642546477806v2.png

    2) Honestly I do not know the answer to this question. I could not find an answer for myself as well. I hope some other members can help with this question. 

  • jkop Profile Picture
    jkop 5 on at
    RE: Reporting Sales Orders by Date before/after Posting to Sales Invoice

    Thank you for sharing this helpful feedback. I did come across a roadblock.

    In Power BI, when connecting Business Central via "Get Data", the Navigator shows:

    Dynamics 365 Business Central

    > Production

    > Company

    > Advanced APIs

    > Standard APIs v.2.0

    > Web Services (legacy)

    None of the folders above have the necessary tables which I do have access to in Business Central. Namely, Sales Header (Table ID 36) and Sales Invoice Header (Table ID 112). 

    Questions: 

    1) Where / how can I access tables that are not displayed in the Navigator? Alternatively, is Sales Header and/or Sales Invoice Header displayed as a different name within the subfolders?

    2) What is the difference between Advanced APIs vs. Standard APIs v.2.0 vs. Web Services (legacy)? Why do tables differ between these sub-folders for a same Company?

    3) How do I add tables from Business Central to these sub-folders?

    The closest tables I could find within the options above are:

    Standard APIs v.2.0 > salesOrders and salesInvoices. 

    While salesOrders does have a column 'orderDate', table salesInvoices does not.

    Within Business Central however, the tables Sales Orders and Sales Invoice Header indeed have 'Order Date' as per your recommendation.

    Note that I am the administrator and have access to everything.

    Thanks so much MahGah 

  • Suggested answer
    MahGah Profile Picture
    MahGah 15,428 on at
    RE: Reporting Sales Orders by Date before/after Posting to Sales Invoice

    Hi

    You need to have access to two pages (table) then calculate what you need and combine.

    In "Posted Sales Invoice Header" you can use "Order Date" field to identify all SO for certain period of time. Then adds value or qTY together.

    In Sales Order Header page you can use the same field (Order Date) to identify all SO for the period you are looking for.

    Then you can add them together to calculate the total.

    Extra note: you can create a new measure then use Var to define variable then calculate it and add them together.  

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

Product updates

Dynamics 365 release plans