Alpha Video Training
Results 1 to 5 of 5

Thread: Where to define SQL filter for Layout Reports

  1. #1
    Member
    Real Name
    Garry Allen
    Join Date
    Oct 2011
    Location
    Kingston, ON, Canada
    Posts
    119

    Default Where to define SQL filter for Layout Reports

    I have created a layout report using the report builder. It works fine (after many hours of screwing around!).
    In order to do this I had to build a SQL select statement which I made fairly generic, only specifying arguments for a date range (which will apply to all reports)
    I have created a dialog to get the date range arguments and additional parameters for the report which also works fine.
    I want the user to be able to specify one Company or all, and one Boat or all (this is a reservation system for a Boat Tour). I can do this ok in my dialog.
    My problem is that I need to modify the report select statement to take into account these extra parameters and I'm at a loss as to where to do this.
    I guess I would like to modify the where clause based on the dialog input.
    Can anyone point me in the right direction?
    Garry

  2. #2
    Member -Jinx-'s Avatar
    Real Name
    Jinx
    Join Date
    Jul 2012
    Location
    Wisconsin
    Posts
    963

    Default Re: Where to define SQL filter for Layout Reports

    Right click on the report in your control panel and choose "edit data source definition".

  3. #3
    Member
    Real Name
    Garry Allen
    Join Date
    Oct 2011
    Location
    Kingston, ON, Canada
    Posts
    119

    Default Re: Where to define SQL filter for Layout Reports

    Yes, I realize that I can do that but how do I generalize the statement:
    i.e. in one case the customer chooses a particular client and the resulting statement would be:
    select * from (some file) where Cust_ID = :CustID
    but if he chooses ALL clients it would just be:
    select * from (some file)

    So far, the options seem to be to duplicate the reports with separate select statements (horrible in terms of maintenance)
    or
    I noticed another thread where the select statement looked like select * from (somefile) where somefunction().
    The latter looks interesting but might be slow.
    It seems this sort of thing must be a very common requirement and I'm just missing something obvious.

  4. #4
    Member -Jinx-'s Avatar
    Real Name
    Jinx
    Join Date
    Jul 2012
    Location
    Wisconsin
    Posts
    963

    Default Re: Where to define SQL filter for Layout Reports

    Sorry, I misunderstood. Can't you do it in the report action in the button? There are quite a few filtering options there. I believe you could also define your argument as the entire WHERE clause. For example, the value of the argument :CustID could be "WHERE Cust_ID = 'yourvalue'" but I'm not positive that it works as I haven't tried it myself. You could also make a calculated field on your dialog that would set the filter.

  5. #5
    Member
    Real Name
    Garry Allen
    Join Date
    Oct 2011
    Location
    Kingston, ON, Canada
    Posts
    119

    Default Re: Where to define SQL filter for Layout Reports

    Thank you! I had put Submit/Reset buttons on the form and I couldn't see anything related to "Can't you do it in the report action in the button?"
    Your comment made me think of just putting a regular button on the form, which I did and Add an Action allowed me to add a Report complete with a Select statement.
    I then realized that all I had to do was select Action javascript on the original button and ignore the warning about 'existing code cannot be converted...'.
    All this is just a round about way of saying I was stupid and thanks for pointing me in the right direction.

Similar Threads

  1. Layout reports - styles
    By Peter.Greulich in forum Application Server Version 11 - Web/Browser Applications
    Replies: 6
    Last Post: 07-12-2012, 06:03 PM
  2. Update Active Link Table and SQL reports SQL Statements from Xbasic?
    By compuaid in forum Alpha Five Version 11 - Desktop Applications
    Replies: 0
    Last Post: 05-01-2012, 07:27 AM
  3. Converting Freeform reports to Layout reports
    By Peter.Greulich in forum Application Server Version 11 - Web/Browser Applications
    Replies: 2
    Last Post: 04-10-2012, 11:55 AM
  4. Define source of list box need sql statement with where on page var
    By bobalston in forum Application Server Version 11 - Web/Browser Applications
    Replies: 0
    Last Post: 02-24-2012, 05:12 PM
  5. Define filter in link to child table of set, problem/Bug ?
    By dpawley in forum Alpha Five Version 7
    Replies: 2
    Last Post: 04-03-2006, 01:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •