Skip to main content

Notifications

Announcements

No record found.

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

Sales summary report for yesterday's orders by dimension

(0) ShareShare
ReportReport
Posted on by T Henry 74

Hi,

I am having trouble figuring out how to create this report.  In our old ERP system we had a daily report (saved search) that shows the sales orders approved yesterday by order type.  It would show the order type, quantity of sales orders, and sum of the total before taxes & shipping.

I have a dimension for the order type.  Now I am trying to figure out how to create this.  I have read that I can:

- create a customer report but that requires dev work for the RDLC. 

- export to excel and using pivot tables but that sounds like I would need to give that excel file to all users.

- analysis views?

Basically I would like to have this report emailed daily to specific users.  They should also be able to view it at any time in Business Central.  It seems easy enough but I am a bit lost with the options.

  • Suggested answer
    MahGah Profile Picture
    MahGah 15,428 on at
    RE: Sales summary report for yesterday's orders by dimension

    Hi

    Josh and Steve provided great information. As Steve mentioned Jet Report is flexible for such a report. 

    I also have some questions 

    1) Just to make sure. When you say "released yesterday" does it mean you have many "released" SO in the system but you only want to know what has been released yesterday? 

    2) Also, is "released" date is equal to the date the SO created or it is possible your team create SO and then a few days later send it for Approval to Release it? 

    If creation date of SO and Release date are equal to created date then you need to find the method (may required small Dev) to show the dimension on sales header then you can create a filter on page. (filter based on SystemCreatedAt (2000000001, DateTime) / then new Dim Code) take to Excel to sum up. 

    if release date is not equal to creation date then you need to check SystemModifiedAt (2000000003, DateTime) to see if that date works for you after approval. if it does not work then you need a method (dev required) to capture Released date. 

    If you can create all required filter in SO List and they work then all user needs to do is Export to Excel and sum them up. 

    For daily report you can use Excel / Macro / Odata and replicate the same filter. Use https://archerpoint.com/how-to-use-business-central-odata-feeds-to-get-data-into-excel-for-reporting/ as an example and create a Excel report and define the filter range for it. Then use some Excel Macro and Windows scheduler to email (windows scheduler should open one bat file that opens the Excel and after that macro takes over). 

    Or you can create custom report. 

    Thanks 

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Sales summary report for yesterday's orders by dimension

    Hello,

    You are correct you can create an RDLC report via a Developer. Analysis Views for viewing Dimensions on Posted documents and your notes say you want to see Released ones, not closed and posted. I would recommend something a little quicker and that you look at Jet Reports. You can be up and running in a day and you can have 1 person design all the reports and the users can view them with changing or touching the formulas (Excel based). Also, Users with logins to BC can drill into the values to see the actual information.

    Check here: insightsoftware.com/.../

    I have done hundreds of implementations and each one wants Jet so they can be self-sufficient.

    Thanks,

    Steve

  • T Henry Profile Picture
    T Henry 74 on at
    RE: Sales summary report for yesterday's orders by dimension

    Hi Josh,

    I am referring to the sales order being released.  The dimension being used  is a global dimension.

  • Suggested answer
    JAngle Profile Picture
    JAngle 33,159 on at
    RE: Sales summary report for yesterday's orders by dimension

    Some terminology to breakdown. When you say approved do you mean completed (posted in BC)? If the sales orders still exist then I agree that a list view is doable. Or do you use approvals and therefore all “Released” orders from yesterday should be considered?

    If they have been posted - shipped and/or invoiced - then the sales order is deleted - but available in the archive (depending on settings).

    Is order type a global dimension? If so it could be used as a header filter.

    Best to determine what’s going on with the data and then decide. Account schedules or analysis by dimensions are only good when there are registered/posted/written ledgers. You may want to add Power Bi to the list as that is a nine DEV way of achieving the report/analysis

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