Re: Report Builder and runtime query construction

It's definitely possible, I've done it with Reports 2.5. I created report parameters that were populated during the before-report trigger that contained the additional text needed for one particular execution, then referenced those parameters from the hard-coded SQL statement.

This technique works well if you only have to add text in the FROM, WHERE, and ORDER BY clauses. You can also use it to dynamically select a column in the SELECT clause, but you will always need to have the same number of columns selected.

The SELECT statement in Report Builder would look something like this: SELECT
  &DYNAMIC_COLA COLA,
  TABLEA.COLB,
  TABLEB.COLC
FROM
  TABLEA,
  TABLEB
  &DYNAMIC_FROM_CLAUSE
WHERE
  TABLEB.FK = TABLEA.PK
  &DYNAMIC_WHERE_CLAUSE
ORDER BY
  &DYNAMIC_ORDER_BY_CLAUSE
In this example, DYNAMIC_COLA, DYNAMIC_FROM_CLAUSE, DYNAMIC_WHERE_CLAUSE, and DYNAMIC_ORDER_BY_CLAUSE are all report parameters whose values are set during the before-report trigger.

Gigio wrote:
>
> Hi all!
> I'm writing a report with report builder 3.x and I would like to
> know if someone can tell me how is it possible to build queries
> at runtime. This is because I have some parameters that user can
> enter or not, so the query could have some where criteria only
> if requested.
> Thanks in advance
> Gigio
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
> The fastest and easiest way to search and participate in Usenet - Free!
Received on Mon Jan 31 2000 - 00:00:00 CET