locked
Running Total RRS feed

  • Question

  • 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
    Tuesday, October 9, 2012 8:49 AM

Answers

  • 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

    • Proposed as answer by Charlie Liao Thursday, October 18, 2012 12:55 AM
    • Marked as answer by Charlie Liao Monday, October 22, 2012 1:33 AM
    Tuesday, October 16, 2012 6:40 AM

All replies

  •  

    Hi There

    Thanks for your posting. I don’t 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

    Tuesday, October 9, 2012 9:03 AM
  • 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

    Tuesday, October 9, 2012 9:37 AM
  • 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

    Tuesday, October 9, 2012 9:50 AM
  • 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

    Tuesday, October 9, 2012 11:28 AM
  • 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

    Tuesday, October 9, 2012 3:51 PM
  • 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

    Wednesday, October 10, 2012 1:39 PM
  • 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

    Wednesday, October 10, 2012 2:19 PM
  • 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

    Thursday, October 11, 2012 11:31 AM
  • 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

    Thursday, October 11, 2012 1:26 PM
  • 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

    • Proposed as answer by Charlie Liao Thursday, October 18, 2012 12:55 AM
    • Marked as answer by Charlie Liao Monday, October 22, 2012 1:33 AM
    Tuesday, October 16, 2012 6:40 AM