Actuate Programming Journal

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

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.

Advertisements

Written by Douglas

20071120 at 18:44 pm

8 Responses

Subscribe to comments with RSS.

  1. This seems to be the best explanation of the ObtainSelectStatement method on the Internet. It took me a while to read and figure out how to override this method.
    How about putting in some short and sweet examples that make sense at the first glance. Like SELECT ID, NAME, AGE FROM EMP WHERE NAME=’BOB’
    Good Work.

    sir_custodian

    20080518 at 19:02 pm

  2. Hi Douglas/Everyone else

    (Apologies of this posting is inappropriate here)

    I’m struggling with the OSS – I feel like a stupid person, but I seem to have hit a wall and none of the other guys in the office seem to be able to help. I’ve over ridden the OSS in my report – no big deal but a long query. However, I get the following error in Actuate when I run the report:
    Basic Error: 1011 Module: C:\Program Files\Actuate8\eRDPro\afc\db.bas Line: 193 Database Error 16: The parameter identified is not known to this statement.

    I’m trying to sort it out. One colleague believes there’s an inconsistency regarding the parameters I’m passing to me query and the parameters that the DataStream believes it should get – I’m quite sure it’s not the case, but I’m at my wits’ end.

    I googled the error and found three references, none of which are any help.
    Any ideas?

    Henri-John Kock
    Junior Maximo consultant
    South Africa

    Henri-John

    20090205 at 03:31 am

  3. @Henri-John,

    I feel like a stupid person nearly every day. I’m sure we’re in good company.

    That error comes straight out of a generic raise error bit of code in the DB.bas file. If you actually go to line 193 in the file mentioned you can confirm that it’s of no real help. Most errors arising from db.bas aren’t of much help.

    I’ve not seen this error myself, but because of the wording and the thoughts of your colleagues I’m inclined to think your OSS( ) is generating a SQL string that contains a database variable that the database is expecting you to supply. I’d pull the SQL string and look at it in another tool to find the culprit.

    This may have happened because you’ve copy|pasted chunks of SQL from an external source into the OSS( ) or maybe even accidentally used some syntax your DB is interpreting as a parameter rather than what you think it is. I’m not at all familiar with parameters in SQL so I couldn’t say for sure.

    My email address is actuatejournal@gmail.com if you’re comfortable passing along the code for me to review.

    Douglas

    20090205 at 08:49 am

  4. To Henri-John,
    The error might be due to referencing a non-existing parameter inside some custom code. Chech for any customized methods.

    David

    20090530 at 10:01 am

  5. please reply me!
    I have actaute 8 report containing two textual query. I want to use them in mater-details relation. pasing some parameter from one textual query to other.

    Could some body explain how do i accomplished it?

    Regards, maximo developer

    maximo developer

    20100315 at 02:59 am

    • @maxdev

      Your actual question talks about passing (or maybe sharing parameters) between two TQEs–which you can’t and don’t need to do. The TQE uses report level parameters and any textual query you might have has access to any of those regardless of the other TQE. However, it sounds like you have ‘nested’ textual queries. One is the logical parent of the other and you want to pass information from the parent to the child so the child query is dynamically modified based on the results of the parent.

      I’ll get beat up by any serious OO folks out there, but I like to do this with a report level parameter and not a global variable or a variable on the root control. I’m lazy and this is a fast and foolproof way to get it done.

      You will need to override the OSS( ) as note described above for both parent and child queries. You can’t use the typical TQE functionality.

      Define a report level parameter.
      In the Fetch( ) of the parent query capture the data value you want to modify the child query with.
      Still in the Fetch( ) assign that value to you parameter.
      Write the child OSS( ) in such a way to include that parameter in the string you’re building; similar to how I’ve handled dates above.

      Debug and enjoy!

      palisade14

      20100315 at 07:55 am

  6. Thanks very much for taking the time to write this!
    this saved my arse! 🙂

    cheers

    Matt

    20111001 at 23:16 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: