Using Lookup and SUM within Matrix Report using SSRS 2016

0
votes

Using SSRS 2016 and have inserted a Matrix Report table show / calculate the monthly rate and the total. The monthly rate derived by a LookUp field which looks up the PoolCar and fetches the Daily_Cost from a different DataSet and then multiplied by the Days value field:

= Lookup(Fields!PoolCar.Value, Fields!Registration.Value, Fields!Daily_Cost.Value, "PoolCar_Costs") * Sum(Fields!Days.Value)

However, calculating the sum() of that column does not work.
Any ideas?

I tried to use the Variable option but it does not allow aggregations.

Update:

enter image description here

2
reporting-servicesssrs-2016
I'm assuming that you have testing the lookup function by itself to make sure it returns the expected value? as well the sum by itself in the same section of the report? - Pants
If the answer to the above is yes and they just don't work together, are you able to modify the sql query of the report? - Pants
@Pants answer is yes, the Lookup and the Sum() are on same report. However the DailyRate is from a different DataSet hence why I used the lookup function ... which fetches the values and calculates Days * DailyRate but now how do I sum-up to show total money spend :) - Bucki
@Pants the SSRS fetches the info from the sharepoint list and the query type is <RSSharePointList xmln:xsi="...."> - Bucki
Also, adding a new Field (via Dataset Properties, does not work because it does not allow aggregates as an expression. "The expression used for the calculated field 'MonthlyCost' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions." - Bucki

2 Answers

1
votes

Edited (See code you call in the expressions(Detials Section)):

You can do this many ways. Either do the sum in the query phrase builder...

SQL:

SELECT T1.*,
(SELECT Sum(S1.Daily_Cost) FROM YourTableName S1 WHERE S1.Registrations = T1.PoolCar) AS SumYouWantToCalculateBeforeTheReport
FROM YourTable T1

...or you can do this with custom code.

This solution requires your tablix to have a details section, where you pass the value of the field you want to sum every detail row, then you display/use the sum in the footer section with your calculation

Code you call in the expression(Details Section)

=Code.ValueIWantToSum(Lookup(Fields!PoolCar.Value, Fields!Registration.Value, Fields!Daily_Cost.Value, "PoolCar_Costs"))

Code you call in the expression once the details section is done(Group Footer) 

=Code.DailyCostSum()

Custom Code

Private runningtotal As Decimal = 0

Function ValueIWantToSum(svalue As Decimal) as String

    runningtotal = runningtotal + svalue
    Return "added"

End Function


Private DailyCostSum() As Decimal
    Return runningtotal
End Function
0
votes

After endless hours, I came across this website where it explains in more details.
The important bit, which I figured out at the end, is that the column where the expressions are used, should be outside the grouping!

The following code has been added to the Code section in Report Properties. 

Public Shared Value as Double=0
  Public Shared Function GetValue(Item as Double) as Double
     value= value + Item
     return Item
  End Function
  Public Shared Function GetTotal()
     return value
  End Function

The Data Row has the following expression:

=Code.GetValue( Sum(Fields!Days.Value) * Lookup(Fields!PoolCar.Value, Fields!Registration.Value, Fields!Daily_Cost.Value, "PoolCar_Costs") )

Whilst the total row has got the following expression:

=Code.GetTotal()