Wolf Moritz

An All SQL Option to Densifying Dates

In a previous post I had discussed how to densify a sales report by filling in missing date rows. I accomplished that by referencing an existing table of calendar dates to which I outer joined my sales data. For those who don’t have a handy dates table (as I do in our E-Business Suite), I provided a script to create such a table.

But probably many of you may not be able to (or are allowed to) create a database object just for a report. But, if you noodle around with Oracle long enough you’ll eventually learn nifty alternative ways of doing just about everything.

Using Oracle’s CONNECT BY LEVEL clause we can generate as many rows as we want in pure SQL. And these rows can be interpreted as dates, or months, or any interval we wish. Try this little row generating statement:

select level
from dual
connect by level <= 10;

Although DUAL only has one row, we can generate as many rows as we need, ten in this case. Let’s change these rows of numbers to rows of dates:

select to_date('01-JAN-09','DD-MON-RR') + level - 1 cal_date
from dual
connect by level between to_date('31-JAN-09','DD-MON-RR') - to_date('01-JAN-09','DD-MON-RR') + 1;

Wrap this in an inline view and you have your virtual dates table! You could also make each row be month or quarter or year increments with some judicious use of temporal functions in Oracle.

If you are using bind variables to specify your date range in your query, you can then simply use the begin date and end date bind variable in your row generating query as well as in your main data query.

select to_date('01-JAN-09','DD-MON-RR') + level - 1 cal_date
from dual
connect by level <= to_date(:begin_date,'DD-MON-RR') - to_date(:end_date,'DD-MON-RR') + 1;

However, just for fun let’s just reference the date range in our row generating query by creating a named sub-query using Oracle’s WITH AS syntax:

with dates as (select to_date('01-JAN-09','DD-MON-RR') beg_date, to_date('31-JAN-09','DD-MON-RR') end_date from dual)
select dates.beg_date + level - 1 cal_date
from dual
cross join dates
connect by level <= dates.end_date - dates.beg_date + 1;

The CROSS JOIN creates a cartesian product, but the named sub-query and DUAL only contain one row each, so this is not a problem. Almost there, now let’s just add in the original sales query and move our row generator into an inline view:

with dates as (select to_date('01-JAN-09','DD-MON-RR') beg_date, to_date('31-JAN-09','DD-MON-RR') end_date from dual)
select d.cal_date, nvl(s.total_sales,0) total_sales
from (
      select dates.beg_date + level - 1 cal_date
      from dual
      cross join dates
      connect by level <= dates.end_date - dates.beg_date + 1
      ) d
left outer join (
                  SELECT TRUNC (sales_date) sales_date,
                          SUM (quantity * price) total_sales
                     FROM sparse_sales
                    WHERE sales_date BETWEEN dates.beg_date AND dates.end_date +.99999
                  GROUP BY TRUNC (sales_date)
                ) s on s.sales_date = d.cal_date
order by 1;

In most cases if you can rely on pure SQL you’re much better off. I like this row generating technique better than relying on a physical table of dates, as it’s more portable.

Apex HTML Report Layout Options

Posted by Wolf Moritz on March 6, 2011 6 comments

The Classic and Interactive report regions in Oracle’s Application Express have plenty of options to format the data to suit your taste – if you stick with a basic tabular layout. In most cases, that’s perfect, after all SQL is meant to dish up structured data. But, sometimes the tabular layout option does not do justice to certain columns. For example, records with larger text columns used to capture user notes along with a bit of meta about that record (status, date, who etc.) is not displayed quite so elegantly in Apex, in particular if you have a lot of columns.

Consider this table:

CREATE TABLE NOTES (
	ID INTEGER PRIMARY KEY,
	STATUS VARCHAR2(10),
	USERNAME VARCHAR2(20),
	CREATED_DATE DATE,
	TEXT VARCHAR2(4000)
	);

After inserting some rows, and creating a basic Apex Classic report, the report may look something like this:

HTML Classic Report

Not bad, but if you have a lot of meta columns the most important column Text will get scrunched up and take up a lot of vertical room. Instead, why not display the meta in a row along the top of each entry, with the text as a sub row spanning several columns?

