SUM For Multiple (But Not All) Rows In A Dataset
Hi This is very difficult to explain but here goes. I am trying to create a report showing profit / loss based on a haulage cost figure versus a haulage charged figure. The haulage cost is the cost of the haulage vehicle per order. The tricky part is that the haulage charged is the total of several 'sub orders' which were all delivered by the same vehicle on the same order. For example, my dataset will generate the following data... | Order # | Haulage Cost | Haulage Charged | | 1 | 20 | 30 | | 1 | 20 | 15 | | 1 | 20 | 10 | | 2 | 15 | 25 | | 2 | 15 | 32 | | 3 | 42 | 12 | | 3 | 42 | 20 | | 3 | 42 | 33 | | 3 | 42 | 56 | The table contains 3 orders which ideally need to be 1 row each. I'm getting more than 1 row because I had to select the query from my 'sub orders' table in order to record the different 'haulage charged' values. What I need to do is create an expression that will give me the total 'haulage charged' for each order, as follows... | Order # | Haulage Cost | Haulage Charge | Total Charge | | 1 | 20 | 30 | 55 | | 1 | 20 | 15 | 55 | | 1 | 20 | 10 | 55 | | 2 | 15 | 25 | 57 | | 2 | 15 | 32 | 57 | | 3 | 42 | 12 | 121 | | 3 | 42 | 20 | 121 | | 3 | 42 | 33 | 121 | | 3 | 42 | 56 | 121 | Assuming this expression is possible, I only need to display 1 row for each order as per below... | Order # | Haulage Cost | Haulage Charge | Total Charge | | 1 | 20 | 30 | 55 | | 2 | 15 | 25 | 57 | | 3 | 42 | 12 | 121 | Once I have the 'Total Charge' SUM, I no longer need the 'Haulage Charge' column and the end result would Ideally be... | Order # | Haulage Cost | Total Charge | | 1 | 20 | 55 | | 2 | 15 | 57 | | 3 | 42 | 121 | If anyone has any ideas on how I could acomplish this, I would be very Grateful. I will post my query below in case this is of any help. Thanks Steve Flynn SELECT (SELECT ConsignmentNumber FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))AS Consignment, (SELECT ConsignmentNumber FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))AS Consignment2, (SELECT HaulageRate FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))AS HaulageCost, (SELECT DeliveryDate FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))AS DeliveryDate, (SELECT DepotLocation FROM General WHERE Uber=(SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id)))AS DeliveryPoint, (SELECT CompanyName FROM General WHERE Uber=(SELECT Haulier FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id)))AS Haulier, Pallets, Haulage= CASE WHEN (SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))!='311208104757'AND(SELECT InvoiceCurrency FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Pounds' AND (SELECT InvoiceUnit FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Pallets'THEN ((SELECT UKHaulage FROM Products WHERE Product_id=product)*Pallets) WHEN (SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))!='311208104757'AND(SELECT InvoiceCurrency FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Pounds' AND (SELECT InvoiceUnit FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Cartons'THEN ((SELECT UKHaulage FROM Products WHERE Product_id=product)*Cartons) WHEN (SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))!='311208104757'AND(SELECT InvoiceCurrency FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Euros' AND (SELECT InvoiceUnit FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Pallets'THEN (((SELECT UKHaulage FROM Products WHERE Product_id=product)*Pallets)*@ExchangeRate) WHEN (SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))!='311208104757'AND(SELECT InvoiceCurrency FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Euros' AND (SELECT InvoiceUnit FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Cartons'THEN (((SELECT UKHaulage FROM Products WHERE Product_id=product)*Cartons)*@ExchangeRate) WHEN (SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))='311208104757'AND(SELECT InvoiceCurrency FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Pounds' AND (SELECT InvoiceUnit FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Pallets'THEN ((SELECT EIREHaulage FROM Products WHERE Product_id=product)*Pallets) WHEN (SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))='311208104757'AND(SELECT InvoiceCurrency FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Pounds' AND (SELECT InvoiceUnit FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Cartons'THEN ((SELECT EIREHaulage FROM Products WHERE Product_id=product)*Cartons) WHEN (SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))='311208104757'AND(SELECT InvoiceCurrency FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Euros' AND (SELECT InvoiceUnit FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Pallets'THEN (((SELECT EIREHaulage FROM Products WHERE Product_id=product)*Pallets)*@ExchangeRate) WHEN (SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))='311208104757'AND(SELECT InvoiceCurrency FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Euros' AND (SELECT InvoiceUnit FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))='Cartons'THEN (((SELECT EIREHaulage FROM Products WHERE Product_id=product)*Cartons)*@ExchangeRate) END, (SELECT Item FROM Products WHERE Product_id=order_products.product)AS Product, (SELECT InvoiceCurrency FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))AS InvoiceCurrency, (SELECT InvoiceUnit FROM General WHERE Uber=(SELECT uber FROM Product_link WHERE product_id=product))AS InvoiceUnit FROM Order_products WHERE Status='Active' AND (SELECT DeliveryDate FROM Consignments WHERE Consignment_id=(SELECT ConsignmentNumber_id FROM Orders WHERE Orders.Order_id=Order_products.order_id))BETWEEN @StartDate AND @EndDate ORDER BY Consignment
April 7th, 2011 12:44pm

