Monday, November 2, 2009

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.
Post a Comment