QLR Systems Administration

The Systems Administration functions are only available in the Enterprise Edition of QLR Manager. They are accessible from the  Connect  panel by selecting "User ID Admin and Tools" when logging on. The available functions include the creation of Input Controls, Data Export of QLR data for transfer to another installed instance, Data Import from various file types such as CSV, and User ID Administration. Access is initially limited to the User ID established during the install process, but using the  User ID Admin  panel, other IDs can be granted the same authority. This topic also includes some information about re-branding or integrating QLR Manager with your website and updating the  Connect  panel message.

User ID Administration

The Enterprise Edition of QLR Manager allows you to manage access for each of your users. Access can range from permitting full authority to limiting a user to running reports from a single menu.

Controlling user authority to perform certain actions is accomplished in two ways. The default authority for all users is established by retrieving the *default* User ID from the  User ID Admin  panel and making the appropriate selections. These values will become the defaults for every user. The second level of control is to establish an entry for individual users by creating a User ID that is the same as their database log on ID and make the desired selections. When QLR Manager finds a saved User ID, the selected authority values saved for the ID will take precedence over the values set for the *default* User ID.

Authorities are managed down to the connection level (server name, TNS name server, ODBC source, etc.) within each database engine. For example, if your environment has both MySQL and Oracle databases, then User IDs and the *default* ID will be present for each database engine. In addition, if you were to have separate database servers in either environment, ID's and the default ID can be established for each server.

For example, suppose in your MySQL environment you have data on a localhost server and a remote server of 192.168.2.15. You could establish User ID settings that apply to both servers by leaving the Optional specific server / DSN reference field as blank. Since no server is specified, the same User ID profile would be used for either server connection. You could also create a second profile that is saved with a specific server reference, such as 192.168.2.15. When the specific server reference is found for that User ID, that is the profile that is used to control what that ID can and cannot do. The same holds true for the *default* ID. The generic *default* (which does not specify an optional server) can be overridden for a specific server by specifying the optional server reference when saving an additional *default* ID record.

ID Status:

If you wish to prevent a specific ID from using QLR manager, you can "Lock" them out of the system. This is accomplished by setting their ID Status to "Locked". Setting the *default* ID Status to Locked will prevent all users from using QLR Manager, except those that have their User ID established and saved with a Status of "Active".

Access control:

The Access control setting determines which functions a user has authority to use. When checked, the User will have authority to view the corresponding panel (Query, Layout, Define Macro, and Create Menu, etc.). If access is granted to the  Query  panel, the User will automatically be given access to the  Layout  panel as well. The Select Menu selection will allow a user to select existing menus from the  Menu  panel. If the Select Menu option is unchecked, a "Default menu owner" and "Default menu name" must be defined in order for the User to have menu items to run.

If you wish to limit a user's capabilities, uncheck all the boxes and create a default menu. When this approach is used, QLR Manager is transformed into a reporting interface for that user. All the User will be able to do is to run queries and macros that have already been defined in their Default menu. See [qlr_userid] query variables for information about building dynamic queries based on the User's ID. This may be useful for building a single menu that can be used as the default menu for multiple users.

Query execution restrictions:

The Query execution selections provide a means to restrict the type of queries a particular User ID can execute. This applies to queries executed from the Query, Wizard, Macro and Menu panels. If "None" is selected by itself or with other options, there are no restrictions on the type of queries the User ID can execute. Deselecting all options, including "None", will be treated as if "None" (no restrictions) was selected. The most common restriction would be "Select", which would limit the User ID to executing only SELECT queries.

Save limits:

The Save limit defines how many of each object type can be saved. The values initially displayed are derived from the *default* User ID settings, but can be overridden for a specific ID.

Note: If the "Query save limit" is set to 0, the User ID will not be able to create Input Controls. Any value greater than 0 for the Query save limit will enable the User ID to create and save an unlimited number of Input Controls.

Query edit access:

The "Allow query edit" setting for the User ID determines whether the User can edit, execute and save retrieved queries. If unchecked, the  Query  panel is effectively a read-only environment for that User ID.

Viewing shared objects:

The "View shared objects" setting allows you to hide shared objects from the User. If unchecked, the User will only be able to see queries, layouts, macros and menus that are saved under their ID. Other user's shared objects will not be displayed.

Saving shared objects:

This setting allows the Administrator to control whether a user is allowed to share objects when they save them. If this is unchecked, then the "Share" label and checkbox is omitted from the Save control.

Default menu owner and name:

