Abiquo Documentation Cookies Policy

Our Documentation website uses cookies to improve your experience. Please visit our Cookie Policy page for more information about cookies and how we use them.

Abiquo 5.0

Skip to end of metadata
Go to start of metadata


This section describes how to create reports that query Abiquo data using the JasperReports Server application. It explains how to create and call your own custom report stored procedures from your own custom reports.

Note that this section assumes that the user is familiar with creating reports for JasperReports Server - this section does not attempt to describe how to create report presentation layers (a good starting point for such information is this: http://jasperforge.org/projects/ireport).

How JasperReports Server is configured to use Abiquo RLS

When creating a JasperReports Server reports, we have seen that the report logic is encapsulated in a report stored procedure, and that the report procedure must be supplied the name of the Abiquo user the report is intended for. Therefore, a key part of the report definition in the report application is to identify the Abiquo user, and then supply it to the report stored procedure.

Identifying the Abiquo User

Since Abiquo supply an authentication plugin to allow Jaspersoft Server to perform its user authentication against an Abiquo Enterprise server, identification of the Abiquo user running the report is straightforward - because the JasperReports Server user name will be the Abiquo user who logged in to the JasperReports Server.

Specifying Report procedure parameters

JasperReports Server provides access to details of the user who is running the report, and we can use that user information to create a report parameter containing the name of the Abiquo user - we then simply use that parameter during the call to the report stored procedure.

Note that all parameters required by a report stored procedure should have an equivalent JasperReports Parameter defined for it - this applies to all report procedure parameters including the name of the Abiquo user used to initialise RLS.

Step 1 - Create a custom reports schema

When creating your very first custom report, ensure that you create it in your own 'custom_reports' schema, and not in the 'kinton_reports' schema. By placing them in your own schema you are ensuring that they will not be removed by future upgrades of Abiquo software.

_Note that 'custom_reports' is just a recommended name for your schema and you can choose a more suitable name yourself. _

Step 2 - Copy the report template Stored Procedure

The 'kinton_reports' schema (which contains all of the procedures for the standard Abiquo reports) also contains a template report procedure called 'RPT_Template_Level_v1_0'. It is recommended that you make a copy of this stored procedure in your custom reports schema, and use it as the starting point for your own custom report procedures, since it contains the necessary parameters and RLS initialisation and cleanup to get you started.

When creating your own report procedure, it is simply a question of adding a query in the 'TODO' section of the report template.
Please refer to the 'kinton_reports.RPT_EnterpriseLimits_VDC_v1_0' for an example of a simple report query.

Additionally, if your report needs to supply parameters to the stored procedure (for example to specify drilldown details) then these should be added to the procedure parameters.
Once your reports stored procedure is complete, it is recommended that you manually test it directly in MySQL using CALL statements, and review the result set to ensure it contains the correct data that you need.

Step 3 - Create a separate JasperReports Server report folder

The standard Abiquo reports are all contained within a 'reports' folder on the JasperReports Server. It is strongly recommended that you create and store your own reports in a separate folder (e.g. 'custom_reports'), as the 'reports' folder (and all of its content) may be deleted when the standard reports are upgraded in future Abiquo versions.

Step 4 - Create your Report with iReport (or Eclipse)

When defining the database SQL to identify the report's result set, instead of supplying a SELECT query to obtain the report data, you call your report stored procedure instead. This can be done by supplying a CALL SQL statement instead of a SELECT statement, e.g.

CALL kinton_reports.RPT_EnterpriseLimits_Ent_v1_0('admin');

Note that when you first create your report, you will have to manually supply the procedure parameters - the parameters are automated in the next steps.

Step 5 - Prepare RLS details within your report

When you have created your report, you will need to ensure that the report data returned is in the context of the Abiquo user it is being run by.

This can be done using the following steps:
#Create a Jaspersoft report parameter called 'LoggedInUser'. Ensure that:

    1. 'Parameter Class' of the variable is set to: 'com.jaspersoft.jasperserver.api.metadata.user.domain.User'
    2. 'Use as a prompt' is FALSE.
    3. 'DefaultValue Expression' is empty.
  1. Create a Jaspersoft report parameter called 'LoggedInUserName'. Ensure that:
    1. 'Parameter Class' of the variable is set to: 'java.lang.String'
    2. 'Use as a prompt' is TRUE.
    3. 'DefaultValue Expression' is '$P{LoggedInUser}.getUsername()'.
Step 6 - Using RLS in the call your report Stored Procedure

The final mandatory step is to adjust the report's call to your report stored procedure. This is done by replacing the hard coded parameters from step 4 with appropriate parameters from your report. At the very least you need to supply the 'LoggedInUserName' from step 5, e.g.

CALL kinton_reports.RPT_EnterpriseLimits_Ent_v1_0( $P{LoggedInUserName} );

This will ensure that your report always initialises RLS for the appropriate Abiquo user.

Step 6 (optional) - JasperReports Server Input Control support

If your JasperReports Server report needs to run a query to obtain data for a report Input Control, you can create stored procedures for each Input Control and call them from the control's SQL, using the same technique as the reports use to call the report stored procedures. Note that the Input Control stored procedures can also use the '$P{LoggedInUserName}' report parameter to initialise RLS.

Reference Input Control procedures can be found in the 'kinton_reports' schema, the procedure names all begin with a 'LST' prefix (e.g. 'LST_Enterprises_v1_0')._