Hello, Does InScope function work inside aggregate function? I am using Matrix control and I want to use following expression for DATA cell. =SUM(Code.GetMeValue(InScope("matrix1_RowScope"), InScope("matrix1_ColumnScope"), Fields!ProductSalesID.Value, Fields!Sales.Value)) Function GetMeValue is written in Code block of report which gives me proper value from hashtable according to the scope. This GetMeValue takes four arguments. First and Second arguments are boolean and to decide the scope. But what I observed is, InScope always returns false If I use it inside Sum aggregate. Can anyone please throw light on this? Regards,

Hi sisvis Allscripts, According to your description, I have tried to use InScope function in Sum aggregation, and also found that the InScope always returns false. I suspect that the InScope function maybe doesn’t work when is contained in Sum aggregation. I suggest you try to use Scope function outside the Sum aggregate like following expression: =Iif(Inscope("matrix1_Col"),Iif(Inscope("matrix1_Row"),sum(fields!value.Value),0),0) There is a article about InScope function like following, you can take it as a reference: Robert Bruckner's Advanced Reporting Services Blog: http://blogs.msdn.com/b/robertbruckner/archive/2009/08/10/tablix-multiple-subtotals-are-easy-aka-goodbye-inscope.aspx If you have any question, please feel free to ask. Thanks, Eileen

Hello Eileen, First of all we tried InScope with IIF as you have suggested. But our actual problem is that we have repeated data so we create effect of SumDistinct (which is not present in SSRS) by using Hashtable. But IIF always executes both parts true and false. And because of that if condition is true then also False part is also executed and entry goes to wrong hash table which causes wrong total calculation. i.e. Suppose I write expression like this, =IIF(InScope(scope), call function GetValue for hashtable1, call function GetValue for hashtable2) Now what GetValue does is, it will check value is present or not in given hashtable and if not present it will add it to given hashtabe and will return value. But if value is present it will return Nothing (null). But IIF always executes true part and false part so here if condition InScope(scope) is true then also value is added to hashtable 1 as well as hashtable2. Which in turn gives us wrong totals. So to avoid we tried to use If which executes either true part or false part on the basis of condition evaluation. And to use If we used code block and we called code block from inside aggregate SUM. And to decide region we used InScope inside Aggregate SUM. Regards, Vishal.Vishal

Hi sisvis Allscripts, IIF function do executes both parts true and false, I suggest you to use switch function instead of IIF like below, =Switch(InScope(“matrix1_Row”), call function GetValue for hashtable1,InScope(“matrix1_Col”), call function GetValue for hashtable2) More details about the switch function, you can see the following article, Expression Examples (Report Builder 3.0 and SSRS): http://msdn.microsoft.com/en-us/library/ms157328.aspx If you have any question, please feel free to ask. Thanks, Eileen

Hello Eileen, We tried Switch after IIF. But we observed the same thing with Switch also. It also executes things for all the conditions. Switch(condition1,action1, condition2,action2, condition3,action3). But here Switch executes all actions ie. action1, action2 and action3 irrespective of which condition is true. So Switch also gave us same improper results as IIF. Regards, Vishal

Hi sisvis Allscripts, Could you please try to use switch like following, and then check if it works for you, =Switch(InScope(“matrix1_Row”),call function GetValue for hashtable1,InScope(“matrix1_Col”)= false, call function GetValue for hashtable2) If the issue still exists, could you please supply more details about the issue, and then we could help you better. Thanks, Eileen

Hi Eileen, What I found on some posts is, IIF and Switch in SSRS are functions. And conditions and actions are passed as an argument. And function arguments are always evaluated first before passing to the function so they will always be executed bofore condition will be checked. Regards, VishalVishal

Hi sisvis Allscripts, If possible, could you please supply the custom code you are using in your report. And then, we can understand all of your requirement, through that we can see if could use other methods to reach your goal. Thanks Eileen

