November 4, 2010
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!
December 22, 2009
All I can say is Wow!
That’s it. Nothing more here…
February 19, 2009
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!
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, 2, :SESSION, :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.
December 8, 2008
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:
But here’s what I see in the Development page.
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.
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).
June 16, 2008
[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 »