The Column Names (InitCap) choice uses the column names and also applies the InitCap function to them. InitCap is a simple function that changes the first letter of each word into uppercase and lowercases the subsequent letters of the word. For example, REPORTED becomes Reported. You might think that REPORTED_BY would become Reported_By, but APEX is a bit more sensible than that; it changes it to Reported By, substituting a space for the underscore character so that it looks nicer on the page.

Note that you can use the InitCap function in your own code, like this:

apexdemo@DBTEST> select

  2  initcap('REPORTED_BY') as colname
  3  from dual;

COLNAM
-----------
Reported_By

However, as you can see, the underscore remains. The translation of the underscore into a space is performed by some extra code in the APEX interface (presumably by using the Translate or Replace function), rather than being part of the InitCap code.

You may want the heading names to be something else entirely, rather than based on the column names from the query itself. In that case, you would choose the Custom option at the top of the Column Attributes section. Then you can type anything you like into the Heading attribute for each column,

One of the more interesting choices is to use the PL/SQL option to define the report's column headings. When you choose this option, you get an extra field in the Column Attributes section, where you can specify a function that will return the headings, as shown in Figure 6-4.

Figure 6.4. Using PL/SQL to return heading names

The PL/SQL function that you enter here needs to return a string containing the headings delimited by colons. As a simple example, you can enter the following code:

return 'ID:Bug Id:Reported:Status:Priority:Description:Reported By:Assigned To'

This function just returns a colon-delimited string, with each entry in the string corresponding to each column sequentially.

You can make this much more useful by returning a more generic list of headings. For example, let's say you want to store all the headings for your reports in a table so you can use them in various places throughout the application (for example, you might want to reuse them in an LOV). Listing 6-3 demonstrates creating such a table.

apexdemo@DBTEST> create table report_headings(

  2  id number primary_key,
  3  table_name varchar2(200),
  4  heading_id number,
  5  heading_name varchar2(200)
  6  );

Table created.

apexdemo@DBTEST> insert into report_headings
  2  (id, table_name, heading_id, heading_name)
  3  values
  4  (1, 'BUGLIST', 1, 'Id'),

1 row created.

apexdemo@DBTEST> insert into report_headings
  2  (id, table_name, heading_id, heading_name)
  3  values
  4  (2, 'BUGLIST', 2, 'Bug Id'),

1 row created.

-- Extra rows added here

apexdemo@DBTEST> select * from report_headings;

        ID TABLE_NAME HEADING_ID HEADING_NAME
---------- ---------- ---------- --------------------
         1 BUGLIST             1 Id
         2 BUGLIST             2 Bug Id
         3 BUGLIST             3 Reported
         4 BUGLIST             4 Status
         5 BUGLIST             5 Priority
         6 BUGLIST             6 Description
         7 BUGLIST             7 Reported By
         8 BUGLIST             8 Assigned To

8 rows selected.