Inspecting the page source of the last column using Firebug you can see that Apex simply wraps each column in a pair of table data tags <td></td> (If you don’t use the Mozilla Firefox add-on Firebug as a development tool, I say stop and go get it right now!)

HTML Report Table Structure

If we edit the report SQL source from this:

select id,
	status,
	username,
	created_date,
	text
from notes

And inject a little extra HTML concatenated to our last column:

select id,
	status,
	username,
	created_date,
	'</td><tr><td></td><td colspan="3">'||text||'</td></tr><td>' text
from notes

We can force the text column to it’s own sub-row.

Here’s what’s happening:

The first </td> tag closes the Apex opening table data tag.
The next <tr> tag opens a new row.
Then the next pair of table data tags <td></td> are used to fill in the first column under the Edit link (just for a clean look).
Then we open a new table data tag <td colspan="3"> for our text column with a colspan="3" so it lays nicely across the entire report row.
Then we close our column table data tag and this extra row </td></tr>.
Finally, we open an extra table data tag <td>. Why? Because Apex will try to automatically close the original Text column into which we just inserted our custom HTML. This open tag will match the closing tag and keeps the HTML valid.

One last detail, we are effectively removing the Text column as a column, so change the column title to null to hide it. Now let’s see how it looks:

Apex HTML Report Modified Layout

Hey, that’s way more readable. We can take this one step further, and conditionally set the background color for each Text row based on the status. By using the DECODE() function and adding a custom class to each row:

select id,
	status,
	username,
	created_date,
	'</td><tr><td></td><td class="'||decode(status,'Open','opennote','Closed','closednote')||'" colspan="3">'||text||'</td></tr><td>' text
from notes

And add some simple CSS to the page header:

<style type="text/css">
.opennote {background-color: #FFF4B5;}
.closednote {background-color: #A6D5F3;}
</style>

Now check out the report:

Improved Apex HTML Report Layout

I expect this technique will not work with an Interactive Report, but sometimes a plain old Classic region is all you need.

Edit: You may need to adjust the Report Attributes > Layout and Pagination options to suit your theme or particular needs. This technique should work with most Report Templates, but the most reliable template is the default HTML option; the other templates may affect the look and feel. Also, you may need to set the option to Strip HTML to No. For the above example I had this set to Yes and it still worked, but you might have better success setting this to No. Finally, this example was created using Apex 3.2.0.00.27, I have not tried this in 4.0.

Report Settings

Windows MySQL Backup to Remote Host

Posted by Wolf Moritz on February 19, 2011 No comments

In our home workshop, we have a shared PC workstation running XAMPP to serve up some local PHP applications inside of our home network. These apps are mostly convenience tools, nothing critical, but at the same time the MySQL database holds some data we want to backup – just in case. Backing up the MySQL database is easy with mysqldump, but having a copy of the data on the same machine really wasn’t sufficient, it needed to be pushed off site to another machine. So, my goal was to:

Run mysqldump to get an export of the database.
Load the resulting dump file to an off site server.
Schedule this to happen every night.

The solution was to write a batch file, and use PSCP to upload the file to our off site storage. The mysqldump.exe is a very handy tool to create a backup file of the database. You should read up on the many options, which I won’t go into here. One option you should definitely consider is to compress the dump output file. In my case, the output file is less than 50kb, so I didn’t bother.

XAMPP is really a great development tool, and a decent micro web server for home use. It comes with PHP 5.3 and MySQL already installed, plus more goodies. Installing it is a snap, normally as simple as unzipping it into the root of C:\ (Windows of course, but there are versions for Mac OS X and Linux). But this post is not about using XAMPP, but about backing up a database.

First, create a temp directory somewhere, I created one as C:\temp. Next, if you don’t already have it, install PSCP, an SCP client (command line secure file copy). We’ll use this to copy the files to a remote Linux server (such as a web host). You can get PSCP from the PuTTY download page (look for pscp.exe). The install is simple, just save the pscp.exe executable to a new folder in Program Files, I put it where I already have PuTTY installed:C:\Program Files\Putty\pscp.exe. If you are running a 64bit machine, you’ll probably put it in C:\Program Files (x86)\Putty. You’ll need to have SSH access to your remote server to use PSCP. If you don’t, contact your remote host administrator and ask for permission.

On your off site server create a directory to hold the backup file. Depending on your host (we’re using a web host, and placing the folder outside of the public_html directory), you can use PuTTY and SSH in, or just use the C-Panel to login and create the new directory. You’ll definitely want to place this outside of the web root!

Now, create a batch file. I placed this at the root of the application I wanted to backup, but it doesn’t matter where it goes (well, mostly doesn’t matter). Another good place might be in the c:\temp directory. If you haven’t done this before, create a new text file, and change the name to backup_db.bat. In this batch file, we’ll add a few commands:

@ECHO OFF
REM Backup database using mysqldump.exe.
REM Then send the backup file to remote web host using pscp for offsite storage.

REM To run this script, you need:
REM 1) To have pscp.exe installed
REM 2) Assumes you are using MySQL as part of an XAMPP install
REM 3) A temp directory c:\temp
REM 4) A target directory on your web host for the backup file storage

