BIP Reports - Chapter 11: Creating Parameterized Reports (Global HR Use Case)

 

🎛️ Chapter 11: Creating Parameterized Reports (Global HR Use Case)

Parameters make reports interactive and flexible, letting users filter results without changing SQL. In this chapter, you'll learn:

• How to define parameters in a Data Model

• Add List of Values (LOVs) for user-friendly selection

• Set default values

• Enable cascading filters (e.g., BU → Department → Employee)

---

✅ 1. Common Global HR Report Parameters

Parameter	Purpose
Business Unit	Filter employees by BU
Department	View only specific departments
Job	View specific job roles
Status	Active, Terminated, Pending
Hire Date Range	Filter by joining period

---

✅ 2. Define Parameters in the Data Model

✳️ Example: Basic Parameter for Business Unit

1. Open Data Model

2. Go to Parameters tab

3. Click + Create

   • Name: P_BU_ID

   • Data Type: Number

   • Display Label: Business Unit

   • Required: Yes (or No, as needed)

---

✅ 3. Create List of Values (LOV)

LOVs provide dropdowns instead of free text.

🧾 Example: Business Unit LOV

1. Go to List of Values tab

2. Click + Create

   • Name: LOV_BUs

   • SQL:

     sql
     SELECT name, organization_id FROM hr_organization_units WHERE org_type = 'BU'
     

   • Value Column: organization_id

   • Display Column: name

3. Return to Parameters, select:

   • List of Values: LOV_BUs

---

✅ 4. Use Parameter in SQL Query

Modify the main SQL to use the bind variable:

 
WHERE assign.business_unit_id = :P_BU_ID

You can make this dynamic with more filters:

 
AND (assign.job_id = :P_JOB_ID OR :P_JOB_ID IS NULL)
AND TRUNC(per.hire_date) BETWEEN NVL(:P_FROM_DATE, per.hire_date)
                             AND NVL(:P_TO_DATE, SYSDATE)

---

✅ 5. Cascading Parameters

For example:
Select Business Unit → Filter Departments accordingly

🔄 Steps:

1. Create a second LOV for Departments:

    
   SELECT department_name, department_id
   FROM hr_departments
   WHERE business_unit_id = :P_BU_ID
   

2. Use the dependent parameter P_DEPT_ID

3. Link this LOV to P_DEPT_ID, enabling cascading behavior

BI Publisher will auto-filter department options after BU selection.

---

✅ 6. Setting Default Values

Optional but useful:

• Set default value for BU to your org’s primary BU

• Use SQL-based default:

  sql
  SELECT organization_id FROM hr_organization_units WHERE name = 'Global HR Corp'
  

In Parameter settings, set:

• Default Value Type: SQL

• SQL Text: above query

---

✅ 7. Using Parameters in Layout

In RTF or Excel templates:

• Show selected values in the header

 
Business Unit: <?P_BU_ID?>
Department: <?P_DEPT_ID?>

BI Publisher auto-replaces these with user selections.

---

✅ 8. Tips for HR Parameter Design

Tip	Benefit
Use LOVs with IDs	Ensures correct value submission
Use optional parameters	More flexible filters
Add display fields in layout	Improves report clarity
Use cascading LOVs	Clean UX for large orgs
Validate query performance	Parameters shouldn’t slow it down

---

✅ 9. Summary

You’ve now learned how to:

• Create parameters with or without LOVs

• Implement cascading filters

• Embed parameters in SQL and report layouts

These skills are essential for building interactive HR dashboards and reports used by managers, recruiters, and leadership.

---

📌 Next Chapter Preview:

Chapter 12: BI Publisher Bursting — Personalized Delivery

• Deliver one report to multiple HR users

• Burst by BU or department

• Email, FTP, and archive methods