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.

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.

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".

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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'

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.

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>

Other associated images and colors can be identified by inspecting the HTML source.

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>

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:

<!-- STARTS BOX BORDER AROUND HTML MESSAGE -->
<center><fieldset style="border:2px solid #033E66;padding:1px;width:100%">
<legend><font color="#033E66" style="font-size:10pt;
color:#033E66;font-family:Verdana,Arial,Helvetica,sans-serif"><b>
&nbsp;Welcome to QLR Manager&nbsp;</b></font></legend>
<!-- END START OF BOX -->
<table width="100%" border="0" cellpadding="4" cellspacing="0" bgcolor="#FFFFFF">
<tr><td>This is an optional <b>Connect</b> panel message that can be
edited to convey information to your users or customize this
installation of QLR Manager for any purpose. The contents of this message
exists in the file <u>conmsg_xxx.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_xxx.html.</td></tr></table>
<!-- CLOSES BOX BORDER AROUND HTML MESSAGE -->
</fieldset></center>
<!-- END CLOSE OF BOX -->

<!-- STARTS BOX BORDER AROUND HTML MESSAGE -->
<center><table width="90%" border="0" cellpadding="2" cellspacing="0" bgcolor="#033E66">
<tr><td>
<!-- END START OF BOX -->
<table width="100%" border="0" cellpadding="4" cellspacing="0" bgcolor="#FFFFFF">
<tr><td>This is an optional <b>Connect</b> panel message that can be
edited to convey information to your users or customize this
installation of QLR Manager for any purpose. The contents of this message
exists in the file <u>conmsg_xxx.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_xxx.html.
</td></tr></table>
<!-- CLOSES BOX BORDER AROUND HTML MESSAGE -->
</td></tr></table></center>
<!-- END CLOSE OF BOX -->