REM Set local variables here, this way you can easily update references in one place.
SET localdir=c:\temp
SET mysqldumppath=C:\xampp\mysql\bin\mysqldump.exe
SET mysqluser=root
SET mysqlpassword=<your_root_password>
SET mysqldatabasename=<your_database_name>

REM Uncomment the line below if you want to accumulate a new database backup file each night.
REM SET backupfilename=backup_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.sql

REM Otherwise, this version of the backupfilename just gets over written each night.
REM (Comment out the line below if you use date-unique backup file names.)
SET backupfilename=db_backup.sql

REM If the path has spaces, use double quotes for the path.
SET pscppath="C:\Program Files\Putty\pscp.exe"
SET remotehost=<your_remote_server>.com
SET remoteuser=<ssh_user_name>
SET remotepassword=<ssh_user_password>
SET remotepath=<remote_absolute_path>

@REM Dump database.
%mysqldumppath% -u%mysqluser% -p%mysqlpassword% --opt --databases %mysqldatabasename% > %localdir%\%backupfilename%

@REM Upload new file using pscp to remote server
%pscppath% -pw %remotepassword% %localdir%\%backupfilename% %remoteuser%@%remotehost%:%remotepath%

You’ll need to replace any <your_name_references> with your actual names and paths. If the string has a space in it, wrap it in double quotes. You can also download a copy of this batch file here: Backup MySQL Database 1.

After making your reference changes, test the batch file by double clicking it, you should see a command line window briefly appear as PSCP connects to the remote server and uploads the file. You can verify this by checking that your C:\temp directory has a copy of the database dump file, and then check that your remote server has the same copy of the dump file. Almost there!

Finally, we can schedule this as a Windows task. Open Start > All Programs > Accessories > System Tools > Task Scheduler. Depending on which version of Windows you are using, you’ll see a slightly different Task Scheduler.

Creating a new task is fairly simple, give the task a name, select something to do, and set a repeating schedule, such as every night. On an XP machine, you’ll need to select an application to run. Just choose any application; after you create the task you can then edit the task and change the path to the full path and file name of your new batch file: c:\temp\backup_db.bat. On Windows 7 the wizard appears to let you just define a file to run.

That’s about it.

MG Gallery jQuery Plugin

Posted by Wolf Moritz on January 29, 2011 No comments

New: Version 1.3.0 Released April 5, 2012

There are many nice jQuery gallery plugins available today. Yet I was still looking for the one with the right combination of features, specifically one that supported a generous caption text/HTML block for each image. The MG Gallery jQuery Plugin has just that and:

Navigable thumbnails.
A nice crossfade image transition, or a sequential swap transition.
An optional title that does not cover or obscure the current image.
An optional but built in opacity setting for the thumbnails with hover over animation.
Minimal CSS, HTML, or classes.
Flexible thumbnail and image page arrangement.
All images gracefully fade in on page load.
The thumbnails should degrade nicely if the client browser has JavaScript disabled.
And a of course that generous optional text/HTML block below the current image for more verbose captions.