The "Default menu owner" and "Default menu name" fields allow for the association of a specific menu with a User ID. This is the menu that will be displayed when the User accesses the "Menu" (General User) or the "User Menu" (Restricted User) panels. If the User has "Select Menu" authority, they will be able to choose a different menu from a list of menus shared by other users. If the User ID is being established without Select Menu authority, the Default menu owner and name must be entered.

Defining a QLR Manager ID:

QLR Manager supports the creation of "QLR Manager IDs". You can create IDs that are known to QLR Manager (pseudo ID), but actually connect to the underlying database using an ID that is defined in the database engine. For example, you can create pseudo ID's for each of your customers, but the connection to the database is accomplished using a single common database ID. This can also be useful in a shared hosting server environment where the hosting service provides a single connection ID, but you have more than one person using QLR Manager. These IDs can be established by selecting "User ID Admin and Tools from the  Connect  panel, clicking the  User ID Admin  header tab and expanding the Define QLR Manager ID section. Saving entries from this panel replaces the function previously available using the qlrid.php file.

In order to have the User ID act as a QLR Manager ID, three values must be provided:
 •   The password for the new QLR Manager pseudo ID being created and saved.
 •   The existing database connection User ID with authority to your database(s).
 •   The password for the existing User ID through which the new ID will connect.

Note: If you change the password for the existing database connection ID in the database engine, all User IDs that use this connection ID will be updated to use the new password. This will allow the QLR Manager pseudo IDs to maintain their access.

Forcing the use of a QLR Manager ID:

There may be times when you wish to force the use of a QLR Manager ID and Password. One such example is for security purposes when accessing SQLite databases. SQLite databases do not have User ID and Password management built into them. You can force QLR Manager to look at the qlr_userid table data to check if a User ID and Password has been established by the Systems Administrator. This is accomplished by adding an entry to the qlr_info table to tell QLR Manager which database engines require a qlr_userid table entry:

insert into qlr_info values ('db_engine_needs_id','sqlite3, excel')

The above entry would force the use of qlr_userids for both SQLite3 databases and Excel files. The database names entered are case insensitive. Valid entries are access, db2, excel, firebird, mysql, odbc, oracle, postgresql, sql server, sqlite and sqlite3.

Note: The Systems Administration functions to create QLR User IDs and Passwords are only available in the Enterprise Edition of QLR Manager. They are accessible from the  Connect  panel by selecting "User ID Admin and Tools" when logging on.

Creating input controls

Users with the authority to access the Edit Input Controls panel and the ability to save queries as established in their User ID profile, will be able to create an unlimited number of input controls. For more information about all the available HTML form elements that can be created to collect user input, please reference the separate topic about Input Controls.

Tracking query and macro usage

The use of queries and macros can be tracked by issuing the following query which will add an entry into the qlr_info table:

insert into qlr_info values ('track usage','yes')

This will instruct QLR Manager to add information into the table qlr_usage_log every time a user executes a stored query or macro, including execution from a menu. Running a select query against the qlr_usage_log will provide information about the objects executed, the connected User ID and the date/time of execution.

To turn off usage tracking, the following query can be executed:

delete from qlr_info where ref='track usage'

Connection logging

Every time someone logs into QLR Manager, the event is logged into a table called qlr_connection_log. After one day, the data is moved into a history table called qlr_connection_logh. By default, the data is maintained in the history table for 730 days (2 years). The length of time that the historical data is kept can be adjusted by adding an entry to the qlr_info table to specify the number of days to keep the data, such as:

insert into qlr_info values ('connect_history_days','900')

Exporting QLR data for use in another QLR Manager instance

The Data Export function allows QLR Queries, Layouts, Macros, Menus and User ID profiles to be selectively extracted for importing into another installed instance of QLR Manager. A common use for this capability would be to create new queries and layouts in a test environment and later export them for transfer into a production environment.

The Data Export facility provides a means to specify which type of objects to export (queries, layouts, etc.) and produce a file containing a set of SQL statements. The contents of this file can be copied and pasted into the  Query  panel of another QLR Manager instance and executed as a batch query simply by clicking the  Run Query  button. The "Ignore query variables" checkbox should be checked so that any existing query variables are ignored when the batch query is executed.

SQL delete statements can be created to replace objects that may exist with the same owner and name by checking the "Create SQL delete transactions" checkbox found in the  Data Export  panel.

