SSRS IIF Expression across Multiple DataSets

Question

I'm trying to do an IIF expression on a 2nd dataset to sum the 'BookingsComfirmed2016LASTWEEK' column and then divide it by the sum of the 'Stock2016Week' column in the dataset I'm in and where the PropertyTypeCategory = Cottage, but with no joy. I'm sure it's something to do with the placement of the 2nd dataset name, but would appreciate any help. Regards Claire

Dataset1 = TradingLastWeekandYTD Dataset2 = TradinglastWeekandYTDSTOCK

=(IIF(Fields!PropertyTypeCategory.Value, "TradingLastWeekandYTD" = "Cottage",Sum(Fields!BookingsConfirmed2016LASTWEEK.Value, "TradingLastWeekandYTD")) /(IIF(Fields!PropertyTypeCategory.Value = "Cottage", Sum(Fields!Stock2016Week.Value)),0)

Answer 1

Your iif() won't work like this.

You can't check row by row in a dataset you're not currently working in, which is what you are trying to do with the first part of the iif().

You can use custom code to do an aggregated lookupset() to get the values of the first part.

This link https://itsalocke.com/aggregate-on-a-lookup-in-ssrs/ will help you do the custom code.

For the lookupset(), you would have to do something like..

=Code.SumLookup(lookupset(Fields!PropertyTypeCategory.Value, "Cottage", Fields!BookingsConfirmed2016LASTWEEK.Value))

This assumes that your custom code function is called "SumLookup". It will sum all the values that the lookupset() returns.