Advanced Interactive Reports

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.

Let the Party Begin!

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

Blog Tag - 8 Things you may not know about me

Well thanks to Flavio I am on the hook for providing some information about me you may not know already.

• I HATE the cold! That is why I now live in Northern Indiana which as well as getting a large amount of "lake effect" snow also has the added bonus of getting strong winds that yesterday had the temperature @ -20F. Today the wind is down but the temperature isn't exactly balmy :(
• 

• I grew up in Brisbane, Queensland Australia in Sub-Tropical weather and I LOVE heat and humidity. (These are the night time temperatures)
• 
• My beautiful wife is also Australian
• I have three wonderful kids - My two girls were born in Australia while my son was born in New Zealand but had an Australian Passport by the time he was two months old. They all have Australian accents with some American creeping in.
  
• My favorite sport is Australian Rules Football - My wife also loves watching for completely different reasons. I used to play in my formative years and it is just such a fast paced exciting game.
• I really enjoy building elaborate creations with the kids using Lego. My father just sent over his HUGE Meccano collection so now my kids and I are going to be building even more strange and wonderful thingamebobs - I love seeing their imagination and creativity at work.
• As a family we love playing Board games such as Monopoly, Risk, Sequence, Mouse Trap you name it (we have two old trunks full of games and puzzles) or card games. The biggest problem is finding a time when we can all get together and just have some family time.
  
• I will listen to all different types of music (Modern, rock, oldies, classic, soul, even rap, etc... just not Country & Western) and generally like the music my kids are listening to - which is probably just as well as I often have very little choice but to listen to it - but when I need to write code (especially complex code) I prefer dance/techno as the strong bass beat allows me to zone out all the other distractions, though I rarely listen to it except when coding.
• Scary movies are not my thing at all ... The scarier the movie the more likely I am to be cowering under something - Not my cup of tea at all!
• In my almost 15 years with Oracle I traveled more in my first year as a PM then I did in the previous 13.5 years as an Oracle Consultant - Most Oracle Consultants are TRUE road warriors who are on the road week-in week-out, but I was on several long term assignments and didn't travel at all (other than moving my family from one city to another)
  

Well that took for ever to finish :)

Would you like to be a Certified Oracle Application Express Professional

Firstly Merry Christmas and a Happy New Year :)

Oracle University is conducting a survey (written in Application Express of course) to determine the demand for creating a Certification Program with Oracle Application Express.

Please take a few minutes to fill out the survey http://apex.oracle.com/pls/otn/f?p=33676:1
We need as many people as possible to take the survey to make this a reality. The deadline for getting feedback is mid-January so please don't delay just click on the link, a few minutes now will help the whole Application Express Community!

The information entered will only be used to evaluate the demand for the program.

You can also elect to be emailed when the Oracle Application Express Advanced Course (currently under development) is available.

Be safe over the holiday period!

Regards,
David

UPDATE: Thank-you to all of those who took the time to complete the survey. We had a great response but unfortunately Oracle University will not be building the Certification Program YET. They have other certifications they are currently building and will reassess Oracle Application Express hopefully in the near future.

Oracle University are still pushing hard to finish the Advanced Course and for those who checked the notification you will be sent an email as soon as it is available.

Thanks,
David (14-Feb-2008)

Early Adopters 3.1 Now Available

The Oracle Application Express Team is pleased to announce the debut of our 3.1 release for public evaluation.

To access our hosted instance, please register.
If you have participated in a previous evaluation, you will bypass the short survey.
After registering, you will be provided with access to the evaluation instance from which new accounts can be requested.

You will need to request a workspace and then wait on the email notification.

The new feature that we would really like feedback on is our new region type - Interactive Report. This is a new SQL query based region type that lets end-users customize which columns they see as well as column order. End users can also, sort, filter, break, sum, highlight, and add computed columns using simple pull down menus.

The full list of 3.1 features available with this Early Adopters release is:

• Application Builder
  
  • Interactive Report Regions *
    
  • Templates
    
    • Improve Searching of templates
      
    • Replace Templates
      
    • Publish Templates
      
    • Bulk Unsubscribe of templates
      
    • 2 New Themes
    
    
  • Wizards
    
    • Allow for early exit when creating HTML region
      
    • Create Interactive Report Wizard
      
    • Remove 32k limit on Create Application on a Spreadsheet
    
    
  • Addition of Application Groups
    
  • Simplification of Page Group pages
    
  • Addition of Application Date Format
    
  • Support for Declarative Media Type
    
  • Can view existence of run-only application (but cannot edit them)
    
  • Display Application Name in App Builder footer
    
  
  
• Workspace Administration
  
  • Workspace-specific Builder Notifications
  
  
• Runtime Installation
  
• APIs
  
  • Augment APEX_MAIL API to support adding attachments to e-mails
  
  

Please remember, the success of this Application Express 3.1 evaluation depends on user feedback. All participants are strongly encouraged to provide feedback using the feedback application. The link is also available from within the hosted Application Express 3.1 evaluation instance Home page.

Thank You!

The Application Express Team

Statistics on apex.oracle.com

Every week the APEX development team collects statistics on our FREE hosted environment apex.oracle.com

For the Week ending 11/18/2007:
1,647,648 total page views for the week
2,076 distinct applications accessed during the week
2,773 distinct users logged in throughout the week
243 new workspaces approved in the past week

Total Workspaces: 11,290

As you can see from the numbers given, this is a very active APEX site.

You may or may not be aware that when you are within the APEX Development environment then you are actually running APEX applications.
Our development team uses APEX Development and PL/SQL packages, etc. to build the components you use to build your applications.

