Small and medium business | Business Central, N...

Sales summary report for yesterday's orders by dimension

(0) Share

Report

Posted on by T Henry

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.

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

MahGah 15,515 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
T Henry 74 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

JAngle 115 on at

Like (0)

Report

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

Was this reply helpful? Yes No