Plus:

Thumbnail pagination controls (v1.3.0).
An optional slide show (v1.3.0).
Back/Forward button browsing of images (v1.3.0)
Preload main (large) remaining images after page initialization (v1.3.0)

Examples and Documentation

Download the Plugin

MG Gallery Plugin 1.3.0

Using the MG Gallery Plugin

After linking to the plugin file in your header, the required HTML structure is very simple: just a div with an unordered list of thumbnail images wrapped in anchors to the larger image, and a single empty div to contain the current image. I specifically wanted the plugin to require the fewest HTML objects, classes, or ID’s in the page source. The documentation has all the details on usage, so I won’t repeat myself here.

I have tested the plugin in Firefox, Safari, Chrome, and IE7, 8, 9.

If you have questions or suggestions (or even if you just use the plugin), please let me know!

Apex + E-Business Suite

Posted by Wolf Moritz on May 6, 2009 No comments

I’m at Collaborate 09 in Orlando, Florida; an annual congregation of all things Oracle (plus JD Edwards, PeopleSoft and more). Every year I attend I learn something new, discover a new tool or resource, and get to discuss current issues with like minded individuals.

This year I presented on using Application Express with our Oracle E-Business Suite. Given the overall lower attendance (thanks to the economy), and the specific topic of my abstract, I was expecting zero to five people to attend. Actually over 20 showed up, maybe more! I had intended my presentation to be oriented to EBS users new to Apex, and almost the entire audience fit that description.

When the presentation was done, there were quite a few questions and a lot of interest in our solution. I think we have a great platform (EBS plus Apex), and clearly many other E-Business Suite users were beginning to think the same thing, but many were not sure about how it might work.

I hope I was able to share our success with this platform and encourage others to consider the same approach. I’ll try to share more thoughts and tips here in the future; clearly there is a great interest in this concept.

Authenticate E-Business Suite Users in Apex

Posted by Wolf Moritz on April 25, 2009 1 comment

If you use Oracle’s Application Express (Apex) as part of an E-Business Suite implementation (not a common setup, but I think it’s an awesome combination), you’ll be faced with some basic questions on integrating Apex user authentication and authorization into the existing E-Business Suite. Unless your custom Apex application is totally independent of your E-Business Suite and it just happens to share a common database, you’ll most likely want to to test user credentials against the E-Business Suite.

To recap, Apex has two primary functional user security models: Authentication and Authorization. Authentication schemes control user access to an entire application. The user’s user name and password are checked before the user is allowed to access the application. Authorization schemes, on the other hand, control display and user access to pages, items, buttons, and links within an application. Between these two security tools, you can easily control access to whole applications or just portions of a page.

For authentication, Apex comes with several built in schemes including a database user, a nice workspace-user model, and the ability to create custom authentication schemes. By creating a custom authentication scheme, we can transparently test user credentials in Apex against their E-Business Suite logon. If you use Oracle Single Sign On, this this exercise is moot for you! Application Express is SSO enabled.

First, we need a way to test the supplied Apex user credentials in the E-Business Suite. Oracle nicely provides this in a packaged function: fnd_web_sec.validate_login(). However, this function returns varchar2, while Apex requires an authentication function that returns boolean. By wrapping this in a custom function we can convert the return value to the required boolean value. Here’s an excerpt on calling this function from this handy custom package: apex_custom_pkg

  FUNCTION authenticate (
    p_username IN VARCHAR2,
    p_password IN VARCHAR2)
  RETURN BOOLEAN
  AS
  BEGIN
    RETURN (fnd_web_sec.validate_login (p_username, p_password) = 'Y');
  EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
  END authenticate;

With the package compiled, and the execute grant and synonym created in your custom schema, all that’s left to do is register your custom authentication scheme in Apex. Hint: Using the powerful ability to Publish and Subscribe certain components such as Authentication and Authorization schemes is a really good idea. It’s reusing code at it’s best.

To register a custom scheme, select Shared Components in your Apex Application, and then Authentication Schemes in the Security block. You will most likely see an icon for Application Express – Current along with two inactive Database schemes. We’re going to add a new scheme for the E-Business Suite and make it the current one.

