Sales By Date Report

Hello…

I asked this question over in the Q&A a couple of weeks ago, I never got to a successful report.

Here’s what I’d like to output:

Date | Total Sales
11/28/2014 | $100
11/27/2014 | $200
11/26/2014 | $300
11/25/2014 | $400

Grand Total: | $1,000

Here’s a screenshot of my report’s settings. This report spits out each order, not grouped as a total by date.

What am I missing? Any help would be truly appreciated!

-Darren

Topic

[RodolfoParticipant]

Unfortunately I don’t think we have ‘GROUP BY DAY’ in OroCRM. You can generate your custom report with a sql query like this:

SELECT DATE_FORMAT(o0_.updated_at, '%Y-%m-%d') AS updated_at0, CONCAT('$', FORMAT(SUM(o0_.total_paid_amount), 2)) AS total_paid_amount1 FROM orocrm_magento_order o0_ WHERE o0_.updated_at >= '2014-11-01 04:00:00' AND o0_.updated_at <= '2014-12-01 04:59:00' AND o0_.total_paid_amount > 0 AND o0_.organization_id = 2 GROUP BY DAY(updated_at0) ORDER BY updated_at0 ASC

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT   DATE_FORMAT(o0_.updated_at, '%Y-%m-%d') AS updated_at0,   CONCAT('$', FORMAT(SUM(o0_.total_paid_amount), 2)) AS total_paid_amount1 FROM   orocrm_magento_order o0_ WHERE       o0_.updated_at >= '2014-11-01 04:00:00'       AND o0_.updated_at <= '2014-12-01 04:59:00'       AND o0_.total_paid_amount > 0       AND o0_.organization_id = 2 GROUP BY   DAY(updated_at0) ORDER BY   updated_at0 ASC

[19princeParticipant]

@Rodolfo. Thanks for chiming in. Pardon the total noob question, is there a place in the GUI for me to execute this sort of SQL query?

Thanks again!

[RodolfoParticipant]

Hi @19prince !

I don’t think so. I just tested here using a MySQL Client. Maybe someone from oro-core have other solution.

:/

[Yurii MuratovParticipant]

Hi, 19prince.
Yes, for now, you cannot create this report in report builder UI page.

But you can create custom report mannualy.

An example of such report: http://demo.orocrm.com/report/static/leads/by_date

As you can see, here we have grouping by date.

How it was done.

Main query for this report you can see here: https://github.com/orocrm/crm/blob/master/src/OroCRM/Bundle/ReportBundle/Resources/config/datagrid.yml#L172 .

Here we made datagrid configuration for this report. The name of grid config shoul be in format orocrm_report-group_name-report_name.

After this, we should connect this report to the menu.

Menu item was done here: https://github.com/orocrm/crm/blob/master/src/OroCRM/Bundle/ReportBundle/Resources/config/navigation.yml#L56. As you can see, here we use orocrm_report_index route with parameters group name and report name from the current grid report config name.

And after this, this item was added to the menu tree here: .

[cardiacParticipant]

hi,

do you have any news? I want to work with datetime fields as date to generate sth like the “leads by date”.

Do I still need an independent datagrid or is the product owner able to create it herself?

[Artem LiubeznyiSpectator]

No progress I’m afraid.

[Author]

Replies