Stock Take List report

in
Category: 
Stock

This template provides a list of products and their current stock quantities with room to enter actuals.  Provides filters for product class (medication , merchandise),  product type and product name.

I have updated this to include a filter for Product Classificaton and modified the filter logic on both Product Type and Product Classification to work better with nulls.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Stock Take List report

I have updated this template to resolve issue with product information not displaying correctly

Stock Take List report

Hi Tony,

Is it possible to update this report to list products which do not have a product type set? Currently they are not listed.

Matt.

Stock Take List report

Hi Matt,

Yes.  I believe you just need to change the report query to look like this. 

select e.entity_id as pid, e.name as product, l.name as uom, sl.name as stockLocation, if(d2.value,d2.value,"0") as currentStock
from
entities e inner join products p on e.entity_id = p.product_id
left outer join entity_relationships r on e.entity_id = r.target_id and r.arch_short_name = "entityRelationship.productTypeProduct"
left outer join entities pt on r.source_id = pt.entity_id
left join entity_details d on d.entity_id = e.entity_id and d.name = "sellingUnits"
left join lookups l on l.code = d.value and l.arch_short_name = "lookup.uom"
left outer join entity_relationships s on s.source_id = e.entity_id and s.arch_short_name = "entityRelationship.productStockLocation"
left join entity_relationship_details d2 on s.entity_relationship_id = d2.entity_relationship_id and d2.name = "quantity"
left join entities sl on sl.entity_id = s.target_id
where e.name like $P{productName} and
e.active = true and
((pt.name like $P{productType}) or (pt.name is null)) and
(
    (e.arch_short_name = "product.medication" and $P{includeMedications}) or
    (e.arch_short_name = "product.merchandise" and $P{includeMerchandise})
)
order by e.name

Tell me if it works as expected :-)

 

Cheers

Tony

Stock Take List report

Thanks Tony,

it now works as expected. I have attached the report with the new query which includes products without a product-type as well.

Matt.

AttachmentSize
Stock Take List 2.jrxml 13.92 KB
Syndicate content