Page Session Management

Press Create, and then the “From scratch” radio button. Next, you can name and describe your scheme; no one sees this but other developers so name it something to the point, such as EBS LOGON. You could continue to walk through the wizard, but for this exercise I usually just click Create Scheme at this point.

Now click to edit your newly created EBS LOGON scheme. Scroll down to the Page Session Management block for your first edit. Select your logon page in the Session Not Valid Page list of values, which is normally page 101. Then delete “-BUILTIN-” from Session Not Valid URL.

Authenticate Function

Next, scroll down to the Login Processing block and enter your Authentication Function. Unlike other PL/SQL functions you call in Apex, enter the package and function but do not use parenthesis, list the parameters or include a terminating semicolon.

In this same block, you can optionally include Pre-Authentication or Post-Authentication processes. For example, you may want to cache the fnd_user ID in an Apex Application Item (sort of like a global variable and created separately):

apex_util.set_session_state('FND_USER_ID',apex_custom_pkg.ebs_user_id());

Unlike the authentication function, you do include arguments, parenthesis and terminating semicolons!

Apex Cookie

Apex automatically sets a cookie for session security. If you have multiple Apex applications related to your E-Business Suite (which are simpler to support than one large application), you can set a common cookie to allow seamless deep linking between applications.

Last, you need to modify the Logout URL to redirect users to your logon page.

Change:
wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&amp;p_next_flow_page_sess=4155:PUBLIC_PAGE

To:
wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:101:&SESSION.:LOGOUT

Save your changes. To activate your custom authentication scheme, select the Change Current link on the Authentication Schemes home page. Select your EBS LOGON and confirm your selection. Of course, you should test your custom authentication scheme, both on logon and logout.

Of course, all of this only tests that the user has a valid E-Business Suite logon; whether they are authorized to use your custom application is another matter.

There are many approaches to authorizing users. You could include a responsibility assignment check in the custom authenticate function, such as calling apex_custom_pkg.valid_user_resp(). However, in this case the authenticate function would be limited to a single user role.

Your most versatile option is create a custom authorization scheme and then check the user after initially validating the login. This would first let the user in past the E-Business Suite authentication, but if the user is not authorized to use your application they would be presented with a custom access denied message.

At our work, where we have a suite of Apex applications supporting our E-Business Suite, we created a single “Home” Apex application that consists of just one page with a list of authorized links to the other Apex applications.

This Home Apex application authenticates users against their E-Business Suite credentials, but because each application has the same cookie name as the Home application, users are transparently allowed into the other applications. If a user bookmarks a specific application, they are still challenged for credentials.

However, by applying authorization rules to that list of links, users without a valid authorization do not even see the link and are effectively blocked from the unauthorized application (we also apply the same authorization rules within the target applications).

The Home application also contains our master List of Values, Templates, Navigation Bar Entries and other components to which other applications can subscribe.

Authentication is the first step towards seamless E-Business Suite integration. With Apex you can create custom forms to store related data, execute transactions through public APIs and more.

Yummy Value Sets

Posted by Wolf Moritz on February 21, 2009 No comments

While putting the final touches on a new Concurrent Program for the E-Business Suite at work, I was looking for a value set of monthly periods using a ‘YYMM’ format mask. Before I create a new value set, I usually browse existing value sets, and search Oracle’s knowledge base, Metalink.

Yummy Value Set

You never know what tasty surprises you might find.

Application Express and the E-Business Suite

Posted by Wolf Moritz on February 17, 2009 No comments

Oracle’s E-Business Suite is a powerful enterprise business system right out of the box, and is highly configurable to fit many business models. However, there is a limit to what mere configuration can accomplish, and ultimately most E-Business Suite users must resort to some level of customization (and not just custom reports, something everyone does, no we’re talking about new logic and/or behaviors).

