David Peake

I will be traveling to Canada next week for the Quebec APEX User Group Official Launch. It is my first time in Quebec and am really looking forward to meeting up with the APEX community in the area.

The launch starts off in Quebec on Wednesday May 14 and then Montreal Thursday May 15.

This event has been organized by Francis Mignault from Insum and the local Oracle team.

So if you are in the area please join us and support the local APEX community.

This is certainly not my first time to Canada - I used to live in Michigan (very close to Detroit) and went into Windsor, Ontario almost every week in the summer to play golf. My sister also lived in Toronto for a while so I know the boring 401 and slightly less boring 403 highways very well.

Given where I lived, I am a huge fan of the Detroit Red Wings, arguably the best team in the NHL {Happy to argue with you over this} so will be eagerly watching them destroy the Dallas Stars while I am there :) .

Regards,
David

I had the great pleasure of attending the Paetec - Pinnacle User Conference being held in San Francisco this week.

Pinnacle is the software division of Paetec which is a large telecommunications company in the US. Pinnacle is an application suite for telecommunication companies for expense management. They provide their suite as either Licensed (You get the source and install internally), Hosted (Installed on Pinnacle servers but you run everything) or Managed (Installed on Pinnacle servers and they run the suite for you). What is especially cool for me about Pinnacle is that they completely rearchitected their solution to use Oracle APEX. So here we have a commercial offering that is also an enterprise, software-as-a-service application suite built using Oracle APEX.

The latest Oracle Magazine's (May/June) feature is Developer Nation and includes a great write-up on Pinnacle. Well worth a read and goes to show the flexibility of Oracle APEX.

Oracle APEX + Enterprise + SaaS + Commercial suite is just gold dust for me and look to see / hear more about them in the future.

Unfortunately, I had to do a quick drop in talk and fly out again but it was certainly worth the trip. I conducted an APEX session which went well despite being in a long room with huge pillars in the middle. Got through my presentation, got to the Q&A section, the questions were flying from all-over, then the sirens went off - a "false" Fire Alarm ... One way to bring a session to a screaming halt!

While I was there though I did get to spend some quality time with the Pinnacle team and was very interested to learn and see how they have built a very interesting development framework - Special thank-you to Dennis and Erik and the rest of their team. Carl Backstrom was also in town for the Web 2.0 conference (it was very dangerous Tuesday night with Carl, Christian Shay and myself roaming the unsuspecting streets of SF) so I dragged him along to look under the covers and we got some useful ideas that may well make it into future releases.

The Pinnacle folks (staff and customers) all like to have a good time so it was a shame I had to get the red-eye out. Had to get some rest before driving to Indianapolis {3 hours each way :( }

For those of you in the Indianapolis area I am presenting at the INOUG meeting on Friday. Looking forward to going to my home state's user group meeting for the first time.

Later,
David

Well as Dimitri hinted at here we {The proverbial "we": I actually mean the development team ... I just talk about things - the rest of them actually make the magic happen :) } have been hard at work since releasing 3.1. and have lots of fun plans.

Once we have some more of our ducks in a row we will update our Statement of Direction on OTN. But here is some unofficial insight into what is on the drawing board (Not to be used in any way in making purchasing decisions).


With 3.1 we introduced a lot of new code and despite the best efforts of all of you wonderful Beta testers and our tireless QA team there were a collection of minor bugs found (I personally found a few very obscure undocumented features, why because when I develop applications I often use Oracle APEX in obscure ways). Therefore, we are busy developing a 3.1.1 release to fix issues.


Our team seems to get bored easily, it would seem, because we never seem to be content working on just one thing at a time. So while the push is on to get 3.1.1 out the door we are also working on Forms Migration. We already have a page, Application Express for Forms Developers, which addresses some of the key similarities of the two tools, but now we are working on a new whitepaper which should help you migrate your existing Oracle Forms applications to Application Express.

But that's not all! We are also working on additional built-in migration project capabilities. In much the same way we have the current 'Application Migration' section where you create a Project to migrate Access across we hope to provide the ability to provide tools support for migrating Oracle Forms applications. Using Oracle Form's XML conversion program we are looking to interpret the XML produced and derive the underlying design model to be able to generate Application Express components. As with the Access migration we don't expect to achieve 100% conversion but hopefully provide a valuable first-cut.

One very important difference between migrating Oracle Forms and Access is that Oracle Forms already utilizes the Oracle Database which means some of the messiest parts of migrating the Access application is simple not applicable. Another key differentiator between migration efforts is the developers. Oracle Forms developers have the perfect skill set for developing Application Express, namely SQL & PL/SQL used within a declarative framework. They already know all about the tables, views, packages, procedures, functions, etc. that the Forms were built on top of so most Oracle Forms developers should come up to speed very quickly.


