Overview

Report Objects give the PHP programmer the ability to embed QLR Manager reports into the body of their web pages. These report objects can be tabular report data, a chart, or both depending upon the layout that was applied. ReportObject.php is a PHP Class file that is used to generate a report object. It provides a number of PHP functions that can be used to build and manipulate a report. Ultimately, a report body and set of Cascading Style Sheet (CSS) entries are returned.

By default QLR Manager Report Objects can create PDF output and Charts. This requires that certain support files be loaded. The loading of these files can be suppressed for improved performance and to help relieve memory limitations. This can be accomplished by setting either or both of these session variables:

$_SESSION['qlr_no_pdf']    = 'yes';
$_SESSION['qlr_no_chart']  = 'yes';

The default is for a single report of all records found to be returned in the Report Object. "Report Blocks" can be specified by using the setStartRow() and setBlockSize() functions.

Additional functions in this class such as runQuery(), getDataRow(), and others, also provide the Programmer with basic database interface capabilities.

Input Controls can be used in conjunction with report objects to collect user input for query variables. These input controls can be created by logging onto the "User ID Admin and Tools" function from the QLR Manager Connect panel and selecting Input Controls. They can also be created on-the-fly by clicking the link in the Query or Wizard panels.

The functions getControl() and setControl() are provided to implement the controls in a PHP generated web page. Some controls are returned in a table to preserve formatting. This is the most expedient way of providing an input control to collect user input. However, this may not provide a control that fits well with the web page design. An alternative is for the Author to create their own control and pass the selected value(s) to the report object using the setQueryVar() function. This method provides unlimited flexibility in the design of the input control.

Whether using input controls that have been created in QLR Manager or a control designed by the Author of the web page, the method of implementation is the same.

Create a web page that will display the input controls to collect the desired values. This is accomplished be creating an HTML form, which uses <form> </form> tagging. The key element of this form is the ACTION to process when the "Submit" button is clicked. It is the name of the PHP file that will be called to create the report object.

A simple example:

<form name="varform" action="./custinv.php" method="post">
<div style="display:inline-block;text-align:center">
<select name="customer" size="1">
<option value="17">Asher, Artimus</option>
<option value="15">Bagadonuts, Nessa</option>
<option value="11">Benton, Edward</option>
<option value="6">Billings, Billy Bob</option>
</select><br />
<input type="submit" value=" Submit "> style="margin:4px"></div>
</form>

This will create a simple input form:


Example #5 contains the code for creating an input control web page.

When the "Submit" button is clicked, it will execute a file called custinv.php. custinv.php should contain the PHP code (see examples below) to create a report object. custinv.php can use the customer number that is being passed to it via the HTML <form>. It's value can be accessed via PHP using the $_POST array entry of $_POST['customer']. The customer number can be supplied to a stored QLR Manager query using the setQueryVar() function. See Example #2 for an example of how to set query variables.

Report Objects are only available in the Enterprise Edition of QLR Manager.

Basic steps

There are several basic steps used to create a report object. It is assumed that these are performed within a PHP environment.

1. A PHP session is started.
2. A session variable called 'inc_path' (or optionally, qlr_inc_path) is defined. This variable identifies where the QLR Manager files are located in relation to the file that is requesting to create a report object.
3. Include the file ReportObject.php in the code.
4. A report object is created using database connection values that have authority to run the query to produce the report.
5. The query and layout used to produce the report are defined.
6. Optionally, values for query variables and changes to the layout can be made.
7. The report is created.
8. The report and CSS values are requested.

Steps 4 through 7 are repeated for each report to be embedded into a page.

Note: When executing an html page that contains many report objects, it may be desirable to assess the performance of each Report Object to determine how fast the queries run and layout formatting is applied. This can be accomplished by adding a search string to the end of the the URL:

http://my_domain/rptobj_directory/filename.php?query_stats=1

The following stats will be provided:

Query execution time: 0.001 seconds.
Layout formatting time: 0.008 seconds.
Examples

The following examples provide more information about how report objects can be customized to meet different needs.

Depending on how calls to report objects are nested inside functions, it may be necessary to include a reference to a global variable called $mconn (for the QLR master connection) in that function. If an error is encountered stating that the function dbquery() cannot be found in MasterBlock.php, the function may need to be modified as follows:

function myreportcreator() {
  global $mconn;
  (User code starts here)
}

Example #1: This first example uses the basic steps to create a report from a stored query, owned by 'frank', named '13. Break level formula'. The necessary statements are highlighted in blue.

This example can be executed at http://www.qlrmanager.com/demo_rptobj/example_1.php.

<?php

// First create a PHP session
session_start();

// Define the relative path to the QLR Manager file directory
// In this example, the Report Object file is located in a different directory called /demo_rptobj
// demo_rptobj is at the same directory level as the root QLR Mananger directory called /demo
// Use directory names and relative path references that are appropriate for your environment
$_SESSION['inc_path'] ='../demo/';

// Include the Report Object file with the correct path reference for your environment
include ('../demo/ReportObject.php');

// Create a new Report Object
// Provide the Host, Database associated to the query to be run, User ID and Password
// The User ID must have authority to execute the query, meaning that the query was saved as 'shared'
$rptObj = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');

// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name
$okay = $rptObj->defineReport('frank', '13. Break level formula', 'frank', '13. Break level formula');

// Create the report table
$rptObj->createReport();

// HTML for the web page
echo '<!DOCTYPE html>'."\n";
echo '<html><head>'."\n";
echo '<meta charset="utf-8" />'."\n";
echo '<title>Example #1</title>'."\n";
echo '</head><body>'."\n";

// Get both the CSS entries and the report table
echo $rptObj->getOutput();
echo '</body></html>'."\n";

// End the PHP session
?>

Example #2: This second example uses a few additional functions. It shows how to set the values for query variables that may be present in the query, and retrieves the CSS entries separately from the report table data. Also, the row count is requested to determine what to display when the query does not produce any records.

This example can be executed at: http://www.qlrmanager.com/demo_rptobj/example_2.php.

<?php

// First create a PHP session
session_start();

// Define the relative path to the QLR Manager file directory
// In this example, the Report Object file is located in a different directory called /demo_rptobj
// demo_rptobj is at the same directory level as the root QLR Mananger directory called /demo
// Use directory names and relative path references that are appropriate for your environment
$_SESSION['inc_path'] ='../demo/';

// Include the Report Object file with the correct path reference for your environment
include ('../demo/ReportObject.php');

// Create a new Report Object
// Provide the Host, Database associated to the query to be run, User ID and Password
// The User ID must have authority to execute the query, meaning that the query was saved as 'shared'
$rptObj = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');

// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name
$okay = $rptObj->defineReport('frank', '07. Customers by Country', 'frank', '07. Customers by Country');

// Set query variable values
// The Query Variable name is referred to exactly how it appears in the query text
// Notice how this is done AFTER defineReport() and BEFORE createReport()
// The double quoting is used because the query variable used in the query does not have quotes around it
// If the query allowed, multiple values could be submitted separated by commas: "'Brazil','UK','USA'"
$rptObj->setQueryVar('frank.country', "'USA'");

// Create the report table
$rptObj->createReport();

// HTML for the web page
echo '<!DOCTYPE html>'."\n";
echo '<html><head>'."\n";
echo '<meta charset="utf-8" />'."\n";
echo '<title>Example #2</title>'."\n";

// Get the CSS values and place them in the HTML <head>
echo '<style type="text/css">'."\n";
echo $rptObj->getCSSvalues();
echo '</style>'."\n";


echo '</head><body>'."\n";

if ($rptObj->getRecordCount() > 0) {

    // Optional div to center the tabular report
    echo '<div style="display:table;margin:0 auto">'."\n";
    echo $rptObj->getReport();
    echo '</div>'."\n";
}
else {
    echo 'No invoice records found for this customer number';
}


echo '</body></html>'."\n";

// End the PHP session
?>

Example #3: This is an example of a page containing more than one report object. For each report, a separate report object is created. Notice how the Cascading Style Sheet (CSS) entries are retrieved separately from the report and included in the HTML Head section. When creating multiple reports that require multiple sets of CSS entries, this method is safer for supporting older browsers.

By default, a report object is created pointing to the same database engine where the QLR Manager master tables reside. The comments below describe how $rptObj2 can be defined to run against an SQLite3 database. Valid values for the database engine are 'db2', 'sql server', 'sqlite3', 'mysql', 'oracle', and 'postgresql'. Once the database engine has been set, that is the database engine that will be referenced until changed to a different value.