Yes, customizing major business systems incurs significant drawbacks, not the least of which is the added complexity of patching and system upgrades. If custom behavior is inevitable, add it in the least invasive way possible. This where Application Express (Apex) shines so well. An Apex installation is contained almost entirely in the database as a bunch of PL/SQL and tables, it consumes no resources until requested, is super simple to use (by functional analysts as well as professional developers), and provides tight integration to your system and data – all without actually customizing your ERP!

With Apex, you can build a suite of mini web applications (or just one main application) to complement your business system with those needed solutions. By moving custom solutions out of modified or custom Oracle Forms into Apex, E-Business Suite patches and upgrades are much more painless and quicker. Plus, in most cases users seem to prefer using streamlined web forms in Apex over working in Oracle Forms (not a scientific survey, but just gauging by the common user reaction).

If you haven’t had a chance to check out Application Express, take a look at the official Apex site on the Oracle Technology Network: Oracle Application Express. I’m curious to hear if other E-Business Suite implementations also use Apex.

That Report Isn’t Dense Enough

Posted by Wolf Moritz on February 3, 2009 No comments

Recently I have written several SQL reports where there were just not enough rows. No, I wasn’t getting paid by the word, but the reports were misleading. Data is stored as a result of something happening, not when something doesn’t happen. Sometimes those missing rows are very interesting clues to business performance.

Adding missing rows is called data densification. For example, a purchasing report might show item and quantity levels for a given period, and the transactions that affected inventory within that period. If the month end inventory for a normally popular item is sufficient, but sales were low, that would be interesting. However, knowing that for 15 days of the month there were none available to sell which may have caused the low sales would be insightful.

In my most recent need for densification, a monthly sales line graph showed some sales variation throughout the month. However, the graph showed consistent sales.

That sales report is based on a SQL statement similar to this, which returns the total sales for each day:

SELECT TO_CHAR (sales_date, 'Mon DD, YYYY') sales_date,
       SUM (quantity * price) total_sales
  FROM sparse_sales
 WHERE sales_date BETWEEN to_date ('01-JAN-09', 'DD-MON-YY') AND to_date ('31-JAN-09', 'DD-MON-YY') +.99999
GROUP BY TO_CHAR (sales_date, 'Mon DD, YYYY')
ORDER BY sales_date;

Output:

SALES_DATE    TOTAL_SALES
Jan 05, 2009  502.5
Jan 06, 2009  125
Jan 15, 2009  225
Jan 25, 2009  758.75
Jan 26, 2009  120
Jan 27, 2009  337.5
Jan 30, 2009  402

Sparse Sales Information

What about all those days between actual sales? How do we select something that isn’t there?

The answer is an outer join to another, unrelated table to fill in the missing rows. The E-Business Suite I support has a bom_calendar_dates table that I often use to densify sales data. It’s simply a list of consequtive dates for the next 50 years. If the database you are reporting from does not have a dates table, you might consider creating one for densification as well. Here’s a simple script to create a dates table in your schema of choice: Dates Table (Edit: If creating a database object is not an option for you, then you can use a row generator to accomplish the same thing. In fact, I now think this is preferable. Check out my updated post: An All SQL Option to Densifying Dates.)

First, we’re going to select the same report date range from our new calendar table:

SELECT c.dates
  FROM calendar c
 WHERE c.dates BETWEEN to_date ('01-JAN-09', 'DD-MON-YY') AND to_date ('31-JAN-09', 'DD-MON-YY');

Next, LEFT OUTER JOIN our original sales data (as an inline query) to the set of rows provided from the calendar table. A NVL() function will supply any missing sales numbers (using zero as the null value). I also replaced the date format mask with TRUNC() in the SELECT and GROUP BY clauses to allow for a clean join to the calendar dates column.

SELECT TO_CHAR (c.dates, 'Mon DD, YYYY') sales_date,
       NVL (s.total_sales,0) total_sales
  FROM calendar c
  LEFT OUTER JOIN (SELECT TRUNC (sales_date) sales_date,
                          SUM (quantity * price) total_sales
                     FROM sparse_sales
                    WHERE sales_date BETWEEN to_date ('01-JAN-09', 'DD-MON-YY') AND to_date ('31-JAN-09', 'DD-MON-YY') +.99999
                  GROUP BY TRUNC (sales_date)) s ON c.dates = s.sales_date
 WHERE c.dates BETWEEN to_date ('01-JAN-09', 'DD-MON-YY') AND to_date ('31-JAN-09', 'DD-MON-YY')
