Oracle APEX 26.1 AI Interactive Reports: Learning Column Level Settings with a Hands On Demo

Posted on Apex Tech Diary | Oracle APEX 26.1 | AI Interactive Reports | Google Gemini

Interactive Reports have been my favourite part of APEX for years. But I always noticed one thing in every project. Most end users only touch the search bar. The filters, pivots, control breaks and highlights sit quietly inside the Actions menu and nobody finds them.

APEX 26.1 changes this with AI Interactive Reports. Users can now type a plain English question like "big open orders in the south" and APEX applies the right filters and settings on its own. When I read the official post from the Oracle team, one section pulled me in more than anything else. The column level Generative AI settings. This is where the real accuracy of the feature comes from, and I wanted to properly understand it, not just read about it.

So I built a small learning playground with three tables and tested every column level setting one by one. In this post I will share the full setup, and for each scenario I will show you what the AI gets wrong without the setting, and how the setting fixes it. I am using Google Gemini as my AI service, but any configured service works the same way.

A Quick Recap of the Feature

When a user types a natural language request, APEX sends only the report metadata to the LLM. This includes the report definition, column details, the context you write, reference values and the current report state. The LLM converts the request into normal declarative Interactive Report settings, and APEX applies them as regular chips on the report.

Your business data never leaves your environment. Only metadata travels to the AI. And since every AI decision lands as a visible chip, the user can see it, adjust it or remove it. Nothing is hidden.

The supported actions cover almost everything an Interactive Report can do:

Displayed Columns Filter Sort Control Break Highlight Aggregate Chart Group By Pivot Save Report Rows per Page

Why Column Level Settings Matter

Each column now has a Generative AI section in the Property Editor with two attributes:

Column Context: A short note that explains what the column really means in business terms. This teaches the AI your vocabulary.
Reference Data Type: Tells the AI the exact valid values in the column, so filters never use guessed or misspelled values. The options are None, Shared Component, SQL Query and Static Values.

Reading this in the documentation sounds simple. But the interesting part is knowing which attribute solves which problem. The best way I found to learn this was to leave the settings empty first, watch the AI fail, and then add the setting and watch it succeed. That is exactly how the demo below is designed.

Step 1: Create the Demo Tables

I did not want to use EMP and DEPT this time. A small order management schema creates much richer scenarios because it has amounts, two date columns, status values, categories, regions and customer tiers. Run this script in SQL Workshop under SQL Scripts:

create table demo_customers (
  customer_id   number primary key,
  customer_name varchar2(50),
  region        varchar2(20),
  tier          varchar2(10)
);

create table demo_products (
  product_id    number primary key,
  product_name  varchar2(50),
  category      varchar2(30),
  unit_price    number(10,2)
);

create table demo_orders (
  order_id    number primary key,
  customer_id number references demo_customers,
  product_id  number references demo_products,
  order_date  date,
  ship_date   date,
  status      varchar2(15),
  quantity    number,
  order_total number(12,2)
);

insert into demo_customers values (1,'Aarav Traders','SOUTH','GOLD');
insert into demo_customers values (2,'Bloom Retail','NORTH','SILVER');
insert into demo_customers values (3,'Citrus Mart','WEST','BRONZE');
insert into demo_customers values (4,'Delta Supplies','SOUTH','GOLD');
insert into demo_customers values (5,'Everest Stores','EAST','SILVER');

insert into demo_products values (10,'Laptop Pro 14','Electronics',85000);
insert into demo_products values (11,'Office Chair','Furniture',12000);
insert into demo_products values (12,'A4 Paper Box','Stationery',450);
insert into demo_products values (13,'Monitor 27','Electronics',22000);
insert into demo_products values (14,'Standing Desk','Furniture',35000);

