Chapter 6. Ad hoc report creator

The Pull Reports™ Ad Hoc Report Creator is a JavaScript and HTML embeddable client that allows a user to create queries into the Export Report REST API via an easy-to-understand user interface. The Ad Hoc Report Creator also displays report and table meta data, shows the report's relational graph, and allows a user to preview the first few rows of the report.

This chapter explains how to install and use the creator.

6.1. Installation

In order to embed the Pull Reports™ Ad Hoc Report Creator into an HTML page, follow these steps:

6.1.1. Basic HTML template

Follow this HTML template to include the required <meta>, <link>, and <script> tags within the <head> element of the web page. The referenced CSS and JavaScript files are included within the Pull Reports™ JAR file and automatically available from context path of the WAR serving Pull Reports™.

<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="/[context]/assets/pullreports.min.css">
    <script src="/[context]/assets/pullreports.min.js"></script>
</head>
<body>
...
</body>
</html>

Important note for Grails 3

Grails 3 serves static content from JAR META-INF/resources directories beneath the /static URL path. For this reason, prefix all paths to Pull Reports.css and .js files with /static.

<link rel="stylesheet" href="/[context]/static/assets/pullreports.min.css">
<script src="/[context]/static/assets/pullreports.min.js"></script>

Using non-minified CSS and JavaScript

To use non-minified CSS or JavaScript files for easier debugging, reference the non-minified versions like so:

<link rel="stylesheet" href="/[context]/assets/pullreports.css">
<script src="/[context]/assets/pullreports.js"></script>

6.1.1.1. Potential CSS or JavaScript conflicts

The Pull Reports™ Ad Hoc Report Creator CSS and JavaScript files include many additional library dependencies that may conflict with other CSS and JavaScript dependencies within the embedded web page. The dependencies are listed here to help diagnose problems related to CSS or JavaScript collisions. Because of such conflicts, it is recommended to embed the Ad Hoc Report Creator into a web page with no or minimal additional CSS and JavaScript dependencies.

Pull Reports™ Ad Hoc Report Creator CSS and JavaScript dependencies

