Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

Setting AutoSearch criteria after Editing SQL. (Again)

edited May 2003 in End User
Hi,
I'm asking the same question again.(It seems my previous post has been
deleted) How can End-User use AutoSearch criterias after he manually edited
SQL?
Yes, I downloaded "EditSQLAndSearch" program which was supposed to explain
this to me, but unfortunately in my case it was not enough.

You see, the main reason why I need to use workaround methods is because the
reports that are going to be created using ReportBuilder will have VERY
large and complicated SQL queries. It is normal that queries up to 50(!)
lines lenght are not created using your SQL Designer, but rather using
professional tools for SQL creation like TOAD ot TORA. Thus it is only
natural to create complex SQL query and copy-paste it to the report Designer
in the Data Tab. Certainly, another end-user that will use this report with
complex SQL wants to get only the desired data - not the half of database
data for one report. This is where I, as a software developer should help
the end user, providing him with the means to filter resulting data
(received from SQL) thus obtaining only necessary information. But, I don't
know how can I do it, because everything is connected with SQL
representation in the Report Builder.
The example that you provided me with - "EditSQLAndSearch". It uses the fact
that an SQL query has already been inputted using standard means and you
just change several properties of the SQL object and that is that. But what
do I need to do in a case when user makes copy-paste with another - much
more complex SQL query and still wants to be able to filter the reports
using provided AutoSearch means?

I would appreciate it very much i you were able to answer this to me.

Thanks in advance,
MB

Comments

  • edited May 2003
    It should be possible. You'll have to parse the SQL text looking for the
    WHERE clause and update it manually in your code. Then you'll have to have
    AutosearchFields created by the end user, so a RAP pass through function is
    needed to do this from RAP. I started working on an example last week but
    didn't get a chance to finish it. I'll try to get something together for you
    this week.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited May 2003
    Thank you,
    I'm looking forward for an exampe.

    Also I would like to note that SQL parsing is a very hard task.
    Let's see about Oracle conventions. First of all WHERE clause might not be
    presented in the query at all. Then we would have to include it on our own.
    But how do we know where to put it? Also consider select with several
    subqueries some of which may have and may not have WHERE clause.
    Also after WHERE clause might come anythig: ORDER BY, CONNECT BY, SORT BY,
    GROUP BY, UNION, INTERSECT, MINUS and so on...

    Be careful about this because parsing SQL MANUALLY may not be such a good
    idea after all.

  • edited May 2003
    RB was never intended to work this way. Either use the query tools to
    generate SQL or enter it by hand and lose the query tools support as well as
    lose autosearch support. Yes, the workaround will get very complicated if
    parsing the SQL, and any SQL that could be entered by your users. I was
    thinking that if you want to edit the SQL and use autosearch, then it is
    possible, but you'll have to code for it since RB doesn't natively support
    it.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited May 2003
    Check out this updated example. It creates autosearch fields even after the
    user edits the SQL manually. You'll have to add more code to handle the SQL
    parsing, but this can be accomplished.

    http://www.digital-metaphors.com/tips/EditSQLAndSearch.zip


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited May 2003
    Ok, thanks for the help.
  • edited May 2003
    I can?t download from this link.

    Bernd
  • edited May 2003
    Sorry, the website was down a little while last night. Try it again.


    Cheers,

    Jim Bennett
    Digital Metaphors


This discussion has been closed.