Let’s talk about SUM function in BI Publisher. Sometime we might end up doing SUM of columns on the fly.
So we can capitalize the SUM function which is available in BI/XML Publisher.
We can write all the SUM functions within our Data set(SQL/Procedure,etc..) but sometimes we might get a request from users to add some other totals(SUM columns).
Instances like above we can add/change the SUM totals in BI?XML publisher without touching the code.

Simple SUM:

We would like to display the sum salary at the end of the report.

We can use sum(SAL) outside of the for-each loop.

Conditional SUM:

Sometimes we want the totals based on the conditions like user want the sum total for each department.
Input and RTF Template:

Here if we look at the XML input, we have 2 records for department 10 and 1 record for the department 20. We have to use below code to get the SUM of SAL for each department.
sum(//SAL[../DEPTNO=10])
sum(//SAL[../DEPTNO=20])
// and ../ called XPATH in the BI/XML Publisher. We will discuss this in another post.
Report Output:

SUM if NULL values exist:

Let’s assume sometimes we might get null values in the columns. Based on the below XML file we have a null value in the SAL column of the 2nd row.

sum(SAL[.!=’’]) -> This is nothing but some all SAL column from ROWS node except the NULL values.

Let’s assume we have null values in the XML data set for department 10 SAL column.
We have to use below statements to ignore the NULL values and calculate the SUM for not null values.
sum(//SAL[../DEPTNO=10 and .!=”])
sum(//SAL[../DEPTNO=20 and .!=”])


Note: If we notice the report output 2nd row SAL value is NULL, if we don’t use above conditions in RTF template will result fail/error or display NULL value in the SUM of department 10.

All the above examples, we talked about the SUM of columns outside the for-each group.

Now let’s talk about the SUM functions inside the for-each group.

Within the for-each group:
We have two groups(SUMABOVE, ROWS) in the XML data set.
We have moved out the F -> for-each:ROWS and all the SUM values inside the for-each group.
In order to get the correct SUM we have have to below commands.

sum(//SAL[.!=’’]) -> To get all the SUM of SAL with in the same group(ROWS).
sum(//Amount[.!=’’]) -> To get the sum of the AMOUNT from the above group(SUMABOVE).

Even if you change the for-each group to SUMABOVE(for-each:SUMABOVE) node and the SUM’s are going to work.

Sometimes if re-arrange the group we might need to use the below command. We will discuss different grouping sections in BI/XML publisher in another post.

sum(current-group()/AMT)