QLR Manager is a Query, Layout and Report (QLR) Manager that allows you to write SQL queries, run them, save them, and share them with other users. Once a SELECT query has been run, the resulting report can be formatted by creating a layout and applying the Layout to the Report data. Output from non-SELECT queries can also be viewed, but a layout cannot be applied to this type of output.

QLR Manager also provides the ability to Define Macros and Create User Menus. Through the use of User Profiles defined with User ID Administration, the Administrator is provided extensive control over the capabilities of a User ID. These capabilities range from a Restricted User that can only connect and run queries and macros from a menu, through an Advanced User that can create and save queries, wizard, layouts, macros and menus.

QLR Manager uses a series of database tables to manage this information. These tables must be created and populated with control data in your database. It is suggested that you create a separate database space (schema) within your overall database to manage these tables.

The Install & Migration Manager is not only intended to initially install QLR Manager, it can also be used to update the QLR Manager parameters. On installation step Step 2 of 3, there is a button at the bottom of the page to  Update Settings Only . This will bypass dropping and re-creating of the tables used by QLR Manager, and only update the parameters in the qlr.ini file.

The install process can be started by entering install.html, with the proper path information, into your browser. Install.html resides in the top level directory you created to store the QLR Manager files on your server.

Prior to running the install program, you should browse through this documentation to answer the preceding questions.

When QLR Manager runs, it produces HTML output to be displayed by the client's browser. Some of this HTML, particularly report output, must be stored somewhere in order for the application to provide some of its functions such as downloads of various formats and emailing output. It can be stored in either your server's file structure or inside the database you will be working with.

The default install setting for pageSource is to store the pages as files in the /reports directory. There may be times when you are not able to choose the file option for storing pages. An example would be when you are using a UNIX based hosting service that runs its web server ID as "nobody". In this situation, you would have to grant read, write and execute permissions to all users (UNIX permissions of 777) in order for the web server ID "nobody" to write the pages to the /reports directory. The 777 setting leaves you wide open for other persons on your server to access your /reports directory, and from there, the rest of your server directories. In these instances, you may choose to use the database storage option, as opposed to a 777 directory setting.

As with all the settings in the qlr.ini file, you can experiment with both options by updating the settings only.

If you do choose the file option, you can password protect (.htaccess) the /reports directory. This will force the User to enter a User ID and password prior to being able to use the application.

Granting access to the /reports directory:

In order to run QLR Manager, you must have a product license file. Prior to buying QLR Manager, we urge everyone to take advantage of our free 30 day trial of the Enterprise Edition. This will ensure it runs correctly in your environment and the product meets your expectations. If you first request a trial license, and then wish to buy QLR Manager, you will simply upgrade the trial license. To receive a license, you must first create a user account so we can e-mail your license file. A trial or purchased license can be obtained from the Purchase page of our web site.

To install the QLRlicense.inc license file, simply copy the file into the same directory where QLR Manager is installed, which is the same directory where the file qlrmanager.html is located.

A license will entitle you to free upgrades and fixes within the same version. We don't release a new version until we incorporate significant new function and features. Upgrades to new versions are steeply discounted for existing license holders.

In order for QLR Manager to run, it must create a file called qlr.ini in which it stores the parameters that were provided during the install process.

When the install program completes, it attempts to write the contents of this file into the main QLR Manager directory (where qlrmanager.html resides) by either creating or replacing the qlr.ini file. If the application has authority to write this file, it will do so. If not, it sends the content to your browser window. You can simply create a file called qlr.ini (all lower case) in the main QLR Manager file directory and copy and paste this information into the file.

If you wish to have this file automatically created, see the section on Where to store your report pages for information on how to set up proper read/write authority to the main directory.

The qlr.ini content will look as follows. As you can see from the amount of content created below, copying and pasting the information may be easier than setting up the necessary read/write access.

