WYSIWYG Reporting

Including

Introduction

Forena is built of the idea of using SQL to get data out of a database and use XHTML and CSS to format it into web reports. It is designed to leverage existing knowledge of HTML, CSS, SQL and JavaScript to help you create rich interactive web reports.

A What You See Is What You Get (WYSIWYG) compatible report editor is provided to make the creation of a basic reports very easy. You don't have to know XHTML and CSS to create a simple report, but the more you now about HTML and CSS, the more you will be able to achieve.

The main differences with other open source reporting solutions such as JasperReports ® or mondrian are:

  1. Forena leverages HTML and XML in ways that these report engines do not. In Forena everything is XHTML natively, so that is your templating language. This means rather than having users enter parameters, most often they are doing click-through drilling down to their data. Forena produces paper as a secondary thing rather than a primary concern. We are NOT interested in printing most of the time, but rather exploring the data.
  2. Forena lets you consume hierarchical data from non-relational sources such as RSS feeds, etc (anything in XML format) and has special considerations for databases that can generate XML natively (e.g. Postgres or Oracle SQL).
  3. Forena is designed to make generating data sources and reports be report developer friendly. Data queries , often called data blocks, are simple files that can be version controlled and edited using normal development toos. They are often parameterized SQL files, so they don't take much to use. Time from a good select statement to a report is typically 5 minutes or less.
  4. Forena doesn't require a Java stack! So you can install it on a hosting service, etc.

Novice users may want to start by creating reports using the built-in WYSIWYG report editor. Users with knowledge of XHTML, CSS and XML may choose to edit the FRX report definition files directly. The FRX Reporting Guide is available for those who are interested in doing so.

Creating a Report

To create a new report using the WYSIWYG report editor, use either of these methods to do so:

  • Use the menu option Create Report, which is typically located in the navigation menu block, provided it is enabled in your site.
  • Visit the admin/structure/forena page and select the Create New Report link on that page.

Specify the Title and Name

The Title field indicates the title of the report as it will appear for the user. In this example we'll use "Test Simple Report" as the title of our report.

The Report Name is a unique name that is used to save the report definition. In this example we will use test/simple as the Report Name, which implies that our report will be stored in directory test, and that the filename of our report will be simple.frx The .frx extension is automatically added to the filename.

For this simple report, you can leave the remaining fields blank, and hit the CREATE button. After doing so, you'll be taken to the Report Editing screen, which has an URL like /reports/test.simple/edit. Notice the warning message at the top of this edit screen, which states "All changes are stored temporarily. Click Save to make your changes permanent. Click Cancel to discard your changes.".

Save the Report

Saving the report will store all details in the .frx file we specified and display an informational message at the top of the screen, which states "Your report, test/simple has been saved.". Voilà!

Note that your test/simple report does NOT show up in the list of reports shown via menu item My Reports. That's because we have not specified a category yet for our report, something we'll be doing via the General Tab later on.

Retrieving your report

To retrieve your report later on, go to URL /reports/test.simple. If you want to change the report definition click on the tiny edit icon.

Data Blocks

Data blocks correspond to a specific query to a database or application. These data blocks are typically created by a site administrator or developer.

Adding a first Data Block to your report

Data blocks can be added to a report via the Report Editing screen. To add such a data block to our test/simple report, click on the tiny icon with a green plus sign labeled "Insert Data" which can be found on the Report Editing screen. After doing so, you will be taken to the Select Data Block screen. Select a data block to be used on the report. You may search for the name of any provided data blocks.

For this report, we'll use one of the sample queries, i.e. sampledb/users_by_state. Select this data block by just clicking on it, which will take you to the Preview Data Block screen. If the selected data block was contains parameters you'll be prompted to enter a values. You must be able to generate data previews prior to selecting a data block.

The Preview Data Block screen gives you a preview of the data included in the data block you selected. This preview shows a sample table layout of the data returned by that data block. You may need to specify required parameters in a report in order to generate sample data before your report preview can be created. You cannot use the editor to add data to the report unless you can provide parameters that cause the query to actually return data. Keep pressing Preview until you see the data that you're expecting in the report. Don't worry if the preview shows too many fields, or isn't quite in the right format. You'll be able to control that in the Layout tab later on.

To confirm your data block selection press the Select button, which will take you to the Add Data screen. Using the Add Data screen you can customize the columns of the selected data block (their order, which ones to include/exclude, etc), and optionally enter a header for it. You can also select any of the available templates to decide the basic look and feel of the data block that you are adding to the report. Some of these templates are straight forward, such as the Simple Table display. Here are some of the more sophisticated data block templates to choose:

Note: by using the WYSIWYG editor you don't have to know about the .frx syntax of any of these templates, but the constructed report will still be an .frx file saved on the file system which may be manipulated directly.

