Query Report - Stock Ledger

[Paul Dowd]

Hi,

I've created a basic query report as below:

SELECT

`tabStock Ledger Entry`.`posting_date`,

`tabStock Ledger Entry`.`voucher_no` as "Delivery Note:Link/Delivery Note:100",

`tabStock Ledger Entry`.`item_code` as "Item Code:Link/Item:80",

`tabStock Ledger Entry`.`batch_no`,

`tabStock Ledger Entry`.`warehouse`,

`tabStock Ledger Entry`.`actual_qty`,

`tabStock Ledger Entry`.`stock_uom`,

`tabStock Ledger Entry`.`qty_after_transaction`,

(SELECT `tabDelivery Note`.`customer_name` FROM `tabDelivery Note` WHERE `tabDelivery Note`.`naming_series` = `tabStock Ledger Entry`.`voucher_no`) AS Customer

FROM

   `tabStock Ledger Entry`

WHERE

   `tabStock Ledger Entry`.`voucher_type` = 'Delivery Note' AND `tabStock Ledger Entry`.`item_code` = 'NU-1'

   `

This works to display most of the data perfectly fine.

I have a few questions:

1. can I make the link open in a new window? and if so how

2. the stock UOM isn't showing for some reason, it shows fine on the Stock Ledger master report, is there another field I should be using here?

3. how do I pull the Customer name (final line in the select query) Basically I want to display the name of the customer on each line. This requires getting the Customer Name from the delivery note which is linked in the query as `voucher_no`. I need this to happen on each result line.

4. how can I add a filter so that the item and voucher_type can be selected by the user? I'm assuming something has to go in the JS section? I don't want to have to add a customer report for every product!

I'll be using the method in #3 to pull in a number of other bits of linked data from customer files and delivery notes as well.

Many Thanks

Paul

[Paul Dowd]

Got #3 myself in the end :)

other questions still apply

Example working code:

SELECT

`tabStock Ledger Entry`.`posting_date`,

`tabStock Ledger Entry`.`voucher_no` as "Delivery Note:Link/Delivery Note:90",

`tabStock Ledger Entry`.`item_code` as "Item Code:Link/Item:80",

`tabStock Ledger Entry`.`batch_no`,

`tabStock Ledger Entry`.`warehouse`,

`tabStock Ledger Entry`.`actual_qty`,

`tabStock Ledger Entry`.`stock_uom`,

`tabStock Ledger Entry`.`qty_after_transaction`,

`tabDelivery Note`.`customer_name` AS "Customer"

FROM

   `tabStock Ledger Entry` INNER JOIN `tabDelivery Note` ON `tabStock Ledger Entry`.`voucher_no` = `tabDelivery Note`.`name`

[Rushabh Mehta]

On 22-Sep-2014, at 6:35 am, Paul Dowd <pd...@myzealand.com> wrote:

Hi,

I've created a basic query report as below:

SELECT

`tabStock Ledger Entry`.`posting_date`,

`tabStock Ledger Entry`.`voucher_no` as "Delivery Note:Link/Delivery Note:100",

`tabStock Ledger Entry`.`item_code` as "Item Code:Link/Item:80",

`tabStock Ledger Entry`.`batch_no`,

`tabStock Ledger Entry`.`warehouse`,

`tabStock Ledger Entry`.`actual_qty`,

`tabStock Ledger Entry`.`stock_uom`,

`tabStock Ledger Entry`.`qty_after_transaction`,

(SELECT `tabDelivery Note`.`customer_name` FROM `tabDelivery Note` WHERE `tabDelivery Note`.`naming_series` = `tabStock Ledger Entry`.`voucher_no`) AS Customer

FROM

   `tabStock Ledger Entry`

WHERE

   `tabStock Ledger Entry`.`voucher_type` = 'Delivery Note' AND `tabStock Ledger Entry`.`item_code` = 'NU-1'

   `

This works to display most of the data perfectly fine.