I managed to make this a little better by using a matrix rather than a table... | Order # | Haulage Cost | Haulage Charged | | 1 | 20 | 30 | | | | 15 | | | | 10 | | 2 | 15 | 25 | | | | 32 | | 3 | 42 | 12 | | | | 20 | | | | 33 | | | | 56 | Unfortunately, this still doesn't give me a 'total charge' SUM and I can't produce a profit / loss figure. If anyone has a better solution for this you would be helping me immensely. Thanks Steve
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 10:32am

Hi Steve, I am not sure if I understand the whole problem but if your data set returns | Order # | Haulage Cost | Haulage Charged | | 1 | 20 | 30 | | 1 | 20 | 15 | | 1 | 20 | 10 | | 2 | 15 | 25 | | 2 | 15 | 32 | | 3 | 42 | 12 | | 3 | 42 | 20 | | 3 | 42 | 33 | | 3 | 42 | 56 | group the sql query by order # and use max(Haulage Cost) and sum(Haulage Charged). that would return | Order # | Haulage Cost | Total Charge | | 1 | 20 | 55 | | 2 | 15 | 57 | | 3 | 42 | 121 | or you add a group at your report using the order #, hide the details and use a max(Haulage Cost) and sum(Haulage Charged). | Order # | Haulage Cost | Haulage Charged | | 1 | max(=20) | sum(=55 ) | grouped row by order # | 1 | 20 | 30 | detail row hide | 1 | 20 | 15 | detail row hide | 1 | 20 | 10 | detail row hide and so on
April 8th, 2011 5:10pm

Hi Steven_Flynn, Your original dataset structure is like below: |rder # | Haulage Cost | Haulage Charge | Total Charge | | 1 | 20 | 30 | 55 | | 1 | 20 | 15 | 55 | | 1 | 20 | 10 | 55 | | 2 | 15 | 25 | 57 | | 2 | 15 | 32 | 57 | | 3 | 42 | 12 | 121 | | 3 | 42 | 20 | 121 | | 3 | 42 | 33 | 121 | | 3 | 42 | 56 | 121 | What you need is below report layout, besides this you still want to get a total at the bottom of the table, right? |Order# | Haulage Cost | Total Charge | | 1 | 20 | 55 | | 2 | 15 | 57 | | 3 | 42 | 121 | If so I think we could achieve this easily in reporting services, just put a table control on the report with three columns, then drag these three datafields (Order/Haulage Cost/Total Charge) to the relevant columns, then right-click the details in Row Groups pane at the left-bottom corner, select Group Properties...button to add a group to group on Order# field. To add a total row at the bottom of the table follow these: >>Step one : Add custom code in report. 1. Select Report on the top of the menu bar, select Report Properties. 2. Select “Code” tab on the left pane in the Report Properties dialog box. 3. Type below codes in the Custom code text box on the right side dim sum as integer=0 public function add(item as integer) as integer sum=sum+item return item end function public function getSum() as integer getSum=sum end function >>Step two: Call this function 1. Right-click the detail row and then select Insert Row, then move to OutSide Group-Below tab. 2. In the second column of the detail row, type in below expression =code.add(Fields!Haulage Cost .Value) 3. Select Total textbox in second column and then type in the expression =code.getSum() 4. Do the same to the third column After these steps, I think you could get the requirement. If I misunderstand you, please feel free to let me know. thanks, Challen Fu Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
April 11th, 2011 5:49am

Thanks for your replys. I used the the following solution... or you add a group at your report using the order #, hide the details and use a max(Haulage Cost) and sum(Haulage Charged). | Order # | Haulage Cost | Haulage Charged| | 1 | max(=20) | sum(=55 ) | grouped row by order # | 1 | 20 | 30 | detail row hide | 1 | 20 | 15 | detail row hide | 1 | 20 | 10 | detail row hide This worked well for me and also allowed me create a 'profit / loss' column. Challen Fu, I also tried out your solution but for some reason I couldn't get the code to work - the expression wouldn't recognise either of the function names(???) Thankyou both for your input.
April 11th, 2011 11:40am

Hi Steve_Flynn, Solving the issue is the most important, you are welcomed if you have any reporting services issue next time.Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
April 11th, 2011 9:38pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics