How can we use total function in HTML report

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 [mention:c5dec1ce5a5948c5a25cf4bcaeb7db77:e9ed411860ed4f2ba0265705b8793d05] ,working perfect.

Hi [mention:c5dec1ce5a5948c5a25cf4bcaeb7db77:e9ed411860ed4f2ba0265705b8793d05] ,

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.

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

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

thanks for suggestion [mention:05b5f00eae4a468d844fa8bedcafd110:e9ed411860ed4f2ba0265705b8793d05] ,

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

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 ) .

}

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