Customizing report

ERPNext User Forum
1
Hi, I want to customize the standard Purchase Orders Items to be Received by automatically filtering out document numbers based on the series number.

For example, we use two document series based no certain business parameters:

A-###
B-###

The standard report shows all Purchase Orders for both A and B series.  What I want is a report that only shows documents that have the B-### series.  Any tip on how to do this? Below is the code of the report:

select 
    `tabPurchase Order`.`name` as "Purchase Order:Link/Purchase Order:120",
`tabPurchase Order`.`transaction_date` as "Date:Date:100",
`tabPurchase Order`.`supplier` as "Supplier:Link/Supplier:120",
`tabPurchase Order Item`.`project_name` as "Project",
`tabPurchase Order Item`.item_code as "Item Code:Link/Item:120",
`tabPurchase Order Item`.qty as "Qty:Float:100",
`tabPurchase Order Item`.received_qty as "Received Qty:Float:100", 
(`tabPurchase Order Item`.qty - ifnull(`tabPurchase Order Item`.received_qty, 0)) as "Qty to Receive:Float:100",
    `tabPurchase Order Item`.warehouse as "Warehouse:Link/Warehouse:150",
`tabPurchase Order Item`.item_name as "Item Name::150",
`tabPurchase Order Item`.description as "Description::200",
    `tabPurchase Order Item`.brand as "Brand::100"
from
`tabPurchase Order`, `tabPurchase Order Item`
where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
order by `tabPurchase Order`.transaction_date asc

–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

2
Hi Laurence,

Do you want to create separate reports for different series? or do you just want to have the ability to filter out Purchase Order based on ID starting with A- or B-?

If it is the latter, you can already do that using the filter boxes below the resulting table's heading.
See screenshot: http://i.imgur.com/okyQMik.png

-Anand.

On 13-Aug-2013, at 6:39 AM, lxnow <la...@union.ph> wrote:

Hi, I want to customize the standard Purchase Orders Items to be Received by automatically filtering out document numbers based on the series number.

For example, we use two document series based no certain business parameters:

A-###
B-###

The standard report shows all Purchase Orders for both A and B series.  What I want is a report that only shows documents that have the B-### series.  Any tip on how to do this? Below is the code of the report:

select 
    `tabPurchase Order`.`name` as "Purchase Order:Link/Purchase Order:120",
`tabPurchase Order`.`transaction_date` as "Date:Date:100",
`tabPurchase Order`.`supplier` as "Supplier:Link/Supplier:120",
`tabPurchase Order Item`.`project_name` as "Project",
`tabPurchase Order Item`.item_code as "Item Code:Link/Item:120",
`tabPurchase Order Item`.qty as "Qty:Float:100",
`tabPurchase Order Item`.received_qty as "Received Qty:Float:100", 
(`tabPurchase Order Item`.qty - ifnull(`tabPurchase Order Item`.received_qty, 0)) as "Qty to Receive:Float:100",
    `tabPurchase Order Item`.warehouse as "Warehouse:Link/Warehouse:150",
`tabPurchase Order Item`.item_name as "Item Name::150",
`tabPurchase Order Item`.description as "Description::200",
    `tabPurchase Order Item`.brand as "Brand::100"
from
`tabPurchase Order`, `tabPurchase Order Item`
where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
order by `tabPurchase Order`.transaction_date asc


–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 


–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

3
I am looking for former: a pre-formatted report that is already filtered.  The intention is to have faster deployment to end-users.

On Wednesday, August 14, 2013 2:16:46 PM UTC+8, Anand Doshi wrote:
Hi Laurence,

Do you want to create separate reports for different series? or do you just want to have the ability to filter out Purchase Order based on ID starting with A- or B-?

If it is the latter, you can already do that using the filter boxes below the resulting table's heading.

-Anand.

On 13-Aug-2013, at 6:39 AM, lxnow <la...@union.ph> wrote:

Hi, I want to customize the standard Purchase Orders Items to be Received by automatically filtering out document numbers based on the series number.

For example, we use two document series based no certain business parameters:

A-###
B-###

The standard report shows all Purchase Orders for both A and B series.  What I want is a report that only shows documents that have the B-### series.  Any tip on how to do this? Below is the code of the report:

