Home › Forums › implementation › Implementation discussion ›

report problem

Submitted by Matt Young on Fri, 04/09/2015 - 14:21
in

Would someone mind having a look at this report for me? It reports on product sales grouped by product type.

I am getting some products reported as having a null product type even though I have no products in my database without a product type. I am thinking there is an error with the query. I am running this on a 1.8 system. 

This is the query:

select
    product.name as product,
    customer.name as customer,
    patient.name as patient,
    pt.name as productType,
    parent.activity_start_time as date,
    if(credit,-f.quantity, f.quantity) as quantity,
    if(credit,-f.total,f.total)as total,
    if(credit,-f.tax_amount,f.tax_amount)as gst
from acts parent join act_relationships r on parent.act_id = r.source_id
    join acts child on r.target_id = child.act_id and child.arch_short_name like "act.customerAccount%Item"
    join participations product_partic on child.act_id = product_partic.act_id
    join entities product on product.entity_id = product_partic.entity_id and product.arch_short_name like "product.%"
    left outer join entity_relationships er on product.entity_id = er.target_id and er.arch_short_name = "entityRelationship.productTypeProduct"
    left outer join entities pt on er.source_id = pt.entity_id
    left outer join participations patient_partic on child.act_id = patient_partic.act_id and patient_partic.arch_short_name = "participation.patient"
    left outer join entities patient on patient.entity_id = patient_partic.entity_id and patient.arch_short_name like "party.patient%"
    join participations cust_partic on parent.act_id = cust_partic.act_id and cust_partic.arch_short_name = "participation.customer"
    join entities customer on customer.entity_id = cust_partic.entity_id and customer.arch_short_name like "party.%"
    join financial_acts f on f.financial_act_id = child.act_id
where parent.activity_start_time between $P{startDate} and date_add($P{endDate}, INTERVAL "23:59:59" HOUR_SECOND)
    and product.name like $P{productName}
    and customer.name like $P{customerName}
order by pt.name

This is the report: https://www.dropbox.com/s/00gncimru68kmiw/Sales%20by%20Product%20Type.jrxml?dl=0

Thanks,

Matt.

‹ TiBCO Jasper Reports can edit and save OpenOffice ODT Files Date-stamping a backup file ›

Comment viewing options

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

Re: report problem

Submitted by tgething (not verified) on Fri, 04/09/2015 - 14:49.

Matt - it works for me - running under JasperStudio preview, I get:

[Initially I go nothing - until I remembered to set the datasource for the preview.]

Regards, Tim G

Re: report problem

Submitted by Matt Young on Fri, 04/09/2015 - 15:22.

Are they listings under the "Product Type: null" correct for you? 

I have no products with a product type that is null but they still show up somehow in the report. 

Re: report problem

Submitted by tanderson on Fri, 04/09/2015 - 15:07.

Superficially your query looks OK. Are you seeing inactive products?

You can verify your products all have product types by running:
 

select *
from entities product
left join entity_relationships r
    on r.arch_short_name = "entityRelationship.productTypeProduct"
        and r.target_id = product.entity_id
left join entities type
    on r.source_id = type.entity_id
where product.arch_short_name in ("product.medication", "product.service", "product.merchandise")
    and type.entity_id is null;

Any product returned by the query has no product type. To exclude inactive products, add

product.active = 1

to the where clause.

 

Re: report problem

Submitted by Matt Young on Fri, 04/09/2015 - 15:27.

definitely no active products with a null product type. The invoiced amounts listed under null for product type are products that definitely do have a product type. 

Re: report problem

Submitted by tgething (not verified) on Fri, 04/09/2015 - 15:52.

Matt - I adjusted your report to replace the patient name with the product name. This gave:

I then checke the 1st two products - both have no type as shown below.

Regards, Tim G

Re: report problem

Submitted by Matt Young on Fri, 04/09/2015 - 21:45.

The problem was that the query was picking up products created from a template and these appeared duplicated in the null product type as well as the actual product in the correct product type. I changed the query to:

select
    product.name as product,
    customer.name as customer,
    patient.name as patient,
    pt.name as productType,
    parent.activity_start_time as date,
    if(credit,-f.quantity, f.quantity) as quantity,
    if(credit,-f.total,f.total)as total,
    if(credit,-f.tax_amount,f.tax_amount)as gst
from acts parent join act_relationships r on parent.act_id = r.source_id
    join acts child on r.target_id = child.act_id and child.arch_short_name like "act.customerAccount%Item"
    join participations product_partic on child.act_id = product_partic.act_id
    join entities product on product.entity_id = product_partic.entity_id and (product.arch_short_name like "product.medication" or product.arch_short_name like "product.service%" or product.arch_short_name like "product.merchandise%")
    left outer join entity_relationships er on product.entity_id = er.target_id and er.arch_short_name = "entityRelationship.productTypeProduct"
    left outer join entities pt on er.source_id = pt.entity_id
    left outer join participations patient_partic on child.act_id = patient_partic.act_id and pproduct.arch_short_name like "product.patient_partic.arch_short_name = "participation.patient"
    left outer join entities patient on patient.entity_id = patient_partic.entity_id and patient.arch_short_name like "party.patient%"
    join participations cust_partic on parent.act_id = cust_partic.act_id and cust_partic.arch_short_name = "participation.customer"
    join entities customer on customer.entity_id = cust_partic.entity_id and customer.arch_short_name like "party.%"
    join financial_acts f on f.financial_act_id = child.act_id
where parent.activity_start_time between $P{startDate} and date_add($P{endDate}, INTERVAL "23:59:59" HOUR_SECOND)
    and product.name like $P{productName}
    and customer.name like $P{customerName}
order by pt.name

which resolved this. I also left out product.priceTemplate for the same reason. It would be better to split the medications into their unit price or fixed price and priceTemplate to seperate the dispensing fee I guess.   

Re: report problem

Submitted by Matt Young on Fri, 04/09/2015 - 21:59.

I lied it wouldn't work with product.priceTemplate in there so I included it as well. 

Re: report problem

Submitted by tanderson on Mon, 07/09/2015 - 19:43.

Haven't tested this, but you might get better performance replacing the lines:

    join participations product_partic on child.act_id = product_partic.act_id
    join entities product on product.entity_id = product_partic.entity_id and (product.arch_short_name like "product.medication" or product.arch_short_name like "product.service%" or product.arch_short_name like "product.merchandise%")

with

    join participations product_partic on child.act_id = product_partic.act_id and product_partic.arch_short_name = "participation.product"
    join entities product on product.entity_id = product_partic.entity_id

 

Syndicate content