I have two datasets and the only way I can join them causes the first set to duplicate on date. I need to sum the first set of data but only on change of date eg 31/07/2012 207.31 31/07/2012 207.31 03/09/2012 177.31 03/09/2012 177.31 I want to see a total of 384.62 How do I do that please Thank you Ian

Hi There Thanks for your posting. I dont know why you are duplicating your records inside your dataset. Please have a look on this thread that might help you for sum the distinct values http://personalinertia.blogspot.co.nz/2008/09/countdistinct-how-about-sumdistinct-or.html Many thanks Syed Qazafi Anjum

Hello Ian, In this case, you can use runningvalue ( expression > Common functions > Aggregate > Running value) predefined function with scope as Date value.. so that it'll add as per your requirement so then no need of duplicating data. sample expression : =RunningValue(Fields!InvoiceAmount1.Value,Sum,"GroupByDate") 'GroupByDate' is the groupname of Date group created under the tablix control Cheers Arun Gangumalla

Syed Thanks for this but I can not get it to work. I create an expression using the new function report runs but I just get error. My data looks like this PMC1000053284FC 123 3 02/07/2012 102.85 PMC1000053284FC 123 3 02/07/2012 102.85 PMC1000053284FC 123 3 02/07/2012 102.85 PMC1000053284FC 123 4 02/08/2012 102.85 PMC1000053284FC 123 4 02/08/2012 102.85 PMC1000053284FC 123 4 02/08/2012 102.85 PMC1000053284FC 123 5 03/09/2012 102.85 PMC1000053284FC 123 5 03/09/2012 102.85 PMC1000053284FC 123 5 03/09/2012 102.85 PMC1000053284FC 123 6 02/10/2012 102.85 PMC1000053284FC 123 6 02/10/2012 102.85 PMC1000053284FC 123 6 02/10/2012 102.85 I want to see a summary like PMC1000053284FC 411.4 I have used expression =SUM(IIF(Code.getDistinct(Fields!INS_INSTALMENT_NUMBER.Value, "scopeId"), Fields!Balance.Value, 0)) However, if there is only one instalment then function returns 0, and if there is more than 1 instalment I get an error. Report is grouped by Policy number and Instalment Number. Any ideas as to what I am doing wrong Ian

Arun I created an expression also tried a calculated field but both result in same error =RunningValue(Fields!Balance.Value, Sum, "INSTALMENT") Named Instalment Number Group INSTALMENT, and place expression in the Instalment number group. But when I run report I get error Failed to Preview Report An internal error occurred on the report server. See the error log for more details. ---------------------------- The definition of the report '' is invalid. ---------------------------- An error occurred during local report processing. What am I doing wrong? Ian

Arun Worked out my error but it still does not give me results I expect In the post above report is grouped by Policy Number AGR_EXTERNAL_REF which in this case = PMC1000053284FC and by Instalment number. I want to sum the values of Balance on change of instalment no. When I place Runningvalue formula into Policy header I get a total of 1234.20 which means its suming every value of balance this is my formula =RunningValue(Fields!Balance.Value,Sum,"AGR_EXTERNAL_REF") Thank you Ian

Sorry Arun you have missed the point. Each instalment is triplicating. I only want to add up the distinct one each of the 102.85 from each instalment The total for the policy will be 411.4. If I could sum the max of each instalment that would be fine, but I cannot do that. Ian

Hi, With the details which you have mentioned seems like you have two groups on top the data which have included in the first post, if so you should use the scope as second group under the running value expression as bcoz you require w.r.t installment no. i suppose, do try this option once and let me know if any issues. if still not working please post with more details and data so that i can try on my machine with the sample data if possible Regards, Arun Gangumalla

Changed Expression to =RunningValue(Fields!Balance.Value,Sum,"INS_STALMENT_NUMBER") INS_STALMENT_NUMBER is the name of the Instalment number group. Placed in Policy Group header and got this error when trying to run report The Value expression for the text box Textbox2 has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset. My data looks like this Policy REf InsNo PayDate Amt PMC1000053284FC 123 3 02/07/2012 102.85 PMC1000053284FC 123 3 02/07/2012 102.85 PMC1000053284FC 123 3 02/07/2012 102.85 PMC1000053284FC 123 4 02/08/2012 102.85 PMC1000053284FC 123 4 02/08/2012 102.85 PMC1000053284FC 123 4 02/08/2012 102.85 PMC1000053284FC 123 5 03/09/2012 102.85 PMC1000053284FC 123 5 03/09/2012 102.85 PMC1000053284FC 123 5 03/09/2012 102.85 PMC1000053284FC 123 6 02/10/2012 102.85 PMC1000053284FC 123 6 02/10/2012 102.85 PMC1000053284FC 123 6 02/10/2012 102.85 I want to see a summary like PMC1000053284FC 411.4 (102.85 for each instalment) Thank you Ian

Hi Ian, Here you need to create a group with InsNo. with which you'll be to get sum of records under same type of InsNo. like for value '3' you'll get sum as you need 411.4, for which you need to have group footer row where you can apply sum function on Amount value. please follow below steps to achieve it Cheers Arun Gangumalla Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Hi Ian, In you r scenario, each record is triplicating, right? We can get rid of the repetitive recodes. I have tested it on my test environment, I create another dataset using the expression below. select distinct(InsNo) from tablename. Then use lookup function to get the data form another dataset. The expression would like below: =lookup(fields!InsNo.Value,fields!InsNo.Value,fields!PayDate.Value,"DataSet1") The screenshot below are for your reference. If you have any questions, please feel free to ask. Regards, Charlie Liao