<canned_query>

Abstract

Learn to configure the <canned_query> element for Pull Reports™ Ad Hoc report and data service software.

Table of Contents

Catalog Configuration Java API analog: CannedQueryConfiguration.Builder

A <canned_query> is a collection of Export Report REST API parameters with a name and optional description. Use <canned_query> to provide one click access to report exports via the Query Viewer.

Canned query configuration is available via the Report Information API.

Usage

The following example uses the real estate data model from the <relationship> documentation to demonstrate two <canned_query> configurations.

Example 1. Via an XML Catalog file

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="realestate" name="Realestate Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="home" name="Home Report">
        <export_config>
            <canned_queries>
                <canned_query name="Home Owner Report by State" id="home_owner_by_state">
                    <description>A report of homeowners within a state with the
                        addresses of their properties ordered by owner and sale price.</description>
                    <columns>
                        /home/owner@last_name,first_name
                        ;/home/sale@amount
                        ;/home/address@street,city,state,zipcode
                        ;/home@id
                    </columns>
                    <sort>/home/owner@last_name;/home/sale@amount</sort>
                    <filter>/home/address@state = 'California'</filter>
                </canned_query>
                <canned_query name="Realtor Report" id="realtors">
                    <description>A report of homeowners and their realtors.</description>
                    <columns>
                        /home/owner@last_name,first_name
                        ;/home/owner/realtor@realtor_name
                        ;/home@id
                    </columns>
                    <sort>/home/owner@last_name;/home/owner/realtor@realtor_name</sort>
                </canned_query>
            </canned_queries>
        </export_config>
        <table id="home" name="realestate.home" displayName="Home"> 
            <column id="id" name="id" paramType="java.lang.Integer"/>
            <relationship>
                <join_column columnName="home_id" referencedColumnName="id" />
                <table id="sale" displayName="Sale" name="realestate.sale">
                    <column id="sale_id" name="id" paramType="java.lang.Integer"/>
                    <column id="home_id" name="home_id" paramType="java.lang.Integer"/>
                    <column id="amount" name="amount" paramType="java.lang.Double"/>
                </table>
            </relationship>
            <relationship join="inner" cardinality="one">
                <join_column columnName="address_id" referencedColumnName="id" />
                <table id="address" displayName="Address" name="realestate.address">
                    <column id="aid" name="id" paramType="java.lang.Integer"/>
                    <column id="street" name="street" displayName="Street"/> 
                    <column id="city" name="city" displayName="City" /> 
                    <column id="state" name="state" displayName="State"/> 
                    <column id="zipcode" name="zipcode" displayName="Zip Code"/> 
                </table>
            </relationship>
            <relationship>
                <join_table name="realestate.home_owner">
                    <join_columns>
                        <join_column columnName="home_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="owner_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="owner" displayName="Owner" name="realestate.owner">
                    <column id="oid" name="id" paramType="java.lang.Integer"/>
                    <column id="first_name" name="first_name"/> 
                    <column id="last_name" name="last_name"/> 
                    <relationship cardinality="one">
                        <join_column columnName="first_name" referencedColumnName="owner_first_name" />
                        <join_column columnName="last_name" referencedColumnName="owner_last_name" />
                        <table id="realtor" displayName="Realtor" name="realestate.owner_realtor">
                            <column id="realtor_name" name="realtor_name" displayName="Realtor"/>
                        </table>
                    </relationship>
                    <relationship>
                        <join_column columnName="owner_id" referencedColumnName="id" />
                        <join_column columnName="home_id" referencedColumnName="id" referencedTablePath="/home"/>
                        <table id="expense" displayName="Owner Expense" name="realestate.owner_expense">
                            <column id="amount" name="amount" paramType="java.lang.Double"/>
                            <column id="reason" name="reason" />
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

Example 2. Via the Catalog Configuration Java API

The following example is an identical Catalog Configuration Java API configuration. This Java class extends the CatalogConfigurationFactory from the <relationship> documentation and inherits the parent class's report configuration.

package com.pullreports.examples.cannedquery;

import com.pullreports.examples.relationship.RelationshipCatalogConfigurationFactory;
import com.pullreports.model.CannedQueriesProvider;
import com.pullreports.model.QueryId;
import com.pullreports.model.config.CannedQueryConfiguration;
import com.pullreports.model.config.ColumnsTerm;
import com.pullreports.model.config.DefaultCannedQueriesProvider;
import com.pullreports.model.config.SortTerm;
import com.pullreports.model.exportconfig.ExportConfiguration;

