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

Restricting designer queries

edited December 2002 in End User
Up until now, I've only used RB in single agency settings. I now want to
add designer capabilities to an application that runs in a multi-agency
environment. What I'm not sure of is how to restrict queries that users
build so that they only search data for their agency. With SQL queries I
hard-code, this is done by specifying the value of the agency field in
various tables. Is there a way to do this globally in RB designer or to
intercept queries that users develop remotely and add an agency field
value to the SQL?

Mark Shapiro
infocus@swbell.net

Comments

  • edited December 2002
    Use the data dictionary component to control the tables/fields/joins that
    the user has available when designing the dataviews.

    If you want to not use the dictionary, but rather filter the data based on
    user rights, then create stored procedures on the database to limit the
    data. If the DADE plugin supports stored procs for your database, then it
    will just work fine. If the user is not going to create dataviews, then you
    can create autosearch criteria on the fly behind the scenes (no autosearch
    dialog) when the report runs in order to limit the data based on a user.
    See the autosearch topic in the RBuilder.hlp file.

    You can pull out the TdaSQL object from the dataviews in the report. This
    allows you to change the search criteria value as shown in this example:

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

    Set the criteria in a call like this:

    lSQL.Criteria[0].Value := '1221';

    You'll have to write some code to find your field in the criteria array
    property. Use these two properties on TdaSQL:

    property Criteria[aIndex: Integer]: TdaCriteria read GetCriteria;
    property CriteriaCount: Integer read GetCriteriaCount;

    Then compare the TdaCriteria.Field to see if that is the TdaField that you
    want to use to set the "user access rights filter" value.

    Cheers,

    Jim Bennett
    Digital Metaphors

  • edited December 2002
    Jim,

    Your pointers on the TdaSQL object are exactly what I was looking for. I
    will dive into the code in the new year. Thank you.

    Mark Shapiro
    infocus@swbell.net
This discussion has been closed.