We certainly haven't forgotten about Tabular Forms. Now that we have Interactive Reports and beefed up Printing & Report Queries to take care of many of the users viewing/reporting requirements it is time to focus on how the user maintains the data, and that means improving Tabular Forms. We hope to provide better declarative functionality to make it easier to meet your business requirements when entering data without needing extensive manual coding.


In the midst of all this we are also working on numerous other whitepapers on various topics. The first I expect to see on OTN (hopefully in the near future) is the APEX on RAC whitepaper.


Looking at what's on our plate and what we plan on doing next - I'm getting tired just writing this post. I hope this unofficial update helps to shed some light on what we're planning.

Regards,
David

Last year Carl Backstrom was holding down the fort for the Application Express team.
This year I will be there with David Gambino primarily in the Exhibition hall demonstrating APEX and SQL Developer to anyone who will listen.
So if you get a chance drop by and say hello :)

There are also a number of APEX related sessions within IOUG (unless otherwise stated):

• APEX SIG Meeting & Roundtable - Dimitri Gielis, John Scott, Tony Jedlinski, David Peake
  


• Oracle, LDAP and AD (Active Directory) Integrating the Three Together - Debra Addeo, Douglas County School District


• Apex, LDAP and Active Directory - Debra Addeo, Douglas County School District


• Taking APEX Applications to the Next Level - Todd Arave, Intermountain Healthcare


• APEX Cheat Sheet - Karen Cannell, Integra Technology Consulting


• APEX Under the Covers - Karen Cannell, Integra Technology Consulting


• APEX Debug Options - Karen Cannell, Integra Technology Consulting


• Effective Development with Oracle Databases - David Gambino, David Peake, Christian Shay, Kuassi Mensah, Oracle USA Inc.


• Creating advanced charts in Oracle Application Express - Dimitri Gielis, APEX Evangelists


• [AUOG] Integrating the Hiring Process with Oracle HMRS - Anthony Golden, Douglas County School District


• The UPs and DOWNS of Loading External Data via APEX - Tony Jedlinski, Konoso LLC


• Doing Web 2.0 with Application Express (APEX) - Tony Jedlinski, Konoso LLC


• Add Style to Your APEX Apps Using .css - Tony Jedlinski, Konoso LLC


• Extend your existing Business Solution with Application Express developed functions - Wolfgang Scherrer, infomArt GmbH


• Using External Tables with Application Express - David Scott, Intec Billing


• Creating Visual Impact with Custom APEX Templates and Themes - David Scott, Intec Billing


• Housewife meets HTMLDB - Nimmi Verpati

If you want to download a spreadsheet of all these sessions simply go to our OTN APEX Events page.

Look forward to seeing many of you there!
Cheers,
David

Simple Upgrades from Classic to Interactive Reports
So you have been playing with Interactive Reports and you know what an impact they will have on the way your users interact with the data - Now to get serious about replacing Classic Reports and going beyond playing with Interactive Reports to implementing them.

We provide a very nice "Migrate to Interactive Report" Wizard (Right hand Tasks panel when editing a Classic Report) which renames the old region appending " - Deleted" to the region name and changing the Condition to "Never". The new Interactive Report will have the same name and region number as the original Classic Report.

If you have old "manual" search regions you should remove them (I suggest you set Condition to "Never" until the new Interactive Report is fully tested) as that functionality is now built in. Make sure you also update the SQL for the Interactive Report removing the corresponding WHERE conditions and move buttons as appropriate {There is a new Button Position "Right of Interactive Report Search Bar" available}.

Here is a quick example from the Bug Tracker Packaged Application:
Old Screen with Classic Report -



New screen using Interactive Report -



Ok so this is hardly earth shattering and hardly does justice to the heading of this post, namely the "Advanced" bit, I added it just to outline how easy it is to upgrade your Classic reports and some of the easy tricks to make migration simpler.

Now you have done all the easy upgrades ... How do you tackle some of the trickier stuff?

Turning off Interactive Report Options
If you want to prevent users from performing any of the default Interactive Report actions then you can simply go into the Application Builder and select the Interactive Report Attributes. This screen is similar to Classic Report Attributes but with some distinct differences. The following sections are all specific to Interactive Report Attributes:

• Search Bar - Manage the Search bar and Actions Menu options
• Download - Specify available download formats (CSV, XLS, PDF, RTF) and details
• Link Column - Specify the "Link" column as either 'Link to Single Row View', 'Link to Custom Target' or 'Exclude Link column'. It comes complete with various items for controlling the look, target, condition and authorization.
• Advanced Attributes - Report Alias and ID needed for accessing from APIs etc, Show saved reports as Tabs (If not use a separate page with a list of reports), and Page Items to submit

Additionally if you want to disable users options when they select a specific Column Heading then edit that Column (from the Report Attributes screen) and modify the options given under 'Allow Users To:'

NOTE: Before switching off the Actions Menu I STRONGLY recommend you running the report and configuring the columns, filters, sorts, etc. exactly as you want them and then "Save Default Report" and then turn off the Action Menu.