insert into demo_orders values (100,1,10,sysdate-40,sysdate-35,'DELIVERED',2,170000);
insert into demo_orders values (101,2,12,sysdate-30,sysdate-27,'DELIVERED',50,22500);
insert into demo_orders values (102,3,11,sysdate-25,null,'CANCELLED',4,48000);
insert into demo_orders values (103,4,13,sysdate-20,sysdate-14,'DELIVERED',3,66000);
insert into demo_orders values (104,1,14,sysdate-15,sysdate-9,'DELIVERED',1,35000);
insert into demo_orders values (105,5,12,sysdate-10,sysdate-8,'IN TRANSIT',100,45000);
insert into demo_orders values (106,2,10,sysdate-7,null,'NEW',1,85000);
insert into demo_orders values (107,4,14,sysdate-5,null,'NEW',2,70000);
insert into demo_orders values (108,3,12,sysdate-3,sysdate-1,'IN TRANSIT',20,9000);
insert into demo_orders values (109,1,13,sysdate-1,null,'NEW',5,110000);
commit;

Step 2: Create a Shared Component LOV

One of the Reference Data Type options is Shared Component, so we need an LOV ready before wiring it up. Go to Shared Components > List of Values > Create, choose Static, name it REGIONS_LOV and add these entries:

NORTH, SOUTH, EAST, WEST

Step 3: Create the Interactive Report Page

Create a new page of type Interactive Report. I used a join query so the report shows readable business names instead of ID numbers. This also gives the AI friendly columns to work with:

select o.order_id,
       c.customer_name,
       c.region,
       c.tier,
       p.product_name,
       p.category,
       o.order_date,
       o.ship_date,
       o.status,
       o.quantity,
       o.order_total
  from demo_orders o
  join demo_customers c on c.customer_id = o.customer_id
  join demo_products  p on p.product_id  = o.product_id

Step 4: Report Level Settings

Before touching columns, the report itself needs its Generative AI settings. Select the Interactive Report region, open the Attributes tab and find the new Generative AI section. One reminder here. Your app must already have an AI service assigned under Shared Components > AI Attributes, otherwise these settings do nothing.

Natural Language Support : On
Default Search Mode      : Row Search

I kept Row Search as the default so the search bar behaves exactly like before, and users can switch to Search with AI when they want. Then paste this into the Report Context text area:

Order management report showing customer orders. ORDER_TOTAL is the
total order value in INR. "Open orders" means status NEW or IN TRANSIT.
"Big orders" means order total above 50000. The order date is when the
customer placed the order. The ship date is when it was dispatched.

Step 5: The Column Level Scenarios

This is the heart of this post. For every scenario below, my advice is simple. First run the test prompt with the setting empty and watch what goes wrong. Then add the setting and run it again. Seeing the failure with your own eyes is what makes the lesson stick.

AColumn Context Only: ORDER_TOTAL

Test prompt: "show big orders"

Without the setting: the AI has no idea what "big" means for this data. It may pick a random threshold or even filter the wrong numeric column like QUANTITY.

The fix: open the ORDER_TOTAL column in the Property Editor, go to the Generative AI section and add this Column Context:

Total order value in INR. Users may say "order value", "revenue"
or "amount". "Big orders" or "high value" means above 50000.
Do not confuse with QUANTITY.

After the fix: the same prompt now gives a clean filter chip of ORDER_TOTAL greater than 50000. Every single time.

BColumn Context for Date Confusion: ORDER_DATE and SHIP_DATE

Test prompt: "orders placed in the last week"

Without the setting: the report has two date columns, and the AI can easily pick the wrong one. "Placed" and "shipped" mean two different business events, but the AI cannot know that from column names alone.

The fix: give each date its own context. For ORDER_DATE:

Date the customer placed the order. "Placed", "ordered" or
"recent orders" refer to this column.

And for SHIP_DATE:

Date the order was dispatched. "Shipped", "dispatched" or
"delivered late" refer to this column. Null means not yet shipped.

After the fix: try "orders placed in the last week" and then "orders shipped in the last week". Each prompt now lands on the correct column. That small line about null is also useful later, because "which orders have not shipped yet" becomes a null filter on SHIP_DATE.

CReference Data Type as Static Values: STATUS

Test prompt: "exclude cancelled orders"

Without the setting: the AI guesses the value. It may filter on "Cancelled" or "CANCELED" while the real data holds "CANCELLED". A close guess with wrong spelling or wrong case returns zero rows, and the user thinks the report is broken.

The fix: select the STATUS column, set Reference Data Type to Static Values and enter:

NEW, IN TRANSIT, DELIVERED, CANCELLED