Use the Preview button to see the effects of your customizations. Feel free to experiment with these templates to get a report that looks as close as possible to the design you are looking for. Note that you will be able to further customize these layouts later on by using the layout tab on the report editor screen.

When you're finished selecting your customizations on the Add Data screen, use the Insert button to add the selected data block to the report.

Adding additional Data Blocks to your report

The steps required for adding additional Data Blocks to your report are mostly similar to the steps to add a first Data Block to your report using the tiny icon with a green plus sign. If the additional data block is to be inserted before the already existing one, use the icon above the existing data block. If however the additional data block is to be inserted after the already existing one, use the icon below the existing data block.

Editing Data Blocks in your report

Sometimes you may want to edit the presentation of an existing data block in a report, here are a few samples why you'd want to do so:

  • Add some extra columns to the report
  • Remove some of the existing columns in the report
  • Change the order of presentation of the columns

To edit an existing data block in the report, go to the report edit screen and click on the hyperlink related to the data block to be edited. This will take you to the Edit Block screen. The features available on this Edit Block screen are identical to those on the Add Data screen.

Removing Data Blocks from your report

If you want to remove a data block from the report, go to the report edit screen and click on the tiny icon with a red minus sign, and which says "Remove Data" if you move your mouse pointer over it. After doing so, you will be taken to a screen that says something like Are you sure you would like to remove the ... content. Select the Yes button to confirm the removal of the data block from your report or No to cancel your request.

General

Using the General tab on the report editing screen, you can specify various general options of the report. If you're not yet on the report editing screen, you can access it by clicking on the tiny edit icon shown when viewing the report.

Explanation of the available options on this form are as follows:

  • Title - The title of the report, which can be used to modify the original title that was entered when the report was first created.
  • Use the Visibility options to enter these specifications of the report:
    • Category - This controls under which group the report appears under the My Reports list. A report without a category will not show up in it.
    • Hidden - Checking this checkbox will cause the report not to be listed in the My Reports list regardless of Category.
  • Menu - Click on the menu link to expand it so that some additional options related to it become visible. By checking the Provide a menu link checkbox, some additional options will become available to actually specify the various options to create a Drupal menu pointing to the report, i.e.:
    • Menu Path - The site relative path for the menu item. Parameters may be embedded in the url using a :parm syntax (e.g. states/:state).
    • Additional Arguments - Additional parameters that should be extracted after the menu path using a :parm syntax (e.g. :parma/:parmb.)
    • Type of menu to create - Use standard conventions for creating a menu item in the format of normal menu items, tabbed menus, etc.
    Note: changes to values of menu options only become visible after clearing the Drupal cache.
  • Cache - Click on the cache link to expand it so that some additional options related to it become visible. These additional options can be used to enable some options related to the caching of the report, which is a technique to reduce the resources required for rendering the report, in situations such as:
    • reports containing data that don't change a lot (e.g. you know that the nature of the report's data only change on a daily basis).
    • reports that are about some pretty hefty analysis,( e.g. queries that give sums/aggregate for large numbers of rows).
    Available caching options are:
    • Duration - The duration that a cached report remains in the cache. Use relative formats according to the php documentation.
    • Per User - Indicate if you want the cache to be by user.
    • Per Document Type - Indicate if you want the cache to be by Document Type.

Use the Update button to update changes to the report and return to the preview screen. Your changes will not be saved until the Save button is selected.

Document Types

The output of any report is normally an HTML document. It can optionally be exported in several common document formats.

The exact list of document types available for your report is determined by your site administrator and may include:

csv Comma Separated Values.
email eMail message.
html HyperText Markup Language without the drupal theme.
PDF Adobe Portable Document Format
svg Scalable Vector Graphics.
doc MS Word format.
xls MS Excel format.
xml eXtensible Markup Language.

Using the Document Types tab on the report editing screen, you can check one or more of the available options to add a corresponding link to your report. If you're not yet on the report editing screen, you can access it by clicking on the tiny edit icon shown when viewing the report.

Layout

Using the layout tab on the report editing screen, you can specify various layout options of the report.

Explanation of the available options on this form are as follows:

Skin

The skin of a report determines the look and feel of the report and allows you to leverage a common set of CSS and JavaScript libraries to make the most out of your reports. In conventional report writers this can be thought of as what kind of paper form the report printed on. These days most layout is controlled by Cascading Style Sheets (CSS) and JavaScript libraries.

A site administrator may enable additional report skins by creating corresponding .skinfo files. These .skinfo files define which specific CSS files and JavaScript files will get included when that skin is selected in a report.

Refer to the Skins guide for more information on defining custom skins.

