0

I have 2 datasets that I need to populate one table. Dataset 1 contains values that Dataset 2 may not contain. Dataset 2 contains multiple values that I need to get the Sum of. Dataset 2 will always have a matching value (Division_Code) that is contained in Dataset 1.

I need the full list of data from DataSet1, even if no data exists in Dataset2.

Dataset 1 fields: Division_Code, Long_Name

Dataset 2 fields: Division_Code, TotalBillable, Date

i.e. Dataset 1:

Division_Code Long_Name
01 Health
02 Safety
03 Finance
04 Tax

i.e. Dataset 2:

Division_Code TotalBillable Date
01 $200 06/01/2022
01 $100 06/08/2022
01 $200 06/12/2022
02 $800 06/01/2022
04 $100 06/05/2022

I need results like this:

Division_Code Long_Name Sum(TotalBillable)
01 Health $500
02 Safety $800
03 Finance 0
04 Tax $100

I have tried a variety of Lookups and SumLookup expressions and all result in errors. Can anyone offer guidance on how to write an expression that would accomplish what I need? Thanks in advance.

Stephanie
  • 1
  • 1
  • Assuming tat you cannot do this directly in your dataset queries (by far the easiest way) then take a look at this blog post https://salvoz.com/posts/2013-05-27-sum-result-of-ssrs-lookupset-function.html – Alan Schofield Jul 13 '22 at 16:13
  • If you're using a SumLookup like https://stackoverflow.com/questions/36131860/need-help-in-calculation-using-two-datasets-using-expression-ssrs/36143175#36143175 , you're expression would be `=Code.SumLookup(LookupSet(Fields!Division_Code.Value, Fields!Division_Code.Value, Fields!TotalBillable.Value,"Dataset2"))`. Your TotalBillable isn't a text field with the dollar sign, is it? – Hannover Fist Jul 13 '22 at 16:41
  • @AlanSchofield I haven't figured out a way to do it directly in my dataset because dataset2 does not contain the complete list of division_code fields since not all division_codes have billable amounts. I need the list of division_codes in its entirety, regardless if data exists in our billing tables – Stephanie Jul 13 '22 at 17:33
  • @HannoverFist this is what I tried initially. The TotalBillable is an INT field with no dollar sign (I'll just format the cell that way). When I use this expression though, I get an error for the TotalBillable field "Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope" – Stephanie Jul 13 '22 at 17:38
  • The error seems to indicate that a field or dataset name is incorrect. Are you sure they are all correct? – Hannover Fist Jul 13 '22 at 20:19
  • From your earlier comment, you said dataset2 does not have a complete set of divisions. To get around this, how about you drive your query from division I.E. select div.code, div.name, isnull(sum(billing.amount),0) bil_total from division div left join billing on div.code = billing.div_code group by div.code, div.name This way, you are always guaranteed all divisions all the time – Harry Jul 13 '22 at 20:46

1 Answers1

0

Use this Custom Code in Report Builder Properties:

Function SumLookup(ByVal items As Object()) As Decimal  
If items Is Nothing Then  
Return Nothing  
End If  
Dim suma As Decimal = New Decimal()  
Dim ct as Integer = New Integer()  
suma = 0      ct = 0  
For Each item As Object In items  
suma += Convert.ToDecimal(item)  
ct += 1  
Next  
If (ct = 0) Then return 0 else return suma   
End Function

Then use the following expression in your report field

=Code.SumLookup(LookupSet(Fields![LookupDataset1FieldName].Value, Fields![LookupDataset2FieldName].Value, Fields![ResultField].Value, "DataSet2"))

Stephanie
  • 1
  • 1