Listing 6-3 creates a table called report_headings to store the headings that should be used for each table. It then inserts a record for each heading for the buglist table. (Note that we've omitted some of the insert statements for brevity, but the final select statement shows the records that have been inserted.) The heading_id column allows you to order the way headings are listed for a particular table. You could use the id column, but using a separate column makes it easier to rearrange the heading order later.

Now you can create a packaged procedure that will, when a table name is passed into it as a parameter, return the colon-delimited list of headings that should be used, as shown in Listing 6-4.

apexdemo@DBTEST> create or replace package pkg_report_headers as

  2  function get_headers(p_table in varchar2) return varchar2;
  3  end pkg_report_headers;
  4  /

Package created.

apexdemo@DBTEST> create or replace
2  package body pkg_report_headers as
3  function get_headers(p_table in varchar2) return varchar2 is
4    v_temp apex_application_global.vc_arr2;
5    begin
6      select heading_name bulk collect into v_temp
7      from   report_headings
8      where  table_name=p_table
9      order by heading_id;
10     return apex_util.table_to_string(v_temp, ':'),
11   end;
12 end pkg_report_headers;

Package body created.

In the function definition, we declare a variable called v_temp as the type vc_arr2, which is defined in the wwv_flow_global package. The public synonym, apex_application_global, can be used to access this package:

v_temp apex_application_global.vc_arr2;

The vc_arr2 type is a PL/SQL associative array type that maps a binary integer to a varchar2. It is capable of holding an array of strings (you can look in the wwv_flow_global package to see the definition). We used a bulk collect statement instead of a cursor for loop to reduce network traffic. In a cursor for loop, a SQL statement is executed for each iteration of the loop. The bulk collect statement transfers groups of data in one round trip, which can significantly improve performance.

The rows in the report_headings table where the heading_name column matches the p_table input parameter are placed in the v_temp array:

select heading_name bulk collect into v_temp
from   report_headings
where  table_name=p_table
order by heading_id;

Once you've added all the heading names to the array, you use the apex_util.table_to_string function to return the array type as a string:

return apex_util.table_to_string(v_temp, ':'),

Here, the second parameter to the table_to_string function is the character you wish to use as a delimiter in the returned string. The default character is actually the colon, so you don't need to specify it explicitly (we specified it for clarity here).

You can now test to check that the code works by calling the function via some SQL, as shown in Listing 6-5.

apexdemo@DBTEST> select

  2  pkg_report_headers.get_headers('BUGLIST') as headings
  3  from dual;

HEADINGS
----------------------------------------------------------------------
Bug Id:Reported:Status:Priority:Description:Reported By:Assigned To:

So, with just a few lines of code, you are able to return a dynamic list of report headings. Using a bind variable in the call to pkg_report_headers.get_headers allows you to programmatically set or change the input parameter's value. Because the report we're dealing with is called BUGLIST, that's the value that will be sent unless you change it. You can plug this into your report by entering the following code in the "Function returning colon delimited headings" text box (Figure 6-4):

return pkg_report_headers.get_headers(:REPORT_NAME)

You can now dynamically update the report headings and have the changes immediately reflected in the report, as shown in Listing 6-6 and Figure 6-5.

apexdemo@DBTEST> update report_headings

  2  set heading_name = 'Bug Status'
  3  where id = 4;

1 row updated.

apexdemo@DBTEST> commit;
Commit complete.

Figure 6.5. Updated headings reflected in the report

You might be thinking that instead of using an array in the get_headers function, you could have just concatenated the headings together in a string. That's true. However, there are quite a few places in APEX where you will work with arrays and delimited strings (for example, when working with LOVs). If you're not already familiar with the table_to_string function and the corresponding string_to_table function, it's worth exploring them, because you'll find you use them more often as you become more experienced with APEX.

You might also have wondered about passing in a hard-coded value ('BUGLIST') for the p_table parameter of the function. You could instead pass in the value of a page item (for example, &P1_TABLE_NAME.) that represents the table you're using, which would allow the columns to be retrieved even more generically, if that's something that you'd like to try (sometimes there is such a thing as trying to make your code too generic).

As you can see, choosing the PL/SQL option for Headings Type is a powerful way to generate the heading names at runtime.

When you choose the Use Generic Column Names option, you automatically lose the names of the columns in the Column Attributes section of the report, as shown in Figure 6-7. You can see that where the column names were defined by the query before, the columns are now named COL01, COL02 . . . all the way to COL60 (only the first six columns are shown in Figure 6-7).

Figure 6.7. Using generic columns

You can actually control the number of generic columns by modifying the Maximum Number of Generic Report Columns value in the region source definition. The default value is 60. You can increase or decrease this number, depending on the number of columns you expect to return in your query.

So, why are generic columns useful and when would you choose to use them over named columns? Generic columns are handy when you have a report where each column will look more or less the same; that is, the structure of the report is fairly uniform, and you want to define that layout in a simple way that will be repeated across each of the columns defined for the report.

You can see the layout definitions in the templates. The Layout and Pagination section for the report definition shows that the report is using the Standard Report template, as shown in Figure 6-8.

Figure 6.8. Layout and Pagination section for the report

You can examine the Standard Report template by clicking the link in the Templates section of the Page Definition section, which will list each type of template being used by the components on the page. The template defines different sections for particular parts of the report. The Column Heading template contains the following code:

<th class="t15header" #ALIGNMENT#>#COLUMN_HEADER#</th>

This code uses the standard HTML th element to create a table header tag and specifies a class attribute of t15header, which is defined within one of the CSS files included for the standard APEX templates. The substitution values of #ALIGNMENT# and #COLUMN_HEADER# pick up the value for the alignment assigned to each column header and the column header value itself.

The primary difference between generic columns and named columns is in the Column Templates section. For generic columns, the template contains this code:

<td class="t15data" #ALIGNMENT#>#COLUMN_VALUE#</td>

This looks similar to the Column Heading template, except that it uses the td element tag, a different CSS class, and the #COLUMN_VALUE# substitution variable. For every column of the report, this template substitutes the value of each column where the #COLUMN_VALUE# is specified. This is what we were referring to earlier when we said that generic columns are useful when you are not particularly concerned with the look of individual columns, but just want them all to be formatted in more or less the same way (using the same HTML markup and CSS class information).

You might also notice that you can control the template by assigning conditional logic, as shown in Figure 6-9. This example uses two different column templates. The first one encloses the #COLUMN_VALUE# in bold tags (<b> and </b>) if the PL/SQL expression:

'#PRIORITY#' = 'High'

evaluates to true. The other template displays the column value without the bold tags if the converse PL/SQL expression is true (that is, if the priority is not High).

Figure 6.9. Conditional logic in a generic column template

Now you might be surprised to see that even though you're using generic columns (COL01, COL02, and so on), you can use substitution strings such as #PRIORITY# to access the value of a particular column. Even though the template is being applied to each column, you are still able to evaluate particular columns for each row to which the template is being applied.

The end result of customizing the template in this way, if you hadn't guessed already, is that all the records that have a High priority are now displayed in bold, as shown in Figure 6-10.

Figure 6.10. Conditional template logic applied to the report

Three options are available for the conditions applied to the column templates:

You can use the even and odd row options to make it easier to read the report by perhaps using a different CSS class to make alternate rows have a different background color. The PL/SQL expression option allows a great amount of flexibility to apply a different column template depending on specific criteria. Currently, the Application Builder allows you to specify up to four different column templates and associated conditions.

Using named columns with your reports gives you much more flexibility than generic columns afford. You can customize the layout of your report template to a much greater degree since, as the name implies, you can reference each column by name in the report template.

You can change the report query back to using the default Use Query-Specific Column Names and Validate Query option. However, the report will still be using a column-based template. To take advantage of named columns, you can create a new report template, as shown in Figure 6-11.

Figure 6.11. Creating a new report template

When you create a new report template from scratch, you are given the option of making it either a column template or a row template. This determines whether the report template can use generic columns or named columns; a report that uses named columns can use a row-based template.

When you create a row-based template, the definition of the template is slightly different from the definition for a column template, in that the template refers to the row rather than the column. By default, the row template contains a minimal amount of code, as shown in Figure 6-12.

Figure 6.12. Definition of a newly created row template

If you changed the report to use this new template and ran the page, you wouldn't see anything just yet, since the row template defines table rows and columns (using the tr and td elements), but there is no enclosing table element defined in the template yet (in this example anyway; yours may vary).

In Figure 6-12, you can also see that the template is using the substitution strings #1#, #2#, and so on. This is known as using positional notation; for example, #1# refers to the first column in the query.

To make this template a bit more visually pleasing, you can copy some of the CSS styling information that the Standard Report template uses. In the Row Template 1 box, use the following code:

<tr #HIGHLIGHT_ROW#>

<td class="t15data">#1#</td>
<td class="t15data">#2#</td>
<td class="t15data">#3#</td>
<td class="t15data">#4#</td>

<td class="t15data">#5#</td>
</tr>

Here, you're using the positional notation to show each of the first five columns and the same t15data CSS class that the Standard Report template uses for the columns.

Next, in the Before Rows section, add the following code:

<table class="t15standard" summary="Report">

<th class="t15header" #ALIGNMENT#>#1#</th>
<th class="t15header" #ALIGNMENT#>#2#</th>
<th class="t15header" #ALIGNMENT#>#3#</th>
<th class="t15header" #ALIGNMENT#>#4#</th>
<th class="t15header" #ALIGNMENT#>#5#</th>

Again, this is using the same sort of code that is used in the Standard Report template to create a table and then list each of the first five headings using positional notation.

Finally, in the After Rows section, use the following code:

<tr>

    <td colspan="99" class="t15afterrows">
        <span class="left">#EXTERNAL_LINK##CSV_LINK#</span>
        <table style="float:right;text-align:right;" summary="pagination">
            #PAGINATION#</table>

    </td>
</tr></table>

This was again taken verbatim from the Standard Report template. It simply inserts an extra area below the report that adds the ability to paginate the report and export it to a comma-separated values (CSV) format file.

If you save the template and run the report now, it will look exactly as it did before. The report template was changed, but the report is still using the Standard Reports template. The template setting needs to be changed to Buglist Report as shown in Figure 6-13.

Figure 6.13. Selecting the Buglist Report template

If you now run the page, you should see the report looks pretty much as it did with the Standard Report region, although it is displaying only the first five columns of the query, as shown in Figure 6-14.

Figure 6.14. Using the row-based template

As this point, you are probably wondering why you should go to the extra effort of using row-based templates when they end up looking the same as column-based templates. But in this template, you're still using positional-based notation. You have not yet taken advantage of the extra flexibility attained by referring to the columns by name.

In the original report, all the columns are in the same row. If a lot of data has been entered in the Description column, that could make the report difficult to read (perhaps making the report overly wide or limiting the amount of text that can be shown in the Description column). Wouldn't it be nice if you could make the Description column appear in a separate row below the main detail row of the record? Well, as you've no doubt guessed, you can accomplish this sort of layout by using row-based templates combined with named columns.

First, you need to convert the template to use the named columns rather than the positional notation. Change the Row Template 1 section to use the code shown in Listing 6-7.

<tr #HIGHLIGHT_ROW#>

  <td class="t15data">#ID#</td>
  <td class="t15data">#BUGID#</td>
  <td class="t15data">#REPORTED#</td>
  <td class="t15data">#STATUS#</td>
  <td class="t15data">#PRIORITY#</td>
  <td class="t15data">#DESCRIPTION#</td>
  <td class="t15data">#REPORTED_BY#</td>
  <td class="t15data">#ASSIGNED_TO#</td>
</tr>

This code adds the extra columns to the code. Note that in the query, you use the name of the actual column, rather than the heading name.

Also modify the Before Rows section to include the extra heading names, as shown in Listing 6-8.

<table class="t15standard" summary="Report">

  <th class="t15header" #ALIGNMENT#>#1#</th>
  <th class="t15header" #ALIGNMENT#>#2#</th>
  <th class="t15header" #ALIGNMENT#>#3#</th>
  <th class="t15header" #ALIGNMENT#>#4#</th>
  <th class="t15header" #ALIGNMENT#>#5#</th>
  <th class="t15header" #ALIGNMENT#>#6#</th>
  <th class="t15header" #ALIGNMENT#>#7#</th>
  <th class="t15header" #ALIGNMENT#>#8#</th>

Note that you could hard-code the names of the headings in the template. However, using positional notation gives you the ability to modify the headings using the options covered earlier in this chapter (such as via a PL/SQL function), without needing to modify the template again.

Now you should have a template that makes the report look exactly as it did before. Since you're now using named columns, you can modify the row template a bit more to move the Description column into a separate row, as shown in Listing 6-9.

<tr #HIGHLIGHT_ROW#>

  <td class="t15data">#ID#</td>
  <td class="t15data">#BUGID#</td>
  <td class="t15data">#REPORTED#</td>
  <td class="t15data">#STATUS#</td>
  <td class="t15data">#PRIORITY#</td>
  <td class="t15data">#REPORTED_BY#</td>
  <td class="t15data">#ASSIGNED_TO#</td>
</tr>
<tr>

<td class="t15data" colspan="7">#DESCRIPTION#</td>
</tr>

This code creates another row below the original one and makes the Description column span the entire width of the row (by using the colspan attribute to make it span seven columns, which is the number of columns in the row above it). Since you have changed the order of the columns in the row, you also need to modify the Before Rows template, as shown in Listing 6-10.

<table class="t15standard" summary="Report">

  <th class="t15header" #ALIGNMENT#>#1#</th>
  <th class="t15header" #ALIGNMENT#>#2#</th>
  <th class="t15header" #ALIGNMENT#>#3#</th>
  <th class="t15header" #ALIGNMENT#>#4#</th>
  <th class="t15header" #ALIGNMENT#>#5#</th>
  <th class="t15header" #ALIGNMENT#>#7#</th>
  <th class="t15header" #ALIGNMENT#>#8#</th>

The only change in Listing 6-10 is that you have removed the line that represented the heading for the Description column (which was column 6 in positional notation). When you mix positional notation with named notation, things can become confusing. It's not immediately apparent which positional element represents which column. If you probably won't want to later change the column headings in the report definition (or dynamically), you could hard-code them to make it easier to read and modify the template.

After these changes, the report looks like the one shown in Figure 6-15.

Figure 6.15. Using named columns to affect the layout

We can extend this example a bit further, making it a little less cluttered and perhaps more useful to the end users. Imagine that the users want to see only the description from bugs that are still classified as Open. You can do this quite easily by using conditional logic with the row template, much as you saw with the earlier generic columns example. First, modify the row template to include an extra column before the Description, just to add some space and make it easier to read, as shown in Listing 6-11.

<tr #HIGHLIGHT_ROW#>

  <td class="t15data">#ID#</td>
  <td class="t15data">#BUGID#</td>
  <td class="t15data">#REPORTED#</td>
  <td class="t15data">#STATUS#</td>
  <td class="t15data">#PRIORITY#</td>
  <td class="t15data">#REPORTED_BY#</td>
  <td class="t15data">#ASSIGNED_TO#</td>
</tr>
<tr>
  <td class="t15data"></td>
  <td class="t15data" colspan="6">#DESCRIPTION#</td>
</tr>

Next, add a condition to this row template, which is a PL/SQL expression:

'#STATUS#' = 'Open'

This template will be used for records with an Open status. Listing 6-12 shows a second template to be used when the status is not Open (using the reverse PL/SQL expression logic). This second template does not display the Description column at all.

<tr #HIGHLIGHT_ROW#>

  <td class="t15data">#ID#</td>
  <td class="t15data">#BUGID#</td>
  <td class="t15data">#REPORTED#</td>
  <td class="t15data">#STATUS#</td>
  <td class="t15data">#PRIORITY#</td>
  <td class="t15data">#REPORTED_BY#</td>

<td class="t15data">#ASSIGNED_TO#</td>
</tr>

If you now run the report, it should look similar to Figure 6-16. Notice that where the bug is classified as Closed (Bug ID 2 in Figure 6-16), no description is shown underneath.

Figure 6.16. Using conditional logic in a row template

Row-based templates also allow you to use a number of conditional templates, based on even rows, odd rows, or PL/SQL expressions.

Named column templates are probably one of the most underused features of APEX. The amount of control and flexibility they give you is amazing. You can build some really complex-looking reports—ones that don't even look like standard tabular reports—by applying logic to vary how each individual row is represented in the report.

The Column Formatting section of the Column Attributes section for a report includes a Number/Date Format setting. As an example, the Buglist report currently displays the date that the bug was reported in the format DD-MON-YY. You can modify that by changing the formatting of the REPORTED column, as shown in Figure 6-22.

Figure 6.22. Modifying the date formatting for the REPORTED column

Figure 6-22 shows the Number/Date Format value for the REPORTED column changed to use DD-MON-YYYY HH:MIPM. This value was selected from the pop-up list for this field. The text field is a free-format field, so you can type anything into it, as long as the value makes sense for the particular field and is valid. For example, you will get strange results if you try to apply a date-format mask to a numeric column, or if you specify invalid characters in your date-format mask. Note that no validation of the input text takes place at design time; it is used only at runtime.

If you ran the report after making this change to the date format, you'd find that the REPORTED column now displays values such as 27-JAN-2006 12:00AM. In our example, all the times display as 12:00AM, since only a date (not a time component) was used when each record was created. Thus, the time defaults to 12:00AM.

Another interesting format mask for dates uses the keyword SINCE, which displays a text description of how long ago that date occurred. For example, you might see 6 months ago, 2 days ago, 8 minutes ago, and so on. This makes it much easier for the end users to immediately see how long ago an event occurred, rather than having to mentally calculate it themselves. Also, when you use the SINCE mask, the value is calculated each time it is used, so you'll see the age of the record increasing each time you view the report—for example, from 8 minutes ago to 9 minutes ago and so on.

You can also assign particular CSS classes and styles to the column, For example, if you wanted to display the REPORTED field in bold, you could take advantage of the predefined CSS class called fielddatabold, which is defined in the standard APEX CSS files. All you would need to do is to enter the text fielddatabold into the CSS Class field in the Column Formatting section, and APEX would enclose your column data in an HTML span element and assign the class to it, as shown here:

<span class="fielddatabold">27-JAN-06</span>

You can also enter a comma-delimited list of words into the Highlight Words text field in the Column Formatting section, and APEX will automatically highlight any words that match the column data when you run the report. It does this by wrapping any matching words in an HTML span element and applying a CSS style to that span. For example, entering JAN in the Highlight Words field generates the following HTML (manually broken here):

<td class="t15data">27-
  <span style="font-weight: bold; color: red;">JAN</span>−06</td>

Although this built-in highlighting is great, considering you get it for free, you have no control over how the word is highlighted—you can't modify the CSS styling that is used. So unless you always want your words highlighted in bold red text, you might find this feature of limited use.

Perhaps the most interesting, yet often overlooked, part of the Column Formatting section is the HTML Expression text field. This is extremely powerful, since it allows you to essentially apply another template to the column.

As an example, let's imagine that you'd like the Buglist report to give the end users a quick way to e-mail the person who reported the bug. To do this, you could turn the REPORTED_BY column into a link that, when clicked, launches the default e-mail client and automatically fills in the e-mail address of the person who reported the bug. This is not quite as difficult as it might seem at first, but it does require changing the report a bit.

First, you need to modify the query used for the report so you can extract the e-mail address of the person who reported the bug. Recall that the query was originally as follows:

select

  "ID","BUGID",
  "REPORTED","STATUS",
  "PRIORITY","DESCRIPTION",
  "REPORTED_BY", "ASSIGNED_TO"
 from
  "BUGLIST"
where

(
   ... where clause omitted
)

Currently, the users who reported bugs are not maintained in the user_repository table, so you wouldn't be able to get the e-mail address from there. (The reported_by field is actually a free-format field, so any name could be typed in it.) For this example, we have inserted records into the user_repository table to represent each person who has reported a bug, and also modified the bug-editing screens to allow only the reporting person to be selected from a list of people in the user_repository table. We will not show all the steps we performed, since they are not directly relevant to this example, but you can see the changes in the application export included with the downloadable code provided for this chapter.

You can now change the report query to extract the e-mail address from the user_repository table by performing a subquery, as shown in Listing 6-14.

SELECT

  bl.id,
  bl.bugid,
  bl.reported,
  bl.status,
  bl.priority,
  bl.description,
  bl.reported_by,
  bl.assigned_to,
  (select ur.email from user_repository ur
     where ur.username = bl.reported_by)
    as reported_email,
  (select initcap(ur2.forename) || ' ' || initcap(ur2.surname) from
     user_repository ur2 where ur2.username = bl.reported_by)
    as reported_full_name
FROM buglist bl
WHERE(
  ... where clause omitted
)

This query is essentially the same as before, except now the subquery looks up the e-mail address from the user_repository table and also generates the full name of the user by using the InitCap function to uppercase the forename and surname of the person who reported the bug, This is necessary because the reported_by field now contains the username of the user, rather than the free-format text it contained previously. Note also in Listing 6-14 that table aliases have been added to make it easier to remove any ambiguity about which table the columns reference.

If you now looked at the Report Attributes section, you would see the REPORTED_EMAIL and REPORTED_FULL_NAME columns added to the list. You could just modify your report template to display this new column, but it would be nice to use it in a link from the user's full name. To do that, enter the following code in the HTML Expression field for the REPORTED_BY column:

<a href="mailto:#REPORTED_EMAIL#">#REPORTED_FULL_NAME#</a>

Even though you place this code into the HTML Expression field for the REPORTED_BY column, you are actually referencing the two other columns you haven't directly used in the report. The code will generate an HTML a (anchor) element, with the special href attribute of mailto:, which most modern browsers recognize as meaning that the default mail client should be launched when the user clicks the link. You pass the value of the #REPORTED_EMAIL# column into the href, so that when the default mail client launches, it generates a new e-mail message and uses the value of the REPORTED_EMAIL column as the e-mail address to which to send the message. By using the REPORTED_FULL_NAME column value inside the HTML anchor, the text that is displayed for the link is the user's full name, rather than the login username, which you would have gotten if you had used #REPORTED_BY# instead.

But why use the REPORTED_BY column here, when it isn't actually displayed in the report? You could have just as easily shown the REPORTED_EMAIL column in the report, and used the same HTML expression to format it slightly differently. Well, that's a good question. Doing that would make it slightly less confusing when you return to this code in six months or so. However, doing it this way demonstrates that in the HTML expression, you can reference columns other than just the current one. This is what gives HTML expressions their power. Using them, you can completely transform the way a particular column is represented on the screen.

You can use HTML expressions in many different ways. Typical uses include making custom links (as in this example), generating the correct HTML for an image to be displayed, and linking in some custom JavaScript for that item.

One place you should definitely consider using HTML expressions is if you find yourself including HTML markup inside your queries. For example, rather than write code like this:

select

  name,
  '<img src="apexdemo.generate_image?p_file=' ||
    filename || '"</img"' as custom_image
from
custom_files

use code like this:

select

  name,
  filename as custom_image
from
  custom_files

then use an HTML expression to transform the custom_image column like this:

<img src="/apexdemo.generate_image?p_file=#CUSTOM_IMAGE#"></img>

We suggest doing it this way for two reasons:

It is definitely best practice to try to separate your queries from the display markup as much as you can, and using HTML expressions makes that task very easy indeed.

When you use a dial chart, you use the following general syntax:

select

  <value>
  <maximum_value>
  [, <low_value [, <high_value> ]
from
  table

where value is the initial value for the data point, maximum value is the highest possible value allowed for the data point, and low_value and high_value are the historical low and high values (optional).

Flash charts allow you to define multiple series to display in the chart. You can do this by entering additional series in the chart attributes, or you can list each series in a single query (if the data lends itself to being queried in that way). The following is the syntax for querying multiple series:

select

  link,
  label,
  series_1_value,
  series_2_value,
  [, ...]
from
  table

where the values for the different series are determined from the column aliases you use.

If you use a range chart type in a Flash chart, you need to provide two different values for each bar:

select

  link,
  label,
  low_value,
  high_value
from
  table

Candlestick charts require four different values for each data point: a value for open, low, high, and close.

select

  link,
  label,

open,
  low,
  high,
  close
from
  table

Two types of Gantt charts are supported in APEX 4—Project and Resource. Both of these charts are used for project management and, to be used effectively, require a specific set of data. The query syntax describes what is necessary and is shown below:

Project Gantt chart syntax:

SELECT link, task_name, task_id, parent_task_id, actual_start_date, actual_end_date, progress
FROM   ...

Resource Gantt chart syntax

SELECT link, resource_id, resource_name, resource_parent_id, actual_start_date,
actual_end_date
FROM   ...

As you can see, these are fairly specialized reports, but if you are making use of the Team Development features of APEX 4.0, the data to support these reports will likely be available.

Flash Maps are created using map files, which are included with APEX in conjunction with a query. The syntax of the query is basically the same for any map page, but what you include in the query has to match with the type of map you select. If you plan to use your own data to reference geographical information in a map, it must correspond to the region information associated with the map. If, for example, you want to show the population of Dallas County on a map of Texas, you need to have 'dallas' in your data as that is the lowest level of detail associated with state maps of the United States. The syntax used with maps is very simple, as shown here:

SELECT null link, region label, value Populations
FROM (
SELECT 'Florida' region, 18328340 value FROM dual
UNION ALL
SELECT 'Alaska' region, 686293 value FROM dual)

On a map of the United States, this query would result in Florida and Alaska being highlighted, with each displaying the label and value when the mouse is hovered over the region.

Another interesting new addition to Flash Charts is the Mapping feature, which allows you to use maps in your reports to display location-based information. You can display only those maps that are delivered with APEX 4.0 (there are more than 100). The lowest level of detail on each map is considered a "region." In the case of a map of Texas, available regions are counties and 3-digit zip codes. You can't drill down to a specific city with this level of detail. The page shown in Figure 6-29 contains a map of Texas with two counties, Tarrant and Dallas, highlighted. To make this work, the county names had to be known as they are stored in the map as the regions.

Figure 6.29. Flash Map using the state of Texas

Notice that when the mouse is hovered over Dallas County, the population value is shown. The query used to add this functionality is below:

select null as link, region Label, value Population
from
(
select 'Dallas' region, 1500000 as value from dual
UNION ALL
select 'Tarrant' region, 600000 as value from dual
)

As stated previously, the name of the county is synonymous with the region. When the county name is selected along with a value (population was used in this example), both are displayed upon hovering the mouse over the region.

You can take advantage of CSS styling to display Flash charts in other languages. For example, the Application Express User's Guide suggests that to display the text in your chart in the Korean language, you can modify the CSS definitions for the text and tspan classes as follows:

text{font-family:Batang;fill:#000000;}
tspan{font-family:Batang;fill:#000000;}

Note that the charts are not automatically translated. You are simply modifying the font-family directive to use the correct font type, which can display the text in the correct format. It is still your responsibility to make sure the actual text is correct.

Flash charts provide a very handy feature called Asynchronous Updates, which automatically refreshes the chart at a specified interval and displays any changes in the underlying data. The Asynchronous Updates feature can be very useful for dashboard-type applications, where end users want to see constantly updated feedback about particular statistics without having to click a refresh button or resubmit the page.

To enable Asynchronous Updates, change the Asynchronous Updates setting to Yes and specify an Update Interval in seconds in the Refresh section of the Chart Attributes section.

If you use the Asynchronous Updates feature, be aware that any animation features you've used will also be executed as part of the refresh process.

Using multiple series in your charts is no more difficult than using single series, although you need to take a bit of care that the data values you represent on the chart work well with each other. In other words, if you try to display multiple series in the same chart when there is no obvious correlation between the data in each series, you may end up making the chart more difficult to read.

Suppose you wish to modify the sample chart so that instead of just showing the number of bugs reported by people, it also shows the number of bugs assigned to people. You can do this by using a chart type that supports multiple series. Remember that you can't just change the chart type, so you need to delete the chart and create a new one (of another type).

For example, create a new Chart region type and select the Cluster Bar, Horizontal option, which enables you to specify multiple series. However, because you need to have a common link between the two series, you need to rewrite the previous SQL as shown in Listing 6-19, so that the chart shows all the users and the bugs reported by them, rather than just listing the users that have bugs assigned.

select

  null as link,
  ur.username as label,
  (select count(*) from buglist bl
     where bl.reported_by = ur.username) as value
from user_repository ur

Note that this query is simplified a little by using NULL for the link, as well as by just using the username rather than concatenating the forename and surname. As before, you could provide a link to the same page, which generates a report showing the detail for a selected chart item.

If you look at the report attributes, you will see that this chart type has a Chart Series section, which allows you to add an extra series, as shown in Figure 6-30.

Figure 6.30. Adding an extra series to a chart

Add another series, but this time modify the query so that it returns the number of bugs assigned to people, as shown in Listing 6-20.

select

  null as link,
  assigned_to as label,
  count(assigned_to)
from buglist bl
group by assigned_to

You can now see both series displayed in the same chart, as shown in Figure 6-31 (the color difference between the series may be difficult to make out in the screenshot).

Figure 6.31. Using multiple series in a chart

You can further customize the look of Flash charts by modifying the XML used to generate those charts. The Chart XML section of the Chart Attributes section contains code similar to Listing 6-21.

<?xml version = "1.0" encoding="utf-8" standalone = "yes"?>
<anychart>
  <settings>
    <animation enabled="false"/>
    <no_data show_waiting_animation="False">
      <label>
        <text></text>
        <font family="Verdana" bold="yes" size="10"/>
      </label>
    </no_data>
  </settings>
  <margin left="0" top="0" right="0" bottom="0" />

<charts>
    <chart plot_type="CategorizedBySeriesVertical" name="chart_5928617224245708">
      <chart_settings>
        <title text_align="Center" position="Top" >
          <text>Reported by</text>
          <font family="Tahoma" size="14" color="0x000000" />
        </title>
        <chart_background>
          <fill type="Solid" color="0xffffff" opacity="0" />
          <border enabled="false"/>
          <corners type="Square"/>
        </chart_background>
        <data_plot_background>

        </data_plot_background>
        <axes>
         <y_axis >
            <scale   mode="Stacked"    />
            <title enabled="false" />
            <labels enabled="true" position="Outside">
              <font family="Tahoma" size="10" color="0x000000" />
              <format><![CDATA[{%Value}
               {numDecimals:0,decimalSeparator:.,thousandsSeparator:,}]]>
             </format>
           </labels>
           <major_grid enabled="False"/>
           <minor_grid enabled="False"/>
          </y_axis>
          <x_axis>
            <scale  mode="Stacked"    />
            <title enabled="false"/>
            <labels enabled="true" position="Outside">
              <font family="Tahoma" size="10" color="0x000000" />
            <format>
              <![CDATA[{%Value}
              {numDecimals:0,decimalSeparator:.,thousandsSeparator:,}]]>
              </format>

</labels>
            <major_grid enabled="True" interlaced="false">
                <line color="Black" />
              </major_grid>
               <minor_grid enabled="True">
              </minor_grid>
          </x_axis>
        </axes>
      </chart_settings>
      <data_plot_settings enable_3d_mode="true" >
        <bar_series style="Default">
          <tooltip_settings enabled="true">
            <format><![CDATA[{%Name}{enabled:False} –
              {%Value}
              {numDecimals:0,decimalSeparator:.,thousandsSeparator:,}]]>
            </format>
            <font family="Tahoma" size="10" color="0x000000" />
              <position anchor="Float" valign="Top" padding="10" />
          </tooltip_settings>
          <label_settings enabled="true" mode="Outside" multi_line_align="Center">
            <format>
              <![CDATA[{%Value}
              {numDecimals:0,decimalSeparator:.,thousandsSeparator:,}]]>
           </format>
           <background enabled="false"/>
           <position anchor="Center" valign="Center" halign="Center"/>
           <font family="Arial" size="10" color="0x000000" />
          </label_settings>
          <bar_style>
          </bar_style>
          <marker_settings enabled="True" >
            <marker type="None" />
          </marker_settings>
        </bar_series>
      </data_plot_settings>

#DATA#
    </chart>
  </charts>

</anychart>

As you can see in Listing 6-21, you can modify many options in the XML to affect how the chart will look and operate. Before doing so, read the documentation about the various options (and definitely make a backup of the current XML). As noted earlier, the Flash charts are produced with a third-party component, the AnyChart Flash chart component, which Oracle has licensed to ship with the APEX product. For more information and documentation about the various options, you can consult the original component documentation at www.anychart.com.

Generic charting is quite simple. Rather than defining the SQL in the Series Query section for the chart, you can change the Query Source Type setting from SQL Query to Function Returning SQL Query, and then write a query that returns the text to use for the SQL query, as shown in Listing 6-22.

CREATE OR REPLACE FUNCTION generatequery(p_type IN VARCHAR2) RETURN VARCHAR2 IS v_sql
VARCHAR2(2000);

BEGIN
  v_sql := 'select id, name, salary from payroll where ';
  IF p_type IS NOT NULL THEN

    IF(p_type = 'DEPT') THE
      v_sql := v_sql || ' and dept_name = v(''P1_SEARCH'')';
      ELSIF(p_type = 'MANAGER') THEN
        v_sql := v_sql || ' and manager = v(''P1_SEARCH'')';
      END IF;

    END IF;

    RETURN v_sql;
  END;

This example passes in a parameter, p_type, to the function. This parameter is then used to determine whether to append an extra part to the where clause restriction, which compares the value of the P1_SEARCH page item against the dept_name column or the manager column.

Note that the v('ITEM') syntax is used in the function, rather than passing in the value of the search text and then concatenating it to the SQL like this:

v_sql := v_sql || ' and dept_name = ' || p_query_string;

Using this type of concatenation is extremely dangerous and makes your application susceptible to SQL-injection attacks. A malicious user could manipulate the value of the p_query_string parameter so that the string returned from the function contains some code you didn't anticipate (such as deleting from a table).

For performance reasons, you should use bind variable notation (:ITEM), rather than using the v('') function. We will come back to this issue of using bind variables in more detail in Chapter 14.

Rather than using the Function Returning SQL Query option, an alternative way to achieve generic charting relies on a feature called pipelined functions. You know that the chart is expecting certain columns to be returned by the query, namely link, label, and value, like this:

select

  null as link, ename as label,  sal as value
from
  scott.emp
where
  deptno = :P101_DEPTNO

So you need a way of returning a link column, a label column, and a value column dynamically for each row of data. First, you need to create a type that will be used as the return type of your pipelined function; that is, this type will represent a single point on the chart:

create or replace type ty_chart_entry as object (

  link varchar2(60),
  label varchar2(60),
  value number
);

Next, create another type that is a collection of the ty_chart_entry type. This will effectively hold the table representing all the data points on the chart:

create or replace type tbl_chart_entry as table of ty_chart_entry;

Now you need to create the function itself, as shown in Listing 6-23.

create or replace package chart_pkg

as
    function generate(p_type in varchar2)
      return tbl_chart_entry PIPELINED;
end;

create or replace package body chart_pkg as
  function generate(p_type in varchar2)
    return tbl_chart_entry PIPELINED is
  begin
    if (p_type = 'E') then
      for rec in (select ename, sal from emp) loop
        pipe row (ty_chart_entry(null, rec.ename, rec.sal));
      end loop;
    end if;
    if (p_type = 'D') then
      for rec in (select d.dname as name,
                  (select sum(e.sal) from emp e
                     where e.deptno = d.deptno) as sal
                  from dept d) loop
        pipe row (ty_chart_entry(null, rec.name, rec.sal));
      end loop;
    end if;
    return;
  end;
end;

Essentially, this function performs two entirely different queries depending on whether you pass in a 'D' (to query the dept table) or an 'E' (to query the emp table).

SQL> select count(*) from table(chart_pkg.generate('E'));

COUNT(*)
--------
14
SQL> select count(*) from table(chart_pkg.generate('D'));
COUNT(*)
--------
4

You can now create a new chart region on a page and add a select list (with submit) that returns 'E' or 'D', and use the value of this page item in the query, as shown here:

select

  link, label, value
from
  table(chart_pkg.generate(:P14_CHARTTYPE))

If you now run the page, you should see the different data, depending on what was chosen from the select list, as shown in Figures 6-32 and 6-33.

Figure 6.32. Querying the emp table with generic charting

Figure 6.33. Querying the dept table with generic charting

You might find that you prefer the approach of using pipelined functions so that you don't risk producing dynamic SQL that could potentially be exploited via SQL injection. If there is a restricted set of queries that you want to enable the user to perform, using a pipelined function is an ideal way of providing that functionality.