How can we use total function in HTML report - Forum - Rollbase - Progress Community

How can we use total function in HTML report

 Forum

How can we use total function in HTML report

  • Hi all,

    i have created a Report with HTML type to get list of records in PDF (we are able to do this) format,at the bottom of PDF we need to total the Each column(we are not able to do this one)

    As shown Above SS AD Today,Ad this week .... all are formula fields it will calculated the related action done from its related object.

    Now my question is how can i get the values of the last row(Total) in PDF(that means in HTML report)

    Thanks,
    Sudhakar.

  • Hi Sudhakar,

    You can summarize the columns using EVAL blocks. Please refer to Chapter 6 page 160 of Rollbase in Action on how to use EVAL blocks. You can achieve this by creating/converting your fields to an expression field and then using the EVAL block to return the SUM of that column field. Formula fields cannot be used in queries so you need to convert it to an expression field.

    #EVAL[rbv_api.selectNumber("SELECT SUM(expression_field_col) FROM object");] //This will be inside the <td> element in the report

    Thanks,

    Gian

  • thanks ,working perfect.

    Thanks,
    Sudhakar.

  • Hi ,

    I have created expression fields to store AD Today,AD this week and etc as suggested by you,

    But here im seeing strange behavior .for individual records it is showing correct values but im calculating Total by using EVAL function it is showing correct Data(total) based on individual values,but after some time it is not displaying correct values (i mean not calculating total based on  individual values).

    if i update(no changes at all just open the expression and save it ) then it is displaying correct for that particular time

    Kindly let me know how can i display total at  any time based on individual records with out update the expression field.

    Thanks,
    Sudhakar.

  • Hi Sudhakar,

    When does the value on the expression field change? If the field changes based on the record update of other objects, you can add a trigger that will update the report record so that it is always updated. If you can provide a snippet of the code inside the expression field, that would be helpful in identifying what approach to use.

    Thanks,

    Gian

  • Hi @Gian Torralba,

    when over there is change in AD Today column values  then at the bottom total should calculate by taking those values.

    Im using expression as below in referral source

    if("{!status#code}"=="ACT") // if Referral source Status=Active

    {

    #CALC_COUNT.R101162498( 1 | createdAt>=TODAY) //count the no of "actions done"(related object to referral source)

    }

    And in HTML report im using as below

    <td width="5%" align="center">#EVAL[rbv_api.selectNumber("SELECT SUM(ad_today) FROM referral_source");]</td>

    Kindly let me know what i need to do to get dynamic total values at the bottom

    Thanks,
    Sudhakar.

  • One suggestion: I recommend always use /*  */ style comments instead of // style comments, this is much safer.

  • thanks for suggestion ,

    In live we are not using any comments,here i used comments in order to  others to make better understand.

    Thanks,
    Sudhakar.

  • Hi Sudhakar,

    Thanks for the quick response. Here are two possible ways to get the dynamic values on the report.

    Since expression fields doesn't update the values when the record is not updated and we still need to use the selectNumber API to compute the total, I suggest changing the expression field to an integer field and then creating an update field trigger on the related object that will update the integer field on create/update.

    The second approach is that we stick to the expression field but instead of the update field trigger, you can use an object script trigger and the updateRecord API that will re update the record.

    I would suggest using the first one since it will only update a single field rather than the whole record.

    Thanks,

    Gian

  • HI @ Gian Torralba,

    i have created trigger(Timing::After update,Type::Object Script--i need to update 4 fields at a time that why i have selected object scipt instead of update field value) as below

    if("{!R101162498.status#code}"=="ACT")//if Referral source status=active

    {

    var a= rbv_api.selectNumber("SELECT SUM(ad_today) FROM referral_source");//ad_today is expression field to store single column value and not working as expected.if  i update the expression field then it is displaying as expected

    rbv_api.setFieldValue("referral_source", {!R101162498.id}, "sum_ad_today", a);//sum_ad_today is integer field to store total at bottom,but not working as expected it is displaying Zero(i have updated a record in Action done and checked the value ) .

    }

    Thanks,
    Sudhakar.

  • Hi Sudhakar,

    If you are already using the integer field, you need to put the setFieldValue api trigger in object B. Since you are using a calc_sum on every added record in object B, you can set the trigger timing to on create so that it will update the field every time a record is added.

    Thanks,

    Gian