Creating a group-by report

[Khurram Shahzad]

Dear All,

I want to create a report which will show the summary of sales grouped by the product category. The user will input the date range i.e. 'Date From' and 'Date To'.

I have tried to use the search method of model but I think it does not support the 'group by' clause. Any ideas or best practices of achieving this?

--

 

Regards,

Khurram.

[Cédric Krier]

Create a ModelSQL with a custom table_query [1] like that you will be
free to use the full features of SQL to make your group by.
Also for Date encoding, you can use the new context_model on
ir.action.act_window to get fast responsiveness to change dates.

An example of that is the GeneralLedger report:
http://hg.tryton.org/modules/account/file/3f86af1e9ebd/account.py#l1197


[1] http://doc.tryton.org/4.2/trytond/doc/ref/models/models.html?highlight=table_query#trytond.model.ModelSQL.table_query

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Khurram Shahzad]

On Fri, Mar 17, 2017 at 1:10 PM, Cédric Krier <cedric...@b2ck.com> wrote:

On 2017-03-17 12:38, Khurram Shahzad wrote:
> Dear All,
>
> I want to create a report which will show the summary of sales grouped by
> the product category. The user will input the date range i.e. 'Date From'
> and 'Date To'.
>
> I have tried to use the search method of model but I think it does not
> support the 'group by' clause. Any ideas or best practices of achieving
> this?

Create a ModelSQL with a custom table_query [1] like that you will be
free to use the full features of SQL to make your group by.
Also for Date encoding, you can use the new context_model on
ir.action.act_window to get fast responsiveness to change dates.

An example of that is the GeneralLedger report:
http://hg.tryton.org/modules/account/file/3f86af1e9ebd/account.py#l1197


[1] http://doc.tryton.org/4.2/trytond/doc/ref/models/models.html?highlight=table_query#trytond.model.ModelSQL.table_query

Thank you so much for such a prompt and effective response!

--

 

Regards,

Khurram.

[Khurram Shahzad]

Dear Cedric,

I am able to create reports using 'select' and 'join' in my custom table_query. I have also used 'where' clause with '==', '<=' and '>=' operators but I am unable to figure out how to use 'IN' operator. I tried:

where sale.state IN ('processing','done')

But failed. Can you please guide me the right syntax of using this operator. Also, can I find some documentation on 'select', 'join' and available operators?

Best Regards,

Khurram.

[Cédric Krier]

On 2017-03-20 11:46, Khurram Shahzad wrote:
> I am able to create reports using 'select' and 'join' in my custom
> table_query. I have also used 'where' clause with '==', '<=' and '>='
> operators but I am unable to figure out how to use 'IN' operator. I tried:
> where sale.state IN ('processing','done')
>
> But failed. Can you please guide me the right syntax of using this
> operator. Also, can I find some documentation on 'select', 'join' and
> available operators?

See https://pypi.python.org/pypi/python-sql
All the operators are in `sql.operators`.