This example can be executed at http://www.qlrmanager.com/demo_rptobj/example_3.php.

<?php

// First create a PHP session
session_start();

// Define the relative path to the QLR Manager file directory
// In this example, the Report Object file is located in a different directory called /demo_rptobj
// demo_rptobj is at the same directory level as the root QLR Mananger directory called /demo
// Use directory names and relative path references that are appropriate for your environment
$_SESSION['inc_path'] ='../demo/';

// Include the Report Object file with the correct path reference for your environment
include ('../demo/ReportObject.php');

// Create a new Report Object
// Provide the Host, Database associated to the query to be run, User ID and Password
// The User ID must have authority to execute the query, meaning that the query was saved as 'shared'
$rptObj1 = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');

// Perform all functions for this $rptObj1 before switching database engines for a 2nd Report Object

// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name
$okay = $rptObj1->defineReport('frank', 'rptobj_example_3', 'frank', 'rptobj_example_3');

// Set query variable values
// The Query Variable name is referred to exactly how it appears in the query text
// Notice how this is done AFTER defineReport() and BEFORE createReport()
$rptObj1->setQueryVar('frank.custnum', 10);
$rptObj1->createReport();
$css1    = $rptObj1->getCSSvalues();
$report1 = $rptObj1->getReport();


// Now define the second Report Object
$rptObj2 = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');
$okay    = $rptObj2->defineReport('frank', 'rptobj_example_3a', 'frank', 'rptobj_example_3a');


// If the Report Object is from a database engine other than where QLR Manager is installed, a 5th argument is required
// By default, QLR Manager looks for the data in the same databases where QLR Manager is installed
// When a different database source is desired, the 5th parameter is passed to identify the database engine
// Valid database engine references are: 'db2', 'sql server', sqlite3', 'mysql', 'odbc', 'oracle', and 'postgresql'

// You can comment out the two lines above that define the second Report Object ($rptObj2 and $okay)
// After commenting the second Report Object, you can generate a report from SQLite3 by uncommenting following 2 lines

//$rptObj2 = new ReportObject('../sqlite/company.sqlite', '', 'guest', 'guest', 'sqlite3');
//$okay    = $rptObj2->defineReport('frank', 'department list', 'frank', 'department list');

$rptObj2->createReport();
$css2    = $rptObj2->getCSSvalues();
$report2 = $rptObj2->getReport();


// HTML for the web page
echo '<!DOCTYPE html>'."\n";
echo '<html><head>'."\n";
echo '<meta charset="utf-8" />'."\n";
echo '<title>Example #3</title>'."\n";

// Get the CSS values to be placed in the <head> section
echo '<style type="text/css">'."\n";
echo $css1;
echo $css2;
echo '</style>'."\n";


echo '</head><body>'."\n";

// Get the tabular report data ($reportX) and wrap in DIVs or other desired formatting
echo '<div style="display:table;margin:0 auto">'."\n";
echo '<div style="display:table-cell;text-align:center">'."\n";
echo '<span style="font:bold 16px sans-serif">Customer Info</span>'."\n";
echo $report1.'</div>'."\n";
echo '<div style="width:10px"></div>'."\n";
echo '<div style="display:table-cell;text-align:center">'."\n";
echo '<span style="font:bold 16px sans-serif">Payment Details</span>'."\n";
echo $report2.'</div></div>'."\n";
echo '</body></html>'."\n";

// If a different database engine was accessed, clear the session variables to be able to rerun the Report Object
session_destroy ();

// End the PHP session
?>

Example #4: This is an example of creating a Microsoft Excel file for download from a report. When creating a download file, no characters can be sent to the browser prior to executing getOutputFile().

This example can be executed at http://www.qlrmanager.com/demo_rptobj/example_4.php.

<?php

// First create a PHP session
session_start();

// Define the relative path to the QLR Manager file directory
// In this example, the Report Object file is located in a different directory called /demo_rptobj
// demo_rptobj is at the same directory level as the root QLR Mananger directory called /demo
// Use directory names and relative path references that are appropriate for your environment
$_SESSION['inc_path'] ='../demo/';

// Include the Report Object file
include ('../demo/ReportObject.php');

// Create a new Report Object
// Provide the Host, Database associated to the query to be run, User ID and Password
// The User ID must have authority to execute the query, meaning that the query was saved as 'shared'
$rptObj = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');

// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name
// Notice when no Layout is desired, the Layout Owner and Layout Name (3rd and 4th arguments) can be omitted
$okay = $rptObj->defineReport('frank', '02. Inventory Reorder (simple rpt)');

// Create the report output
$rptObj->createReport();

// Create an xls output file called invoice that contains the report column headings
// See getOutputFile() for valid output types
$rptObj->getOutputFile('xls', 'invoice');

// End the PHP session
?>

Example #5: This is an example of creating a page to collect input variables to be passed to a page that will create a report object.

This example can be executed at http://www.qlrmanager.com/demo_rptobj/example_5.php.

<?php

// First create a PHP session
session_start();

// Define the relative path to the QLR Manager file directory
// In this example, the Report Object file is located in a different directory called /demo_rptobj
// demo_rptobj is at the same directory level as the root QLR Mananger directory called /demo
// Use directory names and relative path references that are appropriate for your environment
$_SESSION['inc_path'] ='../demo/';

// Include the Report Object file with the correct path reference for your environment
include ('../demo/ReportObject.php');

// Create a new Report Object
// Provide the Host, Database associated to the query to be run, User ID and Password
// The User ID must have authority to execute the query, meaning that the query was saved as 'shared'
$rptObj = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');

// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name
$okay = $rptObj->defineReport('frank','05. Category Sales with Graph','frank','05. Category Sales with Graph');

// Define the input controls that will be called within the HTML page further below
$controls = array('frank.start_date','frank.end_date');
$rptObj->setControl($controls);

// Initial default values for start and end dates
$display_start_date = '01/01/2002';
$display_end_date   = '12/31/2002';

// The following is optional php code that will echo the dates submitted when the Submit button is clicked.
$submitted = '';
if (isset($_REQUEST['start_date'])) {
    $submitted  = '<p>Submitted value for start_date is: '.$_REQUEST['start_date'].'</p>';
    $submitted .= '<p>Submitted value for end_date is: '.$_REQUEST['end_date'].'</p></div>';


    // Set the start and end dates based on what the User entered.
    $display_start_date = $_REQUEST['start_date'];
    $display_end_date   = $_REQUEST['end_date'];
}

// Set the default values for the date selection input controls
// See the on-line help for this function to see which control values can be set
$rptObj->setControlValue('frank.start_date', 'default_val', $display_start_date);
$rptObj->setControlValue('frank.end_date', 'default_val', $display_end_date);

// This ends the php content. The rest of the page is HTML with some embedded php
?>

<!DOCTYPE html>
<html><head>
<meta charset="utf-8" />
<title>Customer Invoice Dates</title>
<style type="text/css">
body,td { background-color:#F6F6F6; font:normal 13px sans-serif }
td { text-align:left }
.pgeTitle  { font:bold 15px sans-serif; text-align:center }
.inpCtrls  { display:table; margin:0 auto; text-align:center; white-space:nowrap }
input[type="text"] {
  height:23px;
  line-height:17px
  font-size:12px;
  font-family:Arial,Helvetica,sans-serif;
  color:#000000;
  margin:0;
  border:1px solid #C0C0C0;
  box-sizing: border-box;
  -moz-box-sizing: border-box;
  -webkit-box-sizing: border-box;
  border-radius:5px;
  -moz-border-radius:5px;
  -webkit-border-radius:5px;
  padding:2px;
  vertical-align:top;
}
</style>

<!-- This next block of js import and css is required to support calendar controls -->
<!-- Notice the relative path back to the /qlr_manager/calendar directory to load the required files -->
<script src="../demo/qlr_manager/calendar/calendar.js" type="text/javascript"></script>
<style type="text/css">
#calWin {
  position:absolute;
  visibility:hidden;
  left:-1000px;
  top:-1000px;
  z-index:10;
  -webkit-box-shadow: 2px 2px 5px 0px rgba(102,102,102,1);
  -moz-box-shadow: 2px 2px 5px 0px rgba(102,102,102,1);
  box-shadow: 2px 2px 5px 0px rgba(102,102,102,1);
  -moz-border-radius:5px;
  -webkit-border-radius:5px;
  border-radius:5px;
  position:absolute;
  visibility:hidden;
  left:-1000px;
  top:-1000px;
  border-style:solid;
  border-width:1px;
  border-color:#2274C6 #0053A6 #0053A6 #2274C6
}

