Monday, April 23, 2007

Totals and blank lines

The following is an interesting conundrum that I have come across recently and is something that you might be interested in knowing about. It's concerned with what happens when you combine sub-totals and additional rows between group sorted items.


In the following worksheet, as shown in illustration 1, you can see that I have a group sort on the Customer with a sub-total on the Profit. A not uncommon scenario I hope you will agree.

Illustration 1: A group sort with the sub-total displayed correctly

Next, I right-clicked in the Customer and from the pop-up menu selected Format Table. Discoverer opened up the Format Data dialog box. This box has three tabs:
  • Format - on this tab you set up the standard formatting characteristics such as font, size, background color, foreground color, alignment and so on

  • Text - on this tab you can set additional characteristics such as uppercase, lowercase or capitalization

  • Breaks - on this tab, which is only enabled if you are formatting a Group Sort, you can control what happens when group sorted items change. As you can see in illustration 2, I have enabled a line width of 2 in red, and the insertion of one blank row between each group sorted item.
Illustration 2: The Format Data dialog box and the Breaks tab

After I clicked the OK button in the above box, Discoverer redrew my screen, but drew it incorrectly as shown below in illustration 3. As you can see, the totals still calculate correctly. But take a close look at what happened on the totals line for each sub-total other than the first one. As you can see, the word NULL has been inserted against each of the other items. In addition, take a very close look at row 9 and in particular at the cell value containing the Profit. As you will see, Discoverer has placed the Sum label here instead of alongside the $23,939.00 on the previous row.

It therefore appears that the insertion of an additional line for the group sort has caused the label to move down by that number of rows also. This means that Discoverer does not know what to do with the cell values on the original sub-total and so it inserted a NULL value. Formatting the table to change the display of NULL values to something other than NULL does indeed change the wording.

Illustration 3: A group sort with the sub-total displayed incorrectly

Therefore, please be very careful when using additional lines between group sorted items when there are sub-totals. The following illustration shows the same worksheet displayed in Viewer. As you can see below in illustration 4, Viewer does not appear to have this issue. I can therefore only assume it is something to do with the Java.

Illustration 4: The same group sort, as seen in Viewer, with the sub-total displayed correctly

Friday, April 13, 2007

The future for Discoverer

From time to time people approach me and ask me what do I think will happen to Discoverer now that Oracle have Siebel and Hyperion (including Brio). The pessimists in the world think that Oracle plan to drop Discoverer. I don't believe this for one minute.

But why don't I believe this? Well for a start Oracle have new releases of Discoverer planned for this year (, and next (11g). However, for fun, let's assume that Oracle do plan to drop Discoverer. They won't announce this in the same year that they release 11g because that would just make folks not even bother to use the latest version. So the earliest I would see such an announcement would have be in 2008. However, as all Oracle products carry a 5 year full support agreement from whenever the drop is announced this would mean that Discoverer would remain a fully supported, patchable, fixable product until at least 2013. That's a lifetime in terms of software. That's 6 years from now. Oracle will have bought SAP by then. Maybe even the San Francisco Giants will have managed to win a game against the Padres!

However, the real reason I don't believe Oracle will drop Discoverer is because of some very favorable comments coming out of Oracle.

Firstly, Oracle has recently revamped its Standard Edition page on (link) which makes for very interesting reading.

Secondly, follow the link for the data sheet (link) and you will get to a PDF document that outlines just where Discoverer fits into the Fusion picture. With acknowledgements to Oracle, here are some interesting snippits from that document:

You've already chosen Oracle database for your business, now make it shine by complementing it with the business intelligence suite that is most tightly integrated with it - Oracle BI Standard Edition.

Oracle BI Discoverer is [tightly] integrated with other components of Oracle Fusion Middleware.

Discoverer reports are easily embedded within Oracle Portal using a simple wizard, making role-based dashboard creation a simple exercise.

Oracle Discoverer is also unique in that it recognizes and respects the Oracle E-Business Suite security model.

Today few of us buy best of breed kit parts and hand craft our own cars; most of us simply go to an established, proven manufacturer to obtain a complete solution, backed by a warranty with a single support organization should anything happen. Software should be like this - and this is where Oracle BI SE delivers. Everything - from ETL (with Oracle Warehouse Builder), to ad-hoc query (with Oracle BI Discoverer), to dashboards (with Oracle Portal), to pixel-perfect reporting (with Oracle XML Publisher), to custom BI development (with Oracle BI Beans) - is available as an integrated set of components.

The entire middleware solution can be effectively clustered with low cost hardware. Combine this deployment flexibility with an attractive pricing model for BI and middleware components, and you will be hard pressed to find a more affordable fit for your growing BI needs.

There - I could not have said it better. I think this clearly shows Oracle's intentions.

Wednesday, April 11, 2007

Looking for a great Discoverer position

If you are an experienced Discoverer developer and are looking for a great position and opportunity in Washington, DC, then we may have the job for you. Click here for more details.

Monday, April 09, 2007

Workbook Migration Document

By popular request I have made my Workbook Migration white paper available for free download on my website. You will find it at this link.

It now joins these other white papers available for download:
  • Discoverer libraries - a concept
  • Setting up an Apps mode EUL
  • Multiple Oracle Homes