Fusion Apps Custom BI Report Creation Part II

Let me recall what I did in Part I. I gave details about how to create

• Data Model Data Sets
• Links between Data Models
• Aggregate Functions
• Parameters 

Here is the link which has the details Fusion Apps Custom BI Report Creation Part I

So let's start the report template generation using  XML data file.
To do that follow the steps mentioned below

Generate XML

Specify the Parameter and number of rows of data you want in your XML. To make generated XML light you specify the number of rows.







Now after that save the XML to your local machine. 

Now before going to next step, let's make the parameters dependent with the previous one. Those who are from EBS background make recall that this is done in concurrent program parameters by prefixing $FLEX$.




The same can be done here. Let's say we want to Create Employee First Name parameter such that, once Department is selected then only employees belong to that department will be listed. We will define LOVs as 

After creating the Dept LOV create a second Employee First Name LOV which refers the Dept LOV as shown. This binds the second LOV with the first one. Now we need to tell system that whenever user will select Dept, refresh the Employee LOV. This is done by PPR(Partial Page Rendering) in ADF. 

Here it is specified as shown in the picture. After doing this if you go to Get XML then it will like like 

Change the Dept and you will see the employee list is changing.










Now that we have developed the basic skeleton, let's make it little bit more user input data sensitive. E.g if you want your query WHERE clause to change based on input parameters then you can do it by creating report triggers. Those who are familiar with Reports6i they know that same can be done using report triggers

• Before Parameter Form
• After Parameter Form
• Before Report
• Between Pages
• After Report

Here in BIP, first we will create a parameter p_where_clause to append condition dynamically 

You can call it as Lexical Parameter. Now we will create a before_data Trigger. For that we need to create a DB Package first. All the parameters defined in the report must be defined In the package specification as global variable.

The logic we are going to implement is that

If Department ID is passed then All the Employees of the Department will be listed

Otherwise if any part of employee name is passed in the parameter First Name then those employees will be listed

If Department ID and First Name both are NULL then no Condition will be applied.

We build this logic inside function before_data of xx_demo_report_pkg package.

Any function used in Data trigger must have return type as BOOLEAN. 

Below is the sample package code you will create in FUSION schema.

CREATE OR REPLACE PACKAGE xx_demo_report_pkg

IS

   p_dept_id        NUMBER;

   p_first_name     VARCHAR2 (100);

   p_where_clause   VARCHAR2 (1000);

   FUNCTION before_data

      RETURN BOOLEAN;

END xx_demo_report_pkg;

CREATE OR REPLACE PACKAGE BODY xx_demo_report_pkg

IS

   FUNCTION before_data

      RETURN BOOLEAN

   AS

   BEGIN

      IF (p_dept_id IS NOT NULL)

      THEN

         p_where_clause   := ' AND emp.department_id = :p_dept_id ';

      ELSIF p_first_name IS NOT NULL

      THEN

         p_where_clause   := ' AND upper(emp.first_name) like ''%'||UPPER(p_first_name)||'%'' ';

      ELSE

    p_where_clause := NULL;

 END IF;

      RETURN TRUE;

   EXCEPTION

      WHEN OTHERS

      THEN

         RETURN FALSE;

   END;

END xx_demo_report_pkg;

After creating the package do the following

Choose the function and click the shuttle arrow. Now go ahead and change the WHERE clause

Specify a space and click Ok.

This completes the Data Model part. In the next post will cover Report Creation..It's 12'O Clock in the night....Time to sleep....