Need help in calculation using two Datasets using Expression SSRS

Question

I am creating an SSRS report where

In Dataset15, I have value Jan - 100 & Feb - 110

in Dataset16, I have value Jan - 80 & Feb - 100

Now I want to calculate same thing in a line chart using expression - Jan - 80/100 which should be 80% & Feb - 100/110 - 91%

When I am trying to find out individual monthly number 100, 110 I am getting 210 which is the summation of Both - Sum(Fields!Total.Value, "DataSet15").

Kindly help me out how can I get individual Numbers.

Answer 1

You would need to restrict your dataset to the desired month and then sum the results.

LookupSet is used to retrieve data from another dataset based on criteria.

A VBA function, SumLookup is needed to add the results from the LookUp. This VB would go into the CODE section of the report (this can be seen under 'Report Properties').

Function SumLookup(ByVal items As Object()) As Decimal

If items Is Nothing Then Return Nothing

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 

You would then use the function in your expression like:

=Code.SumLookup(LookupSet(Fields!MONTH.Value, Fields!MONTH.Value, Fields!Total.Value,"Dataset16"))

If your field is a date, then you'd need to convert both to a MMyyyy field with FORMAT:

FORMAT(Fields!MONTH.Value, "MMyyyy")