title: SSRS - Sum of Lookup Values within a group
body: <p>I'm attempting to create a report which will pull data from multiple inventory databases, and based on the item counts in the various systems provide a Cost Estimate from another data source.  The report has two levels of grouping, and I need to sum the cost values within each group only (not a total for all groups).</p>
  
  <p>As an illustration, my result set looks like this </p>
  
  <pre><code>MODEL  |COMPONENT   |Sys1   |Sys2   |Sys3   |Sys1ID |Match  |Cost
  =====================================================================
  Car    |            |       |       |       |       |       |620 &lt;--- Sum of component costs
         |Wheel       |4      |8      |10     |       |Sys1   |40
                                              |wheel1
                                              |wheel2
                                              |wheel3
         |Brakes      |0      |9      |11     |       |Sys2   |80
         |Horn        |0  |0  |50 |   |Sys3   |500
  ---------------------------------------------------------------------
  Truck  |            |       |       |       |       |       |980 &lt;--- Sum of component costs
         |Wheel       |0      |0      |10     |       |Sys3   |400
         |Brakes      |0      |9      |11     |       |Sys2   |80
         |Horn        |0      |0      |50     |       |Sys3   |500
  </code></pre>
  
  <p>The Table shows the Unique ID for the any matching items in Sys1, and the Sys1 column contains an COUNT() aggregate for these items.  The Sys2 and Sys3 return material counts based on a lookup of Model+Component in two other datasources.</p>
  
  <p>The Match column, indicates which inventory to service from, in order of priority, based on whether assets exist (Sys1, then Sys2, then Sys3).  Finally, the Cost column is populated based on a lookup to a fourth dataset, which is formatted as :</p>
  
  <pre>
  MODEL   |COMPONENT  |System |Cost
  =====================================
  Car |Wheel      |Sys1   |40
  Car |Wheel      |Sys2   |50
  Car |Wheel      |Sys3   |300
  Car |Brakes     |Sys1   |60
  Car |Brakes     |Sys2   |80
  Car |Brakes     |Sys3   |900
  ...
  </pre>
  
  <p>Everything is set up and working, apart from the Sum of Costs within the group.  Does anyone know how to resolve this?</p>
  
  <p>For reference, my original attempt to sum these used this expression :</p>
  
  <p>=SUM(ReportItem!Estimate_Cost.Value)</p>
  
  <p>which yields the following error message :</p>
  
  <pre><code>The Value expression for the textrun 'Textbox39.Paragraphs[0].TextRuns[0]' 
  uses an aggregate function on a report item.  Aggregate functions can be used 
  only on report items contained in page headers and footers.
  </code></pre>