Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Interactive Report won't display beyond 18 columns

PhilMan2Jan 23 2025 — edited Jan 23 2025

Hello. I'm using Apex 24.2.0 on a 21C database. I recently ran into a problem when my attendance tracking report stopped displaying beyond 18 columns. The columns are labeled with the meeting date. The number of meeting dates increases monthly. I use the Pivot clause in this report

It was originally in a Classic Report. I read that the Classic report can not go beyond 16 columns so I created a new page. In that page I added an Interactive Report. The Source / Location is “Local Database”. The Source / Type is “Function Body returning SQL Query” The Source / Language is "PL/SQL".

I continue to get a similar result with the new Interactive Report whereby the report header shows the newer dates, but the body doesn't show them. The PL/SQL is below:

--Attendance Report Original
declare
   l_pivot_cols varchar2(4000);
   l_sql        clob := q'{
select
   *
from
   (select
       vc.last_name || ', ' || vc.first_name as Attendee
     , va.attendance_type
     , ve.event_date
   from
       vol_attendance va
           inner join vol_event ve
             on va.event_fkey = ve.prim_key
           inner join vol_contact vc
             on va.contact_fkey = vc.prim_key
           inner join vol_contact_group vcg
             on vc.prim_key = vcg.contact_fkey
           inner join vol_group vg
             on vcg.group_fkey = vg.prim_key
   where
       vg.group_name = 'Aid'
   and vc.status = 'Active'
   and va.attendance_type is not null
   )
pivot (
   max(attendance_type) for event_date in (%s))
order by
   attendee
}';
begin
   select
     listagg(apex_string.format('date ''%0'' "%0"', to_char(event_date, 'YYYY-MM-DD')), ', ') 
       within group (order by event_date)
   into
     l_pivot_cols
   from
     vol_event;

   return apex_string.format(l_sql, l_pivot_cols);
end;

How do I get a larger number of columns to display on the report?

Thanks for looking at this?

Comments

BluShadow Mar 18 2024

Oracle support documents are available to paying customers through the support portal: support.oracle.com

If you are not a paying customer then I'm afraid you won't have access to the document, and it is a breach of any customer's agreement with Oracle to supply such support documents to others.

1 - 1

Post Details

Added on Jan 23 2025
13 comments
162 views