APEX IR XLSX Downloader - New Release available

Release 1.1.1 of my APEX IR XLSX Downloader has just been published.

Bug Fixes:

• Highlights now also support the operators "in" and "not in".
• Highlights with no user defined name now properly display the highlight condition.
• Still existing filters on removed columns no longer result in an exception, they are simply ignored now.
• Columns used in link targets are now excluded if not visible on their own.

Added Functionality

• Added a wrapper procedure handling the whole download task.
  Parameter list is the same as the function returning the results.
• Initial support to receive the GROUP_BY report if defined.

Known Limitations

• Standard and GROUP_BY report cannot be exported together into a single file.
• Columns used in GROUP_BY also need to be present in the standard report, otherwise the columns are not included in the query generated by currently available APEX API.

You can look a the source code on GitHub.
ZIP Archive of package including dependencies can be downloaded from Demo Application.

APEX Dictionary - Who uses a column in filters?

I just had the requirement to find out if some users have filters defined on a certain column of an interactive report.
First try was looking at the saved reports view in the application builder, but that didn't help.
And again the APEX Dictionary Views to the rescue.
Following query can be used to check if a column is used in a column filter or highlight.

SELECT r.created_by
     , condition_type
     , cond.condition_name
     , condition_column_name
     , cond.condition_operator
     , cond.condition_expression
     , cond.condition_expression2
     , cond.condition_display
  FROM apex_application_page_ir_cond cond JOIN
       apex_application_page_ir_rpt r
         ON r.application_id = cond.application_id
        AND r.page_id = cond.page_id
        AND r.report_id = cond.report_id
 WHERE cond.application_id = :application_id
   AND cond.page_id = :page_id
   AND cond.condition_column_name = :column_name
;

Note: It will not find column usage in a row filter.

May the APEX Dictionary be with you!

APEX Dictionary - Comparing two Interactive Reports

Recently I again had the requirement to compare two Interactive Reports from different applications.
Using the power of the APEX Dictionary this can be done quite easily.

WITH prod AS
(
SELECT column_alias, application_id, report_label, help_text
  FROM apex_application_page_ir_col
 WHERE page_id = :page_id_prod
   AND application_id = :app_id_prod
), dev AS
(
SELECT column_alias, application_id, report_label, help_text
  FROM apex_application_page_ir_col
 WHERE page_id = :page_id_dev
   AND application_id = :app_id_dev
)
SELECT NVL(prod.column_alias, dev.column_alias) column_alias,
       prod.report_label prod_label,
       dev.report_label dev_label,
       CASE WHEN prod.column_alias IS NULL THEN 'Only DEV'
            WHEN dev.column_alias IS NULL THEN 'Only PROD'
            WHEN prod.report_label != dev.report_label 'Differs'
            ELSE NULL
       END status
  FROM prod FULL OUTER JOIN dev ON (prod.column_alias = dev.column_alias)
ORDER BY NVL(prod.column_alias, dev.column_alias)
;

I used "prod" and "dev" as identifiers for the different settings.
This time I was mainly looking for the report labels, but you can easily extend the query to include additional fields from the APEX_APPLICATION_PAGE_IR_COL view.

This has been in my stash for a while but I didn't publish it before.
Oliver Lemm just started a series of APEX Metadata related posts and made me think let's share this snippet.

May the APEX Dictionary be with you!

DOAG 2014 Conference - Program announced

The conference program for DOAG 2014 is available.
It's even harder than last year to plan because there are so many interesting sessions running in parallel.

However one session was easy to choose

APEX und OOXML, Office trifft Web

for one simple reason: I'm the presenter. ;-)

I think the time-slot Thursday 14:00 - 14:45 is also a pretty good one.

Means I have enough time to recover from the community event Wednesday evening...

See you all at DOAG 2014!

   Moritz