Report Builder - Total Expressions

1
votes

I have a report that displays the following data:

  • Quantity
  • Line Item Total/Extended Price (this is based off of a formula of rate * quantity)
  • Product Type (i.e. Type A, B, C, D, & Other)

At the bottom of the report, I would like to add a small three column table that takes the total quantity and price by the product type.

The table will only ever have 5 rows - row 1 = Type A, etc. Columns will be "Qty" and "Total" which should represent the sum of each based on the type.

I am struggling getting a RunningValue formula to work with a IIF statement that would filter based on type.

Here is what I have tried in cell A1 of the table (to display qty total for type a only):

=IIf(Fields!type.Value="A",RunningValue(Fields!quantity.Value, Sum, Nothing)
1
reporting-services
Still stumped on this, any help would be great. Thanks! - d0tnetdude

1 Answers

0
votes

To achieve this it would be best to use a Matrix, as follows

  1. Create a matrix and set the row grouping to be your Type Field.

  2. Right click the column header and select Insert Column – Inside Group Left to give you two columns to display the Quantity and Total values.

  3. In the header you can right click the area and selected Insert -> Rectangle, then added two text boxes to give the column headings (in blue)

  4. In the left Dataset set the expression to be 

    =sum(CInt(Fields!Qty.Value))

    To sum all the values for this Type together (Note this assumes the are integers, you can use CDbl for doubles for example)

  5. Do the same for the Totals text box as well (note I have set the expression to return a currency as well – Right Cick -> Palaceholder Properties -> Number -> Currency)

  6. This matrix looks as follows in design mode

    enter image description here

    And like this when rendered (note the source data I am using to generate this is also shown)

    enter image description here

Is this the sort of output you required? Let me know if I can help further.