select 
    `tabPurchase Order`.`name` as "Purchase Order:Link/Purchase Order:120",
`tabPurchase Order`.`transaction_date` as "Date:Date:100",
`tabPurchase Order`.`supplier` as "Supplier:Link/Supplier:120",
`tabPurchase Order Item`.`project_name` as "Project",
`tabPurchase Order Item`.item_code as "Item Code:Link/Item:120",
`tabPurchase Order Item`.qty as "Qty:Float:100",
`tabPurchase Order Item`.received_qty as "Received Qty:Float:100", 
(`tabPurchase Order Item`.qty - ifnull(`tabPurchase Order Item`.received_qty, 0)) as "Qty to Receive:Float:100",
    `tabPurchase Order Item`.warehouse as "Warehouse:Link/Warehouse:150",
`tabPurchase Order Item`.item_name as "Item Name::150",
`tabPurchase Order Item`.description as "Description::200",
    `tabPurchase Order Item`.brand as "Brand::100"
from
`tabPurchase Order`, `tabPurchase Order Item`
where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
order by `tabPurchase Order`.transaction_date asc


–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 


–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

4
Hi Laurence,

Then you should create different reports for each series type.

For example: http://pastebin.com/7YStCkvq

I've added another condition. See line no. 21.
Here, change the like part to "B-%" or "C-%", etc.

Thanks,
Anand.

On 14-Aug-2013, at 12:19 PM, lxnow <la...@union.ph> wrote:

I am looking for former: a pre-formatted report that is already filtered.  The intention is to have faster deployment to end-users.

On Wednesday, August 14, 2013 2:16:46 PM UTC+8, Anand Doshi wrote:
Hi Laurence,

Do you want to create separate reports for different series? or do you just want to have the ability to filter out Purchase Order based on ID starting with A- or B-?

If it is the latter, you can already do that using the filter boxes below the resulting table's heading.

-Anand.

On 13-Aug-2013, at 6:39 AM, lxnow <la...@union.ph> wrote:

Hi, I want to customize the standard Purchase Orders Items to be Received by automatically filtering out document numbers based on the series number.

For example, we use two document series based no certain business parameters:

A-###
B-###

The standard report shows all Purchase Orders for both A and B series.  What I want is a report that only shows documents that have the B-### series.  Any tip on how to do this? Below is the code of the report:

select 
    `tabPurchase Order`.`name` as "Purchase Order:Link/Purchase Order:120",
`tabPurchase Order`.`transaction_date` as "Date:Date:100",
`tabPurchase Order`.`supplier` as "Supplier:Link/Supplier:120",
`tabPurchase Order Item`.`project_name` as "Project",
`tabPurchase Order Item`.item_code as "Item Code:Link/Item:120",
`tabPurchase Order Item`.qty as "Qty:Float:100",
`tabPurchase Order Item`.received_qty as "Received Qty:Float:100", 
(`tabPurchase Order Item`.qty - ifnull(`tabPurchase Order Item`.received_qty, 0)) as "Qty to Receive:Float:100",
    `tabPurchase Order Item`.warehouse as "Warehouse:Link/Warehouse:150",
`tabPurchase Order Item`.item_name as "Item Name::150",
`tabPurchase Order Item`.description as "Description::200",
    `tabPurchase Order Item`.brand as "Brand::100"
from
`tabPurchase Order`, `tabPurchase Order Item`
where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
order by `tabPurchase Order`.transaction_date asc


–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.


–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

5
Thanks for that. I get the logic on line 21... however, when I add this my custom report goes blank. As if the code was wrong (wrong syntax?).

Here's my snippet based on the doc series I want to filter (starts with letter X) and I've highlighted the line I added based on your advice. What might I be missing?

where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
    and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
    and `tabPurchase Order`.name like "x%"
order by `tabPurchase Order`.transaction_date asc

On Wednesday, August 14, 2013 2:56:46 PM UTC+8, Anand Doshi wrote:
Hi Laurence,

Then you should create different reports for each series type.


I've added another condition. See line no. 21.
Here, change the like part to "B-%" or "C-%", etc.

Thanks,
Anand.

On 14-Aug-2013, at 12:19 PM, lxnow <la...@union.ph> wrote:

I am looking for former: a pre-formatted report that is already filtered.  The intention is to have faster deployment to end-users.

On Wednesday, August 14, 2013 2:16:46 PM UTC+8, Anand Doshi wrote:
Hi Laurence,

Do you want to create separate reports for different series? or do you just want to have the ability to filter out Purchase Order based on ID starting with A- or B-?

If it is the latter, you can already do that using the filter boxes below the resulting table's heading.

-Anand.

On 13-Aug-2013, at 6:39 AM, lxnow <la...@union.ph> wrote:

Hi, I want to customize the standard Purchase Orders Items to be Received by automatically filtering out document numbers based on the series number.

For example, we use two document series based no certain business parameters:

A-###
B-###

The standard report shows all Purchase Orders for both A and B series.  What I want is a report that only shows documents that have the B-### series.  Any tip on how to do this? Below is the code of the report:

select 
    `tabPurchase Order`.`name` as "Purchase Order:Link/Purchase Order:120",
`tabPurchase Order`.`transaction_date` as "Date:Date:100",
`tabPurchase Order`.`supplier` as "Supplier:Link/Supplier:120",
`tabPurchase Order Item`.`project_name` as "Project",
`tabPurchase Order Item`.item_code as "Item Code:Link/Item:120",
`tabPurchase Order Item`.qty as "Qty:Float:100",
`tabPurchase Order Item`.received_qty as "Received Qty:Float:100", 
(`tabPurchase Order Item`.qty - ifnull(`tabPurchase Order Item`.received_qty, 0)) as "Qty to Receive:Float:100",
    `tabPurchase Order Item`.warehouse as "Warehouse:Link/Warehouse:150",
`tabPurchase Order Item`.item_name as "Item Name::150",
`tabPurchase Order Item`.description as "Description::200",
    `tabPurchase Order Item`.brand as "Brand::100"
from
`tabPurchase Order`, `tabPurchase Order Item`
where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
order by `tabPurchase Order`.transaction_date asc


–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.


–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

6
Hi Laurence,

Sorry. My mistake.

Use double % sign.
Here is the rectified code: http://pastebin.com/yQcBm0pp

If the error still persists, please send me the output of javascript console.

Thanks,
Anand.


On 14-Aug-2013, at 1:17 PM, lxnow <la...@union.ph> wrote:

Thanks for that. I get the logic on line 21... however, when I add this my custom report goes blank. As if the code was wrong (wrong syntax?).

Here's my snippet based on the doc series I want to filter (starts with letter X) and I've highlighted the line I added based on your advice. What might I be missing?

where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
    and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
    and `tabPurchase Order`.name like "x%"
order by `tabPurchase Order`.transaction_date asc

On Wednesday, August 14, 2013 2:56:46 PM UTC+8, Anand Doshi wrote:
Hi Laurence,

Then you should create different reports for each series type.


I've added another condition. See line no. 21.
Here, change the like part to "B-%" or "C-%", etc.

Thanks,
Anand.

On 14-Aug-2013, at 12:19 PM, lxnow <la...@union.ph> wrote:

I am looking for former: a pre-formatted report that is already filtered.  The intention is to have faster deployment to end-users.

On Wednesday, August 14, 2013 2:16:46 PM UTC+8, Anand Doshi wrote:
Hi Laurence,

Do you want to create separate reports for different series? or do you just want to have the ability to filter out Purchase Order based on ID starting with A- or B-?

If it is the latter, you can already do that using the filter boxes below the resulting table's heading.

-Anand.

On 13-Aug-2013, at 6:39 AM, lxnow <la...@union.ph> wrote:

Hi, I want to customize the standard Purchase Orders Items to be Received by automatically filtering out document numbers based on the series number.

For example, we use two document series based no certain business parameters:

A-###
B-###

The standard report shows all Purchase Orders for both A and B series.  What I want is a report that only shows documents that have the B-### series.  Any tip on how to do this? Below is the code of the report:

select 
    `tabPurchase Order`.`name` as "Purchase Order:Link/Purchase Order:120",
`tabPurchase Order`.`transaction_date` as "Date:Date:100",
`tabPurchase Order`.`supplier` as "Supplier:Link/Supplier:120",
`tabPurchase Order Item`.`project_name` as "Project",
`tabPurchase Order Item`.item_code as "Item Code:Link/Item:120",
`tabPurchase Order Item`.qty as "Qty:Float:100",
`tabPurchase Order Item`.received_qty as "Received Qty:Float:100", 
(`tabPurchase Order Item`.qty - ifnull(`tabPurchase Order Item`.received_qty, 0)) as "Qty to Receive:Float:100",
    `tabPurchase Order Item`.warehouse as "Warehouse:Link/Warehouse:150",
`tabPurchase Order Item`.item_name as "Item Name::150",
`tabPurchase Order Item`.description as "Description::200",
    `tabPurchase Order Item`.brand as "Brand::100"
from
`tabPurchase Order`, `tabPurchase Order Item`
where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
order by `tabPurchase Order`.transaction_date asc


–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.



–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.


–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

7
Awesome. Your quick and helpful advice is much appreciated.

On Wednesday, August 14, 2013 4:46:52 PM UTC+8, Anand Doshi wrote:
Hi Laurence,

Sorry. My mistake.

Use double % sign.
Here is the rectified code: http://pastebin.com/yQcBm0pp

If the error still persists, please send me the output of javascript console.

Thanks,
Anand.


On 14-Aug-2013, at 1:17 PM, lxnow <la...@union.ph> wrote:

Thanks for that. I get the logic on line 21... however, when I add this my custom report goes blank. As if the code was wrong (wrong syntax?).

Here's my snippet based on the doc series I want to filter (starts with letter X) and I've highlighted the line I added based on your advice. What might I be missing?

where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
    and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
    and `tabPurchase Order`.name like "x%"
order by `tabPurchase Order`.transaction_date asc

On Wednesday, August 14, 2013 2:56:46 PM UTC+8, Anand Doshi wrote:
Hi Laurence,

Then you should create different reports for each series type.


I've added another condition. See line no. 21.
Here, change the like part to "B-%" or "C-%", etc.

Thanks,
Anand.

On 14-Aug-2013, at 12:19 PM, lxnow <la...@union.ph> wrote:

I am looking for former: a pre-formatted report that is already filtered.  The intention is to have faster deployment to end-users.

On Wednesday, August 14, 2013 2:16:46 PM UTC+8, Anand Doshi wrote:
Hi Laurence,

Do you want to create separate reports for different series? or do you just want to have the ability to filter out Purchase Order based on ID starting with A- or B-?

If it is the latter, you can already do that using the filter boxes below the resulting table's heading.

-Anand.

On 13-Aug-2013, at 6:39 AM, lxnow <la...@union.ph> wrote:

Hi, I want to customize the standard Purchase Orders Items to be Received by automatically filtering out document numbers based on the series number.

For example, we use two document series based no certain business parameters:

A-###
B-###

The standard report shows all Purchase Orders for both A and B series.  What I want is a report that only shows documents that have the B-### series.  Any tip on how to do this? Below is the code of the report:

select 
    `tabPurchase Order`.`name` as "Purchase Order:Link/Purchase Order:120",
`tabPurchase Order`.`transaction_date` as "Date:Date:100",
`tabPurchase Order`.`supplier` as "Supplier:Link/Supplier:120",
`tabPurchase Order Item`.`project_name` as "Project",
`tabPurchase Order Item`.item_code as "Item Code:Link/Item:120",
`tabPurchase Order Item`.qty as "Qty:Float:100",
`tabPurchase Order Item`.received_qty as "Received Qty:Float:100", 
(`tabPurchase Order Item`.qty - ifnull(`tabPurchase Order Item`.received_qty, 0)) as "Qty to Receive:Float:100",
    `tabPurchase Order Item`.warehouse as "Warehouse:Link/Warehouse:150",
`tabPurchase Order Item`.item_name as "Item Name::150",
`tabPurchase Order Item`.description as "Description::200",
    `tabPurchase Order Item`.brand as "Brand::100"
from
`tabPurchase Order`, `tabPurchase Order Item`
where
`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
and `tabPurchase Order`.docstatus = 1
and `tabPurchase Order`.status != "Stopped"
and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
order by `tabPurchase Order`.transaction_date asc


–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.



–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.


–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.