ponedjeljak, 6. veljače 2017.

APEX 5 IR Conditional Columns in Export

This is a post about exporting Interactive Report data to CSV, where we want to preserve leading zeros in columns which contain data in such format. In a simple example, if we try to do a straightforward export, we get this result:



If we want to preserve leading zeros, we need to use an old trick which was described in a couple of posts in the past, like this one:
http://www.talkapex.com/2010/06/how-to-only-display-column-when/

As this still is a viable solution with one Interactive Report per page, there is a twist in Apex 5: multiple Interactive Reports on the same page. To achieve the same result on a page with more than one Interactive Reports, we have to dig a little deeper.
 

In the example from the begginning of this text, I use EMP and DEPT tables joined, where EMPNO and DEPTNO columns are formatted to display leading zeros.

These are the steps to achieve our goal:

1) To preserve leading zeros, we need to concatenate =" to the start of our columns EMPNO and DEPNO and " to the end of our columns, so we have one display column and one export column (ending with "_ex"):




2) Next step is to arrange our columns in a desired order and then save this view as a default report:




 

Here you can see that the Column Heading for export columns is the same as for display columns.

3) Here comes the tricky part. There are two IRs on my page, so in the Region Properties for my IR from which I am exporting, I set the Static ID attribute to rgnEmpIR2 (of course, this can be anything, according to your developer needs):






4) After setting the Region Static ID, we can conclude our effort with setting Server-side Conditions (only "Conditions" on Apex versions prior to Apex 5.1) on our columns of interest. Display columns should be visible only in runtime of the Apex Page, while Export columns should be visible only while exporting.


For display columns (EMPNO and DEPTNO), we set Server-side Condition to Request != Value, and the Value to IR[rgnEmpIR2]_CSV:






For export columns (EMPNO_EX and DEPTNO_EX), we set the opposite condition:



As you probably already noticed, the Request has a structure of 

IR[ + Region Static ID + ]_DOWNLOAD_FORMAT

So you can use this technique for RTF, HTML and other IR Export formats.
 

And that's it, try the Live demo here:
https://apex.oracle.com/pls/apex/f?p=DONT_PANIC:IR_COND_COLS


Cheers,

Daniel