web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Supply chain | Supply Chain Management, Commerce / DIFOT/Monthly sales
Supply chain | Supply Chain Management, Commerce
Answered

DIFOT/Monthly sales

(0) ShareShare
ReportReport
Posted on by Community Member

Hi All,

I'm connected to D365 via Odata and I need to produce reports with weekly/monthy DIFOT , Sales by month / 12 month calculations etc and the main table I was planning to use was SalesOrderLinesV2 but i didn't realize how much data it would be. 1 day of SalesOrderLines data was more then 500 mb, and I have gotten rid of extra columns and products that I don't need.

I wonder if there is a better way to achieve to get sales data? Should I fiddle with SalesOrderHeadersV2 table? 

Thanks

I have the same question (0)
  • MATTGUO Profile Picture
    MATTGUO 22,349 Moderator on at

    Hi,

    Which specific fields do you need?

    In my mind, the sales order header/line entity is good way to achieve the data.

  • Verified answer
    Anders Even Girke Profile Picture
    Anders Even Girke on at

    Hi Rigosakh

    This reply does not directly answer your questions but maybe there is another solution you can take

    Have you looked at the Sales performance embedded Power BI shipped by MS out of box. This is using the Entity Store and AXDW DB. The Power BI will then connect via Direct Query Mode. The Refreh of data in AXDW can the batch updated and now also done incremntal

    You can now also edit the Power BI file to a certain extend (Creating new Visuals, Reporting pages and also calculated fields, Columns) 

    You cannot change the underlying data model or add new datasources  

    Here is a link to a demo how users can now change the Out of box embedded Power BI 

    https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/customize-analytical-workspace?toc=/dynamics365/commerce/toc.json

  • Community Member Profile Picture
    Community Member on at

    Hi MATTGUO,

    Thanks for your input. The problem with SalesLines is the size of data, I struggle loading even 1 day of data. I guess will have to do some work on data transformation in Power Query

  • Community Member Profile Picture
    Community Member on at

    Thank you Anders, looking at Sales Performance report gave an idea to use InvoiceLines instead SalesOrderLines and that table seems to be more compact

  • Community Member Profile Picture
    Community Member on at

    Seems like Odata has performance issues dealing with large amounts of data and when I use ?cross-company=true things get even worse. Even if I narrow down the query to 1 entity (but non-default), Power BI load is very slow. I guess one of the solutions would be using a data warehousing solution like Snowflake but that won't happen any time soon  

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Supply chain | Supply Chain Management, Commerce

#1
Siv Sagar Profile Picture

Siv Sagar 283 Super User 2025 Season 2

#2
Laurens vd Tang Profile Picture

Laurens vd Tang 196 Super User 2025 Season 2

#3
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 139 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans