This is an old revision of the document!


Custom Source Code

You may enter your own PHP code into the Custom Source Code Box on the Format menu.

The code you enter here is executed before the main report query and is used to perform preparatory work that for use by the main query. For example, this code may generate and populate temporary tables based on the criteria the user has entered, and this may be then linked in by the main report query. report query.

Custom Code is entered for more complex data preparation than PreSQLs can provide. If used in conjunction with Pre-SQLs then this section is executed after Pre-SQLs and before the main report query.

Note that custom source code may not be entered when running in SAFE mode as the user could create SQL statements here that could delete and update data in the database. For instructions on how to turn on and off Safe Mode see the Reportico Security section.

Functions may be entered into the custom source code. Includes Your custom code may be included using an require or include statement instead of being entered into this box. This is convenient as the code can be edited in a more convenient text editor. Also this aids debugging by allowing you to rerun your report while correcting errors in the custom source code without having to enter Design mode each time. Source is included relative to the top level Reportico directory. It is recommended you create your source file within your report project directory and name it with the same name as the report xml file but with the .php extension. To include your file just enter into the Custom Source box :-

      include("projects/<myproject>/<myreport>.php");

As this code is executed before the main report query, any output statements will appear at the top of the report out. Using echo or print statements here can be useful for debugging purposes.

Any errors or warnings you may wish to raise during the course of this code running can be raised to the user using the trigger_error function which will inform the user of your own error information after the report is run. The format is as follows :-

      trigger_error("<Error Message Test>", E_USER_ERROR);
for example :-
      trigger_error("Error ".$_pdo->errorCode()." occurred in SQL statement.", E_USER_ERROR);

Since the code entered here is generally intended to perform database queries , the following variables are available $_pdo

A connection to your database. This is used to execute SQLs, to create and populate temporary tables and to perform other preparatory work like filtering, reformatting etc. Can also be used to perform database maintenance operations such as deletes and updates. This is a PDO type connection and so can be used as documented in the PHP PDO documentation at http://php.net/manual/en/book.pdo.php.

For example, this value creates a temporary table containing month numbers and names. This could then be joined in with the main report query to generate reports with month names based on a month number from another table.

    $sql ="CREATE TEMPORARY TABLE t_month ( monthno INT, monthname CHAR(12) ) ";
    $stat = $_pdo->query($sql); 
    if ( !$stat ) 
    {
        trigger_error("Query Failed<BR>".$sql."<br>" . 
              "Error ".$_pdo->errorCode(). " - ". 
              $_pdo->errorInfo(), E_USER_ERROR); 
              return; 
    } 

    $sql = "INSERT INTO t_month VALUES (1, 'January')"; 
    $stat = $_pdo->query($sql); 

    $sql = "INSERT INTO t_month VALUES (1, 'February')"; 
    $stat = $_pdo->query($sql);

    $sql = "INSERT INTO t_month VALUES (1, 'March')"; 
    $stat = $_pdo->query($sql); 

    /*.............. And so on for each month ..........*/

    // Also cursors may be used for example :-
    foreach($_pdo->query('SELECT * from t_month') as $row) 
    {
       print_r($row);
    }

$_criteria

This array variable contains an entry for each criteria item on the prepare screen and gives access to the relevant selected criteria values. This allows query statements to be executed in the custom code that incorporate the user's selected criteria. This is advantageous in several ways. For example, reports that use queries selecting from many large tables will be faster if some of the data meeting the entered criteria is first selected into temporary tables which can then be linked into the main query at the end. Also data can be selected into temporary tables and further filtered or modified before the final report query runs.

Accessing criteria values as selected on the prepare screen involves calling the get_criteria_clause() function for the relevant criteria item passing the type of value you wish returned from the criteria which depends on whether the criteria item is a list, lookup or date criteria type. The valid passed argument can be one of “VALUE”, “RANGE1” or “RANGE2”.

For LOOKUP criteria types, the parameter of “VALUE” should be passed to get the user's selection. For example, with a LOOKUP criteria that is named country that presents the user with a set of countries selected from the database, you may fetch the selected countries with something like this:-

  $countries = $_criteria["country"]->get_criteria_value("VALUE");

This returns a comma delimited string containing the selected country values that is suitable for use in an SQL IN clause to help you use it in further queries within the custom code. The values returned relate to the criteria's Return Column attribute.

For List criteria items, again the parameter of “VALUE” should be passed to get the user's selection as a comma-separated list.

For Text Field criteria, pass the “VALUE” parameter to get what the user has typed in to the criteria text field.

For Date type criteria, pass the “VALUE” parameter to fetch the chosen date. This will be returned in the format as defined in the configuration parameter SW_DB_DATEFORMAT. This should be suitable for including in SQL data comparison clauses.

For Date Range Criteria, pass the “RANGE1” parameter to get the lower date range value and pass “RANGE2” to get the upper date range parameter.

The get_criteria_value() function by default returns criteria values which are surrounded by single quotes. This is so that they can be incorporated easily into SQL text e.g. 'January','February','March'. You can return the values without the single quotes by passing false as a second parameter.

  • custom_source_code.1426441039.txt.gz
  • Last modified: 2018/05/04 21:39
  • (external edit)