BPC-Components

Home > Products > BPC PowerForms - Silverlight > Knowledge Base > Scripting and Formulas > Script Example : Calculate average value for a query resultset

Script Example : Calculate average value for a query resultset

For this example, we want to dynamically load data from a SharePoint list, loop through the resultset and calculate the average value of a specific column in the resultset:

To build our script, we assume the following :


- We have a list of “Cases” that have a Rate field (number) and a Customer lookup
- We have a “Customers” list
- In the Customers form, we added an ActionButton and a readonly textbox called “c_Avg”
- When pressing the button, we want to load all cases for the specific customer, and calculate the Average Rate
- Then we want to display the average value in the c_Avg control

We add a script action in the button and write the following code:

Code

T = template

def callback(e) :
  if e.DataItems.Count>0 :
    total = 0
    avg = 0
    for record in e.DataItems :
      rate = e.DataItems[0].GetValue("Rate")
      if rate != "" :
        total = total + float(rate)
    avg = total / e.DataItems.Count
    T.GetControl("c_Avg").SetValue(avg)
   
T.GetControl("c_Avg").SetValue("")
id = T.GetControl("c_ID").GetValue()
template.LoadRecords(http://server1/siteA, "Cases", "Customer", "Equals", id, "Lookup", ["Rate"], callback)

In the above example we follow the steps below to load data and calculate the required value :

- We define the “callback” method that will be executed as soon as loading completes
- We clear the value for the c_Avg control
- We load the “Rate” for all cases for that customer
- Inside the callback method, we calculate the total rate and divide by the count to get the average value
- We set the calculated average value in the c_Avg control.