Learn to configure the <canned_query>
element for Pull Reports™ Ad Hoc
report and data service software.
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.
The following example uses the real estate data model from the <relationship>
documentation to demonstrate two <canned_query>
configurations.
<?xml version="1.0" encoding="UTF-8"?> <catalog xmlns="http://www.pullreports.com/catalog-1.7.0" id="realestate" name="Realestate Reports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0 https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.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 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>
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);
}
}
<filter>
configurationCustomize the <filter>
element as a
Freemarker template.
Additionally, use methods provided by
RequestMethodModels
to access methods from the
HttpServletRequest
associated with the
Report Information
request.
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.7.0" id="hr" name="Human Resources" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0 https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.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 = '${getRemoteUser()}'</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>
<description> ? |
<access_control_voter> ? |
<columns> ? |
<filter> * |
<sort> ? |
<canned_queries> |
The value of the distinct
parameter value for the canned query, either false
or true
.
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.
The name of the canned query. The name is reflected within the Query Viewer header.
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.