BIP Reports - Chapter 5: Creating Data Models from Scratch

 

🏗️ Chapter 5: Creating Data Models from Scratch

In this chapter, you will learn how to build a custom Data Model using SQL queries in BI Publisher. You'll also add parameters, List of Values (LOVs), and multiple data sets to make the report dynamic and interactive.

---

✅ 1. What is a Data Model in BI Publisher?

A Data Model is the back-end logic of a BIP report. It defines:

• What data to retrieve

• How to filter and sort it

• What parameters to prompt the user for

• How data sets relate to one another (joins, hierarchies, etc.)

---

✅ 2. Objective of the Example

We'll create a "Supplier Invoices Report" that:

• Accepts a Supplier Name as input

• Retrieves invoices from the Payables tables

• Displays Invoice Number, Date, Amount, Status

---

✅ 3. Steps to Create a Custom Data Model

🆕 Step 1: Create a New Data Model

1. Go to New > Data Model

2. Save it in your folder: /Custom/Finance/Invoices/

3. Name it: Supplier_Invoices_DM

---

🧾 Step 2: Add a SQL Data Set

1. Click + under Data Sets and select SQL Query

2. Enter Data Source: ApplicationDB_FSCM (this is the default for Fusion Finance)

3. Give it a name: Invoices_DS

4. Add SQL like:

SELECT 
    ap.invoice_num,
    ap.invoice_date,
    ap.invoice_amount,
    ap.invoice_currency_code,
    ap.payment_status_flag,
    asp.vendor_name
FROM 
    ap_invoices_all ap
JOIN 
    ap_suppliers asp ON ap.vendor_id = asp.vendor_id
WHERE 
    asp.vendor_name = :P_SUPPLIER_NAME
ORDER BY 
    ap.invoice_date DESC

:P_SUPPLIER_NAME is a bind parameter — you'll define it next.

---

🧑‍💻 Step 3: Define Parameters

1. Go to Parameters > + New Parameter

2. Name: P_SUPPLIER_NAME

3. Type: Text

4. Required: Yes

5. Default Value (optional): Leave blank

---

📋 Step 4: Create a List of Values (LOV)

1. Go to List of Values > + New List of Values

2. Name: LOV_Suppliers

3. Type: SQL Query

4. SQL:

SELECT vendor_name, vendor_name FROM ap_suppliers
ORDER BY vendor_name

5. Click OK

6. Go back to the parameter P_SUPPLIER_NAME, set LOV = LOV_Suppliers, Display Type = Drop-down

---

🔄 Step 5: Test Your Data Model

1. Click Data tab → Enter Supplier Name or pick from LOV

2. Click View to generate sample XML

3. If successful, click Save As Sample Data

This sample XML will be used to design your layout.

---

✅ 4. Bonus: Adding Multiple Data Sets

You can add more SQL queries in the same model. For example:

• Second dataset: Invoice_Lines_DS to get line-level details

• Use a Group By or Join to combine with header data

---

✅ 5. Summary of What You've Learned

Component	Purpose
SQL Data Set	Retrieves specific invoice data
Parameter	Enables user to filter based on input (Supplier Name)
LOV	Replaces manual typing with a dropdown list
Sample XML	Helps design and preview layouts

---

✅ 6. Best Practices

• Always test your SQL query in SQL Developer or BI Data Model View

• Use meaningful dataset and parameter names

• Avoid hard-coding values—use parameters instead

• Use joins carefully to avoid performance issues

• Reuse LOVs across parameters if possible

---

---

🔹 Next Steps

• Previous Chapter:  Chapter 4: Creating Your First BIP Report

• Next Chapter:   Chapter 6:  Designing RTF Templates Using Word and Template Builder (Global HR Example)

---

📌 Next Chapter Preview:

Chapter 6: Designing RTF Templates using Word and Template Builder

• Installing Template Builder

• Loading XML

• Using table, group, and conditional formatting

• Exporting and uploading your final RTF layout

---

Would you like me to continue with Chapter 6 next?