6.1.1.2. Potential anchor (#) and query string (?) conflicts

The Pull Reports™ Ad Hoc Report Creator tracks the state of the user's interactions via the web page URL's anchor component (after the # sign) and query string (after the ? sign). Thus, it is required that the web page into which the Ad Hoc Report Creator is embedded must not depend on neither the anchor nor query string.

For example, the following URLs are acceptable:

/some/url/which/embeds/pull/reports.html
/another/url

However, the following URL is problematic because the creator will overwrite the anchor and query string.

/some/bad/url.html?b=a#d

Note

See the parameter API section for more information on how the creator uses the URL's query string.

6.1.2. Create the containing DOM element

Create an empty HTML block element (e.g. div or section) into which the Pull Reports™ Ad Hoc Report Creator will install. This element should be attributed with a unique id attribute and must contain the required data-pr-creator attribute. The element is typically located within the main content section of the web page. For the best experience when using the Ad Hoc Report Creator, the element should span 100% of the screen width.

<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="/[context]/assets/pullreports.min.css">
    <script src="/[context]/assets/pullreports.min.js"></script>
</head>
<body>
    <header>...</header>
    <section data-pr-creator id='pull-reports-container'></section>
    <footer>...</footer>
</body>
</html>

6.1.3. JavaScript initialization

To initialize the Pull Reports™ Ad Hoc Report Creator, use the following template code to call the prMain.init method. This code uses RequireJS to ensure that the appropriate Pull Reports™ dependencies are loaded and angular.element(document).ready to ensure that the containing DOM element is available. Then, it initializes the Ad Hoc Report Creator via the prMain.init method.

<!doctype html>
<html lang="en">
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="/[context]/assets/pullreports.min.css">
    <script src="/[context]/assets/pullreports.min.js"></script>
    <script>
        require(['pr-require-config'],function(){
            require(['angular','pr-main'], function(angular,prMain) {
                    angular.element(document).ready(function() {
                        prMain.init({
                            container:'pull-reports-container'
                            ,url:'http://localhost:8080/[context]'
                        });
                    });
                }
            );
        });
    </script>
</head>
<body>
    <header>...</header>
    <section data-pr-creator id='pull-reports-container' ></section>
    <footer>...</footer>
</body>
</html>

6.1.3.1. prMain.init options

The init method takes a single argument, a JavaScript Object whose property values initialize the Pull Reports™ Ad Hoc Report Creator and returns the creator's AngularJS module object.

The module object may be used for further customization such as setting global HTTP headers

var module = prMain.init({
    ...
});

module.run(['$http',function($http) {
    $http.defaults.headers.common.Authorization = '...';
}]);
Required options
container

The id of the empty DOM element into which to embed the Ad Hoc Report Creator. The element is typically is a <div> or <section> element. The element must have the data-pr-creator attribute.

url

The URL to the WAR into which Pull Reportsis installed. The Ad Hoc Report Creator will append /pullreports/... to this URL to invoke the Pull Reports REST API.

Since the creator will initiate AJAX requests to this url, ensure that the creator is embedded in a web page served from the same domain as the Pull Reports™ WAR or access to the Pull Reports™ WAR is permitted via appropriate Access-Control-Allow-Origin headers.

Restricting the Pull Reports™ Ad Hoc Report Creator to a <catalog>

By default, the Ad Hoc Report Creator's Switch report menu displays an option list of all reports available from the Pull Reports™ installation identified by the url option. Use the following options to restrict the Switch report menu to a list the reports within a single catalog.

catalogId

The id of the <catalog> to which to restrict the available <report> options within the Switch report menu. Use this option to restrict user navigation to reports within one catalog.

Map options

For those Pull Reports™ which declare a <geojson> element, the map option allows control over the behavior of the embedded Leaflet map on the results preview panel.

map

An object with the following optional properties.

initCallback

A JavaScript function to be invoked after map initialization. The arguments to the function are:

  • map: The Leaflet map.

  • geoJsonLayer: The Leaflet markercluster layer which contains the GeoJSON Layer which in turn contains the GeoJSON result of the Export REST API, geojson format.

geoJsonOptions

A JavaScript object to be passed to the Leaflet GeoJSON layer upon construction. See the Leaflet GeoJSON documentation for a complete list of configuration options.

If not specified, Pull Reports™ will set the following default GeoJSON layer option values:

  • onEachFeature: Set to a function which binds a Leaflet popup to the layer for each feature. The content of the popup is a two column table of GeoJSON feature property names to values. The property names and values are the exported <column>s of the report's base table.

options

A JavaScript object to be passed to the Leaflet map upon construction. See the Leaflet map options documentation for a complete list of configuration options.

If not specified, Pull Reports™ will set the following default map option values:

  • center: Set to the geographic center of the lower 48 United States.

  • layers: Set to an array with a single Open Street Map base layer.

  • zoom: Set to 4

Example 6.1. Example initialization using the Leaflet map options

In this example, two base layers are created, 'MapQuest Open Aerial' and 'Open Street 'Map'. 'Open Street Map' is set as the default base layer via the options.layers property. Additionally, both base layers are added to a Leaflet Layers control as toggleable base layers while the exported GeoJSON layer is labeled 'Student residences' and also made toggleable.

<script>
    require(['pr-require-config'],function(){
        require(['angular','pr-main','leaflet']
            , function(angular,prMain,L) {

                // Create two base layers
                var baseLayers = [];
                baseLayers['MapQuest Open Aerial'] = 
                    L.tileLayer('http://otile{s}.mqcdn.com/tiles/1.0.0/{type}/{z}/{x}/{y}.{ext}', {
                    type: 'sat',
                    ext: 'jpg',
                    attribution: 'Tiles Courtesy of <a href="http://www.mapquest.com/">MapQuest</a> &mdash; Portions Courtesy NASA/JPL-Caltech and U.S. Depart. of Agriculture, Farm Service Agency',
                    subdomains: '1234'
                    ,maxZoom: 18
                });
                baseLayers['Open Street Map'] = 
                    L.tileLayer('http://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
                    attribution: 'Map data © <a href="http://openstreetmap.org">OpenStreetMap</a> contributors'
                    ,maxZoom: 18
                });
    
                angular.element(document).ready(function() {
                    prMain.init({
                        container:"report-container"
                        ,url:'https://www.example.domain.com/'
                        ,map: {
                            options: {
                                // Set the default (lowest) layer to be 'Open Street Map'
                                layers:[baseLayers['Open Street Map']]
                            }
                            ,initCallback:initializeMap
                            ,geoJsonOptions: {
                                // Customize the Leaflet popup.
                                onEachFeature:function(feature,layer){
                                    layer.bindPopup(function(){
                                        return '<h3>' + feature.properties['Some Property'] + '</h3>';
                                    });
                                } 
                                // Customize the GeoJSON style
                                ,style:{"color": "#FA8072",
                                    "weight": 5,
                                    "opacity": 0.8
                                }
                            }
                        }
                    });
                });

                function initializeMap(map,geoJsonLayer){
                    // Initialize a Leaflet layers control with the two base layers plus
                    // the report's geojson layer. The layer's control will allow users to 
                    // toggle the base layers and turn on/off the visibility of the geojson layer. 
                    L.control.layers(baseLayers, {'Student residences':geoJsonLayer}).addTo(map);
                }
            }
        );
    });
</script>

6.2. Parameter API

The Pull Reports™ Ad Hoc Report Creator responds to query string parameters encoded within the browser's location. Use these parameters to construct a URL to load a specific <report> within the creator.

For instance, if the web page into which the Ad Hoc Report Creator is embedded is http://www.mycompany.com/reports/index.html, then a url structured as http://www.mycompany.com/reports/index.html?catalogId=mycatalog&reportId=myreport will load the <report> of id myreport within <catalog> with id mycatalog.

6.2.1. Query string parameters

catalogId

Designates the <catalog> id to load. Ignored if the catalogId init option is specified during Ad Hoc Report Creator initialization.

columns

Designates the tables to be included within the Result Preview panel. The columns parameter value is formatted identically to the Export Report REST API columns parameter.

distinct

Boolean value which sets the "Distinct" checkbox of the Result Preview panel.

filter

Designates the filters to be applied to the results within the Result Preview and Filter Summary panels. The filter parameter value is formatted identically to the Export Report REST API filter parameter.

limit

Designates the result limit of the Result Preview panel. Allowable values are 10 or 100.

offset

Designates the result offset of the Result Preview panel.

reportId

Designates the <report> id to load.

sort

Designates the sort to be applied to the columns of the Result Preview panel. The sort parameter value is formatted identically to the Export Report REST API sort parameter.

6.3. Component overview

Each component of the Pull Reports™ Ad Hoc Report Creator corresponds either to an element or elements of the selected <report> or parameters of the Export Report REST API.

Figure 6.1. The user interface components of the Pull Reports™ Ad Hoc Report Creator
The user interface components of the Pull Reports™ Ad Hoc Report Creator


6.3.1. Header

The header displays the current <report> title and controls to navigate between reports.

A. Report title

From the <report>'s title attribute.

B. Switch report

Allows the user to change the <report> available within the creator. Only displayed if there is more than one <report> available.

C. Full Screen

Toggles the Ad Hoc Report Creator between normal and full screen mode.

Press the Full Screen button or the Escape key to exit full screen mode.

6.3.2. Navigation Tab: Tables

The Tables Tab presents the graph of tables, the data columns available within each table, and controls to add columns to the report export, filter columns, and read data definition metadata.

The Tables Tab focuses to one table within the tree graph at a time. Navigate to a different table from the Related Tables panel or via the Join Path navigation bread crumbs.

A. Table display name

From the <table>'s displayName attribute.

B. Join path breadcrumbs

The table resource path of the current <table>. Click parent path elements to change the Tables Tab focus <table>.

6.3.2.1. Columns Panel

The Columns Panel lists the <column>s of the currently focused <table> and provides controls to add columns to the report, create filters, and view metadata.

A. Column label

Click or drag column labels into the Results Preview to add them to the export results. Clicking a label adds the column as the last column in the Results Preview while dragging allows precise column positioning. The column labels are from the <column>'s displayName attribute.

When the column is included the export results, a check icon is displayed to the right of the column name.

Shift+click a column label to remove that column from the Results Preview.

B. Add filter menu item

Select the Add filter menu item to open the Filters Tab, Create Filter form with the column pre-selected.

C. Metadata menu item

Select the Metadata menu item to view the <column> metadata such as the description, id, and paramType. Clicking the Metadata button again closes the metadata.

D. Add all button

Click the Add all button to add all exportable columns to the end of the export results.

E. Column order toggle

Toggles the column order between alphabetical (the default) and the order in which the <column>s are listed within the Columns Panel.

F. Non-exportable columns

Any columns attributed as export='false' are listed separately. They may not be added to the export results.

6.3.2.3. Description Panel

The Description Panel displays the focused <table>'s description.

A. Table description

The description is taken from the <table>'s <description> element.

6.3.3. Navigation Tab: Search

The Search Tab supports full text searching of <table> and <column> displayName attributes and <description>s. Tables and columns within the search results contain controls to add columns to the export result and create filters.

A. Table search result

Table search results contain the same controls for adding the table's columns to the export result, filtration, and metadata viewing as does the Tables Tab, Related Tables Panel.

B. Column search result

Column search results contain the same controls for adding the column to the export result, filtration, and metadata viewing as does the Tables Tab, Columns Panel. Additionally, the column's parent table name and path display below the column control to provide additional context.

6.3.4. Navigation Tab: Filters

The Filters Tab supports creation and editing of filter parameters to be applied to the exported report.

Normal mode: A. Filter indicator

The Filters Tab reflects the number of user defined filters as a superscript.

Normal mode: B. Add Filter button and Add filter drop zone

Click the Add Filter button to create a new filter to be logically AND'ed together with other filters.

Alternatively, drag a column header from the Results Preview to the Add filter drop zone to create a filter on the <column>.

Normal mode: C. Existing filter

Each filter term's column, operator, and value information display within the Filters Tab with accompanying Edit and Remove buttons. Multiple filter terms within the same filter are logically OR'ed together. Multiple filters are logically AND'ed together.

Click the hyperlinked table name within a filter term to focus the table within the Columns Tab.

Normal mode: D. Add OR term button

Click the Add OR term button to create a new term to be logically OR'ed to the existing terms in the filter.

Normal mode: E. Pre-filter

Any <pre_filter>s applied to the report display as non-editable filters with a lock icon instead of edit controls.

Create/Edit mode: F. Table selection

Drop down list of the <table> elements available for filtration.

Create/Edit mode: G. Column selection

Drop down list of the table's <column> elements available for filtration based on the selected table.

Create/Edit mode: H. Operator selection

Drop down list of the filter operators available based on the selected <column>'s paramType.

Create/Edit mode: I. Value

User supplied value for the filter if applicable for the selected operator.

For <column>s with a child <label_value_list> element, the user will be able to select one or more of the pre-defined values from a select list.

Create/Edit mode: J. Save buttons

Click the Save button to persist the filter term but not apply the filter criteria to the Results Preview. Use the Save button when creating multiple filters to prevent unnecessarily refreshing the Results Preview. Then use the Results Preview Refresh button to refresh the preview after completing all filter configuration.

Click Save & Refresh to both persist the filter and refresh the Results Preview.

6.3.5. Results Preview

The Results Preview displays a limited preview of the export results and contains controls to export the report to any export format. By default, the Results Preview displays the first 10 records within the exported report.

A. Refresh

Refreshes the export results from the database.

The Refresh button turns dark blue to indicate that the current export results are stale due to the creation or editing of a filter or addition of a new column.

B. View mode buttons

Toggles the Results Preview between table and map view. The View mode buttons are only available if the <report> may be exported to the geojson format.

C. Pagination

Allows paging through the available export results. The total number of results displays red if it exceeds the configured export.report.maxResults.

D. Limit

Determines the number of preview results displayed at one time.

E. Distinct checkbox

Toggles the distinct export parameter.

F. SQL

Displays the Structured Query Language (SQL) and query parameters to be used when creating the report export.

The SQL button is visible when the export.sql.active configuration property is true for the report and the current user is permitted to request the GET Export SQL end point.

G. Export format

The export select list displays the available export format parameter values. The Export button exports the full report to a new browser window in the chosen format.

The Export button turns red and becomes disabled if the total number of results exceeds the configured export.report.maxResults.

H. Export link

The export link button reveals the Export Report REST API URL that will be invoked when the Export button is clicked.

I. Table cell header

Table cell headers are above adjacent columns within the same table. Re-position all columns under the header by dragging the table cell header to the left or right. Click the remove button to remove all columns under the header from the export results.

J. Column cell header

Column cell headers are above each column. Re-position a column by dragging the column cell header to the left or right.

K. Sort indicator

Column cell headers display an ascending or descending indicator if the respective sort is applied to the column.

L. Column cell header controls

Click the Column cell header remove button to remove the column from the export results. Click the Column cell header menu button to display additional controls for column sorting, filtering , and positioning.

M. Filter indicator

Column cell headers display a filter icon if the column is used within a filter.

N. Cross join indicator

The cross join indicator displays within a table cell header when the table contributes to a cross join in the report export. Cross joins happen when multiple tables in a to-many join relationship to the base table are included in the report export without a join condition.

Clicking the indicator button opens a dialog which explains the cross join and possible courses of action.

O. Column hyperlinks

<column>s with a child <url_template> element have hyperlinked values.

6.4. Usage notes

6.4.1. Use of the browser's back and forward buttons

Most operations within the Pull Reports™ Ad Hoc Report Creator are responsive to the browser's back and forward buttons. This allows a user to undo/re-do an operation such as adding or removing a filter by simply using the back and forward buttons.

6.4.2. Bookmarking the creator

Since the current state of the Ad Hoc Report Creator is captured within the browser's location, a user may bookmark or email the creator URL to a colleague. When a user returns to the creator, it will read the component state from the browser's location and return the creator to its expected state. An exception to this behavior is if the underlying <report> configuration changes in a way that is incompatible with the saved state. Examples of such incompatible changes are the removal of a <table> or the change of an id attribute value.

6.4.3. Bookmarking the export results

Similar to the creator, the export results are also bookmark-able since the export parameters are encoded within the bookmarked URL. Additionally, each time the export is rerun, the values are fetched again from the database, so the results are always current.

However, also like the creator, the export result bookmarks will potentially break due to incompatible <report> configuration changes.