Embedding Reportico
Embedding a report within a web
page
In order to place a fully functional report within your own web pages you need to include code similar to the contents of the run.php within your script. It is necessary therefore to include reportico by providing its fully pathed position. When embedding you probably also want to customise how Reportico appears ot the user. For example you may want to include a single report output within a div, or include a single graph within a div or give the user the ability to enter criteria for a singl erpeort and execute it. You may want to give user access to a single report suite but disallow entry to report menus.
You can also use any of the parameters described in the above runner script for configuring your page.
An example web page may
look as follows :
<HTML>
<BODY>
...... Your web content ......
<!-- Start of Reportico Report -->
<?php
set_include_path('{PATH TO REPORTICO DIRECTORY}');
require_once('reportico.php');
$a = new reportico();
$a->embedded_report = true;
$a->forward_url_get_parameters = "x1=y1&x2=y2";
$a->execute();
?>
< !-- End of Reportico Report -->
...... More web content ......
</BODY>
</HTML>
Note the embedded report is very important when embedding and needs to be set to true.
Control user access to projects, reports
Using a combination of the initial_execute_mode and access_mode properties, it is possible to take a user to a specific report or specific report menu to execute report, execute a report to show just the graphs, just the report detail or just the group headers, for example. When generating report out you have control whether the user has a return button to go back to criteria entry.
initial_execute_mode indicates what type of screen user is first taken into. Valid values for initial_execute_mode are ADMIN, MENU, PREPARE or EXECUTE. ADMIN takes user to the ADMIN page, MENU takes user to a report menu ( an initial project must be specifiedd ), PREPARE takes user to the criteria entry screen of a specfic report ( project and report must be specified ) and EXECUTE runs a specified report ( project and report must be specified ).
Allow using the properties initial_project and initial_report take users directly to the specified report suite and report files respectively. If a project is controlled by a password this can be provided in the initial_project_password so the user doesnt have to type the password in.
access_mode indicates what Reportico pages and functions a user has access to. Valid options are
FULL
provides access to the Admin Page and the ability to enter Design mode, if users give the correct Admin password
ALLPROJECTS
users can use admin page to select a project (but not be able to enter admin mode). Users can select and run reports and navigate between report output, report criteria and the report project menu where users can choose a report to run. Dropdown menus ( see documentation ) can be used to swap between project reports and projects if required
ONEPROJECT
users can use a single project, select and run reports and navigate between report output, report criteria and the report project menu where users can choose a report to run. Swapping between project menus in rhis mode must also be down by a dropdown menu (see documentation for how to do this)
ONEREPORT
user gets access to a single report's criteria entry mode and can run the report and then jump back to criteria entry again
REPORTOUTPUT
the output of a single report is presented and the user may not navigate to any other Reportico page
Example 1 - Run a single report salesforecast,xml in project sales (which is password protected with password "password" and run to HTML output.
<HTML>
<BODY>
...... Your web content ......
<!-- Start of Reportico Report -->
<?php
require_once('{FULL PATH TO REPORTICO DIRECTORY}/reportico.php');
$q = new reportico();
$q->initial_project = "sales";
$q->initial_project_password = "password";
$q->initial_report = "salesforecast.xml";
$q->initial_execute_mode = "EXECUTE";
$q->initial_output_format = "HTML";
$q->access_mode = "REPORTOUTPUT";
$q->embedded_report = true;
$q->execute();
?>
< !-- End of Reportico Report -->
...... More web content ......
</BODY>
</HTML>
Example 2 - Run the menu for project "stock" with no access to menu
<HTML>
<BODY>
...... Your web content ......
<!-- Start of Reportico Report -->
<?php
require_once('{FULL PATH TO REPORTICO DIRECTORY}/reportico.php');
$q = new reportico();
$q->initial_project = "stock";
$q->initial_project_password = "password";
$q->initial_execute_mode = "MENU";
$q->access_mode = "SInGLEPROJECT";
$q->embedded_report = true;
$q->execute();
?>
< !-- End of Reportico Report -->
...... More web content ......
</BODY>
</HTML>
Passing Reportico an SQL query
When running reportico from an embedded page or via custom scripts it is possible to avoid the need to write a report and just give Reportico the SQL.
This is possible in two ways:
Option 1 - Use the initial_sql parameter and the initial_project parameter.
Note that the column aliases are useful to provide more meaning ful column titles ( other column titles will be derived by changing any underscores to spaces and having word initial letters capitalized.
<HTML>
<BODY>
...... Your web content ......
<!-- Start of Reportico Report -->
<?php
require_once('{FULL PATH TO REPORTICO DIRECTORY}/reportico.php');
$q = new reportico();
$q->initial_project_password = "password"; // If required
$q->initial_sql = "SELECT column1 AS columntitle1, column2 AS columntitle2, expression() as columntitle3 FROM table";
$q->initial_output_format = "HTML";
$q->access_mode = "REPORTOUTPUT";
$q->embedded_report = true;
$q->execute();
?>
< !-- End of Reportico Report -->
...... More web content ......
</BODY>
</HTML>
Option 2 - use the importSQL feature - this provides extra customisation features which will be documented later ( the idea being to be able to programatically build a report from scratch).
<HTML>
<BODY>
...... Your web content ......
<!-- Start of Reportico Report -->
<?php
require_once('{FULL PATH TO REPORTICO DIRECTORY}/reportico.php');
$q = new reportico();
$q->initial_project = "stock";
$q->initial_project_password = "password"; // if required
$q->importSQL("SELECT column1 AS columntitle1, column2 AS columntitle2 FROM table";
$q->get_column("column1")->set_attribute("column_display","hide");
$q->get_column("column1")->set_attribute("column_title","Custom Title");
$q->set_attribute("ReportTitle","New Report Title");
$q->embedded_report = true;
$q->execute();
?>
< !-- End of Reportico Report -->
...... More web content ......
</BODY>
</HTML>
Controlling Output
When directly taking a user to report output or criteria entry you can control certain output features.
To control whether output is provided in HTML, PDF, CSV, XML or JSON format just set the initial_output_format property to HTML, PDF, CSV, XML or JSON respectively For example :-
$q->initial_output_format = "PDF";
To control whether output shows or hides the report detail/graphs, criteria etc ...
$q->initial_show_detail = "hide";
$q->initial_show_graph = "show";
$q->initial_show_group_headers = "hide";
$q->initial_show_group_trailers = "hide";
$q->initial_show_column_headers = "hide";
$q->initial_show_criteria = "hide";
To control whether reports are generated as a single line per report output in multi-column format or a page of data with one line per column value, set the initial_output_style to either TABLE or FORM, e.g.
$q->initial_output_style = "FORM";
To provide a refresh button set the following :-
$q->show_refresh_button = true;
Reportico Styling
Reportico by default is styled according to Bootstrap 3, a copy of which is supplied with Reportico, which Reportico loads up.
However when embedding into a web page this may not be desirable. If your web page is not compatible with Bootstrap then you can revert to Reportico Classic styling by setting bootstrap_styles = false.
If your web pages already have Bootstrap 3 loaded then set bootstrap styles to "3" and set bootstrap_preloaded. If your site has bootstrap 2 then set this to "2".
$q->bootstrap_styles = "3"; // Set to false for reportico classic, or 2 to use bootstrap 2 styling conventions
$q->bootstrap_preloaded = false; // Set to true to use existing site bootstrap
Passing criteria parameters within embedded reports
One way of passing parameters reports is to use the "MANUAL_" method mechanism of passing parameters within the $_REQUEST record. A better way when embedding reportico or generating custom reportico scritps is to use the initial_execution_parameters array property. Just biuld this array up with the key of the criteria name ( as specified in the report criteria definitions ) and the criteria value you want used witihn the report query. For date ranges use the "-" notation and for multiple options for a list or lookup type criteria specify the values with comma separated values.
The following example runs a report for specified countries and covers the dates between the start of 2013 and today :-
$q->initial_execution_parameters = array();
$q->initial_execution_parameters["country"] = "Italy,Australia";
$q->initial_execution_parameters["invoice_dates"] = "2013-01-01-TODAY";
$q->execute();
Connecting to a framework or CMS database
Reportico can be embedded into PHP frameworks and CMS. In this case you may want to use the database connection settings from the framework rather than having to specify them again in your project definition. To achieve this, when you create to configure your project, you need to choose the "framework" database driver option. Then in your calling script, before the Reportico execute() call, you need to specify the following databse constants ( quite often CMSs or frameworks provide functions to access these values :-
define('SW_FRAMEWORK_DB_DRIVER','<DATABASETYPE>'); e.g. pdo_mysql, pdo_pgsql, pdo_mssql etc
define('SW_FRAMEWORK_DB_USER', $myframework->getuser());
define('SW_FRAMEWORK_DB_PASSWORD',$myframework->getPassword());
define('SW_FRAMEWORK_DB_HOST',$myframework->getHostName()); // Use ip:port to specifiy a non standard port
define('SW_FRAMEWORK_DB_DATABASE',$myframework->getDatabase());
$q->execute();
Turning on AJAX Reportico operation
AJAX mode is turned on by default.
In AJAX mode, all button presses are made as an AJAX call meaning that there is no full page refresh performed. This makes its operation look nicer when embedded in complex web pages. It also allows it to work alongside other web apps in a page.
To include reportico in this fashion you need to set a Reportico property alongside the other paramters. Just set For example :-
$q->reportico_ajax_mode = true;
Dynamic Grids
You can turn on dynamic sortable, searchable, pageable grids with the following parameters -
$q->dynamic_grids = true;
$q->dynamic_grids_sortable = true;
$q->dynamic_grids_searchable = true;
$q->dynamic_grids_paging = false;
$q->dynamic_grids_page_size = 10;
Report Menus - Static And Dropdown
When embedding it is easier to specify the menu items available to the user both in the dropdown control panel at the top and in the front end menu page.
Just set up the following arrays :-
// Static - main menu definition
$q->static_menu = array (
array ( "report" => "an_xml_reportfile1", "title" => "<AUTO>" ),
array ( "report" => "another_reportfile", "title" => "<AUTO>" ),
array ( "report" => "", "title" => "BLANKLINE" ),
array ( "report" => "anotherfreportfile", "title" => "Custom Title" ),
array ( "report" => "", "title" => "BLANKLINE" ),
array ( "report" => "andanother", "title" => "Another Custom Title" ),
);
// To auto generate a static menu from all the xml report files in the project use
$q->static_menu = array ( array ( "report" => ".*\.xml", "title" => "<AUTO>" ) );
// To hide the static report menu
$q->static_menu = array ();
// Dropdown Menu definition
$q->dropdown_menu = array(
array (
"project" => "projectname",
"title" => "dropdown menu 1 title",
"items" => array (
array ( "reportfile" => "report" ),
array ( "reportfile" => "anotherreport" ),
)
),
array (
"project" => "projectname",
"title" => "dropdown menu 2 title",
"items" => array (
array ( "reportfile" => "report" ),
array ( "reportfile" => "anotherreport" ),
)
),
);
Hiding Reportico UI features
It is possible to turn off the report output title, some of the control panel buttons and checkboxes in crtieria entry mode and some of the the menus by setting any of the following to "hide"
$q->output_template_parameters["show_hide_navigation_menu"] = "show";
$q->output_template_parameters["show_hide_dropdown_menu"] = "show";
$q->output_template_parameters["show_hide_report_output_title"] = "show";
$q->output_template_parameters["show_hide_prepare_section_boxes"] = "show";
$q->output_template_parameters["show_hide_prepare_pdf_button"] = "show";
$q->output_template_parameters["show_hide_prepare_html_button"] = "show";
$q->output_template_parameters["show_hide_prepare_print_html_button"] = "show";
$q->output_template_parameters["show_hide_prepare_csv_button"] = "show";
$q->output_template_parameters["show_hide_prepare_page_style"] = "show";
Passing criteria parameters within embedded reports
One way of passing parameters reports is to use the "MANUAL_" method mechanism of passing parameters within the $_REQUEST record. A better way when embedding reportico or generating custom reportico scritps is to use the initial_execution_parameters array property. Just biuld this array up with the key of the criteria name ( as specified in the report criteria definitions ) and the criteria value you want used witihn the report query. For date ranges use the "-" notation and for multiple options for a list or lookup type criteria specify the values with comma separated values.
The following example runs a report for specified countries and covers the dates between the start of 2013 and today :-
$q->initial_execution_parameters = array();
$q->initial_execution_parameters["country"] = "Italy,Australia";
$q->initial_execution_parameters["invoice_dates"] = "2013-01-01-TODAY";
$q->execute();
Namespaces - running more than one Reportico report on a single web page
It is possible to embedded reportico so that it generates several instances on a single page. You could for example chain report output together or use the hide and show options to run several reports with graphs to present only the graphs in a kind of dashboard.
To do this you need to use the namespace property to ensure that successive embedded reports do not get their sessions confused. You also need to create a new instance of reportico for each successive report to avoid other conflicts.
Note that reportico_ajax_mode is set to true. If you want to allow the user to interact wih more than one report on a page ( enter criteria, select from menus ) then you need this option to avoid full screen refreshes.
Note that currently you cannot chain together reports from different projects, this will be addressed in a future release.
This example chains together two reports in graph mode, note the use of the namespace property :-
require_once('<PATHTOREPORTICO>/reportico.php');
// Run first report
$q = new reportico();
$q->allow_debug = true;
$q->reportico_ajax_mode = true;
$q->initial_project = "myproject";
$q->initial_project_password = "password";
$q->initial_report = "graph1.xml";
$q->initial_execute_mode = "EXECUTE";
$q->initial_show_detail = "hide";
$q->initial_show_graph = "show";
$q->initial_show_group_headers = "hide";
$q->initial_show_group_trailers = "hide";
$q->initial_show_column_headers = "hide";
$q->initial_show_criteria = "hide";
$q->access_mode = "REPORTOUTPUT";
$q->embedded_report = true;
$q->session_namespace = "GRAPH1";
$q->show_refresh_button = true;
$q->execute();
// Run second report
$q = new reportico();
$q->allow_debug = true;
$q->reportico_ajax_mode = true;
$q->initial_project = "myproject";
$q->initial_project_password = "password";
$q->initial_report = "graph2.xml";
$q->initial_execute_mode = "EXECUTE";
$q->initial_show_detail = "hide";
$q->initial_show_graph = "show";
$q->initial_show_group_headers = "hide";
$q->initial_show_group_trailers = "hide";
$q->initial_show_column_headers = "hide";
$q->initial_show_criteria = "hide";
$q->access_mode = "REPORTOUTPUT";
$q->embedded_report = true;
$q->session_namespace = "GRAPH2";
$q->show_refresh_button = true;
$q->execute();
Using external parameters and variables within reports
If you want to pick up external system parameters such session variables or constants in tour reports you can set them in the main run.php or your own calling script. For example you might want to pass a user name,a user role or a site id. Just pass in
$q->user_parameters["your_parameter_name"] = "your parameter value";
Then you can pick up in Reportico SQL queries using the construct {USER_PARAM,your_parameter_name}.
Reporting by user
Quite often you want to produce reports that show records relevant to the logged in user. This is especially relevant to an embedded solution. To do this you need to provide the currently logged on user in the external_user property. Then to make use of the user in your queries use the {FRAMEWORK_USER} notation. For example in your calling script put :-
$q->external_user = $user;
$q->execute();
Then you can pick up in Reportico SQL queries using the construct {FRAMEWORK_USER} e.g..
SELECT x
FROM y
WHERE 1 = 1
AND userid = {FRAMEWORK_USER}
