Print

IR Report with Dynamic Columns

Written by Mandy. Posted in Interactive Reports.

My client has an application where they manage their consulting resources, and assign them to different projects.

The format of the 'Assignment' table is somewhat like:

Consultant Name | Project Name | Start Date | End Date | Utilisation % | Tentative Y/N

 

They wanted to take this data and display it in a tranposed format, so that it looks more like a calendar. This is so each team leader can view their team's schedule and they can easily see who is available between two given dates.

This is what the report looks like:

schedule report

 

Note the following differences compated to a regular Interactive Report:

  • IR Toolbar is hidden - I have used a 'toolbar' on the right rather than utilising the out-of-the-box features. Since it was necessary to have the 'Start Date' and 'End Date' as external fields, as well as other attributes that are not displayed in a column (i.e. Only show tentatives), I decided to implement all popular filters on the right (as having two options was confusing for some users, since the 1st version of report was a non-IR report).
  • Dynamic Column Titles - the 'Date' column titles are dynamic and are set to the range of dates between the 'Start Date' and 'End Date'
  • Dynamic Column Display - the 'Date' columns conditionally display depending on if they exist between the range. See below where only 3 weeks are displayed instead of the default 5.

dynamic columns

 

 

So I'll just describe here how I accomplished this. This is a tad easier using a normal report, as you can set the column titles dynamically using PL/SQL. But I wanted the sorting/filtering extra features of the IR report (which I have now turned off most of them anyway).

I also know that the way I have configured this is a bit tedious and hard to manage, but is the way I could get the little features that the End Users wanted.

 

1) Create an APEX COLLECTION that stores the results of your query

For more info on Apex Collections in v4.0, see: http://docs.oracle.com/cd/E17556_01/doc/apirefs.40/e15519/apex_collection.htm

As my query is dynamic (dynamic select, where, group by, etc), my query is generated from a procedure which takes my page items as parameters and spits out a SQL statement.

To get the results, we need to store them in an APEX COLLECTION. At the time of development of this report, an IR Report could not display results from a dynamic query.

I have a page process that runs on page load that fills the Collection - note that the page items may not exist in the session until the page is submit, therefore I have hidden the report region until the user hits the 'Apply/Run' button for the first time.

apex collection process

 

2) Create the IR Report

Now to create your Interactive Report region. The query should be:

select * from apex collections where collection_name = '[COLLECTION_NAME]'

IR report region

 

 

3) Create Page Items to store Dynamic Date values.

These items will store the dynamic dates that our report will need for each column title.

I have limited my users to choosing up to a 25 week period (between Start Date and End Date), thus have created 25 page items.

page items

 

For each item, you will need to set it's unique calculation. DATE1 will be equavalent to START_DATE,  DATE2 will be equivalent to START_DATE+7 and so on.

I have defined this in the item's Source Value or Expression (and also copied the same into the Default Value).

page item value

 

4) Set the Report Column Title

Now that we have the items storing the dates, we can use these for the column headings in our interactive report.

We can enter a column heading using a Substitution String to refer to our item.

For more info on substitution strings: http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/concept_sub.htm

IR Column headings 

 

5) Set the column to conditionally display

We don't want the date column to display if it does not belong within the range.

In the case of my report, the query that runs and fills the collection will not populate the column with data if the date is not within the range. Thus I have used this to determine whether the column displays or not.

My dates populate from c009 onwards. Make sure your condition is searching within the correct column.

column conditions

 

 

 

I hope this helps somewhat, or gives you some good ideas.

Note: I did have some trouble with session state and making sure the page items were set before the APEX COLLECTION process was run.

 

About Me

I currently specialise in developing bespoke applications using Oracle APEX.I have worked with APEX since it was known as HTMLDB, but have also dabbled with E-Business Suite, Oracle AS Portal, ECM, Webcenter and Website Design.

I enjoy working with new clients and assisting them develop custom applications to enhance and improve internal business processes.

Find Me

mandy nerd2