Objects can be selectively extracted (except for User ID profiles) by using the SQL selection criteria area that is provided for both the Owner like and Name like fields. For example, entering "nickd%" into the Owner like field would select all the selected object types belonging to "nickd". To create a file containing all the saved queries and layouts beginning with "prod", Queries and Layouts could be selected as the object types to export and "prod%" could be entered into the Name like field. The Data Export feature uses the SQL LIKE syntax for data selection.

Integrating QLR Manager into a website or product

The recommended method of integrating QLR Manager into your website is to launch the application in a separate window. It is also possible to nest the QLR Manager frameset (3 frames) into its own frame on your site. When using a frameset, it is important to name the frame loading qlrmanager.html as QLR. This is necessary for the Javascript code to execute correctly. A typical frameset configuration with QLR Manager nested in a frame may look like:

<frameset border="0" frameborder="0" framespacing="0" rows="50,*">
  <frame src="top_frame.html" scrolling="no" />
  <frameset border="0" frameborder="0" framespacing="0" cols="100,*">
    <frame src="left_frame.html" scrolling="no" />
    <frame src="./qlrmanager.html" name="QLR" scrolling="no" />
  </frameset>
</frameset>

You may have an application where you wish to integrate QLR Manager into your website or bundle it with a product and provide a more seamless look and feel. After purchasing a license or entering into an OEM marketing agreement with Tatler Software, we do permit this re-branding. There are three aspects of customization available, depending on the extent of the modifications desired:

 •   Replacing the header files used throughout the product to incorporate your corporate logo and change background colors.
 •   Modify the stylesheets located in the /qlr_manager/styles directory.
 •   Add entries to the qlr_info table to override defaults for some styles that must remain inline.

We have a set of files that can be provided on request that show examples of extensive re-branding and customization. We also offer the service to customize QLR Manager to meet your requirements. For more information about re-branding, please contact us at sales@qlrmanager.com

Short of a total customization, there are some basic entries that can be inserted into the qlr_info table to modify some common attributes.

The page background color for every panel in QLR Manager can be changed with the following entry:

 •   insert into qlr_info values ( 'background color','#FFFFFF');

The default values for PDF creation in Report tools can be updated with these entries:

 •   insert into qlr_info values ('tls_pdf_hdr_text','your value');
 •   insert into qlr_info values ('tls_pdf_background','your value');
 •   insert into qlr_info values ('tls_watermark_text','your value');
 •   insert into qlr_info values ('tls_widget_title','your value');
 •   insert into qlr_info values ('tls_filename_text','your value');

The Report tools and Output tools options that are displayed to the User can be limited by adding the following entries into the qlr_info table. The values shown are the actual values that are used to control which options will be available.

Note: The QLR Master ID is considered the "Super User" and will always see all the output options instead of those limited by the table entries below:

 •   insert into qlr_info values ('tls_report_options','print, format, data, pdf, xml, email, dist, widget, table');
 •   insert into qlr_info values ('tls_output_options','print, html, email, widget');
Bypassing the Connect panel

QLR Manager provides a means of bypassing the  Connect  panel to log on. This can be very useful if you set up a common User ID for your user community to execute items from a menu environment. In the top level QLR directory, there is a file called qlrgo.html that can be launched instead of qlrmanager.html to create such access. The bottom frame of qlrgo.html references a file called autolog.html, also found in the top level QLR directory. This file must be edited to include the following logon variables for your particular application (highlighted in blue):

var User_ID = "guest2";
var Password = "guest2";
var DB_Server = "localhost";

The variables above are each required. In addition, if your logon requires a connection to a specific database, you can update the following variable:

var Database = "my_database";

Note: When using this feature, you are exposing the User ID and Password in the source of the web page, so please consider the security aspects of this approach.

If the specified User ID only has User Menu access as configured using User ID Admin, executing this html file will take the User directly to the User Menu panel. If the User ID has Query panel access as well, then they will be taken to the Query panel. If you wish to direct a user with both Menu and Query panel access to the Menu panel instead of the Query panel, a value of &umenu=umenu should be declared for the User_Menu variable in autolog.html:

var User_Menu = "&umenu=umenu";

The files that facilitate this  Connect  panel bypass (qlrgo.html and autolog.html) can be copied and renamed to create as many instances of this type access as needed. Be certain to synchronize the two files to achieve the expected result. For example, if you create a second copy of autolog.html, such as autolog2.html, make sure you update the second copy of qlrgo.html to reference the correct autolog file:

<frameset onunload="closeWin()" rows="68,26,*" border="0" frameborder="0" framespacing="0">
  <frame src="./qlr_manager/qlr_logon.html" name="header" scrolling="no" />
  <frame src="./qlr_manager/messages.html" NAME="message" scrolling="no" />
  <frame src="./autolog2.html" name="QLRmanager" scrolling="auto" />