Body Here you can edit directly the contents of the report. This can be used when you need more customization than can be provided using the custom templates provided by forena. For more information how to specify a proper body section and the use of the special frx attributes in the html, see the FRX Reporting guide.
CSS Styles

Specify small CSS snippets that can be used anywhere in the report, as in this example:

div.FrxTable { 
              line-height: 2.5; color: #FF6633; 
            }
            

This will change the look-and-feel of the table content a little bit. In this specific case the height of the table rows will be higher then the height for standard table displays, while the color of the text in the table cells is shown in a kind of orange.

Parameters

Parameters can be used to provided inputs to a report. Using the Parameters tab on the report editing screen, you can enter new report parameters, and/or edit or delete existing parameters.

To add a new parameter, click on the General tab, and then click on the link that says Add Parameter. After doing so, you will be taken to the Parameters screen. Available options are:

label The label displayed to the user when prompted in the parameters form.
id The unique id for the report parameter. Generally these must match exactly the parameters expected by the data values.

Enter an appropriate value for each of these fields, and confirm them by pressing the Add button, which will take you to the next Edit screen.

On the Parameters screen, click the tiny gear icon next labeled 'Configure Parameter'. This will allow you to further specify the following:

label The label displayed to the user when prompted in the parameters form.
require Indicate of a value for this parameter is required to display the report. If there is not a default value, the user will be prompted to enter a value.
default value Value to be used when none is specified.
description Tell the users about what the parameter is used for as well as proper formats for entry.
Input Control Type Indicates the type of control or widget used prompt for user input.
Source Data Used in conjunction with the control field to determine which values will be made available to radio buttons, check-boxes or select lists. The value should be a data block that users of this report will have access to. Blocks should be written to return key value pairs for use in these controls.
Data Field The name of the column from the data block specified in Data Source that is used to populate the return values of the select or radio buttons. If omitted, then the 1st column of the data block is used as the Data Field.
Label Field The name of the column from the data block specified in Source Data that is used to populate the labels of select or radio buttons. If omitted, then the 2nd column of the data block is used as the Label Field, provided such 2nd column exists. If the query returns only one column the Data Field column is used.
Options Used to configure the Input Control Type.
Class Specify the html class to be applied to this parameter.

Fields

Fields in the report are the tokens which are used to substitute data into the report. These report fields are found between the curly braces, and exists in multiple flavors:

  • Data fields as contained in the report's data blocks
  • The fields defined on the spot while editing the report, and this by just typing the variable name (= token) of the field, surrounded by curly braces, anywhere within the body part of the report.
  • The fields corresponding to the arbitrary variables defined in a report skin file.

Select on the gear symbol next to the first use of the particular report field to configure the following properties:

  • The formatting of the field's output.
  • The field's links to be created.
  • The field's default value to be displayed if no value is present.

The options available to configure these properties are further detailed below.

Field output formatting

Fields can be formatted a number of different ways depending on the format and format-string specified for each field. The following table illustrates the supported options:

Format Description Format String
drupal_date_format Formats a Drupal date. Drupal dates are natively large numbers that are expressed as the number of seconds since the UNIX epoch date. Use small, medium or large to specify any of the site defined dates, or alternatively specify a custom date format. See http://php.net/manual/en/function.date.php for possibilities for custom formatting.
drupal_node_content Loads content from the given nid and display it using teaser or full display. Specify teaser to get teaser view.
drupal_translation Use Drupal's translation API to translate the value prior to display. Specify a field containing the serialized data used for translations (e.g. watchdog table). Normally you can leave this blank.
iso_date ISO standard dates are of the form, YYYY-MM-DD followed by a 24 hour timestamp (e.g. 2012-12-01 20:30:30). Dates in this format may be reformatted. Use small, medium or large to specify any of the site defined date formats, or alternatively specify a custom date format. See http://php.net/manual/en/function.date.php for possibilities for custom formatting.
number Use the PHP number formatter function. Indicate a sample numeric format for decimal places and thousands separator. (eg. 9.999.00).
sprintf Use PHP's sprintf function for adding labels and such. See http://us.php.net/manual/en/function.sprintf.php for possibilities.
template The field is expected to contain a template containing further tokens for expansion. This is useful for form letter data that may be stored in the database.

Field linking

Fields can be transformed into hyperlinks also. The following table illustrates the supported options:

Link Specify a site relative URL. When specified, all references to this field in the report will be displayed as a links to the URL specified.
Pass through URL parameters Automatically pass through URL parameters on this link.
rel Relationship attribute to apply to the link.
class CSS class applied to be applied to the link.
Target Specify a target of the page you want the window to open in. BLANK can be used to always open a page in a new window. If you specify a target beginning with the word "popup", Forena will automatically open up the window in a popup.

Field default value

Default Value The value to use when no value is present in the data.