Oleg Oleg - 5 months ago 28
SQL Question

How using Lookup in SSRS get the ratio based o Year and Month from different datasets

I have two tables in SSRS 2010 from two different datasets.How can I calculate ratios based on values for each year and month? In excel its pretty easy, one cell divide by another cell. But how achieve this in SSRS?
enter image description here

Trying to use lookup function but it doesnt go very well so far.

Tables are in a group in SSRS
enter image description here

Answer

Add a third matrix to the report, make the Dataset for the that matrix match the name of the dataset you used for the second matrix. Or, you can simply copy and paste the second matrix, and move it below the others. Then use this expression to do the calculations.

=Fields!PaidLoss.Value / Lookup(Fields!AccidentYear.Value & Fields!AccidentMonth.Value, Fields!YearStartRisk.Value & Fields!MonthStartRisk.Value, Fields!EarnedPr.Value, "DataSet1")

You will have to adjust the field names since I can’t see all of then names in the screen cap. Adjust the dataset name as well. Format the number to show as a percentage. (0%)

You are concatenating the year and month to get a proper lookup here. That should make it work. You may have to play around with this is it doesn’t work right the first time.

Good luck!