Hello Eileen, Here is my complete problem, We have repititive data due to inner join so internally we are using hash table for getting effect of SumDistinct. Code for the same is as below, Dim distinctScope As System.Collections.Hashtable Public Function DistinctValueProvider(ByVal scope As String, ByVal distinctId As Object, ByVal value As Object) Dim distinctIds As System.Collections.Hashtable DistinctValueProvider = Nothing If (distinctScope Is Nothing) Then distinctScope = New System.Collections.Hashtable End If If (Not distinctScope.Contains(scope)) Then distinctIds = New System.Collections.Hashtable distinctScope.Add(scope, distinctIds) Else distinctIds = distinctScope.Item(scope) End If If (Not distinctIds.Contains(distinctId)) Then DistinctValueProvider = value distinctIds.Add(distinctId, value) End If End Function For matrix, we tried to use following expression for Data cell, =IIF(InScope("matrix1_Name"), IIF(InScope("matrix1_Region"),Sum(Code.DistinctValueProvider("RowColumnBoth",Fields!ProductSalesID.Value,Fields!Sales.Value)) ,Sum(Code.DistinctValueProvider("RowOnly",Fields!ProductSalesID.Value,Fields!Sales.Value))), IIF(InScope("matrix1_Region"),Sum(Code.DistinctValueProvider("ColumnOnly",Fields!ProductSalesID.Value,Fields!Sales.Value)) ,SUM(Code.DistinctValueProvider("OutOfRowAndColumnBoth",Fields!ProductSalesID.Value,Fields!Sales.Value)))) Here matrix1_Name is the name of our row grouping and matrix1_Region is the name of our column grouping. DistinctValue function takes Scope as first argument, ID (from which uniqueness will be identified) as second argument and Value (which is to be aggregated) as third argument. Now only problem is that IIF will call DistinctValueProvider function four times regardless of which condition is true and which is false. So 3 out of 4 hash tables are having entries though condition is false for them and that is why when actually entry should be added to hash table we find that entry is already there and we get Nothing from the DistinctValueProvider function so SUM aggregate gets Nothing to add and total remains 0 for cells in the scope RowColumnBoth, RowOnly and ColumnOnly. Only the cell which is outside of both row and column scope (RightBottom corner cell) gets the proper value (Because I believe that SSRS first evaluates that scope for each record). All other cells displays Nothing(blank). I hope I could explain my problem. Regards,Vishal

Hello Eileen, Here is my complete problem, We have repititive data due to inner join so internally we are using hash table for getting effect of SumDistinct. Code for the same is as below, Dim distinctScope As System.Collections.Hashtable Public Function DistinctValueProvider(ByVal scope As String, ByVal distinctId As Object, ByVal value As Object) Dim distinctIds As System.Collections.Hashtable DistinctValueProvider = Nothing If (distinctScope Is Nothing) Then distinctScope = New System.Collections.Hashtable End If If (Not distinctScope.Contains(scope)) Then distinctIds = New System.Collections.Hashtable distinctScope.Add(scope, distinctIds) Else distinctIds = distinctScope.Item(scope) End If If (Not distinctIds.Contains(distinctId)) Then DistinctValueProvider = value distinctIds.Add(distinctId, value) End If End Function For matrix, we tried to use following expression for Data cell, =IIF(InScope("matrix1_Name"), IIF(InScope("matrix1_Region"),Sum(Code.DistinctValueProvider("RowColumnBoth",Fields!ProductSalesID.Value,Fields!Sales.Value)) ,Sum(Code.DistinctValueProvider("RowOnly",Fields!ProductSalesID.Value,Fields!Sales.Value))), IIF(InScope("matrix1_Region"),Sum(Code.DistinctValueProvider("ColumnOnly",Fields!ProductSalesID.Value,Fields!Sales.Value)) ,SUM(Code.DistinctValueProvider("OutOfRowAndColumnBoth",Fields!ProductSalesID.Value,Fields!Sales.Value)))) Here matrix1_Name is the name of our row grouping and matrix1_Region is the name of our column grouping. DistinctValue function takes Scope as first argument, ID (from which uniqueness will be identified) as second argument and Value (which is to be aggregated) as third argument. Now only problem is that IIF will call DistinctValueProvider function four times regardless of which condition is true and which is false. So 3 out of 4 hash tables are having wrong entries because condition is false for them and that is why when actually entry should be added to hash table we find that entry is already there and we get Nothing from the DistinctValueProvider function so SUM aggregate gets Nothing to add and total remains 0 for cells in the scope RowColumnBoth, RowOnly and ColumnOnly. Only the cell which is outside of both row and column scope (RightBottom corner cell) gets the proper value (Because I believe that SSRS first evaluates that scope for each record). All other cells displays Nothing(blank). I hope I could explain my problem. Regards, Vishal

Hi sisvis Allscripts, Thanks for the clarification. According to your description, I suspect that you would like to show values in four different zones like below: 1: The matrix cell is within the scope of both: row scope and column scope. 2: The cell is within the scope of the row, but outside of the column scope. 3: The cell is within the column scope, but outside of the row scope. 4: The subtotal cell is outside both grouping scopes. Actually, we would define the expression in the matrix cell using the following general pattern instead of using custom code: =iif(InScope("RowGroup"), iif(InScope("ColumnGroup"), "In Matrix Cell (1)", "In Subtotal of RowGroup (2)"), iif(InScope("ColumnGroup"), "In Subtotal of ColumnGroup (3)", "In Subtotal of entire Matrix (4)")) So now I would like to confirm with you what values you want to show in the four different zones of your matrix, you could give some logic explanation to these four zones, after getting the requirement, we could help you with the expression further. If I misunderstand, please feel free to let me know. Thanks, Eileen

Hello Eileen, Sorry for my late reply. Our team has decided to move to SSRS 2008. And as far as I know in 2008 in Matrix (Tablix) it is possible to add more column groups and row groups at same level. So I think in 2008 we can achieve the behavior we are searching for. We have not yet given a try to 2008. If we get any problem in 2008 we will post the query. Thank you for your help. Regards, Vishal