<canned_query>

Abstract

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


Catalog Configuration Java API analog:  CannedQueryConfigurationBuilder

A <canned_query> is a collection of Export Report REST API parameters with a name and optional description. Use a <canned_query> to provide one click access to report exports via the Canned 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.0" id="realestate" name="Realestate Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.0.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 home owners 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 home owners 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 java.util.Arrays;
import java.util.List;

import com.pullreports.examples.relationship.RelationshipCatalogConfigurationFactory;
import com.pullreports.export.request.ColumnsTerm;
import com.pullreports.export.request.SortTermTemplate;
import com.pullreports.model.CannedQueriesProvider;
import com.pullreports.model.QueryId;
import com.pullreports.model.config.CannedQueryConfiguration;
import com.pullreports.model.config.CannedQueryConfiguration.CannedQueryConfigurationBuilder;
import com.pullreports.model.config.DefaultCannedQueriesProvider;
import com.pullreports.model.exportconfig.ExportConfiguration.ExportConfigurationBuilder;

public class CannedQueryCatalogConfigurationFactory extends RelationshipCatalogConfigurationFactory {
    
    @Override
    protected ExportConfigurationBuilder makeExportConfigurationBuilder() {
        CannedQueriesProvider cannedQueriesProvider = 
            new DefaultCannedQueriesProvider(makeCannedQueryConfigurations());
        return super.makeExportConfigurationBuilder().setCannedQueriesProvider(cannedQueriesProvider);
    }
    
    private List<CannedQueryConfiguration> makeCannedQueryConfigurations(){
        CannedQueryConfiguration homeOwnerConfig = 
            new CannedQueryConfigurationBuilder(
                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[]{
                ColumnsTerm.fromString("/home/owner@last_name,first_name")
                ,ColumnsTerm.fromString("/home/sale@amount")
                ,ColumnsTerm.fromString("/home/address@street,city,state,zipcode")
                ,ColumnsTerm.fromString("/home@id")
            }))
            .setSortTermTemplates(Arrays.asList(new SortTermTemplate[]{
                SortTermTemplate.fromString("/home/owner@last_name")
                ,SortTermTemplate.fromString("/home/sale@amount")
            }))
            .setFilterTemplates(Arrays.asList(new String[]{
                "/home/address@state = 'California'"
            })).build();

        CannedQueryConfiguration realtorConfig = 
            new CannedQueryConfigurationBuilder(
                new QueryId("realtors"),"Realtor Report")
            .setDescription( "A report of home owners and their realtors.")
            .setColumnsTerms(Arrays.asList(new ColumnsTerm[]{
                ColumnsTerm.fromString("/home/owner@last_name,first_name")
                ,ColumnsTerm.fromString("/home/owner/realtor@realtor_name")
                ,ColumnsTerm.fromString("/home@id")
            }))
            .setSortTermTemplates(Arrays.asList(new SortTermTemplate[]{
                SortTermTemplate.fromString("/home/owner@last_name")
                ,SortTermTemplate.fromString("/home/owner/realtor@realtor_name")
            }))
            .build();

        return Arrays.asList(new CannedQueryConfiguration[]{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.0" id="hr" name="Human Resources"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.0.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 distict 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 Canned Query Viewer.

name

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

viewMode (Default: table)

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