Keep sending in those details on your large applications!

Hardware
The database server is a Dell PowerEdge 1950, 2 dual-core CPUs @ 2.33 GHz. The machine has 16GB RAM with NAS for storage. I have no details on the Web server machine(s), as these servers are shared by other instances, and not only for apex.oracle.com.

Regards,
David

The Bigger the Better

I'm often asked for examples of "large" Oracle APEX installations.

A friend of mine (Mr. B) is working with a large customer who asked that question of me again ... so I thought I would use this blog to ask YOU for further examples and also relay my answer to these sorts of queries.

What I want is examples of applications with a large number of concurrent users and/or a large number of pages. The more detailed information the better.

{Our policy is to not publicly use any company or organization names unless we have authorization to do so. Further to that, I am always interested in getting new references but that is entirely up to you, so please indicate if you would be interested}

If you prefer you can email me directly rather than replying to this blog.


As for my response to "Is APEX popular and can it scale to support large numbers of users?":

As a starting point they can look at the APEX OTN Page and then Community and Partners where you can see the large number of Consulting companies, Hosting companies, Internet Applications, even Commercial Applications. There is also close to 30 different people blogging about APEX. Some of these sites have very large user bases (Metalink, Think, Ask Tom, etc.)
Oracle has several very large internal sites running Oracle Application Express including sites such as ARIA People Search (also available as a Packaged Application) and sites for tracking customers, etc.

A review of some of our internal lists, on who is using Oracle APEX, reads like a who's who of the Fortune 1000 and also includes numerous government agencies and public institutions. The popularity amongst Universities around the world is growing rapidly as they have discovered what a great tool Oracle APEX is for teaching Web design, SQL, and database fundamentals.

We also have some large customers in the process of migrating commercial Oracle Forms applications to APEX (Some with 1,000's of Forms).

The APEX OTN Forum is the third most popular Forum on OTN behind SQL & PL/SQL, and General Database (http://forums.oracle.com/forums/category.jspa?categoryID=18)

Our Packaged Applications are downloaded an average of 3,000 / month.

The free hosted instance on http://apex.oracle.com receives an average of 1.4 to 1.5 million page views a week and now hosts over 12,000 Workspaces.

The number of sessions at Oracle Openworld is growing annually (see earlier blog) which all goes to show how popular APEX has become [15 Sessions, Hands-On-Lab, 2 DemoGround booths, etc.]

To highlight the importance of APEX within Oracle - with 11g APEX is now included in the seed database. Therefore, whenever 11g is installed so is APEX, it is no longer on the Companion CD.

I could go on but you get the idea ... What I really need is some customer details on those big applications I know are out there. Personally I am sick of hearing (from ill informed people) what Oracle APEX supposedly can't do - I want more ammunition on what it DOES do.

So please get back to me with details on those Oracle APEX applications and the Bigger the Better.

Regards,
David

Oracle Openworld Sessions

For anyone attending Oracle Openworld in San Francisco we have put together a paper on all the Oracle Application Express sessions, hands-on-labs, demo ground details, etc.

From the link above, select 'Customized Schedules' in the Sessions, Keynotes and Exhibits section and then scroll down to the Technology "Focus On" papers.

I suggest you download the FOCUS ON Database Application Development – Oracle Application Express paper to ensure you don't miss any of the great APEX sessions and labs.

Another useful "Focus On" paper is FOCUS ON Database Application Development – Oracle SQL Developer.

Look forward to catching up with lots of people throughout the event.

Latest Statement of Direction

We have updated our Statement of Direction in respect of 3.1 and 4.0.

One of the biggest changes is the fact that "Dynamic Reporting Regions" feature has moved from 4.0 up to 3.1. Carl Backstrom's blog Oracle Application Express (APEX) 4.0 - Preview Video shows a video of what sort of functionality to look forward to.

Other notable enhancements now planned for 3.1 include improvements to the PDF report layout and enhanced print attributes allowing for more control of PDF document structures.

Previously our plan was to not include any UI changes in 3.1, therefore, we would not need to go through and translate the source. Obviously introducing this new region type and changes to our PDF printing screens includes significant UI changes.

The down side of this is that it will take us longer to release 3.1 but the upside I think will make the wait VERY worthwhile.

For those of you attending Oracle Openworld we are planning on having one of our Demo machines running our "Pre-Production" version of 3.1. We can't guarantee that everything will function 100% (it is pre-poduction after all) but we should be able to give you a nice sneak peak of what to expect. So please stop by the Demo Grounds and say G'day!

New OTN Feature Casts

I know there have already been several blogs on Scott Spendolini's podcast with Oracle Magazine.

We have gone and added a link on our Podcasts and Viewlets page to his podcast together with one from Tony Jedlinski. These and other excellent podcasts can be found by clicking on the Podcasts link on the left hand column of all OTN pages and then selecting Oracle Magazine Feature Casts.

The official blurb on each of these podcasts:
Oracle Application Express 3.0: A Closer Look (Sep 18, 2007) Scott Spendolini, CEO, Sumner Technologies, speaks with Oracle Magazine senior editor, Jeff Erickson, about the benefits of Oracle Application Express 3.0 and how it works with the Oracle database. Learn how to get started customizing this powerful tool to meet your business needs.

Catching the Express (Jun 25, 2007) Tony Jedlinski, Oracle Magazine author and editorial board member and the Director of Web Content for the IOUG, talks with Oracle Magazine about his use of Oracle Application Express at the IOUG today and the upcoming Oracle Application Express-based OPEN project.

Given the vast experience both Scott and Tony have with Oracle APEX, I certainly enjoyed listening to them and getting their perspectives.