</frameset>

Customizing the Connect panel
The  Connect  panel can be customized to omit the following login fields:
 •   DB engine
 •   Server
 •   Function

The DB Engine and Server fields are the 1st and 4th fields displayed on the  Connect  panel. When omitted, the values used for login will be those found in the qlr.ini file as the db and appServer values entered when QLR Manager was first installed. The Function selections are normally displayed below the login fields and will also be omitted when the following technique is used.

Removing these login fields is accomplished by modifying the Javascript in qlrmanager.html. Following is the default Javascript init() function in qlrmanager.html:

function init(){
  if(iniChk)return;
  iniChk=1;
  window.location.replace("./processreport.php?Language=1");
}

In order to omit fields 1 an 4, the init() function is altered to specify that only fields 2 and 3 are displayed by adding &Login=2%2C3 to the URL string. The %2C is the url encoded value for a comma, which the server will decode to 2,3. Fields 2 and 3 must always be present. If it is desired to omit only the database engine field and display fields 2,3 and 4, the url adjustment would look like &Login=2%2C3%2C4.

The "Start-up preference" can also be added to the URL string by adding &Preference= after the login values. A preference value of 1 would direct the User to the Query Editor, a value of 2 would start with the User Menu and 3 the Query Wizard. If no preference is added, the login default will load the User Menu. The modified URL string in the init() function might look like the following:

function init(){
  if(iniChk)return;
  iniChk=1;
  window.location.replace("./processreport.php?Language=1&Login=2%2C3&Preference=1");
}
The Connect panel message

QLR Manager provides a means of displaying a message to your user community on the  Connect  panel. This is accomplished by editing the file conmsg_xxx.html (where xxx represents the language code) that exists in the directory where QLR Manager was installed, the same directory where qlrmanager.html is located. This file can be used to convey a simple message to your users, such as "The Inventory database is down", or a more complex HTML formatted file with graphics. An example can be viewed at our Online Demo page.

Following are two examples of different box style formats for conmsg_xxx.html:

Fieldset box style:
 Welcome to QLR Manager 

This is an optional Connect panel message that can be customized to convey information to your users or personalize this installation of QLR Manager for any purpose. The contents of this message exists in the file conmsg_enu.html, which is located in the directory where QLR Manager was installed. If you want to omit this message box, you can delete or rename (recommended) conmsg_enu.html.

HTML source:

<!-- STARTS BOX BORDER AROUND HTML MESSAGE -->
<div style="margin-right:10px">
<fieldset style="padding:2px; width:100%; border:2px solid #033E66;
-moz-border-radius:10px; -webkit-border-radius:10px; border-radius:10px">
<legend><font style="font:bold 13px Arial,Helvetica,sans-serif;
color:#033E66; white-space:nowrap">
<nobr> Welcome to QLR Manager </nobr>
</font></legend>
<!-- END START OF BOX -->

<p style="background:#FFFFFF;padding:6px">This is an optional
<b>Connect</b> panel message that can be customized to convey
information to your users or personalize this installation of
QLR Manager for any purpose. The contents of this message exists
in the file <u>conmsg_enu.html</u>, which is located
in the directory where QLR Manager was installed. If you want
to omit this message box, you can delete or rename (recommended)
conmsg_enu.html.</p>

<!-- CLOSES BOX BORDER AROUND HTML MESSAGE -->
</fieldset></div>

Plain box style:

This is an optional Connect panel message that can be customized to convey information to your users or personalize this installation of QLR Manager for any purpose. The contents of this message exists in the file conmsg_enu.html, which is located in the directory where QLR Manager was installed. If you want to omit this message box, you can delete or rename (recommended) conmsg_enu.html.

HTML source:

<!-- STARTS BOX BORDER AROUND HTML MESSAGE -->
<div style="padding:2px; border:2px solid #033E66; -moz-border-radius:10px;
-webkit-border-radius:10px; border-radius:10px">
<!-- END START OF BOX -->

<p style="background:#FFFFFF;padding:6px">This is an optional
<b>Connect</b> panel message that can be customized to convey
information to your users or personalize this installation of
QLR Manager for any purpose. The contents of this message exists
in the file <u>conmsg_enu.html</u>, which is located
in the directory where QLR Manager was installed. If you want
to omit this message box, you can delete or rename (recommended)
conmsg_enu.html.</p>

<!-- CLOSES BOX BORDER AROUND HTML MESSAGE -->
</div>