I have a few questions:

1. can I make the link open in a new window? and if so how

Thats not so easy - the entire "desk" is basically one page.

2. the stock UOM isn't showing for some reason, it shows fine on the Stock Ledger master report, is there another field I should be using here?

can you define the type as in:

as "Stock UOM:Link/UOM:80"

3. how do I pull the Customer name (final line in the select query) Basically I want to display the name of the customer on each line. This requires getting the Customer Name from the delivery note which is linked in the query as `voucher_no`. I need this to happen on each result line.

you go this :)

4. how can I add a filter so that the item and voucher_type can be selected by the user? I'm assuming something has to go in the JS section? I don't want to have to add a customer report for every product!

Check this:

https://github.com/frappe/erpnext/blob/develop/erpnext/accounts/report/gross_profit/gross_profit.js

I'll be using the method in #3 to pull in a number of other bits of linked data from customer files and delivery notes as well.

Many Thanks

Paul

--
Note:
 
If you are posting an issue,
 
1. ERPNext is a free and open source software and support is given on this forum by a team (https://frappe.io/webnotes). So please consider donating if you find this forum useful (https://frappe.io/buy). Even a small amount would be helpful.
2. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
3. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
4. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.
 
End of Note
---
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/e4cc5646-1773-464d-9f46-79fedb733bd5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Paul Dowd]

#2 made no difference, I'm using:

`tabStock Ledger Entry`.`stock_uom` as "Stock UOM:Link/UOM:80",

#4 - I added the following under "Javascript" in the query report page and got no change in the report, no select fields or anything

frappe.query_reports["Stock Movement Test"] = {

"filters": [

{

"fieldname":"item_code",

"label": __("Item"),

"fieldtype": "Link",

"options": "Item",

"default": frappe.defaults.get_user_default("item_code")

},

{

"fieldname":"from_date",

"label": __("From Date"),

"fieldtype": "Date",

"default": frappe.defaults.get_user_default("year_start_date")

},

{

"fieldname":"to_date",

"label": __("To Date"),

"fieldtype": "Date",

"default": frappe.defaults.get_user_default("year_end_date")

},

]

}

thanks

Paul

[Rushabh Mehta]

Can you share the link to your repo?

--
1. Please be kind and don't send large attachments
2. For issues, features requests use https://github.com/frappe/erpnext/issues

---
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/6f679c77-71f2-4144-9aef-cf48f38312ab%40googlegroups.com.

[Paul Dowd]

Hi Rushabh,

I'm assuming you mean a github repository, in which case I don't have one I'm afraid. I'm making these code changes in the "Query" type report generator when logged in as Administrator.

I have a support package and you have my access details so please login with them to check. The JS side doesn't seem to change anything which is strange since I thought it would at least do something (like break it) if it was incorrect.

The company is Nutriacare.

Thanks

Paul

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

[Paul Dowd]

This is a screenshot of the report builder

[Rushabh Mehta]

Can you check if you see an error in your Javascript Console? Could be a syntax error in your JS code.

On 25-Sep-2014, at 4:18 am, Paul Dowd <pd...@myzealand.com> wrote:

This is a screenshot of the report builder

--
1. Please be kind and don't send large attachments
2. For issues, features requests use https://github.com/frappe/erpnext/issues
---
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/e59dc607-f980-41f9-8055-25c2e0601d29%40googlegroups.com.

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

<Stock Movement Test.png>

[Paul Dowd]

Now it seems that the fields are coming up, but I don't get any effect when I try and use them.

On Friday, 26 September 2014 17:43:19 UTC+12, rushabh wrote:

Can you check if you see an error in your Javascript Console? Could be a syntax error in your JS code.

On 25-Sep-2014, at 4:18 am, Paul Dowd <pd...@myzealand.com> wrote:

This is a screenshot of the report builder

--
1. Please be kind and don't send large attachments
2. For issues, features requests use https://github.com/frappe/erpnext/issues
---
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

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