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.
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.
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.
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.
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.
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'
You can also set the number of days of usage you want to retain. Anything older will be deleted. The following will maintain two years worth of data in the qlr_usage_log table:
insert into qlr_info values ('track usage days','730')
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')
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 into its own <iframe> on your site. When using an iframe, it is important to name the iframe loading qlrmanager.html as QLR:
<iframe id="QLR" name="QLR" src="./qlrmanager.html" style="height:500px;width:100%;margin:0"></iframe>
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. It also is possible to configure the top <iframe> height to accommodate a different header configuration. Please see Customizing the Connect panel below.|
|•||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.|
For more information about re-branding, please contact us at firstname.lastname@example.org
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');|
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"; var DB_Engine = "MYSQL";
Valid values for the DB_Engine are DB2, MSSQL, MYSQL, ODBC, ORACLE, POSTGRESQL or SQLITE3.
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:
<div id="bdy"> <iframe src="./autolog.html2" id="QLRmanager" name="QLRmanager" frameborder="0"></iframe> </div>
The Connect panel can be customized to omit the following login fields:
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.
var loc = './processreport.php?Language=1';
In order to omit fields 1 an 4, the variable 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 panel, a value of 2 would start with the User Menu and 3 the Wizard. If no preference is added, the login default will load the User Menu. The modified URL variable might look like the following:
var loc = "./processreport.php?Language=1&Login=2%2C3&Preference=1";
The QLR Manager pages are comprised of 2 iframes... The header and messages <iframe>, and the main content window. It is possible to configure the height of the header <iframe> with an additional &Frameset variable. For example, to set the header iframe height to 85px and the messages div height to 30px, the URL variable might be modified as follows:
var loc = "./processreport.php?Language=1&Frameset=85%2C30&Login=2%2C3&Preference=1";
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 --> <div style="margin-right:10px;white-space:normal"> <fieldset style="padding:0; width:100%; border:2px solid #1D5084; -moz-border-radius:10px; -webkit-border-radius:10px; border-radius:10px"> <legend><span style="font:bold 13px Arial,Helvetica,sans-serif; color:#1D5084; white-space:nowrap"> Welcome to QLR Manager </span></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>
<!-- STARTS BOX BORDER AROUND HTML MESSAGE --> <div style="padding:2px; border:2px solid #1D5084; -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>