Transposed Report Example

Sometimes the way data is stored in the database can make it difficult to display in a report. Let’s say, for example, I was creating an “Events Tracking” application. If the application was only being designed to track one type of event (concerts) I could probably get away with one table:

With this single table design, I can create a report in Apex with a simple query using a classic report with the type set to SQL Query:

SELECT artist,
   description,
   venue,
   event_date,
   price
FROM events

But what would happen if the application was being designed to track multiple types of events where each event type could have a variable number of event attributes? In this case I might consider a design that uses a few different tables:

With this design I have a lot of flexibility but when I generate a report on the data things get a little tricky. I want a report similar to the simple query where each column is an event attribute and each row represents one event. Of course, I’d only be displaying one type of event at a time…
In order to accommodate the flexible table design, I need to take advantage of a more complex report type in Apex: SQL Query (PL/SQL function body returning SQL Query). At the end of the day, both reports are based on a SQL Query. But in this one I use PL/SQL to dynamically build the query on the fly.
You can check out the demo here. Follow these steps to recreate the demo in your environment:

1. Set up the demo tables and data - do this in a development environment. 
   

   CREATE TABLE EVENT_TYPES( 
      ID NUMBER NOT NULL, 
      TITLE VARCHAR2(100 BYTE), 
      CONSTRAINT EVENT_TYPES_PK PRIMARY KEY (ID) 
   )
   /
   
   CREATE TABLE EVENT_TYPE_ATTRIBUTES( 
      ID NUMBER NOT NULL, 
      EVENT_TYPE_ID NUMBER, 
      TITLE VARCHAR2(100 BYTE), 
      DISPLAY_ORDINAL NUMBER, 
      CONSTRAINT EVENT_TYPE_ATTRIBUTES_PK PRIMARY KEY (ID), 
      CONSTRAINT EVNT_TYPE_ATRS_EVNT_TYPES_FK FOREIGN KEY (EVENT_TYPE_ID) 
         REFERENCES EVENT_TYPES (ID) 
   )
   /
   
   CREATE TABLE EVENTS ( 
      ID NUMBER NOT NULL, 
      EVENT_TYPE_ID NUMBER NOT NULL, 
      EVENT_DATE DATE NOT NULL, 
      DESCRIPTION VARCHAR2(4000) NOT NULL, 
      CONSTRAINT EVENTS_PK PRIMARY KEY (ID), 
      CONSTRAINT EVENTS_EVENT_TYPES_FK FOREIGN KEY (EVENT_TYPE_ID) 
         REFERENCES EVENT_TYPES (ID) 
   )
   /
   
   CREATE TABLE EVENT_DETAILS( 
      ID NUMBER NOT NULL, 
      EVENT_ID NUMBER NOT NULL, 
      EVENT_TYPE_ATTRIBUTE_ID NUMBER NOT NULL, 
      EVENT_TYPE_ATTRIBUTE_VALUE VARCHAR2(4000), 
      CONSTRAINT EVENT_DETAILS_EVENTS_FK FOREIGN KEY (EVENT_ID) 
         REFERENCES EVENTS (ID), 
      CONSTRAINT EVENT_DTLS_EVENT_TYPE_ATRS_FK FOREIGN KEY (EVENT_TYPE_ATTRIBUTE_ID) 
         REFERENCES EVENT_TYPE_ATTRIBUTES (ID) 
   )
   /
   
   INSERT INTO event_types (id, title) VALUES (1, 'Football Game');
   INSERT INTO event_types (id, title) VALUES (2, 'Concert');
   INSERT INTO event_types (id, title) VALUES (3, 'Movie');
   
   INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (1,1,'Home Team');
   INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (2,1,'Visiting Team');
   INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (3,1,'Line');
   INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (4,2,'Band or Singer');
   INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (5,2,'Venue');
   INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (6,2,'Ticket Cost');
   INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (7,3,'Title');
   INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (8,3,'Genre');
   
   INSERT INTO events (id,event_type_id,event_date,description) VALUES (1,1,TO_DATE('12-NOV-09 6:00 PM','DD-MON-RR HH:MI PM'),'USF back in the top 25 - can they stay there?');
   INSERT INTO events (id,event_type_id,event_date,description) VALUES (2,2,TO_DATE('25-DEC-09 8:00 PM','DD-MON-RR HH:MI PM'),'He''s back for one day only');
   INSERT INTO events (id,event_type_id,event_date,description) VALUES (3,1,TO_DATE('28-NOV-09 3:30 PM','DD-MON-RR HH:MI PM'),'A big rivalry this week!');
   INSERT INTO events (id,event_type_id,event_date,description) VALUES (4,3,TO_DATE('09-DEC-09 11:00 PM','DD-MON-RR HH:MI PM'),'Very scary movie!');
   
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (1,1,1,'USF');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (2,1,2,'Rutgers');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (3,1,3,'-10');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (4,3,1,'Florida Gators');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (5,3,2,'FSU');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (6,3,3,'???');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (7,2,4,'Elvis');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (8,2,5,'NY');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (9,2,6,'$500');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (10,4,7,'Paranormal Activity');
   INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (11,4,8,'Horror');
   
   COMMIT;

2. Create a new report region on a page. Make sure to select the “generic columns” option below the source. Also, replace PXX_EVENT_TYPE_ID with the appropriate item name (created in the next step).
   

   DECLARE
   
      l_retval        VARCHAR2(32767);
      l_crlf          CHAR(2) := CHR(13)||CHR(10);
      l_event_type_id EVENT_TYPES.ID%TYPE;
      
      CURSOR event_type_attributes_cur (
         p_event_type_id IN EVENT_TYPE_ATTRIBUTES.EVENT_TYPE_ID%TYPE
      )
      IS
         SELECT *
         FROM event_type_attributes
         WHERE event_type_id = p_event_type_id;
         
      l_event_type_attributes_rec EVENT_TYPE_ATTRIBUTES_CUR%ROWTYPE;
   
   BEGIN
   
      l_event_type_id := nv('PXX_EVENT_TYPE_ID');
   
      l_retval := 'SELECT evt.event_date AS "Event Date", ' || l_crlf
         || ' evt.description AS "Description", ';
      
      OPEN event_type_attributes_cur(l_event_type_id);
      
      LOOP
         FETCH event_type_attributes_cur INTO l_event_type_attributes_rec;
         EXIT WHEN event_type_attributes_cur%NOTFOUND;
         
         l_retval := l_retval ||
               '   (' || l_crlf
            || '      SELECT event_type_attribute_value' || l_crlf
            || '      FROM event_details' || l_crlf
            || '      WHERE event_id = evt.id' || l_crlf
            || '         AND event_type_attribute_id = ' || l_event_type_attributes_rec.id || l_crlf
            || '   ) AS "' || l_event_type_attributes_rec.title || '",' || l_crlf;
      END LOOP;
      
      CLOSE event_type_attributes_cur;
      
      l_retval := RTRIM(l_retval, ',' || l_crlf);
      
      l_retval := l_retval || l_crlf
         || 'FROM events evt' || l_crlf
         || 'WHERE event_type_id = ' || l_event_type_id;
   
      RETURN l_retval;
   
   END;

3. Add an item to select the event type. Choose an item type of Select List with Submit, set the name to PXX_EVENT_TYPE_ID (replace XX with page number). Note that the report will fail to render if the value of this item is not set. There are various ways to “handle” this  - I used a page process to set the value to 1 if the item’s value was NULL. Use the following for the LOV:
   

   SELECT title AS display,
      id AS return
   FROM event_types
   ORDER BY display

4. Add a branch back to the same page.