0

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Lizzi McCarthy
  • 1
  • do you have groups? are you doing a grand total or a group total? is that regular summary, running total or a formula? – cojimarmiami Feb 03 '16 at 18:26
  • you will face this problem when you are manipulating the data that has the single value to the data that has the multiple value then single value will be duplicated to the number of records.. can you show the design and where did you place the `{customer.veh_capex_2016` data – Siva Feb 04 '16 at 05:16
  • yes, there are three groups: "ordinv_rec"."activity" / "ordinv_rec"."op_region_2" / "ordinv_rec"."operator"... the design is a drill down structure, so you double click on activity to show region, and again to show operator (which then shows the orders in the data)... basically the customer.veh_capex_2016 data is only linked to the operator/customer in a single value field but in a summary it wants to multiply the number or orders by the capex 2016 value... – Lizzi McCarthy Feb 04 '16 at 08:49

0 Answers0