Actuate Programming Journal

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

Posts Tagged ‘ObtainSelectStatement

Commonly Overridden: ObtainSelectStatement( )

with 8 comments

It is entirely possible to have a significant installation of Actuate that never needs to override the ObtainSelectStatement( ) (OSS).  I haven’t seen one and I doubt it happens much.  Understanding how to override this method is a core skill for a good Actuate developer.

Overriding the DataStream object’s OSS allows you to programmatically design your SQL query based on any sort of external information: user input, configuration files, or even live data.  Users running reports directly or by proxy through schedules could feed your query simple information about the date range desired or the shipping destination of an order.  An external flat file might direct the report to use a development database instead of a production database or maybe one schema instead of another.  A report can even be designed such that one part of the report queries the database first and returns information transmitted in a second query that then populates your report.  There are lots of reasons to modify a SQL statement at run time.

Of course you may just want to preserve the whitespace and layout of your SQL as you go from one tool to build the code to Actuate to run it.

Disable the Superclass

Function ObtainSelectStatement( ) As String 
''' ObtainSelectStatement = Super::ObtainSelectStatement( )  

    ' Insert your code here  

End Function

In nearly every case you are going to comment out or remove the call to the superclass.  Your code will be doing all the work.

Local Variables

Function ObtainSelectStatement( ) As String  

''' CREATE LOCAL VARIABLES 
    dim sqlstmt      as String 
    dim NL           as String 
    dim sqlSchema    as String 
    dim beginDate    as String 
    dim endDate      as String 
    dim category     as String  

''' ASSIGN LOCAL VARIABLES 
    sqlstmt          = ""                    'used throughout to contain the growing SQL statement 
    NL               = Chr$(13) & Chr$(10)   'easier to read output 
    sqlSchema        = cfgSchema             'aids scaling from one schema to another 
    beginDate        = reqBeginDate          'allows for user or schedule input at run time 
    endDate          = reqEndDate            'same 
    category         = reqCategory           'user input  

    [snip - sql statement]  

    ObtainSelectStatement = sqlstmt  

End Function

Plain Substitution

Function ObtainSelectStatement( ) As String  

    [snip - variable declarations]  

''' SELECT '''''''''' 
    sqlstmt = sqlstmt & "SELECT c.customer_name as NAME "    & NL 
    sqlstmt = sqlstmt & ", c.customer_id        as CUSTID"   & NL 
    sqlstmt = sqlstmt & ", c.address            as ADDRESS " & NL 
    sqlstmt = sqlstmt & ", c.city               as CITY "    & NL 
    sqlstmt = sqlstmt & ", c.state              as STATE "   & NL 
    sqlstmt = sqlstmt & ", c.postal_code        as ZIP "     & NL  

''' FROM '''''''''' 
    sqlstmt = sqlstmt & NL & "FROM " & sqlSchema & ".customer c " & NL  

''' WHERE '''''''''' 
    sqlstmt = sqlstmt & NL & "WHERE c.active_dttm BETWEEN timestamp('" & beginDate & "') " & NL 
    sqlstmt = sqlstmt & "                             AND timestamp('" & endDate   & "') " & NL  

''' ORDER BY '''''''''' 
    sqlstmt = sqlstmt & NL & "ORDER BY NAME " & NL 
    sqlstmt = sqlstmt & ", CUSTID "           & NL  

    ObtainSelectStatement = sqlstmt  

End Function

Above you should locate five of the local variables used in the construction of the SQL statement.  ‘sqlstmt’ and ‘NL’ are employed in constructing the statement itself.  ‘sqlSchema’, ‘beginDate’, and ‘endDate’ are used for replacement of strings based on implied user input for the date range and configuration information for the schema.

Yes, I am a little nuts when it comes to whitespace and readability.

Conditional Code

Function ObtainSelectStatement( ) As String  

    [snip]  

''' WHERE '''''''''' 
    If myCategory = "Active" Then 
    sqlstmt = sqlstmt & NL & "WHERE c.active_dttm BETWEEN timestamp('" & beginDate & "') " & NL 
    sqlstmt = sqlstmt & "                             AND timestamp('" & endDate   & "') " & NL 
    ElseIf myCategory = "Closed" Then 
    sqlstmt = sqlstmt & NL & "WHERE c.closed_dttm BETWEEN timestamp('" & beginDate & "') " & NL 
    sqlstmt = sqlstmt & "                             AND timestamp('" & endDate   & "') " & NL 
    Else 
    'No Where Clause 
    End If  

    [snip]  

    ObtainSelectStatement = sqlstmt  

End Function

Based on the value a user (or a pre-made schedule) submits for the “Category” parameter a different date field can be used to filter the data.  ACTIVE_DTTM when “Active” or CLOSED_DTTM when “Closed”.

I’ve seen the OSS overridden to swap out values for every aspect of the SQL statement; swap out values in the select, choose different tables–even add additional tables, drastically remodel the filtering of the where, or resort the ordering.  Most typically I see it used to customize the where clause.

Written by Douglas

20071120 at 18:44 pm

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.

Written by Douglas

20061201 at 12:33 pm