Hi There Thanks for your posting. Can you please try report item collection in order to get those values =reportitems!TotalValuefortable1.value+reportitems!TotalValuefortable2.value Check the name of your textboxes where you are doing aggregation in table1 and table2 and please replace in above expression If you have any questions please do ask Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi There Thanks for your posting. Can you please try report item collection in order to get those values =reportitems!TotalValuefortable1.value+reportitems!TotalValuefortable2.value Check the name of your textboxes where you are doing aggregation in table1 and table2 and please replace in above expression If you have any questions please do ask Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Im trying to come up with a working aggregate function for the scenario below. I dont know what code to use to get the values where there are question marks in Tablix3 below. I would appreciate help please. I have supplied examples of codes that I have used in in Tablix1 and Tablix2. It is Tablix3 which is an issue. Tablix1 Arrears Band Number Current Balance 0 30 =Sum(Fields!Number.Value) 1,000.00 =Sum(Fields!Current_Balance.Value) 1 15 300,000.00 2 56 50,000.00 101 =Sum(Fields!Number.Value, "Tablix1") 351,000.00 =Sum(Fields!Current_Balance.Value, "Tablix1") Tablix2 Arrears Band Number Current Balance 0 20 =Sum(Fields!Number.Value) 2,000.00 =Sum(Fields!Current_Balance.Value) 1 5 70,000.00 2 35 250,000.00 60 =Sum(Fields!Number.Value, "Tablix2") 322,000.00 =Sum(Fields!Current_Balance.Value, "Tablix2") Tablix3 (Sum of Tablix1&Tablix2) Arrears Band Number Current Balance 0 50 ??????????????? 2,000.00 ????????????? 1 20 70,000.00 2 91 250,000.00 161 ???????????????? 322,000.00 ????????????? Many thanks. Mpumelelo

Hi There Thanks for your posting. Can you please try report item collection in order to get those values =reportitems!TotalValuefortable1.value+reportitems!TotalValuefortable2.value Check the name of your textboxes where you are doing aggregation in table1 and table2 and please replace in above expression If you have any questions please do ask Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi Syed Thank you for your response. Im pretty new to this. You have given me a suggestion which Im failing to apply. I have further questions in addition to the one that I asked earlier. My setup is such that I have a separate dataset for each tablix. Does that affect the reportitems! function?Should I create one dataset for all the tablixes. Its only that Im getting the following error when I try to run my report. [rsReportItemReference] The Value expression for the text box Textbox87 refers to the report item Number2. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.Mpumelelo

Hi Syed Thank you for your response. Im pretty new to this. You have given me a suggestion which Im failing to apply. I have further questions in addition to the one that I asked earlier. My setup is such that I have a separate dataset for each tablix. Does that affect the reportitems! function?Should I create one dataset for all the tablixes. Its only that Im getting the following error when I try to run my report. [rsReportItemReference] The Value expression for the text box Textbox87 refers to the report item Number2. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.Mpumelelo

Hi Mpumelelo, From your description, it seems that you are using SQL Server 2008 or a later version. If you are using SQL Server 2008 R2 or a later version, you can use the lookup function to retrieve the value from the specified dataset for a name-value pair where there is a 1-to-1 relationship. Since the aggregate function cannot be used in a lookup function, we need to rewrite the query of the datasets that map to Tablix1 and Tablix2 to get the summary of Current_Balance field. The query may looks like: SELECT Arres_Band, SUM(Number) AS Number, SUM(Current_Balance) AS Current_Balance FROM ( SELECT TOP (100) PERCENT Arres_Band, Sum(Number) AS Number, SUM(Current_Balance) AS Current_Balance FROM ) AS tab_1 GROUP BY Arres_Band ORDER BY Arres_Band Then, you can use the Lookup function in the expressions to get the summary value in Tablix3. The expression in the Tablix3 should be like: =Lookup(Fields!Arres_Band.Value, Fields!Arres_Band.Value, Fields!Number, DataSet1)+ Lookup(Fields!Arres_Band.Value, Fields!Arres_Band.Value, Fields!Number.Value, DataSet2) If you are using SQL Server 2008, the lookup function is not provided. In this condition, you can write a new query to join those two datasets and get the corresponding calculated column. Reference: Lookup Function Hope this helps. Regards, Mike Yin

Hi Mpumelelo, From your description, it seems that you are using SQL Server 2008 or a later version. If you are using SQL Server 2008 R2 or a later version, you can use the lookup function to retrieve the value from the specified dataset for a name-value pair where there is a 1-to-1 relationship. Since the aggregate function cannot be used in a lookup function, we need to rewrite the query of the datasets that map to Tablix1 and Tablix2 to get the summary of Current_Balance field. The query may looks like: SELECT Arres_Band, SUM(Number) AS Number, SUM(Current_Balance) AS Current_Balance FROM ( SELECT TOP (100) PERCENT Arres_Band, Sum(Number) AS Number, SUM(Current_Balance) AS Current_Balance FROM ) AS tab_1 GROUP BY Arres_Band ORDER BY Arres_Band Then, you can use the Lookup function in the expressions to get the summary value in Tablix3. The expression in the Tablix3 should be like: =Lookup(Fields!Arres_Band.Value, Fields!Arres_Band.Value, Fields!Number, DataSet1)+ Lookup(Fields!Arres_Band.Value, Fields!Arres_Band.Value, Fields!Number.Value, DataSet2) If you are using SQL Server 2008, the lookup function is not provided. In this condition, you can write a new query to join those two datasets and get the corresponding calculated column. Reference: Lookup Function Hope this helps. Regards, Mike Yin

Thank you Mike. Im using SQL Server 2008. You have suggested that if Im using SQL Sever 2008 I should write a new query to join those two datasets. Is it possible to provide me with a sample query for that please as Im out of ideas. Many thanks.Mpumelelo

Hi There Thanks for your posting again, Let me try in more simple way in you current datasets please do the following SELECT Arres_Band ,Number ,Current_Balance ,sum(Number)OVER(PARTITION BY Arres_Band) as Total_Number_1 ,sum(Current_Balance)OVER(PARTITION BY Arres_Band) as Total_Current_Balance_1 FROM [dbo].[ChartExample] You have to do this for second data set also SELECT Arres_Band ,Number ,Current_Balance ,sum(Number)OVER(PARTITION BY Arres_Band) as Total_Number_2 ,sum(Current_Balance)OVER(PARTITION BY Arres_Band) as Total_Current_Balance_2 FROM [dbo].[ChartExample] Now in your third dataset you have to put the expression for sum look like this one =lookup(Fields!Arres_Band.Value,Fields!Arres_Band.Value,Fields!Total_Current_Balance_1.Value,"DataSet1")+ lookup(Fields!Arres_Band.Value,Fields!Arres_Band.Value,Fields!Total_Current_Balance_2.Value,"DataSet2") You need to do this for number as well I am putting ascreenshot for you help I hope this will help If you have any question please do ask Many thanks Syed Qazafi Anjum

Hi There Thanks for your posting again, Let me try in more simple way in you current datasets please do the following SELECT Arres_Band ,Number ,Current_Balance ,sum(Number)OVER(PARTITION BY Arres_Band) as Total_Number_1 ,sum(Current_Balance)OVER(PARTITION BY Arres_Band) as Total_Current_Balance_1 FROM [dbo].[ChartExample] You have to do this for second data set also SELECT Arres_Band ,Number ,Current_Balance ,sum(Number)OVER(PARTITION BY Arres_Band) as Total_Number_2 ,sum(Current_Balance)OVER(PARTITION BY Arres_Band) as Total_Current_Balance_2 FROM [dbo].[ChartExample] Now in your third dataset you have to put the expression for sum look like this one =lookup(Fields!Arres_Band.Value,Fields!Arres_Band.Value,Fields!Total_Current_Balance_1.Value,"DataSet1")+ lookup(Fields!Arres_Band.Value,Fields!Arres_Band.Value,Fields!Total_Current_Balance_2.Value,"DataSet2") You need to do this for number as well I am putting ascreenshot for you help I hope this will help If you have any question please do ask Many thanks Syed Qazafi Anjum