I thought I would follow up a previous blog post on the Ext JS layout plugin I’m building for APEX, it’s 95% complete and the results are making me go WOW and I thought I would share the “high” that I’m currently feeling.
Amid the task of documenting the plugins (the above screenshot) I started to tweak and enhance certain parts of the code to add in more flexibility. On a side note, I’m think I’m my own worst enemy at times because I find it so hard to resist improving things which I’ve read is a typical developer trait. The benefit in this case though is that I’ve coded in the ability for multiple layouts to be defined on a single page which don’t consume the full browser window. What this means is that a border layout can be defined within an existing APEX region and sized accordingly, either to fill the width of the region or using a fixed setting defined within the plugin settings.
Here’s an example of what an Ext border layout looks like which generally is used to fill the browser window.
and here’s a screenshot of an APEX page with 2 border layouts defined in 2 APEX regions with a bunch of editable grids and trees defined in a nested layout structure. I have to say that testing has been made extremely easy by being able to copy regions and their sub regions in a few mouse clicks. A big thanks to the APEX dev team on that 4.0 enhancement.
Now the above is a pretty dense example and has been built to highlight the flexibility of the plugin and does not necessarily mean you’ll build applications this way. The benefits of adding in a layout that can be controlled in size means that it can be easier to add the layout plugin into existing APEX applications and also embed within actual websites. The layout can either be a border (as per the above screenshot), tab, accordion, column, stacked, or single stretched region. Here’s an example from the Dow Jones Indexes to give you an idea…
I’m pretty excited about what we can do with APEX and Ext JS. What’s currently missing is Ext JS charts, forms, menu’s, and an Ext Super LOV (Dan you’ve inspired me) but those are coming very soon and here’s a screenshot of the tree grid that’s almost ready…
Once the above plugins are completed the next step is to build some amazing APEX applications to showcase APEX as a heavyweight contender in the UI development arena, and if you ask me it’s already got the serverside belt.
Recently I saw a question on LinkedIn asking how to create Matrix style reports in APEX and found the discussion quite interesting as there were a mix of answers but nothing really concrete on how you could create one. So I thought I’d shed some light on a technique I have been using quite successfully for a number of years, with the most recent implementation just a few weeks ago.
About 4 years ago I came across a couple of posts from Anton Scheffer and Lucas Jellema from AMIS which described the use of a custom type which allowed you to pivot the last 2 columns of your query and turn it into a matrix style report. The great thing was that it is quite straight forward to use in APEX as we can use generic columns for our classic report using the option “Use Generic Column Names (parse query at runtime only)” e.g.
and for the report columns we have the option of either using “Column Names” or “PLSQL” e.g.
Here is the link to download the PIVOTIMPL function code written by Anton. I take absolutely no credit for this amazing bit of PLSQL code. All that I have done is write a simple wrapper around generating the SQL statement, replacing any substitutions, and replacing binds with the “V” function. I am considering updating Anton’s code in the future to accept binds that reference APEX session state items to boost query performance as on large data sets there can be significant degrade on performance using the “V” function.
To define your Classic report you can use a report type of “SQL Query (PLSQL function body returning SQL query)”
with a PLSQL function body returning a SQL query, similar to the following:
RETURN pd_matrix_reports.generate_matrix_query ( p_query_stmt => 'SELECT dept_directorate_id'|| ', dept_directorate'|| ', region_id'|| ', region'|| ', manager_id'|| ', manager_name'|| ', period_cal_year'|| ', period_cal_year||'' ''||lpad(period_no,2,''0'') period'|| ', end_year_count_link end_year_count '|| 'FROM pd_pdp_period_manager_vw '|| 'WHERE (dept_directorate_id =:P54_DEPT_DIRECTORATE_ID OR nvl(:P54_DEPT_DIRECTORATE_ID,''ALL'') = ''ALL'') '|| 'AND (region_id =:P54_REGION_ID OR nvl(:P54_REGION_ID,''ALL'') = ''ALL'') '|| 'AND period_cal_year = nvl(''&P54_PERIOD_YEAR.'',period_cal_year) '|| 'AND ( '|| 'instr(upper(nvl(manager_name,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(manager_name,''ALL'')))) > 0 or '|| 'instr(upper(nvl(region,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(region,''ALL'')))) > 0 or '|| 'instr(upper(nvl(dept_directorate,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(dept_directorate,''ALL'')))) > 0 '|| ')' , p_parse_query => FALSE );
and if you need to further filter the actual result set produced by the pivot operation (this is the actual data returned from the table cast so your pivoted data will now be in columns) you can concatenate the resulting string with an additional where clause e.g.
RETURN pd_matrix_reports.generate_matrix_query ( p_query_stmt => 'SELECT dept_directorate_id'|| ', dept_directorate'|| ', region_id'|| ', region'|| ', manager_id'|| ', manager_name'|| ', period_cal_year'|| ', period_cal_year||'' ''||lpad(period_no,2,''0'') period'|| ', end_year_count end_year_count '|| 'FROM pd_pdp_period_manager_vw '|| 'WHERE (dept_directorate_id =:P54_DEPT_DIRECTORATE_ID OR nvl(:P54_DEPT_DIRECTORATE_ID,''ALL'') = ''ALL'') '|| 'AND (region_id =:P54_REGION_ID OR nvl(:P54_REGION_ID,''ALL'') = ''ALL'') '|| 'AND period_cal_year = nvl(''&P54_PERIOD_YEAR.'',period_cal_year) '|| 'AND ( '|| 'instr(upper(nvl(manager_name,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(manager_name,''ALL'')))) > 0 or '|| 'instr(upper(nvl(region,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(region,''ALL'')))) > 0 or '|| 'instr(upper(nvl(dept_directorate,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(dept_directorate,''ALL'')))) > 0 '|| ')' , p_parse_query => FALSE )|| ' AND EXISTS '|| '( SELECT NULL '|| 'FROM pd_access_vw '|| 'WHERE dept_directorate_id = v.dept_directorate_id '|| 'AND region_id = v.region_id '|| ')'|| ' AND "2011/12 08" > 0';
In the above we can use a column filter on an actual data value e.g. “2011/12 08″ since our second last column has been transposed into columns thanks to Anton’s magical code. I say magical because it’s not the easiest piece of code to understand, but if you’re interested you can find more here’s the 11.2 documentation reference.
Here’s an example screenshot of 3 reports which use this technique to pivot 13 calendar periods in a year…
I can’t thank Anton, Lucas, and AMIS enough for documenting and sharing this code as it’s been a life and time saver in lots of past situations, so I hope you find it as useful as I have within APEX. The only thing you may want to keep your eye on is the dynamic creation of TYPES within your application schema which the code seems to be creating e.g.
Today seems to be going from bad to worse. Not only am I fighting a number of IE6 issues with APEX and CSS I’ve run into a couple of issue with tabular form validations in APEX 4.1.
Please Note: the workarounds which I’m about to describe in this post are not supported by the APEX team and that they may not work in future versions of APEX. Please proceed with caution. I take no responsibility for any negative outcomes from this.
To circumvent the problem I created a “PLSQL Function Returning Boolean” validation which runs after all others validations and strips the line breaks for the first two columns e.g.
BEGIN FOR i IN 1.. apex_application.g_f01.count LOOP apex_application.g_f01(i) := regexp_replace(apex_application.g_f01(i), '('||CHR(10)||'|'||CHR(13)||')',''); apex_application.g_f02(i) := regexp_replace(apex_application.g_f02(i), '('||CHR(10)||'|'||CHR(13)||')',''); END LOOP; RETURN TRUE; END;
The point to note is that our validation always returns TRUE as we’re not actually validating anything, just running some PLSQL to strip the line breaks. I find this hack quite useful in certain situations, since we can’t use computations as they are bound to page and application items. In order to work out which f0x array you need to strip the EOL characters from, use firebug or an equivalent DOM inspector.
Now if you want to keep line breaks in your tab form columns and only strip them to avoid validation errors then set a condition on this validation to “Inline Validation Errors Displayed”.
BEGIN ''; RETURN TRUE; END;
Please Note: in the above code there is an intentional space between the colon and item name because wordpress is replacing these with smilies.
The next step is that we create one more fake validation which is the last in out list (i.e. has the highest display sequence). It is a “PLSQL Function Returning Error Text” validation which has the following PLSQL:
In the end it’s a pretty heavy and ugly solution for something which is expected to be built-in within APEX, but the bright side is that APEX is still flexible enough for us to code workarounds when there’s bugs within the product.