I'm working on crystal reports and have a humdinger of a problem with summaries - I have a batch of data which has three levels of summaries:
"ordinv_rec"."activity"
"ordinv_rec"."op_region_2"
"ordinv_rec"."operator"
this is my query:
SELECT "ordinv_rec"."activity", "ordinv_rec"."status_2", "ordinv_rec"."operator_2", "ordinv_rec"."quote_no", "ordinv_rec"."vehicle_class_2", "customer"."veh_capex_2016", "ordinv_rec"."date_created", "ordinv_rec"."capex_value", "ordinv_rec"."activity_2", "ordinv_rec"."sub_class_2", "ordinv_rec"."budget_yn", "ordinv_rec"."budget_year", "ordinv_rec"."op_region_2", "customer"."activity"
FROM "dbase"."dbo"."customer" "customer"
FULL OUTER JOIN "dbase"."dbo"."ordinv_rec" "ordinv_rec" ON "customer"."customer_code"="ordinv_rec"."operator"
WHERE "ordinv_rec"."date_created">={ts '2015-01-01 00:00:00'}
AND ("ordinv_rec"."status_2"='DELIVERED' OR "ordinv_rec"."status_2"='LETTER OF INTENT' OR "ordinv_rec"."status_2"='LIVE')
AND ("ordinv_rec"."budget_year"=0 OR "ordinv_rec"."budget_year"=2016)
Basically the "customer"."veh_capex_2016" field shows a single value for that operator e.g. £100 and I need the variance to be calculated between "customer"."veh_capex_2016" and my VARIANCE formula {customer.veh_capex_2016}-{@Total Committed YTD} but inserting this in the summary returns multiple values. i.e. {customer.veh_capex_2016} gets multiplied by the number of orders and then deducts the {@Total Committed YTD} instead of returning just the single field value
Any help or advice very gratefully received.