After the fix: the exclusion filter uses the exact value CANCELLED. Also try "show open orders" here. It works because the Report Context defined open orders as NEW or IN TRANSIT, and the reference data supplies the exact spellings. Two settings working together.

DReference Data Type as SQL Query: CATEGORY

Test prompt: "only electronics and furniture orders"

Why SQL Query here: categories live in the data and can grow over time. A static list would go stale. A query keeps the reference list always fresh. Set Reference Data Type to SQL Query for the CATEGORY column and enter:

select distinct category from demo_products order by 1

After the fix: the prompt produces an exact in list filter with Electronics and Furniture, matching the real values in the table.

EReference Data Type as Shared Component: REGION

Test prompt: "orders from the south region"

Why Shared Component here: in real applications the region list almost always already exists as an LOV for forms and filters. Instead of duplicating values, point the AI to the same LOV. Set Reference Data Type to Shared Component for the REGION column and pick REGIONS_LOV.

After the fix: the filter uses the exact value SOUTH. One list of values, maintained in one place, used by both your forms and your AI.

FThe Combo: Column Context Plus Reference Data on TIER

Test prompt: "show orders from premium customers"

Without the settings: this is the most interesting failure. The AI understands that "premium" is about customer quality. The idea is right. But it has no clue that premium maps to the value GOLD. The intent is correct and the execution fails. This is exactly the case the Oracle team describes where both attributes are needed together.

The fix: give TIER a Column Context that explains the meaning and the ordering:

Customer loyalty tier. Order of importance: GOLD is highest,
then SILVER, then BRONZE. "Premium", "top" or "best customers"
means GOLD. "Top two tiers" means GOLD and SILVER.

And set Reference Data Type to Static Values:

GOLD, SILVER, BRONZE

After the fix: "premium customers" filters TIER to GOLD, and "group orders by the top two customer tiers" correctly picks GOLD and SILVER. Context gives the meaning, reference data gives the exact values.

Step 6: The Full Test Drive

Once everything is configured, run the page, switch the search bar to Search with AI or open the Assistant panel and try these prompts. Each one exercises different settings together:

Prompt	What it should produce	Settings at work
"Big open orders in the south"	Three filter chips for total, status and region	A + C + E together
"Total order value by category as a bar chart"	Chart view with sum of order total by category	D
"Pivot order value by region with status across the top"	A pivot view	C + E
"Highlight premium customer orders"	Highlight rule where TIER is GOLD	F
"Which orders have not shipped yet"	Filter where SHIP_DATE is null	B
"Save this as Pending Review"	A saved report named Pending Review	Built in action

Try this too: in the Assistant panel, ask one prompt and then follow up with "now only gold tier". The Assistant refines the existing chips instead of starting over. And remember the Assistant only configures the report. It never shows data or summaries in the chat. The report stays the single source of truth.

What I Learned from This Exercise

My biggest takeaway: the AI is only as smart as the context you write. The feature works out of the box, but the accuracy jumps massively after ten minutes of writing good Column Context and wiring Reference Data for list style columns. If a phrase in your business can map to two different columns, say clearly which one wins. If a word like "big" or "premium" has a specific meaning in your data, define it with a number or a value.

A simple rule of thumb that came out of my testing:

If users speak in	Then give the column
Business words and synonyms like "deal size" or "closing soon"	Column Context
Specific list values like "Negotiation stage" or "cancelled"	Reference Data Type
Business words that resolve to list values like "premium" or "late stage"	Both together

One more thing worth repeating. Natural Language Support is disabled by default for both new and existing Interactive Reports. Nothing changes in your existing apps until you switch it on, and the AI can only use the report capabilities you have enabled. If Filter is off for the report, no prompt can filter it. The governance model stays fully in your hands.

If you have an APEX 26.1 environment with an AI service ready, this whole playground takes about thirty minutes to build. Try breaking it with your own vague prompts and then fix it with context. That is honestly the fastest way to learn this feature.

Have you tried AI Interactive Reports yet? Which prompt surprised you the most? Tell me in the comments.

Reference: Talk to Your Data, Trust the Result. Introducing APEX AI Interactive Reports, Oracle APEX Blog

Search This Blog

Narmadha D