// *********************************************************************
// Created by QLR Install Manager on 06-12-2006 19:16:47
// Contains QLR Manager configuration variables for product version 5.0
// This file can be manually edited, except for the userid and password.
// You must use the install program to update these two values.
// *********************************************************************
db            = baitshop
host          = localhost
useSchema     = Yes
userid        = VmxaV05GVXhaM2hWVkDFOStY
password      = Z28yFmKtMCtN
dbType        = mysql
appServer     = localhost
adminEmail    =
sysEmail      =
cacheSize     = 5000
reportRows    = 500
maxLife       = 30
maxTime       = 300
imageCache    = 20
DBfilter      =
startFile     = qlrmanager.html
logoffFile    = http://www.mysite.com
maxSizeMB     = 3
pageSource    = database
isGraphing    = Yes
archiveDays   = 60
charset       = iso-8859-1
ldap          = Yes
DBlogin       = No
primerDB      =
security      = concat(
showErrors    = No

The following selections and values can be entered during installation to customize many features of QLR Manager. The values will be populated in the qlr.ini file.

QLR Manager allows you to specify the character set that is used in the HTML page encoding. This allows your browser to interpret various character sets. QLR Manager provides a drop down list of the most common character sets, but this entry in the qlr.ini file can be specifically edited if necessary.

The MySQL version of QLR Manager, when using MySQL version 4.1 or later, allows for you to install the product as UTF-8 database tables. This option is found in the Advanced Settings section of Step #2 of the installation process. The selection is stored in the qlr.ini file as the charset value.

The Database specifies the type of database engine with which QLR Manager will interact. This selection is stored in the qlr.ini file as the dbType.

Once you have run the install process and created a qlr.ini file, the master userid and password that were created in the qlr.ini must be entered to update the file when running the install program for a second time. You will be given 4 attempts to enter the correct ID and Password. After that, it is necessary to close your browser session and try again.

Click on these links for install information specific to MySQL or Oracle configurations.

An ID must exist or be established that will have the ability to update the QLR Manager tables. Its purpose is to be able to save, update and delete the queries, wizards, layouts and other data that will be created by QLR Manager. It is best to create a separate ID to run QLR Manager, as the installer will grant SELECT, UPDATE, INSERT and DELETE privileges to this ID for each of the tables created to support QLR Manager.

MS SQL Server requires that the QLR Manager master ID is an existing SQL Server ID and password that has been set up in SQL Server to use "SQL Authentication".

In order to connect to the database where the QLR tables reside, the QLR Manager ID requires a password. It is suggested that a password be 6 to 16 characters in length, and contain both alpha and numeric characters.

When the ID and password are saved in the qlr.ini file, they are encrypted by the install program. All values, except for the QLR ID and password can be edited with a text editor in the qlr.ini file.

Note: Do not attempt to manually edit the QLR userid or password values in the qlr.ini file. Use the install program to update the userid or password by launching your web browser and accessing the file install.html. Select the Upgrade existing installation option and proceed through the panels to Step 2 of 3 and click  Update Settings Only .This will update the userid and password in your qlr.ini file. Close your browser and open a new browser session to test the change.

When using PostgreSQL, you have the option of creating a separate schema for the QLR Manager tables. If you do choose to create a schema, it will be given the same name as the QLR Manager ID you create. If you do not choose this option, the QLR Manager tables will be created in the "public" schema. This option will only appear for a PosgreSQL install.

If you are creating a new Microsoft SQL Server database for storing your QLR Manager tables, you must reference the absolute path where SQL Server should store the database on your file system. Do not include the database file name in this reference. QLR Manager will default this value to where the SQL Server master.mdf file is stored on your system.

An example of a path name is C:\Program Files\Microsoft SQL Server\MSSQL$LOCALHOST\data\

The email address for User ID and Password request is used on the QLR Manager  Connect  panel. It is presented as an expandable/collapsible section entitled Request ID and Password. It contains fields for the User to enter their email address, subject, and description. This option is not available with the Basic edition of QLR Manager and will only be visible if a value is provided for this field. The value is recorded in the qlr.ini file as sysEmail.

The email address for reporting database problems is used on the QLR Manager  Connect  panel. It is presented as an expandable/collapsible section entitled Report Database Problem. It contains fields for the User to enter their email address, subject, and description. This option is not available with the Basic edition of QLR Manager and will only be visible if a value is provided for this field. The value is recorded in the qlr.ini file as adminEmail.

The sending of e-mails that contain charts can be done in one of two ways. The first is that the charts are sent as attached files. The second is to reference the charts as an in-line image, using an HTML <img> tag. When sent as an in-line image reference, QLR Manager has to maintain a copy of the chart on the server. This setting specifies the number of days for the files to be retained before being erased. The value is recorder in the qlr.ini file as archiveDays.

It is possible to install server software, such as "mod Security", that prohibits certain words from being submitted to the server as part of an HTML form. Generally, these words are associated with SQL injection attacks. One such common word is the SQL concatenation function concat(. The qlr.ini security setting allows for these phrases to be submitted by QLR Manager. Multiple words can be listed, separated by a comma, such as:

security = concat(, ><

It is possible to limit the amount of time that a user's session will stay alive with the server. This is accomplished by adding the maxLife parameter, with a value of minutes, to the qlr.ini file. This will prevent an idle session from lasting longer than the specified number of minutes. Please note that depending on the server environment, the server itself may end the session sooner than the time specified. The following setting would ensure that a user could not set idle more than 30 minutes:

maxLife = 30

There are several advanced settings that control how QLR Manager behaves.

The cacheSize value controls how much data from a query is stored in memory after the query has run. The larger this number, the quicker the response will be when formatting your data with Layout information. The drawback is the amount of server memory that will be used, which if too much, will actually slow performance. A typical setting is 5000.

The reportRows value sets a default which controls the total number of rows of data that are displayed when a query result is shown. A larger number will show more data, but it takes longer to display results, especially if a user is connected via a modem. This value can be overridden with the Max displayed rows control in the Report Body section of the Layout panel for specific reports. If the Layout is saved as the same name as the query or wizard producing the report, it will become the default layout applied when the query, wizard or menu item is executed. When the number of rows produced by the query exceeds this reportRows value, navigation links (First, Back, More, Last) are presented above the report to move between page sets.

The number of rows displayed will not always be exactly equal to this setting, as subtotaling data will add additional rows to reports. QLR Manager will always complete the current page with data. If you have a reportRows setting of 500 and the 500th row appears in the middle of the page, QLR Manager will fill up the final page with rows of data, if there is more data available.

This maxTime setting determines the maximum amount of time that is allowed for an action to complete, such as running a query. A typical setting is 300 seconds.

QLR Manager is able to display binary data (BLOB fields) as images. In order to do this, it must temporarily store the images outside of the database on the server. The imageCache setting determines the amount of memory in megabytes to allocate for image management. A typical setting is 30. Once this limit is exceeded, no more images of this type will be displayed in the report.

This option allows you to change the name of the start up file for QLR Manager. The default value is qlrmanager.html. If you wish, you can rename qlrmanager.html so that the Users can start QLR Manager by referencing a different file name. If the file is renamed, the name of the new file should be entered and will be recorded in the qlr.ini file as startFile.

if (self == top) location.replace("../qlrmanager.html")

The reference to qlrmanager.html should be replaced with the new start file name.

This option allows you to specify a URL that the User will be directed to when clicking the  Log off  button in the header. The default behavior is to direct the User to the  Connect  panel when logging off QLR Manager.

This maxSizeMB value allows you to specify the maximum size of files to be created to send to the User's browser session. This value is entered as a number between .5 and 16, representing page sizes from .5 to 16 megabytes. The default value is 5 megabyte.

If a query produces a report with numerous columns of data, then the number of rows returned will be limited to stay under the size limit. Where this setting is most relevant, is in the production of reports in Macros. As there is no row limitation to the number of rows of data the will be returned in a SELECT query when used in a Macro, the output will be restricted to fall under the set limit.

Note: For MySQL users, the mysql database engine has a parameter called max_allowed_packet, which determines the amount of data that can be sent to the server in the form of a query. More information on this can be found here. When using MySQL and choosing to store your report pages in the database, the default value is 1 megabyte, which is the default setting for the max_allowed_packet setting in MySQL.

The DBfilter value allows QLR Manager to filter out the database selection list that is presented to the User. If you're accessing a database with hundreds of schemas, it allows you to narrow down the display list. This may very well be the case if you are on a hosting service that shares a database engine. It uses the SQL LIKE command format and is case sensitive. A setting of fin% would select all databases beginning with "fin". You do not need to enclose this filter text in quotes.

If a DBfilter of **omit** is specified, then the database/schema selection control will be suppressed from appearing altogether. This may be helpful in large Oracle installations with thousands of schemas.

LDAP (Lightweight Directory Access Protocol) support allows for the interfacing with an external directory to authenticate a user when they attempt to connect to QLR Manager. The qlr.ini keyword is ldap. If enabled, when the  Connect  button is clicked at logon time, QLR Manager looks for a file called ldap.php and executes it. It is up to the installer of QLR Manager to place the php code they wish to execute into the ldap.php file. The php code can be designed to execute LDAP functions, or any other functions that are desired.

At the bottom of the file, there are several variables that are passed back to the login process. These are:

QLR Manager's default behavior examines your system's php.ini file to determine the SMTP server (php.ini setting for "smtp") and port (php.ini value of "smtp_port"). It is possible to override these values, as well as providing a User ID and Password, by adding entries to the data table called qlr_info (notice this is not the qlr.ini file, but a database table).

insert into qlr_info values ('smtp_server','mysmtpsite.com');
-- optional port, default value is 25 if not specified
insert into qlr_info values ('smtp_port', '22');
-- optional userid, provide if necessary
insert into qlr_info values ('smtp_userid','fred');
-- optional password, but must be used if userid is provided
insert into qlr_info values ('smtp_password','mydog8it');

The base function of QLR Manager is to validate the User IDs and passwords that have been established in the database engine. It is also possible to create "pseudo" QLR Manager IDs and passwords that will allow persons to connect to the database. The actual ID and password that these pseudo IDs will connect through is the QLR master ID and password.

Pseudo IDs may be useful in instances such as establishing IDs for a set of customers, a class of students or other groups of users. Each ID can manage its own set of Queries, Layouts, etc. and authority levels can be controlled using QLR Manager's User ID Admin and Tools function accessed from the  Connect  panel.

In order to utilize this function, the qlr.ini file must be edited to set ldap = qlrid. The second step is to edit a php file (found in the main QLR directory) called qlrid.php. This file will contain the pseudo IDs and their passwords, so take care to limit access to this file. The content of the editable portion of qlrid.php is included below. The two user IDs that are present in the file are for example purposes only. You should edit this file to add your desired IDs and their passwords.

<?php
// don't edit above this line

// **********************************************************************
// This file is designed to allow you to create 'pseudo' users
// that will connect to QLR Manager using the QLR master ID and password
// but will appear as individual users with their own queries, layouts, etc.
// NOTE: the qlr.ini file must be updated so that the ldap entry is
//       ldap = qlrid
// **********************************************************************

// initial state of authentication
$qlrid_authentic = FALSE;

// build up an array of all the pseudo users you want to manage.
// the format is an associative array where
// the $user[userid] is assigned it's password

$user = array();
// userid          password
$user['tom']     = 'misty';
$user['charlie'] = 'i82much';

// validate that the password is correct for
if (isset($user[$_POST['userid']])) {

    // good password for the userid.
    if ($user[$_POST['userid']] == $_POST['password']) {
        $qlrid_authentic = TRUE;
    }
    // password did not match. This is the failure message
    else {
        $qlrid_message  = 'Could not authenticate login ID '.$_POST['userid'];
    }
}
// this allows all existing other normal database id's to be used
// remove the following line if you want to block their access
else $qlrid_authentic = 'bypass';

// don't edit below this line
?>

When distribution e-mails are sent, QLR Manager attempts to connect directly to the SMTP mail server's socket. However, in some environments, QLR returns a false positive that the socket connection method is working properly. If this is the case in your situation, an optional entry of useSocket = No can be added to your qlr.ini file. This will instruct QLR Manager to not directly use the SMTP socket. The drawback of employing this setting is that distribution e-mails will be sent at a slower rate.

In a single database server environment, it may be desirable to preset the server value so that the DB server input field on the  Connect  panel is not displayed to the User and the server value is automatically assigned. This is accomplished by manually adding a line item to the qlr.ini file to set the "presetServer" value. An example of such an entry is:

presetServer = localhost

The Enterprise Edition of QLR Manager supports chart creation. In order to produce charts, PHP must be running with GD support, either basic GD or GD2. GD support is enabled by uncommenting the proper PHP extension in your php.ini file, which is either extension=php_gd.dll or extension=php_gd2.dll. You can easily tell if GD support is enabled for your installation by clicking on the Click here to test for GD support link, which is found in Step 1 of the installation routine. If it is installed, the message PHP GD support is installed will appear.

From an article located at http://www.macromedia.com/devnet/mx/dreamweaver/articles/php_graphics_03.html, below is a brief explanation on how to install GD support:

GD is an ANSI C library for the dynamic creation of images. Much like PHP, GD is an open source library that is maintained by Boutell.com; the official home page of GD is http://www.boutell.com/gd. As of this writing the current version of GD is 2.0.3 for the stable version. GD was once able to output both the GIF format and the JPG formats, however because of the Unisys patent, version 1.6 was the last with GIF support. The GD developers moved in favor of PNG, which we'll be using here.

Installing GD is pretty straightforward on both Windows and Linux. On the Macintosh I'd recommend a precompiled binary. An excellent source for Mac users is from Marc Liyanage at http://www.entropy.ch. GD is going to be standard in the next upcoming release of PHP 4.3. Once this version becomes available, GD will be there by default. Many precompiled versions of PHP come with GD support.

;extension=php_gd2.dll

Then make sure the php_gd2.dll library is in the correct directory. You will have to restart your web server if PHP is running as a module. No restart is needed if PHP is running as a CGI.

<?php
dl('php_gd2.dll');
?>

Binding GD to PHP under Linux is more challenging, but only required if running a PHP version earlier than 4.3. You'll need to download GD as well as FreeType if you plan to do TrueType font manipulation You can get GD from the Boutell homepage at http://www.boutell.com/gd/. You can get FreeType at http://www.freetype.org/.

make
make install

./configure --with-gd --with-freetype-dir
make
make install

If you have any additional parameters to configure PHP, you'll need to add them to the configure statement.

For RedHat and other RPM users, binary installs for the i386 platform are available at: http://rpms.arvin.dk/php/.

A simple call to phpinfo() reports whether the GD library is bound to your install of PHP.

Once you have confirmed that GD support is installed, the true test for creating charts is done by clicking on the Click here to test chart creation link. It should create the following chart:

You must be able to create this chart in order to have QLR Manager produce charts. If you can't, then you should return to the start and uncheck the Install with charting support checkbox on the initial install panel.

A migration is necessary when the underlying QLR Manager database tables, or data in those tables, must be updated. A migration is always necessary when upgrading from one major version to another, e.g. version 4.1.5 to version 5.0. In addition to updating the QLR database tables, the migration will copy your existing queries, layouts, etc. to the new version.

If you wish to erase your existing queries and layouts, or this is a new installation, simply access install.html with your browser and choose the New Installation option to install the product.