ORDER BY c.dates;

Now our output shows a more complete picture:

SALES_DATE      TOTAL_SALES
 Jan 01, 2009    0
 Jan 02, 2009    0
 Jan 03, 2009    0
 Jan 04, 2009    0
 Jan 05, 2009    502.5
 Jan 06, 2009    125
 Jan 07, 2009    0
 Jan 08, 2009    0
 Jan 09, 2009    0
 Jan 10, 2009    0
 Jan 11, 2009    0
 Jan 12, 2009    0
 Jan 13, 2009    0
 Jan 14, 2009    0
 Jan 15, 2009    225
 Jan 16, 2009    0
 Jan 17, 2009    0
 Jan 18, 2009    0
 Jan 19, 2009    0
 Jan 20, 2009    0
 Jan 21, 2009    0
 Jan 22, 2009    0
 Jan 23, 2009    0
 Jan 24, 2009    0
 Jan 25, 2009    758.75
 Jan 26, 2009    120
 Jan 27, 2009    337.5
 Jan 28, 2009    0
 Jan 29, 2009    0
 Jan 30, 2009    402
 Jan 31, 2009    0

Dense Sales Information

For several weeks sales were flat with increasing activity at the end of the month. Although this technique is most often applied to sparse dates, it can also be used to fill in missing number sequences as well. For some reports you may need to repeat the densification across some subset, such as for a set of products. For this a partition outer join is just the ticket, but that is material for another post.

BI Publisher Graph Values

Posted by Wolf Moritz on February 1, 2009 No comments

I was writing a sales report to be published through BI Publisher (a.k.a. XML Publisher on our E-Business Suite), and in addition to a simple table of the sales numbers, I added in one of those nifty bar graphs. Oracle’s BI Publisher Desktop plugin for Word makes writing nice looking reports very easy (that is, once you have defined your data source, but that is another matter). So adding a bar graph to better illustrate those sales numbers was as simple as following the Template Builder Insert Chart wizard.

The report looked similar to this:

BI Publisher Bar Graph Without Values

Nice! That plus a few more bits of data and the report was ready to publish. As great as the report looked, the initial management response was, “Wonderful, can we add actual values to the bars in the graph?”

I noticed that initially myself, but I did gloss over that detail. The sales reports were a lot more complex than my simple list of tasty foods. One could approximate values from the y-axis, but if the numbers were large enough it was a rough guess at best.

I looked through the Chart Wizard, my local BI Publisher documentation, the usually helpful BI Publisher Forum on Oracle Technology Network, but to no avail. If the answer was there, I couldn’t find it. Finally, Google brought me to the excellent Tim Dexter Blog, where I found this post on Misbehavin’ Baa Chart Labels. Bingo, I discovered I was looking for: MarkerText.

Charts inserted into RTF templates using the Word Template Builder toolbar use images as placeholders for chart size and location. Tucked away in the Web Properties of the image is the Oracle BI Beans DTD code that actually defines the chart. With care, you can fiddle with the DTD to create some really dazzling charts, far beyond the simple options listed in the wizard.

Here’s the recipe to display values in BI Publisher graphs. Double click the placeholder image to open the Word Format Picture dialog (but not to open the BI Publisher Chart properties dialog). Then select the Web tab. This is where you need to add some properties for MarkerText.

Web Properties for Placeholder Image

Here’s some sample MarkerText to add (there are many more options), right after the <Graph seriesEffect=.....> tag:

<MarkerText visible="true"><Y1ViewFormat><ViewFormat decimalDigit="0" decimalSeparatorUsed="true" decimalDigitUsed="true"/></Y1ViewFormat></MarkerText>

And the result:

Now we can see actual Tastiness!

There is much more one can do with MarkerText. We can change the font, angle, position and much more. Some good stuff can be found in Oracle’s documentation on DTD for Customizing Graphs in Oracle Reports.

on flying, hiking, canoeing and Oracle