Oracle By Example (OBE)
Marcie Young , our curriculum developer, has done a wonderful job of creating several OBEs for this release. I suggest you go to our APEX OTN Site and look at them here: Oracle By Examples. Specifically go through the Utilizing Advanced Interactive Report Region Techniques in Oracle Application Express 3.1 for a good run through Interactive Report advanced features.

Resetting Interactive Reports
End-users can make all sorts of changes to their view of the data and even save off their own reports. Now the end-user wants to start fresh and go back to the Default Layout - Simple he/she just clicks on the action menu and selects Reset.

What if you have hidden the action menu? There are any number of reasons you may want to utilize Interactive Reports but not give the end-user full capabilities.

The answer lies within the documentation: Linking to Interactive Reports. Effectively we have provided the ability to manipulate an Interactive Report from a link.

For our "Reset" example the solution is to create a button with an action of 'Redirect to URL without submitting page' with Clear Cache specified as 'Page Number,RIR'.

RIR is equivalent to the end-user pressing the Reset and will return the Interactive Report back to the default layout.

Using a Search Region
Now lets say we have a report where the users need to enter multiple criteria hit a "Search" button and have the report display the resultant set of data. Using Classic Reports this was as simple as defining additional WHERE conditions within the SQL which optionally inspected the fields for the given criteria.

Can I use the same WHERE Conditions in an Interactive Report? Yes you can and it will work in exactly the same manner as a Classic Report whereby only the subset of the data will be displayed.

An example using the Interactive Report within the New Features App -



The additional WHERE Conditions are:
and (DEMO31_EMPLOYEES.SALARY > :P2_SAL_LOW OR :P2_SAL_LOW IS NULL)
and (DEMO31_EMPLOYEES.SALARY < :P2_SAL_HIGH OR :P2_SAL_HIGH IS NULL)

Let me illustrate a different solution where instead of using the WHERE conditions you use the linking capabilities to create filters based on the criteria entered .

If you remove the WHERE conditions (for the search criteria) from the Interactive Report Source then you can use the linking capabilities to create filters directly on the report.

Same Interactive Report using Linking -



The Branch now includes:
Set these Items: IRGT_SALARY,IRLT_SALARY
With these Items: &P2_SAL_LOW.,&P2_SAL_HIGH.

Now you may well ask "Why bother?" the results are the same in both. Well yes they are sort of in that the data initially displayed is the same. The big difference lies in the fact that when you use the linking option the users can easily disable any of the filters and see additional data.

Alternatively users can now select the filters and modify them to enter different values, etc.

If you do not want the users to be able to even edit the filter displayed, but still be able to enable / disable the filters you will need to specify the link on "HIDDEN" columns.

Same Interactive Report using Linking on HIDDEN columns -



I added two additional columns (HIDDEN) to my source:
DEMO31_EMPLOYEES.SALARY AS SAL_LOW
DEMO31_EMPLOYEES.SALARY AS SAL_HIGH

The Branch was updated to:
Set these Items: IRGT_SAL_LOW,IRLT_SAL_HIGH
With these Items: &P2_SAL_LOW.,&P2_SAL_HIGH.

Now you have restricted filters that can only be enabled / disabled but can't be changed. The downside is you may have to add additional columns, as I did in my case, if you still want to see the column being filtered within the report.

Link One Interactive Report to Another
The OBE uses the same linking capabilities outlined above to link one Interactive Report to another Interactive Report. This is probably the most common use case but I wanted to show a different take on using the linking capabilities to help you identify additional use cases.

If you want to learn how to link two Interactive Report Regions together please read the OBE (given above).

Gotchas
Keep an eye on the 3.1 Known Issues & Limitations as we have found a few issues with all this new code. In fact when doing the sample application as part of this Blog I found a few issues with the linking.

As you have already probably heard from one of the many mails sent out or from the OTN Forum or one of the many Blogs Oracle Application Express 3.1 has hit the street.

So while many in the community are madly downloading our Latest and Greatest I will be relaxing (I hope) and having a few drinks {Just keep em coming bar keep}.

The APEX Developers, Testers, Documenters, Translation folk, etc. have all worked exceedingly hard and I hope you enjoy the fruits of their toil.

We have also revamped a large majority of out OTN Site with new and updated information.
Our curriculum developers have already produced 3 Oracle By Example (OBEs) specific to 3.1 and are working on several more. I strongly recommend you have a look at these. We have even developed one on Interactive Reports for end-users.

As always we need to also thank-you the community for your invaluable input through the Early Adopters and on the Forum offering your feedback, suggestions, logging issues, etc. APEX is that much better and stronger due to all of your contributions.

I hope to Blog shortly on some of the "advanced" stuff you can do with Interactive Reports.

Have fun playing with 3.1 and please be quite in the morning I may be slightly under the weather!

Cheers,
David