import java.util.Arrays;
import java.util.Collections;
import java.util.List;

public class CannedQueryCatalogConfigurationFactory extends RelationshipCatalogConfigurationFactory {

    @Override
    protected ExportConfiguration.Builder makeExportConfigurationBuilder() {
        CannedQueriesProvider cannedQueriesProvider = 
            new DefaultCannedQueriesProvider(makeCannedQueryConfigurations());
        return super.makeExportConfigurationBuilder().setCannedQueriesProvider(cannedQueriesProvider);
    }
    
    private List<CannedQueryConfiguration> makeCannedQueryConfigurations(){
        CannedQueryConfiguration homeOwnerConfig = 
            new CannedQueryConfiguration.Builder(
                new QueryId("home_owner_by_state"),"Home Owner Report by State")
            .setDescription("A report of home owners within a state with the " +
                "addresses of their properties ordered by owner and sale price.")
            .setColumnsTerms(Arrays.asList(
                new ColumnsTerm("/home/owner","last_name","first_name")
                ,new ColumnsTerm("/home/sale","amount")
                ,new ColumnsTerm("/home/address","street","city","state","zipcode")
                ,new ColumnsTerm("/home","id")
            ))
            .setSortTerms(Arrays.asList(
                new SortTerm("/home/owner","last_name")
                ,new SortTerm("/home/sale","amount")
            ))
            .setFilterTemplates(Collections.singletonList(
                "/home/address@state = 'California'"
            )).build();

        CannedQueryConfiguration realtorConfig = 
            new CannedQueryConfiguration.Builder(
                new QueryId("realtors"),"Realtor Report")
            .setDescription( "A report of home owners and their realtors.")
            .setColumnsTerms(Arrays.asList(
                new ColumnsTerm("/home/owner","last_name","first_name")
                ,new ColumnsTerm("/home/owner/realtor","realtor_name")
                ,new ColumnsTerm("/home","id")
            ))
            .setSortTerms(Arrays.asList(
                new SortTerm("/home/owner","last_name")
                ,new SortTerm("/home/owner/realtor","realtor_name")
            ))
            .build();

        return Arrays.asList(homeOwnerConfig,realtorConfig); 
    }
}

Customizing `<filter>` configuration

The <filter> element may contain any construct supported by Groovy's SimpleTemplateEngine. For instance, expressions embedded within the <filter> may use Groovy or Java operators and invoke static method calls such as java.lang.System.getProperty(String). Additionally, the HttpServletRequest associated with the Report Information request is available as a the request template binding variable. This allows the <filter> value to be responsive to HttpServletRequest state.

In the following simple example, the value of the <filter> is set to the remote username associated with the HttpServletRequest. This permits the "Employees by Manager" canned query to return records from the employee table whose manager_username column references the username of the user viewing the query.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="hr" name="Human Resources"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="employee" name="Employee Report">
        <export_config>
            <canned_queries>
                <canned_query name="Employees by Manager" id="employee_by_manager">
                    <description>A report of employees for a given manager.</description>
                    <columns>/employee@last_name,first_name,salary</columns>
                    <sort>/employee@last_name;/employee@first_name</sort>
                    <filter>/employee@manager_username = '${request.remoteUser}'</filter>
                </canned_query>
            </canned_queries>
        </export_config>
        <table id="employee" name="employee" displayName="Employee"> 
            <column id="id" name="id" displayName="Employee ID" paramType="java.lang.Integer"/>
            <column id="last_name" name="last_name" displayName="Last Name" />
            <column id="first_name" name="first_name" displayName="First Name" />
            <column id="salary" name="salary" displayName="Salary" paramType="java.lang.Double"/>
            <column id="manager" name="manager_username" displayName="Manager Username" >
                <label_value_list>
                    <label_value_query_default/>
                </label_value_list>
            </column>
        </table>
    </report>
</catalog>

Children

<description>?

<access_control_voter>?

<columns>?

<filter>*

<sort>?

Parents

<canned_queries>

Attributes

distinct (Default: false)

The value of the distinct parameter value for the canned query, either false or true.

id

Specifies the unique id of the <canned_query> within the <report>. Must be composed of alphabetical characters, numbers, or the hyphen or underscore characters.

The id attribute is used as a parameter when creating a hyperlink directly to the Query Viewer.

name

The name of the canned query. The name is reflected within the Query Viewer header.

viewMode (Default: table)

The default view mode, table or map, to use when displaying the canned query within the Query Viewer. The map value is only applicable for <report>s which support the geojson export format.

Prev		Next
<access_control_voter>	Home	<canned_queries>