.calBtn	 { background:#2274C6 url(../demo/qlr_manager/calendar/calbtn.png) 0 0 no-repeat }
.timeBtn { background:#2274C6 url(../demo/qlr_manager/calendar/timebtn.png) 0 0 no-repeat }
.calTime { background:#2274C6 url(../demo/qlr_manager/calendar/caltime.png) 0 0 no-repeat }

.calBtn,.timeBtn,.calTime {
  border-top:solid #2274C6 1px;
  border-right:solid #003E90 1px;
  border-bottom:solid #003E90 1px;
  border-left:solid #2274C6 1px;
  height:19px;
  width:30px;
  margin:0;
  filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#4495E7',endColorstr='#0053A6',gradientType=0);
  background-image: -moz-linear-gradient(top,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-image: -ms-linear-gradient(top,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-image: -o-linear-gradient(top,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-image: -webkit-linear-gradient(top,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-image: linear-gradient(to bottom,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-size: auto 400%;
  background-position: 0 0;
  vertical-align: middle;
  -webkit-border-radius:5px;
  -moz-border-radius:5px;
  border-radius:5px;
  -moz-box-sizing:border-box;
  -webkit-box-sizing:border-box;
  box-sizing:border-box;
  -webkit-transition: all .4s ease 0s;
  -moz-transition: all .4s ease 0s;
  -ms-transition: all .4s ease 0s;
  -o-transition: all .4s ease 0s;
  transition: all .4s ease 0s;
}

.calBtn:hover,.timeBtn:hover,.calTime:hover {
  color:#FFFFFF;
  background-color: #1D5084;
  border:1px solid #04376B;
  background: #1D5084;
  background-position: 75% 100%;
}

.calBtn:active,.timeBtn:active,.calTime:active {
  border: 1px solid #04376B;
  -webkit-box-shadow: inset 0 0 5px 2px #1D5084, 0 1px 0 #F6F6F6;
  box-shadow: inset 0 0 5px 2px #1D5084, 0 1px 0 #F6F6F6;
}

.datein { width:100px }
.bothin { width:140px }
.timein { width:100px }
</style>


</head><body>
<form name="varsForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<div class="inpCtrls">
<p class="pgeTitle">Please select date range for invoices</p>
<table style="border-spacing:0;margin:0 auto"><tr>
<td>Select starting date:</td>
<td><?php echo $rptObj->getControl('frank.start_date'); ?></td>
</tr><tr>
<td>Select ending date:</td>
<td><?php echo $rptObj->getControl('frank.end_date'); ?></td>
</tr></table>
<p><input type="submit" value="  Submit  "></p>
</div>
</form>
<div style="display:table;margin:0 auto">

<!-- The following will echo the submitted values for demonstration purposes -->
<?php echo $submitted ?>
</div>

<!-- This next line is required to support calendar controls (keep on one line) -->
<!-- Notice the relative path back to the /qlr_manager/calendar directory to load the required html file -->
<iframe id="calWin" name="calWin" src="../demo/qlr_manager/calendar/calendar.html" width="192" height="75"
frameborder="0" style="overflow:hidden"></iframe>


</body></html>

Example #6: This is an example of using a Report Object to create the contents for a report file. A report is generated and using standard php functions, a file is written.

This example can be executed at: http://www.qlrmanager.com/demo_rptobj/example_6.php.

<?php

// First create a PHP session
session_start();

// Define the relative path to the QLR Manager file directory
// In this example, the Report Object file is located in a different directory called /demo_rptobj
// demo_rptobj is at the same directory level as the root QLR Mananger directory called /demo
// Use directory names and relative path references that are appropriate for your environment
$_SESSION['inc_path'] ='../demo/';

// Include the Report Object file with the correct path reference for your environment
include ('../demo/ReportObject.php');

// Create a new Report Object
// Provide the Host, Database associated to the query to be run, User ID and Password
// The User ID must have authority to execute the query, meaning that the query was saved as 'shared'
$rptObj = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');

// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name
$okay = $rptObj->defineReport('frank','02. Inventory Reorder (simple rpt)','frank','02. Inventory Reorder (simple rpt)');

// Create the report output
$rptObj->createReport();

// Grab the report output
$content  = $rptObj->getOutput();

// Define the relative path and file name to write the report output
// We are writing the file to the /reports directory for demonstration purposes only
// It is not advisable to use the /reports directory within QLR as files are purged regularly for cleanup
$filename = '../demo/reports/example_6.html';

if (!$handle = fopen($filename, 'w')) {
     echo "Cannot open file ($filename)";
     exit;
}


// HTML for the web page
echo '<!DOCTYPE html>'."\n";
echo '<html><head>'."\n";
echo '<meta charset="utf-8" />'."\n";
echo '<title>Example #6</title>'."\n";
echo '<style type="text/css">'."\n";
echo 'body {'."\n";
echo '  font:normal 15px Arial,Helvetica,sans-serif;'."\n";
echo '}'."\n";
echo '</style>'."\n";
echo '</head><body>'."\n";

// Write content to the opened file.
if (fwrite($handle, $content) === FALSE) {
    echo "<p>Cannot write to file ($filename)</p>";
    exit;
}
else {
echo '<p><b>Success!</b></p>';
echo '<p>Wrote content to file ($filename).</p>';
echo '<p>The file can be viewed at this URL: <a href="http://www.qlrmanager.com/demo/reports/example_6.html">'.
     'http://www.qlrmanager.com/demo/reports/example_6.html</a>.</p>';
}


echo '</body></html>'."\n";

fclose($handle);

// End the PHP session
?>

Example #7: This is an example of using a Report Object to generate a report and FusionChart or FusionWidget in the same page. There is some Javascript required to support the rendering of the FusionChart or FusionWidget.

This example can be executed at http://www.qlrmanager.com/demo_rptobj/example_7.php.

<?php

// First create a PHP session
session_start();

// Define the relative path to the QLR Manager file directory
// In this example, the Report Object file is located in a different directory called /demo_rptobj
// demo_rptobj is at the same directory level as the root QLR Mananger directory called /demo
// Use directory names and relative path references that are appropriate for your environment
$_SESSION['inc_path'] ='../demo/';

// Include the Report Object file
include ('../demo/ReportObject.php');

// Create a new Report Object
// Provide the Host, Database associated to the query to be run, User ID and Password
// The User ID must have authority to execute the query, meaning that the query was saved as 'shared'
$rptObj = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');

// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name
$okay = $rptObj->defineReport('frank', 'customer invoice', 'frank', 'invoice details');

// Set query variable values
// The Query Variable name is referred to exactly how it appears in the query text
// Notice how this is done AFTER defineReport() and BEFORE createReport()
// The double quoting is used because the query variable used in the query does not have quotes around it
// If the query allowed, multiple values would be submitted separated by commas: "'northwest','northeast'"
$rptObj->setQueryVar('frank.region', "'northwest'");

// Create the report and chart output
$rptObj->createReport();

// HTML for the web page
echo '<!DOCTYPE html>'."\n";
echo '<html><head>'."\n";
echo '<meta charset="utf-8" />'."\n";
echo '<title>Example #7</title>'."\n";
// Get the CSS values and place them in the HTML <head>
// The CSS values are used for the report table only
// If the saved layout is only rendering a FusionChart, the CSS values are not necessary
echo '<style type="text/css">'."\n";
echo $rptObj->getCSSvalues();
echo '</style>'."\n";


// Below is the Javascript necessary to support the rendering of a FusionChart within a Report Object
// Notice the relative path to the fusioncharts directory within the QLR Manager directory structure
echo '<script type="text/javascript" src="../demo/fusioncharts/fusioncharts.js"></script>'."\n";


echo '</head><body>'."\n";

if ($rptObj->getRecordCount() > 0) {

    // <div> tags optional to center the output
    echo '<div style="display:table;margin:0 auto">'."\n";
    echo $rptObj->getReport();
    echo '</div>'."\n";

}
else {
    // Display error message if desired - useful for debugging
    echo 'Error encountered: '.$rptObj->getErrorNum().' '.$rptObj->getErrorMsg();

    // Can also display a more user friendly message
    echo 'No data found';
}
echo '</body></html>'."\n"; // End the PHP session ?>

Example #8: When many report objects belonging to the same User ID owner are used in the same page, and recognizing that establishing the database connection for each report or chart affects page load performance, the DB connection can be reused by adding true as the fifth argument passed to defineReport as illustrated in the following example.

This example can be executed at http://www.qlrmanager.com/demo_rptobj/example_8.php.

<?php

// Create a PHP session
session_start();

// Define the relative path to the QLR Manager file directory
$_SESSION['inc_path'] ='../demo/';

// Include the Report Object file
include ('../demo/ReportObject.php');

// Create a new Report Object
// Provide the Host, Database associated to the query to be run, User ID and Password
// The User ID must have authority to execute the query, meaning that the query was saved as 'shared'
$rptObj = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');

// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name for the first report
$okay1 = $rptObj->defineReport('frank', 'overstock', 'frank', 'overstock');

// Create the report table or chart for first report
$rptObj->createReport();
if ($rptObj->getRecordCount() > 0) {
    $rptCss .= $rptObj->getCSSvalues();
    $report1 = $rptObj->getReport();
}
else $report1 = 'No records found';


// Define the Query Owner and Query Nname, followed by the Layout Owner and Layout Name for the second report
// NOTE: true is added as the 5th argument to reuse the existing DB Connection defined with new ReportObject()
$okay2 = $rptObj->defineReport('frank', 'order info', 'frank', 'order info', true);

// Set query variable values
$rptObj->setQueryVar('customer_number', 15);

// Create the report table or chart for second report
$rptObj->createReport();
if ($rptObj->getRecordCount() > 0) {
    $rptCss .= $rptObj->getCSSvalues();
    $report2 = $rptObj->getReport();
}
else $report2 = 'No records found';


// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name for the third report
$okay3 = $rptObj->defineReport('frank', 'Sales by Region Graph', 'frank', 'Sales by Region Graph', true);

// Set query variable values
$rptObj->setQueryVar('frank.region', 'northwest');

// Create the report table or chart for third report
$rptObj->createReport();
if ($rptObj->getRecordCount() > 0) {
    $rptCss .= $rptObj->getCSSvalues();
    $report3 = $rptObj->getReport();
}
else $report3 = 'No records found';


// Define the Query Owner and Query Name, followed by the Layout Owner and Layout Name for the fourth report
$okay4 = $rptObj->defineReport('frank', 'Category Sales Graph', 'frank', 'Category Sales Graph', true);

// Create the report table or chart for fourth report
$rptObj->createReport();
if ($rptObj->getRecordCount() > 0) {
    $rptCss .= $rptObj->getCSSvalues();
    $report4 = $rptObj->getReport();
}
else $report4 = 'No records found';


// HTML for the web page
echo '<!DOCTYPE html>'."\n";
echo '<html><head>'."\n";
echo '<meta charset="utf-8" />'."\n";
echo '<title>Multiple Reports Using One DB Connection</title>'."\n";

// Below is the Javascript necessary to support the rendering of a FusionChart within a Report Object
echo '<script type="text/javascript" src="../demo/fusioncharts/fusioncharts.js"></script>'."\n";

echo '<style type="text/css">'."\n";

// Some optional CSS for the body font
echo 'body { font:normal 13px sans-serif }'."\n";

// Insert the CSS classes accumulated above for all reports
echo $rptCss;

echo '</style>'."\n";

echo '</head><body>'."\n";

echo '<div id="report1" style="display:table;margin:0 auto;padding-bottom:10px">'."\n";
echo '<p style="text-align:center"><b>This is the first report: frank.overstock</b></p>'."\n";

// Insert the report table or chart
echo $report1;

echo '</div>'."\n";

echo '<div id="report2" style="display:table;margin:0 auto;padding-bottom:10px">'."\n";
echo '<p style="text-align:center"><b>This is the second report: frank.order info</b></p>'."\n";

// Insert the report table or chart
echo $report2;

echo '</div>'."\n";

echo '<div id="report3" style="display:table;margin:0 auto;padding-bottom:10px">'."\n";
echo '<p style="text-align:center"><b>This is the third report: frank.Sales by Region Graph</b></p>'."\n";

// Insert the report table or chart
echo $report3;

echo '</div>'."\n";

echo '<div id="report4" style="display:table;margin:0 auto;padding-bottom:10px">'."\n";
echo '<p style="text-align:center"><b>This is the fourth report: frank.Category Sales Graph</b></p>'."\n";

// Insert the report table or chart
echo $report4;

echo '</div>'."\n";
echo '</body></html>'."\n";

// End the PHP session
?>

Note: If multiple FusionWidgets are used in a Report Object page, there are specific Javascript references used in the Custom Layout HTML that must be unique. QLR Manager will automatically increment these references if the following naming conventions are used:

<div id="chartContainer">FusionWidgets XT will load here</div>
var myChart = new FusionCharts("fusioncharts/AngularGauge.swf", "myChartId", "400", "200", "0");

Example #9: This is an example of creating a page to use slider input controls. Since sliders are much more complex than form controls that are often used to set query variables, there are some specific requirements for the page to render sliders.

This example can be executed at http://www.qlrmanager.com/demo_rptobj/example_9.php.

<?php

// Create a PHP session
session_start();

// Define the relative path to the QLR Manager file directory
$_SESSION['inc_path'] ='../demo/';

// Include the Report Object file
include ('../demo/ReportObject.php');

// Included code to read the hidden form elements to update defaults with latest values
// To see the saved slider name, run a query within QLR using the slider input control and view the page source
if (isset($_REQUEST['slider_sp_1'])) {
    $default1 = $_REQUEST['slider_sp_1'];
}
else $default1 = '30';

if (isset($_REQUEST['slider_sp_date'])) {
    $default2 = $_REQUEST['slider_sp_date'];
}
else $default2 = "BETWEEN '2015-02-15' AND '2015-09-12'";


// Create a new Report Object
// Provide the Host, Database associated to the query to be run, User ID and Password
// The User ID must have authority to execute the query, meaning that the query was saved as 'shared'
$rptObj = new ReportObject('localhost', 'qlrmtat0_baitshop', 'guest', 'guest');

// Define the input controls that will be called within the HTML page further below
$controls = array('frank.slider 1','frank.slider date');
$rptObj->setControl($controls);


// HTML for the web page
echo '<!DOCTYPE html>'."\n";
echo '<html><head>'."\n";
echo '<meta charset="utf-8" />'."\n";
echo '<title>Slider Input Controls</title>'."\n";

// Imports the style sheets necessary to support the sliders defined in $controls above
echo $rptObj->getSliderCSSfile();

echo '<style type="text/css">'."\n";
// Some optional CSS for the font
echo 'p, td { font:normal 13px sans-serif }'."\n";
// Include the slider specific CSS classes
echo $rptObj->getSliderCSS();
echo '</style>'."\n";


// Below are the Javascript imports necessary to support the rendering of sliders within a Report Object
echo '<script type="text/javascript" src="../demo/qlr_manager/jslib/jquery-1.11.3.min.js"></script>'."\n";
echo '<script type="text/javascript" src="../demo/qlr_manager/jslib/jquery-ui-1.11.4.min.js"></script>'."\n";
echo '<script type="text/javascript" src="../demo/qlr_manager/jslib/jQAllRangeSliders-withRuler-min.js"></script>'."\n";
echo '<script type="text/javascript" src="../demo/qlr_manager/jslib/slider.js"></script>'."\n";


// Include the Javascript required to create the slider instance(s)
echo '<script type="text/javascript">'."\n";
echo $rptObj->getSliderJS();
echo '</script>'."\n";


echo '</head><body>'."\n";
echo '<form name="vars" target="_self">'."\n";
echo '<p style="text-align:center">Slide the handles and Submit the form. The submitted values ';
echo 'will become the new default values by updating the associated hidden form elements.</p>'."\n";
echo '<table style="border-spacing:5px;margin:0 auto"><tr>'."\n";

// Notice the class that references the name of the slider with a suffix of "_cel".
// This must be used for any container, div, td, etc. to set the margins saved with the slider.
echo '<td style="padding:6px;vertical-align:bottom;white-space:nowrap"><b>Numeric slider:</b></td>'."\n";

// Notice the class that references the name of the slider with a suffix of "_cel".
// This must be used for any container, div, td, etc. to set the margins saved with the slider.
echo '<td class="slider_sp_1_cel">'.$rptObj->getControl('frank.slider 1').'</td>'."\n";
echo '</tr><tr>'."\n";
echo '<td style="padding:6px;white-space:nowrap"><b>New default:</b></td>'."\n";
echo '<td>'.$default1.'</td>'."\n";
echo '</tr><tr>'."\n";
echo '<td style="padding:6px;vertical-align:bottom;white-space:nowrap"><b>Date slider:</b></td>'."\n";

// Notice the class that references the name of the slider with a suffix of "_cel".
// This must be used for any container, div, td, etc. to set the margins saved with the slider.
echo '<td class="slider_sp_date_cel">'.$rptObj->getControl('frank.slider date').'</td>'."\n";
echo '</tr><tr>'."\n";
echo '<td style="padding:6px;white-space:nowrap"><b>New default:</b></td>'."\n";
echo '<td>'.$default2.'</td>'."\n";
echo '</tr><tr>'."\n";
echo '<td></td>'."\n";
echo '<td style="padding:20px;text-align:center"><input type="submit" value="  Submit  " /></td>'."\n";
echo '</tr></table>'."\n";

// These hidden form elements are updated with the submitted values and read as the new defaults
// To always use the defaults defined with the slider input control, omit these hidden elements
echo '<input type="hidden" name="slider_sp_1" value="'.$default1.'" />'."\n";
echo '<input type="hidden" name="slider_sp_date" value="'.$default2.'" />'."\n";


echo '</form>'."\n";
echo '</body></html>'."\n";

// End the PHP session
?>
Class functions
addAttachment:
null addAttachment(file_content, file_name)

This function allows for content to be added as a file attachment to an e-mail. The e-mail is sent using the sendMail() function. Multiple files can be attached by calling this function more than once.

Example:

// Create the report table
$rptObj->createReport();

// get ready to send an e-mail
$to      = 'jake@gmail.com';
$cc      = '';
$bcc     = '';
$from    = 'greg@iris.com';
$subject = 'Inventory report';
$body    = 'Please find attached the latest inventory report.';

// This command gets the report data formatted for Excel
$content = $rptObj->getFileData('xlsf');

// Now add the attachment
$rptObj->addAttachment($content,'inventory.xls');

// Note: Once sendMail is executed, it resets the current attachments to empty.
$rptObj->sendMail($to, $cc, $bcc, $from, $subject, $body);

See sendMail() for information about sending an e-mail. In addition, getFileData() is used to retrieve the current report in a particular file format.

cleanUpFiles:
void cleanUpFiles()

This calls the QLR Manager clean up routine. It will delete QLR Manager pages that are over 12 hours old. This is intended for installations that primarily run Report Objects and rarely use the traditional QLR Manager interface. The traditional interface invokes this routine once a day with the first log on through the Connect panel.

Example:

$rptObj->cleanUpFiles();
createReport:
string createReport()

Creates the report based on the query and layout parameters provided in defineReport(). Returns either "report" when a report table is successfully created or "info" when an informational message is generated instead of a report.

Example:

$outputType = $rptObj->createReport();
defineReport:
boolean defineReport(string query_owner, string query_name,
                     string layout_owner, string layout_name)

Defines the query and layout to be used as the basis for the report. Alternately, if the query_owner is defined as '*query_text*', a query string can be entered as the query_name as illustrated in Example #2. If no layout is desired, the layout_owner and layout_name can be entered as '' (empty single quotes). The queries used in this function should be a single SELECT query, to which a layout can be applied.

Note: The query and layout specified in defineReport() must either be owned by the ID that was provided when the new ReportObject() was created, or the query and layout must have been saved to be SHARED with other users.

Example #1:

$okay = $rptObj->defineReport('frank', 'invoice report',
                              'frank', 'invoice summary');

Example #2:

$query = 'select custID, first_name, last_name from customer';
$okay = $rptObj->defineReport('*query_text*', $query, '', '');
getControl:
string getControl(string control_name)

Returns an HTML formatted string for the specified Input Control. If the Input Control is not found, a "not found" message is returned. The HTML "name" is the Input Control name. Some controls are returned in a table to preserve formatting. If the Input Control is a date input with the calendar, there are some CSS Classes, a JS file import and an <iframe> element that must be included in the web page. Please see Calendar input control for more information.

Note: This function should be called after setControl() has initialized the input control(s) for use.

Example:

echo $rptObj->getControl('custlist');

Would return:

<select name="custlist" size="1">
<option value="17">Asher</option>
<option value="15">Bagadonuts</option>
<option value="11">Benton</option>
<option value="8">Billings</option>
</select>

The Input control owner can also be used when setting and getting a control. This is necessary when the control does not belong to the connection ID used when defining the report object.

echo $rptObj->getControl('kenny.custlist');

The saved name of the input control is used as the name attribute of the HTML form element. When the input control was saved, it may contain characters that are not allowed in an HTML name attribute, such as a space. This is important to know if the form element is going to be referenced for any purpose in the PHP code. Following is a list of character substitutions made in the form element name:

. Period is replaced with _pe_
  Space is replaced with _sp_
( Opening parenthesis replaced with _lp_
) Closing parenthesis replaced with _rp_
- Hyphen is replaced with _ds_
# Hash is replaced with _pd_
> Greater than is replaced with _gt_
< Less than is replaced with _lt_
: Colon is replaced with _cl_
, Comma is replaced with _cm_

Input Controls designed in QLR Manager offer some advanced features that can be deployed when used as Report Objects:

[Select all] [Deselect all] [Reverse] for checkbox groups and multi-select lists.
Character limit for text areas.
Selection limit for checkbox groups and multi-select lists.
Regular Expression validations and string replacement for text inputs and text areas.

If any of the above features are used with an input control, the following js file must be imported into the <head> of the HTML page to support these functions. The path for the "src=" attribute may have to be adjusted to correctly locate inptctl.js relative to the PHP file producing the HTML page:

<script language="javaScript" src="./qlr_manager/jslib/inptctl.js" type="text/javascript"></script>

For features such as Character/Selection limit and Regular Expression validations, it is also wise to validate the submitted data on the server. These functions on the client side rely on JavaScript and it is possible to disable JavaScript in the browser which would disable these limits and validations.

getCSSvalues:
string getCSSvalues()

Returns the Cascading Style Sheet (CSS) values needed to format the report table.

Note: It does not return the opening and closing <style> tags.

Example:

$css = $rptObj->getCSSvalues();
getDataRow:
array getDataRow()

Returns an associative array of the values in the current result set and then points to the next record in the result set.

Example:

$okay = $rptObj->runQuery('select partnum,boh from inventory where boh>10000');
if ($okay) {
    echo 'Record count: '.$rptObj->getRecordCount()."<br />\n";
    for ($i=0; $i < $rptObj->getRecordCount(); $i++) {
        $row = $rptObj->getDataRow();
        while (list($key,$value) = each ($row)) {
            $key = strtolower($key);
            echo " $key = $value";
        }
        echo "<br />";
    }
}
else echo 'Error: '.$rptObj->getErrorNum().' '.$rptObj->getErrorMsg();
getErrorMsg:
string getErrorMsg()

Returns the database engine error message when runQuery() returns false.

Example:

$okay = $rptObj->runQuery('select * from inventory where boh>10000');
if (!$okay) {
    echo 'Error: '.$rptObj->getErrorNum().' '.$rptObj->getErrorMsg();
}
getErrorNum:
integer getErrorNum()

Returns the database engine error number when runQuery() returns false.

Example:

$okay = $rptObj->runQuery('select * from inventory where boh>10000');
if (!$okay) {
    echo 'Error: '.$rptObj->getErrorNum().' '.$rptObj->getErrorMsg();
}
getFieldName:
string getFieldName(integer field_number)

Returns the field name from a result set associated to a column number. The first column in a result set is equal to zero.

Example:

$okay = $rptObj->runQuery('select * from inventory where boh>10000');
if ($okay) {
    echo 'The second column is called: '.$rptObj->getFieldName(1)."<br />\n";
}
else echo 'Error: '.$rptObj->getErrorNum().' '.$rptObj->getErrorMsg();
getFileData:
string getFileData(output_type)

This returns the current report data formatted in a particular file format.

Valid output types are as follows:

Output
Type
Description
csv Comma separated value file.
csv+ Comma separated value file with report column headers.
csvr Comma separated value file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
csvr+ Comma separated value file with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
doc Microsoft Word Doc file.
html HTML formatted file.
html+ HTML formatted file that can be imported into MS Excel or Word.
txt Semi-colon separated value file.
txt+ Semi-colon separated value file with report column headers.
txtr Semi-colon separated value file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
txtr+ Semi-colon separated value file with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
xls Microsoft Excel file (data only).
xls+ Microsoft Excel file (data only) with report column headers.
xlsr Microsoft Excel file (data only) produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
xlsr+ Microsoft Excel file (data only) with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
xlsf Microsoft Excel file formatted with coloring, etc.
xml XML formatted file.
xmlr XML formatted file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.

Example:

// Create the report table
$rptObj->createReport();

// This command gets the report data formatted for Excel
$content = $rptObj->getFileData('xlsf');

// Now add the attachment
$rptObj->addAttachment($content,'inventory.xls');

See addAttachment() for information about adding an attached file to an e-mail.

getInfo:
string getInfo()

Returns the informational message text generated when createReport() returns "info".

Example:

$msg = $rptObj->getInfo();
getOutput:
string getOutput()

Returns both the Cascading Style Sheet values (enclosed in <style> tags) and the report table when createReport() returns "report".

Example:

$output = $rptObj->getOutput();
getOutputFile:
(browser download interface) getOutputFile(string output_type, string file_name)

Creates an output file for user download as defined by the output type. The file extension is automatically appended to the file name based on the output type. This function is called after createReport().

Valid output types are as follows:

Output
Type
Description
csv Comma separated value file.
csv+ Comma separated value file with report column headers.
csvr Comma separated value file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
csvr+ Comma separated value file with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
doc Microsoft Word Doc file.
html HTML formatted file.
html+ HTML formatted file that can be imported into MS Excel or Word.
txt Semi-colon separated value file.
txt+ Semi-colon separated value file with report column headers.
txtr Semi-colon separated value file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
txtr+ Semi-colon separated value file with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
xls Microsoft Excel file (data only).
xls+ Microsoft Excel file (data only) with report column headers.
xlsr Microsoft Excel file (data only) produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
xlsr+ Microsoft Excel file (data only) with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.
xlsf Microsoft Excel file formatted with coloring, etc.
xml XML formatted file.
xmlr XML formatted file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications.

Example:

$rptObj->getOutputFile('csv','invoices');

Note: It is very important that no information is sent to the browser, not even blank characters, prior to calling this function.

getPDFfile:
PDF data stream getPDFfile(integer left_margin,
                           integer right_margin,
                           integer top_margin,
                           integer bottom_margin,
                           string page_orientation,  ('p' for portrait, 'l' for landscape)
                           string paper size, ('A0','A1','A2','A3','A4','A5','A6','A7','A8','A9','A10',
                                               'B0','B1','B2','B3','B4','B5','B6','B7','B8','B9','B10',
                                               'C0','C1','C2','C3','C4','C5','C6','C7','C8','C9','C10',
                                               '4A0','2A0','RA0','RA1','RA2','RA3','RA4','SRA0','SRA1',
                                               'SRA2','SRA3','SRA4','Demy','Executive','Folio','Letter',
                                               'Legal','Ledger','Royal','Tabloid','A','B')
                           integer page_width, (0. No longer used. Included for backward compatability)
                           string  page_content(an empty string '', or custom content)

This function supports the creation of a PDF file stream sent to the browser. Since it does send data back to the browser, no output should be sent from the server prior to this function returning data. The "page_orientation" is determined by examining the first letter of the value provided and is case insensitive. The "paper_size" arguments are also case insensitive. As the "page_width" value is increased, more information can be fit onto a page, similar to how the screen resolution setting works on a computer display.

There are two basic ways to provide content to this function. The first is by executing the createReport() function prior to calling getPDFfile and entering '' (empty quotes) as the "page_content" value. The second way is to provide customized content for the entire PDF output stream via the "page_content" value. Following are examples of the two methods.

Report created by QLR Manager:

$rptObj = new ReportObject('localhost', 'baitshop', 'frank', 'my02dog');

// Define the query owner and query name, followed by the layout owner and name
$okay = $rptObj->defineReport('frank','sales','frank','sales summary');

// Create the report
$rptObj->createReport();

// The following will create a PDF stream based on the report created in
// createReport(). Notice how the last argument for page_content is ''.
$rptObj->getPDFfile(10,10,5,5,'p','letter',0,'');

Customized output:

$rptObj = new ReportObject('localhost', 'baitshop', 'frank', 'my02dog');

// Define the query owner and query name, followed by the layout owner and name
$okay = $rptObj->defineReport('frank','invoice','frank','invoice_layout');

// Create a report body
$rptObj->createReport();

// Start some custom content
$content  = 'Dear Mary,<br /><br />Here is your invoice<br />';
// Optionally embed a report into the custom content
$content .= '<div style="display:table;margin:0 auto">';
$content .= $rptObj->getStyledReport();
$content .= '</div>';
// Finish the custom content
$content .= '<br /><Sincerely,<br /><<br />Frank';

// The following will create a PDF stream based on the custom content.
$rptObj->getPDFfile(15,15,20,20,'portrait','a4',0,$content);
getQuery:
string getQuery()

Returns the query string that was executed by createReport(). This is helpful for debugging, especially when a query includes query variable replacement.

Example:

$okay = $rptObj->runQuery('select * from inventory where boh>10000');
if (!$okay) {
    echo 'Query text: '.$rptObj->getQuery();
}
getRecordCount:
integer getRecordCount()

Returns the number of rows in the report that is generated after createReport() is executed.

Example:

$count = $rptObj->getRecordCount();
getReport:
string getReport()

Returns the report HTML table generated when createReport() returns "report".

Example:

$reportBody = $rptObj->getReport();
getSliderCSS:
string getSliderCSS()

Returns the Cascading Style Sheet (CSS) values used to customize sliders used as input controls. Example #9 demonstrates usage.

Note: It does not return the opening and closing <style> tags.

Example:

echo $rptObj->getSliderCSS();
getSliderCSSfile:
string getSliderCSSfile()

Returns the appropriate style sheet file(s) to support the rendering of the sliders.

Example:

echo $rptObj->getSliderCSSfile();

Would return:

<link type="text/css" rel="stylesheet" href="../demo/qlr_manager/styles/slider_medium_lite.css"/>
getSliderJS:
string getSliderJS()

Returns the Javascript required to create the slider instances.

Example:

echo $rptObj->getSliderJS();
getSQLdate:
string getSQLdate(string raw_date_value, string input_control_name)

Returns a date string in the database yyyy-mm-dd format by converting dates that are in a different format for a given Input Control.

Example:

$sql_date = $rptObj->getSQLdate('12-31-2005', 'first_date');
getStyledReport:
string getStyledReport()

Returns the report HTML table generated when createReport() returns "report". The CSS entries are created as in-line style="(values)" entries as part of each report table cell. This provides formatted output without having to accommodate CSS entries. The drawback is that it returns more data.

Example:

$reportBody = $rptObj->getStyledReport();
ReportObject:
object ReportObject( string host, string database_name,
                     string userid, string password [, string database engine])

Creates an object of type ReportObject and establishes database connection parameters. When using QLR Manager with Oracle, the database name is be entered as '' (empty single quotes).

Example:

$rptObj = new ReportObject('localhost', 'custdata', 'jake', 'myd0g22');

By default, a report object is created pointing to the same database engine where the QLR Manager master tables reside. In this example, the report object specifically points to an SQL Server database. Valid values for the database engine are 'sql server', 'mysql', 'oracle', and 'postgresql'. Once the database engine has been set, that is the database engine that will be referenced until changed to a different value.

$rptObj = new ReportObject('x37\sqlexpress', 'inventory', 'sa', 'h0meb0y', 'sql server');
replaceNameChars:
string replaceNameChars(string input_control_name)

Returns a string that converts special characters back to their original form in an input control name.

When input controls are created as report objects, the name of the input control is used as the HTML form element NAME. The name applied to the input control when saved in QLR Manager may contain invalid characters that cannot be used in the HTML form element NAME attribute. Certain characters must be converted to produce valid HTML. For example, an input control with the name of "first.date(yyyy-mm-dd)" is converted by QLR Manager to "first_pe_date_lp_yyyy_ds_mm_ds_dd_rp". replaceNameChars will convert the QLR use of the form element NAME attribute back to the original input control name.

Example:

$name = $rptObj->replaceNameChars('first_pe_date_lp_yyyy_ds_mm_ds_dd_rp');
runQuery:
boolean runQuery(string query_text)

Executes an SQL query string using the connect values as provided when the ReportObject() was created.

Examples:

$rptObj->runQuery('select * from customer');
$rptObj->runQuery('update inventory set boh=495 where partnum=65099');
saveOutputFile:
boolean saveOutputFile(string output_type, string path/file_name)

Saves a file to disk as defined by the output type. The path can be either relative or absolute. The file extension is automatically appended to the file name based on the output type. This function is called after createReport(). Returns TRUE if the file is successfully written.

Valid output types are as follows:

Output
Type
Description
csv Comma separated value file.
csv+ Comma separated value file with report column headers.
doc Microsoft Word Doc file.
xls Microsoft Excel file (data only).
xls+ Microsoft Excel file (data only) with report column headers.
xlsf Microsoft Excel file formatted with coloring, etc.
html HTML formatted file.
html+ HTML formatted file that can be imported intoMS Excel or Word.

Example:

$okay = $rptObj->saveOutputFile('doc', '../myfiles/invoices');
sendMail:
boolean sendMail(string to_addresses, (multiple address separated by commas)
                 string carbon_copy_addresses, (multiple address separated by commas)
                 string blind_copy_addresses, (multiple address separated by commas)
                 string from_address,
                 string email_subject,
                 string email_content)

This function supports HTML formatted email distribution. It requires that you have a Simple Mail Transfer Protocol (SMTP) server running. This returns true if the email content is successfully sent to the SMTP server, but it does not know if the STMP server has successfully sent the message.

Example:

// Create the email body
$Body  = '<p>Fred, here is your account activity:</p>';
$Body .= $rptObj->getStyledReport();
$Body .= '<p>Sincerely,<br />Frank</p>';

$To   = 'fred@yahoo.com';
$Cc   = '';
$Bcc  = 'jake@comcast.net, tom@hotmail.com';
$From = 'john@tekoutsight.com';
$Subject = 'Account activity';

// Returns true or false based on getting mail to SMTP server.
$sent = $rptObj->sendMail($To,$Cc,$Bcc,$From,$Subject,$Body);

See addAttachment() for information about adding an attached file to an e-mail.

setBlockSize:
void setBlockSize(integer number_of_rows_to_display)

Allows the User to specify the number of rows of data to return in a Report Object. It is used in conjunction with setStartRow() to create Report Objects that contain ranges of report row output.

This function should be used after defineReport() is called and before createReport() is called.

Examples:

$rptObj->setStartRow(1001);
$rptObj->setBlockSize(500);
setColumnAttribute:
void setColumnAttribute(integer column_number, string field_name, string value)

Allows the User to set the attributes associated with given column within a report. The first column is equal to 1. These are the same fields as found in the Report Columns section of the Layout panel. Generally this function does not need to be used. It is much simpler to create and save a Layout and then reference the Layout in defineReport().

The allowable field_names are column_title, column_align, column_action, format, decimal_places, display width and evaluate. Please view the allowable choices for these fields in the Report Columns section of the Layout panel.

This function should be used after defineReport() is called and before createReport() is called.

Examples:

$rptObj->setColumnAttribute(1, 'column_title','customer<br />number');
$rptObj->setColumnAttribute(1, 'column_align','Left');
setControl:
void setControl(string or array of  control_names)

This function initializes the Input Controls to be used in a page. A single control can be specified by providing a string argument. If more than one control is to be used in a page, an array of string arguments should be provided.

Examples:

$rptObj->setControl('custlist');

$controls = array('custlist','startDate','endDate','region');
$rptObj->setControl($controls);
setControlQueryVar:
void setControlQueryVar(string query_var_name, string value)

This function allows for the values of query variables that are present in the input control query text to be set with a value. Note: This must be set prior to calling the setControl() function.

The control_name references a control that was defined in setControl().

Here is an example, assuming that the query text in the following Input Control named "customer number" has a query that contains the text of "select acnt_num from customer where region='[region code]' ".

$rptObj->setControlQueryVar('region code','southwest');
$rptObj->setControl('customer number');

If need be, an entire query can be dynamically set if the query text in the Input Control is nothing more, for example, than [my query text].

$rptObj->setControlQueryVar('my query text',$query);
$rptObj->setControl('custom list');
setControlValue:
void setControlValue(string control_name, string field_name, mixed value)

This function allows for certain input control characteristics to be overriden and set at the time of use.

The control_name references a control that was defined in setControl().

The field_name can be one of the following. Different fields can be set by issuing this function more than once.

Field Description
default_val This allows for the default value of a field to be set. If the control is a checkbox set or multiple select list, an array of values can be used to set multiple values.
error_msg The error message to display when a regular expression check fails.
list_text This allows for the replacement of query variables within an Input Control's list text as the data source.
max_size the character width of a text input field or text area, or the number of columns of checkboxes or radio buttons when a grid arrangement is specified.
reg_exp A regular expression string to perform data validation.
select_limit The number of characters that can be entered into a text field or textarea, or the number of selections that can be chosen from a checkbox set or multiple select list.
text_rows The number of rows to display in a textarea or the height of a select list.

Examples:

$rptObj->setControlValue('customer number','default_val', 230099);
$rptObj->setControlValue('customer number','select_limit', 5);

$names = array('Fred','Nick','Kadir','Anitha');
$rptObj->setControlValue('first names','default_val',$names);
$rptObj->setControlValue('organizer event list','list_text', 'option1,option2,option3');
setPageAttribute:
void setPageAttribute(string field_name, string value)

Allows the User to set the attributes associated to the overall page appearance of a report. These are the same fields as found in all the sections of the Layout panel, except for the setColumnAttribute() which corresponds to the Report Columns section of the Layout. Generally this function does not need to be used. It is much simpler to create and save a Layout and then reference the Layout in defineReport().

The allowable field_names for this function can be found by looking at the Table info (from the Query panel) for the table qlr_layout_master.

This function should be used after defineReport() is called and before createReport() is called.

Examples:

$rptObj->setPageAttribute('title_text','Customer Invoice report');
$rptObj->setPageAttribute('title_font_face','Arial,Helvetica,sans-serif');
setPDFdestination:
void setPDFdestination(integer 1 (default browser), 2 file, 3 data stream)

Allows the User to specify that PDF output should be sent to a file or to return a data stream.

This function should be used before getPDFfile() is called. When a file is created, it will be saved in the QLR Manager /reports directory unless setPDFfiledir() is used to specify a different location. Please note that when saving to a file, the process will cause the user's browser window to go blank, so some sort of HTML should be sent after the creation of the file.

Examples:

$rptObj->setPDFdestination(2);
$rptObj->setPDFfilename('juneSales');
$rptObj->setPDFdestination(3);
$attachment = $rptObj->getPDFfile(10,10,5,5,'p','A4',0,'');
setPDFfiledir:
void setPDFfiledir(String file_directory)

Allows the User to specify the directory of where the PDF file will be created. This can be used when creating a file for downloading or when saving the PDF output to a file. The directory is relative to where the executing report object is located. If not specified, the QLR Manager reports directory is used.

Examples:

$rptObj->setPDFfiledir('../../june_reports/');
$rptObj->setPDFfilename('sales');
setPDFfilename:
void setPDFfilename(String file_name)

Allows the User to specify the name of the PDF file that is being created. This can be used when creating a file for downloading or when saving the PDF output to a file. QLR Manager will automatically add the .pdf file extension.

Examples:

$rptObj->setPDFfilename('juneSales');
$rptObj->setPDFfilename('invoice224');
setPDFoption:
void setPDFoption(String option_name, String value)

Allows the User to specify options to be used in PDF file creation.

Option_name Value
header Header text that can be applied to PDF pages. Format is left||center||right text. HTML tagging can be used.
footer Footer text that can be applied to PDF pages. Format is left||center||right text. HTML tagging can be used.
filedir The destination directory when saving data to a file.
filename The file name when saving data to a file or displaying a download dialogue.
copy Create file without the ability to copy text. Value = no.
edit Create file without the ability to edit text. Value = no.
print Create file without the ability to print text. Value = no.
watermark Watermark text that will be applied diagonally across each page.
background This is a Cascading Style Sheet (CSS) entry that can be used to set the PDF page background. Please see Report Tools help for details.

Examples:

$rptObj->setPDFoption('copy', 'no');
$rptObj->setPDFoption('edit', 'no');
$rptObj->setPDFoption('print', 'no');
$rptObj->setPDFoption('watermark', 'Confidential');
$rptObj->setPDFoption('background','background-color: #EEEECC');
$rptObj->setPDFdestination(3);
$attachment = $rptObj->getPDFfile(10,10,5,5,'p','A4',0,'');

Please note that when using optional settings in a Macro, in order to use a comma as an option value, it needs to be "escaped" by entering a double comma in the value text. That is not the case when using this function.

setQueryVar:
void setQueryVar(string variable_name, string value)

Allows the User to set the value of a query variable when present. It is not necessary to use the ** prefix, or the [brackets] used in QLR Manager version 5, with the variable_name.

This function should be used after defineReport() is called and before createReport() is called.

Examples:

$rptObj->setQueryVar('customer_id','73490');
$rptObj->setQueryVar('year','2004');
setStartRow:
void setStartRow(integer first_row_to_display)

Allows the User to specify the first row number of data to return in a Report Object. It is used in conjunction with setBlockSize() to create Report Objects that contain ranges of report row output. The first report row is 1.

This function should be used after defineReport() is called and before createReport() is called.

Examples:

$rptObj->setStartRow(1001);
$rptObj->setBlockSize(500);
setWatermark:
void setWatermark(text)

Allows the User to specify text that will be displayed as transparent text rendered diagonally across the first page of the generated PDF file.

Example:

$rptObj->setWatermark('Draft');
setXMLRecordTag:
void setXMLRecordTag(text)

Allows the User to set the record level tag for XML output. The default value is "record".

Example:

$rptObj->setXMLRecordTag('customer');
setXMLRootTag:
void setXMLRootTag(text)

Allows the User to set the root level tag for XML output. The default value is "data".

Example:

$rptObj->setXMLRootTag('allcustomers');
suppressSelectAll:
void suppressSelectAll(void)

This function will suppress the addition of the [Select all] [Deselect all] [Reverse] controls that appear on the top of checkbox sets and multiple select list input controls.

Example:

$rptObj->suppressSelectAll();
Calendar input control

A calendar is provided for use with input controls that require date and time selections. The calendar is automatically included within QLR Manager if an input control is defined using one of the "Calendar" selections available under Control type. These input controls can be created by clicking the link from either the Query or Wizard panels. An example of the calendar can be viewed in the Input Controls topic. This calendar can also be used when input controls are used in conjunction with Report Objects. Since this use requires the calendar be available in the web page using the getControl() and setControl() functions, there are some additional steps required for the calendar to be accessible and function correctly.

Note: All the path references below must be adjusted to reference the calendar files relative to the web page PHP directory. All the files required to support the calendar input control are located in the /qlr_manager/calendar directory. It may be more convenient to copy this directory and its contents to a location under the PHP directory to more easily reference the path. The instructions below assume this path configuration. Be sure to read the comments in the top of calendar.html for available configuration options.

CSS classes:

There are several CSS classes that should included in the <head> of the web page:

#calWin This class is required to define the <iframe> that will hold the calendar.
.calBtn,
.timeBtn,
.calTime
This defines the size, color and background image for the button used to launch the calendar. There are 3 calendar configurations to select date only, date plus time, and time only. When used to select a time format only, i.e. 22:59 or 22:59:59, the button image is a clock instead of a calendar.
.bothin,
.datein,
.timein
Since the rendering width of text inputs varies greatly among different browsers, these classes are included within the <input type="text" produced with the input control. Using the width rule produces a consistent input width across all browsers.
<style type="text/css">
#calWin {
  position:absolute;
  visibility:hidden;
  left:-1000px;
  top:-1000px;
  z-index:10;
  -webkit-box-shadow: 2px 2px 5px 0px rgba(102,102,102,1);
  -moz-box-shadow: 2px 2px 5px 0px rgba(102,102,102,1);
  box-shadow: 2px 2px 5px 0px rgba(102,102,102,1);
  -moz-border-radius:5px;
  -webkit-border-radius:5px;
  border-radius:5px;
  position:absolute;
  visibility:hidden;
  left:-1000px;
  top:-1000px;
  border-style:solid;
  border-width:1px;
  border-color:#2274C6 #0053A6 #0053A6 #2274C6
}

.calBtn	 { background:#2274C6 url(./calendar/calbtn.png) 0 0 no-repeat }
.timeBtn { background:#2274C6 url(./calendar/timebtn.png) 0 0 no-repeat }
.calTime { background:#2274C6 url(./calendar/caltime.png) 0 0 no-repeat }

.calBtn,.timeBtn,.calTime {
  border-top:solid #2274C6 1px;
  border-right:solid #003E90 1px;
  border-bottom:solid #003E90 1px;
  border-left:solid #2274C6 1px;
  height:19px;
  width:30px;
  margin:0;
  filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#4495E7',endColorstr='#0053A6',gradientType=0);
  background-image: -moz-linear-gradient(top,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-image: -ms-linear-gradient(top,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-image: -o-linear-gradient(top,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-image: -webkit-linear-gradient(top,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-image: linear-gradient(to bottom,#4495E7 0,#0053A6 25%,#1D5084 50%,#1D5084 100%);
  background-size: auto 400%;
  background-position: 0 0;
  vertical-align: middle;
  -webkit-border-radius:5px;
  -moz-border-radius:5px;
  border-radius:5px;
  -moz-box-sizing:border-box;
  -webkit-box-sizing:border-box;
  box-sizing:border-box;
  -webkit-transition: all .4s ease 0s;
  -moz-transition: all .4s ease 0s;
  -ms-transition: all .4s ease 0s;
  -o-transition: all .4s ease 0s;
  transition: all .4s ease 0s;
}

.calBtn:hover,.timeBtn:hover,.calTime:hover {
  color:#FFFFFF;
  background-color: #1D5084;
  border:1px solid #04376B;
  background: #1D5084;
  background-position: 75% 100%;
}

.calBtn:active,.timeBtn:active,.calTime:active {
  border: 1px solid #04376B;
  -webkit-box-shadow: inset 0 0 5px 2px #1D5084, 0 1px 0 #F6F6F6;
  box-shadow: inset 0 0 5px 2px #1D5084, 0 1px 0 #F6F6F6;
}

.datein { width:100px }
.bothin { width:140px }
.timein { width:100px }
</style>
JS file import:

A JS file must be imported that will launch the calendar and position it immediately below the text input it is associated with. The following should be placed in the <head> of the web page following the <style> declarations:

<script language="javascript" src="./calendar/calendar.js" type="text/javascript"></script>

If the input control is defined as one of the time only formats, i.e. 22:59 or 22:59:59, an additional JS file import is required to support a regular expression validation of the text input field. This file is located in a different folder within the QLR Manager directory structure:

<script src="./qlr_manager/jslib/inptctl.js" type="text/javascript"></script>
Calendar html file:

The main component of the calendar is the HTML file. This file is loaded into an <iframe> that must be defined in the web page. Include the following <iframe> element in the bottom of the web page before the closing </body> tag:

<iframe id="calWin" name="calWin" src="./calendar/calendar.html" width="192" height="75" frameborder="0" style="overflow:hidden"></iframe>

Keep the <iframe> tagging on one line.

Calendar alternative:

The instructions above describe the most expedient way of creating a date selection input control. However, this may not provide a control that fits well with the web page design. An alternative is for the Author to create their own control and pass the date value to the report object using the setQueryVar() function. This method provides more flexibility in the design of the control, but it does require that the arguments passed to the openCalendar() function be carefully constructed with the selected value passed to the setQueryVar() function as described in the Overview.

Color schemes:

When used as a date and time input control associated with report objects, all the colors that make up the calendar can be easily changed to allow the Author to coordinate the look of the calendar with their web site. There are commented instructions in the top of calendar.html that describe how to change the colors. Following are examples of 3 predefined color sets that can be selected:

GRN   BRN   GRY
Green scheme Brown scheme Grey scheme