A friend dropped me a line saying her Apex app’s Interactive Report wasn’t working for her users anymore and she asked if I knew what could cause it.  She said it worked for her in the Apex development environment, but not outside.  I talked her into giving me an account on her Apex workspace and started digging through it.

For me in the developer environment, it wouldn’t do any of the JSON updates, like search for text, filter, change columns, sort, etc. – all the cool things we love about IRs.  I’d seen this before, but it’d been so long I’d forgotten.  I finally dug into the page template and noticed that there wasn’t much inclusion of external CSS going on and when I added that in, the report worked.  So I sent her that as “fixed!”  I couldn’t figure out how this application could have worked before, but had stopped!

Unfortunately, she wrote back saying that didn’t do it for someone running outside the development environment!  Aaagh!

But I love a good challenge, so I dug back in.  I got another session going in another browser so it was outside Apex’s environment.  I noticed a difference between the two user sessions, that in the Development Environment, it showed my username on the top of the page, but not when I wasn’t.  That was the hint I needed!  It reminded me that the Interactive Reports store your queries by the APP_USER name into the database.  No user name, no interactivity!

It turned out that this was a new application, she was using a custom authentication method that was new to her and she wasn’t setting the APP_USER value!

I’d used this same custom authentication method and found I needed to call

APEX_CUSTOM_AUTH.SET_USER(p_user => 'some custom value');

in the Post section of the authentication method.

Once that was set, everything worked great.

It was only after I told my friend the solution that she said that this was a new application she was building.  Oops!  If she’d said that up front, I might have figured out what was wrong sooner!  :-/


All I can say is Wow!

That’s it.  Nothing more here…

Read the rest of this entry »

Update: April 16, 2009 – I found and fixed a bug in how it handled the contains clause in the filters.  Stupid me, I’d used contains in other flavors of SQL so never bothered to confirm that Oracle SQL supports it!

Sorry about that!!!

The purpose of this package is to grab the user’s current Filters and Searches and recreate them in a WHERE clause, so I could do some external reporting.  A couple other people have shown me their solutions to this but this is my original work (with a hint from Anton and Shunt).

The little bit extra mine adds that I haven’t seen elsewhere is that it supports User-Saved Reports.  So if a user clicks the report tab for one of their saved reports and possibly customizes it, my routine can figure that out and recreate their current query.

It also supports the user typing a value into the Search box, which Apex matches against all text and numeric fields currently displayed.  That was an amusing trick!  If you care, you can view the query that IR generates by using Debug mode.

What this code does not support is a sort order, since my external report tool has a fixed sort.  Shunt did something that works with sort orders that you should check out here:


If you compare how others handled the various filter options (IN, CONTAINS, etc) versus how I did it, I think you’ll prefer their method.  I know I did, and wished I could have figured it out when I was writing this!  :-/

From the IR report page (e.g. 2), you add the following Javascript to the Page HTML Header:

<script language="JavaScript" type="text/javascript">
function SaveAndRunReport() {
            'P7_IR_REPORT_ID:'+$v('apexir_report_id'), 1024, 768);

You can certainly make a simpler call, mine just happens to use a pop-up page.

In the target page,  (e.g. 7), you can display the WHERE clause by creating an item computed with the following SQL query:

SELECT apex_ir_query.ir_query_where(:APP_ID,
           :P7_IR_REPORT_ID) FROM DUAL;

Here’s the package.  It’s hosted on MediaFire, so you’ll have to live with the ads.

Let me know if you find it useful or have suggestions to offer.

– Stew

Is there some reason that my Parent tabs don’t display in the order of their sequence in the Development environment?

For example, here’s what the users see with the sequences:

Tabs as displayed in my application

But here’s what I see in the Development page.

Tabs in Development

I find this pretty confusing, anyone else?

I realize that you can have multiple tab sets (which confuses me even further at times!) but these are all in the same tab set.

Apex Training days

November 12, 2008

We’re bringing an Apex trainer to campus for the next 3 days to get everybody (15 of us including a few managers auditing?) rolling with their Intro to Apex.

Anton from Concept 2 Consulting is coming up from the Boston area.  We’ve talked with him enough already that he should know pretty well what we’re after.  I’ll be sitting in the back listening for gems and connecting information that I haven’t already learned and trying not to act like a know-it-all!

Really, I’ll try.  But I’m currently developing on my fourth Apex application and working my way through John Scott and Scott Spendolini’s mid-level book “Pro Oracle Application Express” (which is pretty good so far).

Apex Navigation Bar Entries

October 15, 2008

Maybe someone can suggest a better way?

I’m at a loss to understand why I can’t easily stack these nav bar entries.

Nav Bar with no New Line


Notice that Help is on the same line as Logout, because I have both set to not start on a new line.

But I’d really prefer to stack them over eachother.

Nav Bar with New Line on Logout


I’ve tried setting each nav bar entry to start on a new line, but when I do that, the default Logout entry shows up on the far left, above the application title, as you can see above.

But if the Help sequence is before the Logout entry’s sequence (which is what I want), why does Logout appear on the previous line???

Forcing them to Stack


The only way I could stack them the way I want is to add <br> at the end of the Icon Subtext for the Help entry.

Does anyone have a better way?

BTW, I created this post with a new documentation tool I’m trying out – ScreenSteps2.  It lets you quickly take screenshots, annotate them, add a bit of text and output to PDF, HTML, a blog/wiki or their hosting site.  Very quick, very basic, decent output, cheap price.  I’m going to try to get work to pay for it, but it’s only $40, so very affordable.

…  Monday

[Please excuse the weird formatting of this post.  I’m having compatibility troubles between EverNote (which I took notes with) and WordPress.]

Great conference for Apex Developers. Tons of sessions, many very good quality.  The following session just finished and unfortunately I wasn’t as impressed with the presentation of this one.  Though I’ll bore you with my notes anyway. (?)

Using the Apex Dictionary Views to increase the quality of your Apex Applications – Karen Van Hellemont, iAdvise
Read the rest of this entry »