Hi all, What seems to me to be a simple thing is making me think about training to be a bus driver. I just want to display the sum of a field based on the value of another field. So I have this in a cell on a table: =Sum(iif(Fields!Revenue_Group.Value = "Sale - Discount",Fields!TY_Sales_Revenue_NET.Value,0),"ds_Report") I get the error : "[rsFieldReference] The Value expression for the textbox ‘textbox31’ refers to the field ‘TY_Sales_Revenue_NET’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope." The "ds_Report" is the name of the dataset that holds all the fields I am using. The source is an SSAS cube. Any ideas ? Everything I have seen on the web indicates this should work.

lucky, First check whether the table you are using is set to ds_report scope (check the dataset name in properties of tablix and set it to ds_report) Note: wherever you are using this formula the cell must be a part of this tablix, and there should not be any row groups. And then try using this, =Sum( iif(Fields!Revenue_Group.Value = "Sale - Discount",Fields!TY_Sales_Revenue_NET.Value,0) )

HI Lucky, =Sum( iif(Fields!Revenue_Group.Value = "Sale - Discount",Fields!TY_Sales_Revenue_NET.Value,0) ) this should work . Please do as Manoj advised above,go through the scope of textbox and retry. Hope would work. Amit

Thanks all - will give them a try as soon as we have sorted out a stock take fubar.

I am in 2005 and do not have a Tablix control afraid. Amit :- I had tried the code you have above but the error message comes back: [rsMissingAggregateScope] The Value expression for the textbox ‘textbox9’ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set. And if I stick ds_report as the dataset I get exactly the same message. Any ideas ? BTW Where do I set the scope of the TextBox ? If I drag a field from the dataset to the body the code looks like this: =Sum(Fields!TY_Sales_Revenue_NET_VAT.Value, "ds_Report") - which works. So I would assume my code would also work.

Hi, Based on the posts above, I found that one field name in expression =Sum(iif(Fields!Revenue_Group.Value="Sale-Discount",Fields!TY_Sales_Revenue_NET.Value,0),"ds_Report") is Fields!TY_Sales_Revenue_NET. However, in the expression =Sum(Fields!TY_Sales_Revenue_NET_VAT.Value, "ds_Report") which generated by Report Designer, the fields name is Fields!TY_Sales_Revenue_NET_VAT. So, if they indicate a same field, please correct the original expression to =Sum(iif(Fields!Revenue_Group.Value="Sale-Discount",Fields!TY_Sales_Revenue_NET_VAT.Value,0),"ds_Report") Thanks, Tony Chain Please remember to mark the replies as answers if they help and unmark them if they provide no help

Hi, Based on the posts above, I found that one field name in expression =Sum(iif(Fields!Revenue_Group.Value="Sale-Discount",Fields!TY_Sales_Revenue_NET.Value,0),"ds_Report") is Fields!TY_Sales_Revenue_NET. However, in the expression =Sum(Fields!TY_Sales_Revenue_NET_VAT.Value, "ds_Report") which generated by Report Designer, the fields name is Fields!TY_Sales_Revenue_NET_VAT. So, if they indicate a same field, please correct the original expression to =Sum(iif(Fields!Revenue_Group.Value="Sale-Discount",Fields!TY_Sales_Revenue_NET_VAT.Value,0),"ds_Report") Thanks, Tony Chain Please remember to mark the replies as answers if they help and unmark them if they provide no help

Thanks Tony - it was indeed a typo. Why I cannot select the report fields from the (crappy) IDE is beyond me. And why can't I just set the dataset property of the text box if it so bl**dy important. Anyway. Right, now I am getting this error: The Value expression for the textbox ‘textbox12’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type. Again here is my code: =Sum(iif(Fields!Revenue_Group.Value = "Sale - Discount",Fields!TY_Sales_Revenue_NET_VAT.Value,0),"ds_Report") It's as though the TY_Sales_Revenue_NET_VAT field has non numeric data. I assume SSRS handles null values as my matrix manages to sum the values ok. Thanks for all help on this.

Hi, In order to make data types consistent in the aggregate function, we can convert the data to the same data type by using conversion function. For example, to convert to double, please modify the expression to =Sum(iif(Fields!Revenue_Group.Value = "Sale - Discount", CDdl(Fields!TY_Sales_Revenue_NET_VAT.Value), CDbl(0)), "ds_Report") Thanks, Tony ChainPlease remember to mark the replies as answers if they help and unmark them if they provide no help

Tony - you have reaffirmed my faith in man. Cannot believe I did not try that. Woods for trees and all that. Worked perfectly. Thank you. I think I was thrown by the Matrix conrtol doing a similar thing with no problem. I guess the textbox needs more attention. Again thank you.