Actuate Programming Journal

Report design from a guy who thinks he knows what he’s doing; you decide . . . .

Overriding ObtainSelectStatement( ): Why

with 2 comments

20061218 Update: Sorry for the big tease.  Life slapped a big old “Hold” on me and it’ll be a bit before I can get to the other parts.  Check back, leave comments, or for God’s sake subscribe to the RSS.

This is one part of a five part series: Why, How 1, How 2, How 3, When, Conclusion

Without reservation I always override the ObtainSelectStatement (OSS( )) in lieu of using the Graphical Query Builder (gQE) or even the Textual (tQE) one. It’s been years since I wrote a simple SQL statement without parameters or other dynamic elements, but I am sure I would find myself typing it out by hand in the OSS( ) even if I did get a chance to write something plain.

Why?

I like to work with my hands. And, while I wouldn’t go so far as to claim to be a real programmer, I do like to be able to manage the SQL at the written code level rather than just draw pictures of it. Overriding the OSS( ) allows me to program old school.

The gQE is a great tool for visualizing the database and even constructing SQL (more so than it used to be). You can easily indicate what fields you want to report on, create reasonably sophisticated predicates for filtering data, and even add different kinds of parameters to change things on the fly. The first project I went on back in ’98 used a combination of the gQE and the OSS( ) to build out the SQL logic. SQL statements that could be designed in the gQE was. The more complex needs were worked out by an unholy dovetail of the gQE and the OSS( ).

Even the years I taught the Actuate Suite, I relied upon the gQE. It was fast and obvious and something the students could see. Plus the official materials used it. At the time I hated the syntax for identifying parameters and to this day don’t know how to write them into the gQE.

But let’s fast-forward to my more recent efforts…

In order to meet the often schizophrenic needs of our customers I need to be able to write flexible SQL. SQL that can add an additional table and the requisite joins based on a runtime parameter. Or I need to collect a piece of data from another query in the report and use it in some nested Report Section. Or I need to conditionally modify the predicates in the Where Clause. Or I just have a big-ass database and can’t be bothered with waiting on all the thousands of tables to load in the browser.

In any case, I need more control.

More access.

Less hand-holding.

Overriding the OSS( ) gives me the power I crave.

I’ll show you how.

Advertisements

Written by Douglas

20061201 at 12:33 pm

2 Responses

Subscribe to comments with RSS.

  1. Interesting, yes~ but what would be nice, is some examples. I have searched the internet for examples. No such luck. I use Actuate because the reports that come with Clarity are writen using Actuate. I am a report writter and have had 2 classes, eRD Pro and Developing Advanced Reports. I used the canned reports that where sent with Clarity (Using actuate 7 sp2) to learn how to Override the ObtainSelectStatement for Clarity reports. It would be nice to have a spot in the [internet] for new users to come and learn. Thanks
    Keri~

    Keri

    20070502 at 11:34 am

  2. Absolutely some examples are in order.

    Just gotta find the time and pull my head out–maybe it’s the other way around. In any case, I will put that as high up on the list as I can get.

    Douglas

    20070502 at 12:50 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: