BI Publisher: Dynamic Columns in Excel output

BI Publisher: Dynamic Columns in Excel output

In this article we shall see how to display columns dynamically in BI Publisher excel output using RTF template. In this example we will be using HR schema, locations, departments, employees tables.  Sample requirement is to display total no of employees in each location department wise. Output should be generated as shown below:-

For this report, our query will be

SELECT SUM(EMP_COUNT) EMP_COUNT,DEPARTMENT_NAME,DECODE(GROUPING_ID(CITY,DEPARTMENT_NAME),2,'000000000',CITY) CITY FROM (

SELECT   COUNT (*) EMP_COUNT, department_name, l.city

    FROM employees e, departments d, locations l

   WHERE e.department_id = d.department_id AND l.location_id = d.location_id

GROUP BY city, department_name

UNION ALL

-- This will return all combinations of department and city with zero employee count

SELECT   0, department_name, city

    FROM locations, departments)

GROUP BY grouping sets ((city, department_name),(department_name))

HAVING (SUM(EMP_COUNT)>0 AND GROUPING_ID(CITY,DEPARTMENT_NAME) = 2) OR GROUPING_ID(CITY,DEPARTMENT_NAME) <>2

ORDER BY city, department_name

In the above query I have used GROUPING SETS to get department totals to be displayed as the last row.

We shall eliminate the (a)city with zero employee count(in RTF using IF condition) and (b)departments with zero employee count in all cities in RDF using place holders and formula fields.

Below the RDF file snapshot

PL/SQL code for formula columns is as shown in below:-

1.       CF_SET_DEPART_DISP

function CF_SET_DEPART_DISPFormula return Number is

begin

                IF :CP_DISPLAYABLE_DEPARTS IS NULL THEN

                                :CP_DISPLAYABLE_DEPARTS := '';

                END IF;

  IF :CITY='000000000' AND :EMP_COUNT !=0 THEN

                :CP_DISPLAYABLE_DEPARTS := :CP_DISPLAYABLE_DEPARTS || ','||:DEPARTMENT_NAME;

  END IF;

  return 1;

end;

2.       CF_DSP_THIS_DEPT

function CF_DSP_THIS_DEPTFormula return CHAR is

begin

  IF INSTR(:CP_DISPLAYABLE_DEPARTS,:DEPARTMENT_NAME)!=0 THEN

                RETURN 'TRUE';

  ELSE

                RETURN 'FALSE';

  END IF;

end;

Sample XML generated by RDF file is shown below

In RTF we shall use “split-column-header”  and “split-column-data” to display the departments dynamically.

RTF snapshot is shown below:-

 

Download all code components mentioned below using Download Here link

1.       HR Schema Load Script

2.       RDF file

3.       RTF file