Jump to content
Changes to the Jaspersoft community edition download ×
  • This documentation is an older version of JasperReports Server User Guide. View the latest documentation.

    This section explains each of the XML elements and attributes in a design file and how they relate to the settings in the Domain Designer. The sample XML code is based on the example Domain created in Example of Creating a Domain, with table names that do not include database schema names. Some elements have been added to show structures that did not appear in the example.

    note-icon-ns_28x28.png.8eed74acacecdb6fc3c8a7c68bfa15df.png

    Because certain XML elements correspond to objects in the Domain design, this section refers to XML elements that contain Domain objects such as sets. This is a short-hand description that means the XML elements contain other XML elements that represent the Domain objects.

    The top-level container elements of a design file are:

    schema (no relationship to the database schema)
    itemGroups
    items
    resources

    The following schema representation shows the top-level structure of the design file:

    schema – The outer-most container element of a design file.

    When exported from the Domain Designer, the schema element includes the xmlns and version attributes.

         The xmlns attribute specifies an XML namespace for all element names.

    This string must be unique to Jaspersoft, but it does not correspond to a valid URL. For more information, see http://www.w3.org/TR/REC-xml-names.

         The version attribute gives the version of the XSD used to create this design file.
         The schemaLocation attribute is often added by XML editors to locate the XSD file.
    itemGroups – Contains all the sets and items within sets in the Domain.

    Along with items, this element corresponds to all the sets and items defined on the Display tab of the Domain Designer. Therefore, itemGroups and items define what users see when they create a report based on this Domain. The sets and items defined under itemGroups and items must be internally consistent with the tables and columns under resources.

    items – Contains all the items that are not within sets.

    These correspond to the items at the root level of the Display tab. When all items are contained in sets, this element is absent.

    resources – Contains all the definitions in the design:
         columns
         tables
         derived tables
         joins
         calculated fields
         filters

    These definitions in the design correspond to the first five tabs of the Domain Designer:

    Domain Designer Tabs

    js-DomainDesigner-tabs_510x47.png.d25160ef0336b44cf666084aecd85031.png

    Because the elements under resources refer to database objects, they must be externally consistent with the data source intended for this Domain.

    Even though the itemGroups appear first in the design file, this section documents the resources first so that design elements are presented in the same order as the tabs of the Domain Designer.

    The resources element contains the jdbcTable and jdbcQuery elements to represent database tables and derived tables, respectively. Join trees are represented as a jdbcTable element with additional contents to define the joins.

    <resources> <jdbcTable datasourceId="SugarCRMDataSource" tableName="accounts" id="accounts">  ... </jdbcTable> <jdbcTable datasourceId="SugarCRMDataSource" tableName="accounts_opportunities"       id="accounts_opportunities">  ... </jdbcTable> <jdbcTable datasourceId="SugarCRMDataSource" tableName="opportunities"       id="opportunities">   ...  <filterString>opportunity_type == 'Existing Business'</filterString> </jdbcTable> <jdbcQuery datasourceId="SugarCRMDataSource" id="p1cases">  <fieldList>   <field id="account_id" type="java.lang.String"/>   <field id="assigned_user_id" type="java.lang.String"/>   <field id="case_number" type="java.lang.Integer"/>   <field id="date_entered" type="java.sql.Timestamp"/>   <field id="description" type="java.lang.String"/>   <field id="id" type="java.lang.String"/>   <field id="name" type="java.lang.String"/>   <field id="resolution" type="java.lang.String"/>   <field id="status" type="java.lang.String"/>  </fieldList>  <filterString>status != 'closed'</filterString>  <query>select * from cases where cases.priority=1 and cases.deleted=false</query> </jdbcQuery>[/code]                    

    Representing Tables in XML

    In the Domain Designer, you can select only entire tables, not individual columns. In the XML design file, however, you can specify any subset of columns that you need.

    jdbcTable – Represents a table or a copy of a table in the data source. A Domain design must reference all the tables that it needs to access. The jdbcTable element is also used to describe join trees, but this case is documented separately on . All three attributes of jdbcTable are required:
         datasourceId – Alias that identifies the data source. When created in the Domain Designer, this is the data source alias defined in Derived Tables Tab. When creating a design file, this alias may be any name you choose, but it must be identical for all tables and derived tables. When uploading the file, the datasourceId automatically becomes the alias associated with the data source defined for the Domain.
         tableName – Literal name of the table in the data source. Depending on the data source type and the database, this name includes additional information:
    For PostgreSQL and Oracle-based data sources, the table name includes the database schema name in the form schema.table, if you opted to include schema names when exporting the design.
    For PostgreSQL, Oracle, and other schema-based data sources within a virtual data source, the table name includes the data source prefix (defined when the virtual data source was created) and the database schema name in the form dataSourcePrefix_schema.table, if you opted to include schema names when exporting the design. The table name includes the data source prefix in the form dataSourcePrefix.table if you opted not to include schema names when exporting the design.
    For MySQL and similar data sources within a virtual domain, the table name includes the data source prefix and the database name in the form dataSourcePrefix_database.table.
         id – Table ID that is used to reference the table in the Domain design. If you copy a table in order to join it multiple times, each has the same datasourceId and tableName but must be given a different id.
    fieldList – A container for field elements. Required on jdbcTable elements because it would not make sense to have a table without columns in the Domain design. You must reference all the columns that you use in the Domain.
    field – Represents a column of a table in the data source. All the columns that you want to reference in the Domain defined with this element. Both attributes of field are required:
         id – Literal name of the column in the data source. As in the JDBC model that the data source is based on, the id must be unique within the jdbcTable, but not necessarily within the Domain.
         type – The Java type of the column, as determined from the data source by the JDBC driver. The type is one of the following:

    java.lang.Boolean

    java.lang.Byte

    java.lang.Character

    java.lang.Double

    java.lang.Float

    java.lang.Integer

    java.lang.Long

    java.lang.Short

    java.lang.String

    java.math.BigDecimal

    java.sql.Date

    java.sql.Time

    java.sql.Timestamp

    java.util.Date

    Unless you know the name and type of every column in the data source, it is often easier to select and export tables from the Domain Designer. The Domain Designer accesses the data source to find the names of all tables and columns, as well as their types. You may then export the XML design file with this information and refine your design.

    note-icon-ns_28x28.png.c454f138dd84671b46d8e39d6a487428.png

    If you have proprietary types in your database, the server may not be able to map its Java type from the JDBC driver. You can configure the mapping for proprietary types, as described in the JasperReports Server Administrator Guide.

    Alternatively, you can override any mapping by specifying the type attribute for any given field in the XML design file. The server uses this Java type for the field, regardless of its mapping. If your proprietary type cannot be cast in the specified type, the server raises an exception.

    Representing Derived Tables in XML

    Derived tables are similar in structure to tables, but they use the jdbcQuery element which contains the query element:

    jdbcQuery – Represents a derived table that is the result of an SQL query.

    Both attributes of jdbcQuery are required:

         datasourceId – Alias that identifies the data source for the Domain. The alias designates the data source to be queried. In the design file, this alias must be identical for all tables and derived tables.
         id – Table ID that is used to reference the derived table in the Domain design. Any reference to the id of a jdbcTable may also reference the id of a derived table.
    fieldList – A required container for the field elements.

    When the derived table is created in the Domain Designer, the set of columns corresponds to the selection of columns in the query result on the Derived Tables tab.

    field – Represents a column in the results of the query.

    Only the columns represented by a field element are available for reference by other elements. The columns of a derived table must be among those returned by the query.

         id – Literal name of the column in the query result. If the query gives an alias to the column in a SELECT AS statement, the id is the same as the alias. The id must be unique within the query results, but not necessarily within the Domain.
         type – The Java type of the column, as determined from the data source by the JDBC driver.
    query – The SQL query sent to the database server.
    You can use any valid SQL that returns results, as long as the tables and columns in the query exist in the data source, and the columns in the result match the id and type of all field elements of the derived table given in the fieldList. The syntax for a valid SQL query does not include a closing semi-colon (;). If you add a derived table in the Domain Designer, it runs the query and generates columns based on the result set. You can then export the design file containing the generated column list.

    note-icon-ns_28x28.png.2ce3f1668f72b402476f5fa965426398.png

    SQL queries for a derived table are with respect to the JDBC driver for the data source. If you are working with a virtual data source, SQL queries are validated against Teiid SQL, which provides DML SQL-92 support with select SQL-99 features. For more information, see the Teiid Reference Guide under the Documentation link on the Jaspersoft Support Portal.

    The following sample query selects some columns, including a field calculated in the SQL, from the result of a join with sorted results. In this case, only exp_date, store_id, amount, currency, conv, and as_dollars can be exposed as columns of this derived table.

    A derived table provides an alternative way to create joins and calculated fields. Here are some things to keep in mind when deciding how to implement the Domain:

    Unlike joins defined in the Domain, joins within a derived table are not restricted to equality comparisons when uploaded to the Domain Designer. For more information, see Representing Joins in XML.
    Unlike calculated fields in DomEL, calculated fields within derived tables may use any function call recognized by the RDBMS. See The DomEL Syntax for restrictions on function calls in calculated fields.
    The Domain mechanism applies filters, aggregation, and joins to derived tables by wrapping the SQL in a nested query, which may be less efficient on some databases than the equivalent query generated for a non-derived table.

    Representing Joins in XML

    A join is represented in the design file as a special jdbcTable element. It contains the joinInfo and joinedDataSetList elements to define the actual joins. It also contains a list of columns that are exposed through the Domain, each with a prefix on the field id attribute to identify its originating table. The Domain Designer automatically exposes all columns of all tables in a join, but in the design file you only need to specify those you want to reference elsewhere in the Domain.

    <jdbcTable datasourceId="SugarCRMDataSource" id="JoinTree_1" tableName="accounts"> <fieldList>  <field id="accounts_opportunities.account_id" type="java.lang.String"/>  ... </fieldList> <joinInfo alias="accounts" referenceId="accounts"/> <joinedDataSetList>  <joinedDataSetRef>   <joinString>join accounts_opportunities accounts_opportunities on    (accounts.id == accounts_opportunities.account_id)</joinString>  </joinedDataSetRef>  <joinedDataSetRef><joinString>join opportunities opportunities on    (accounts_opportunities.opportunity_id == opportunities.id)</joinString>  </joinedDataSetRef>[/code]
      <joinedDataSetRef><joinString>join users1 users1 on    (opportunities.assigned_user_id == users1.id)</joinString>  </joinedDataSetRef>  <joinedDataSetRef><joinString>left outer join p1cases p1cases on    (accounts.id == p1cases.account_id)</joinString>  </joinedDataSetRef>  <joinedDataSetRef><joinString>right outer join users2 users2 on    (users2.id == p1cases.assigned_user_id)</joinString>  </joinedDataSetRef> </joinedDataSetList></jdbcTable>[/code]                    
    jdbcTable – Represents the results of one or more joins between tables. If not all tables are joined together, there is one jdbcTable representing each join tree and containing only the join expressions for that tree. To define the join, the attributes and elements have a different meaning than for a regular table.
         datasourceId – Alias that identifies the data source for the Domain. The alias designates the data source where the join is performed. In the design file, this alias must be identical to that for all tables and derived tables.
         id – ID that is used to reference the join results in the Domain design. In the Domain Designer, each join tree is automatically given the ID JoinTree_n, where n is a sequential number. In the design file, you can give the join any name, as long as it is unique among all other tables and derived tables.
         tableName – Literal name of the first table in the join. This table name is combined with those in the joinInfo and joinedDataSetList to define the join expressions.
    For PostgreSQL and Oracle-based data sources, the table name includes the database schema name in the form schema.table, if you opted to include schema names when exporting the design.
    For PostgreSQL and Oracle-based data sources within a virtual data source, the table name includes the data source prefix defined when the virtual data source was created and the database schema name in the form dataSourcePrefix_schema.table, if you opted to include schema names when exporting the design. The table name includes the data source prefix in the form dataSourcePrefix.table if you opted not to include schema names when exporting the design.
    For MySQL and similar data sources within a virtual domain, the table name includes the data source prefix and the database name in the form dataSourcePrefix_database.table.
    fieldList – A required container for the field elements in the join tree.
    field – Represents a column in the join tree. The table of each column is identified by a prefix on the id attribute. When created in the Domain Designer, the design file includes every column in every table of the join. When you create your own design file, only the columns you want to reference are needed. Both attributes of field are required:
         id – Field ID composed of the ID of the table in the design and the literal name of the column in the data source. The syntax is table_ID.field_name.
         type – The Java type of the column, identical to the type in its table definition.
    joinInfo – Gives the table ID and alias for the table given by the tablename attribute. The table ID and alias are used as the first table in the join definition. This element and its two attributes are required even if they are identical.
         alias – Alternative name within the join expression for the table identified in referenceId. By default, the alias is the same as the referenceID, but you may supply a true alias in handwritten design files. If you use a distinct alias, you must be careful to use the alias throughout the joinString element that defines the join.
         referenceId – Table id of the table within the design whose data source name is given in tableName.
    joinedDataSetList – Container for the list of join statements.
    joinedDataSetRef – Container for the join statement.
    joinString – A string expressing an SQL join statement in the following format:

    join_type join table_ID table_alias on join_expression

    Where:

         join_type – One of right outer, left outer, or full outer. Inner join is the default if no join type is specified.
         table_ID – The ID of a table within the design.
         table_alias – Alternative name to use for the table_ID within the join expression. By default, the alias is the same as the table_ID, but you may supply a true alias in handwritten design files.
         join_expression – Expression that compares the columns on which the join is made, in the form

    left_table_alias.field_name == right_table_alias.field_name

    warning-icon-ns_28x28.png.f31babd5ff8ee0f33032b93ef1ad546b.png

    Join expressions are actually written in the DomEL syntax to allow more complex expressions, such as boolean operators and other comparisons. See The DomEL Syntax.

    However, join expressions other than equality between field values are not supported in the Domain Designer. You can upload a design file with such joins, but they are overwritten with equi-joins if you open the design in the Domain Designer. Join expressions with several boolean clauses are not supported in the Domain Design either. You can sometimes replace them with filters on tables in the join.

    The order of joinedDataSetRef elements is important. The first one must contain a join expression between the table_alias it defines and the alias in the joinInfo element. The subsequent ones may only reference the table_alias they define and ones that appear in joinString elements before them.

    Representing Calculated Fields in XML

    Calculated fields are defined as regular columns in a field element with an additional attribute. Calculated fields that rely only on columns of the same table appear in jdbcTable for that table, as well as in the join tree. Calculated fields that rely on columns from different tables that are joined appear only in the join tree.

    The following example shows the XML for a calculated expression in the accounts table. Because it references only the columns of accounts, it appears in that table and in the join tree.

    The attributes of the field element have a different meaning when defining a calculated field:

    dataSetExpression – Expression which calculates a value based on other columns. The syntax for the expression, including how to reference columns, is documented in The DomEL Syntax.
    id – User-defined name of the calculated field. The format of the id is dependent on how the calculated field appears in the design file:
    1. If the expression references columns in the same table:
    a. The field appears in the table and the id is a simple column name.
    b. The field also appears in a join tree that uses the table, the id has the form table_ID.field_name.
    2. When the expression references columns in different tables, the field appears only in the join tree of those tables, and the id has the form jointree_ID.field_name.
    3. When the expression computes a constant value. The field appears in a table named Constant, and the id is a simple column name. Constant fields are further explained below.
    type – The Java type of the value calculated by the expression, for example java.lang.String. This type must be compatible with the result of the DomEL expression and among the JDBC-compatible types listed on .

    A special case of a calculated field occurs when the expression does not reference any column names. The calculated field always has the same value and is said to be a constant. In the Domain Designer, constant fields are automatically grouped in a table named Constant and may be used in other calculated fields, filters, or even as an item. Because constant fields are not dependent on any column values, they may be used in any join tree and exposed to the user along with the items from any join tree. When editing a design file, you must treat constant calculated fields in the same way.

    Representing Filters in XML

    Filters are defined as optional filterString elements inside of jdbcTable and jdbcQuery elements. They impose a condition on any results that are returned for that table, query, or join tree, thereby limiting the number of rows returned when accessing the data source. Whereas other settings mainly determine which columns are available for use in a report. A filter determines which rows are available when running the report.

    filterString – Expression which evaluates to true or false when applied to each row of values in the data source. The expression refers to columns using their id attribute. Thus, a filter on a table or derived table refers to the simple column name, but a filter on a join tree refers to the table_ID.field_name. The full syntax for the expression is documented in The DomEL Syntax.

    warning-icon-ns_28x28.png.ff51ab18c6e7d8d60633e6f6fad16cac.png

    Filters defined in the Domain Designer are limited to conditions on one column or comparisons of two columns, with more complex filters created by the conjunction (logical AND) of several conditions. Other filter expressions are not supported. You can upload a design file with more complex filters, but they are overwritten or cause errors if you open the design in the Domain Designer.

    For example, the following filters are defined in the example Domain on :

     

    </jdbcQuery>[/code]                    

     

    Representing Sets and Items in XML

    Now that all the table and field IDs have been defined, look at the definitions of sets and items that are exposed through itemGroups and items elements at the top of the Domain design file. The itemGroups and items elements are equivalent to the selection of sets and items on the Display tab of the Domain Designer. They create a hierarchy of sets, subsets and items and hold attributes that define all the properties available on sets and items. For a description of each possible property, see The Properties Panel. The following example shows two levels of sets, with items inside each level as well as at the root, outside of any set.

    itemGroups – A container for itemGroup elements.
    itemGroup – Represents a set. The itemGroup element may contain an itemGroups element, an items element, or both, representing its subsets and items, respectively. The attributes of itemGroup are the properties of the set it represents:
         id – The unique identifier of the set among all set and item IDs. This attribute is required.
         label – The set’s name, visible to users of the Domain. If the label is missing, the Ad Hoc Editor displays the id.
         description – The optional description of the set, visible to users as a tooltip on the set name in the Ad Hoc Editor.
         labelId – The internationalization key for the label in the Domain’s locale bundles.
         descriptionId – The internationalization key for the description in the Domain’s locale bundles.
         resourceId – A reference to the table on which the set is based. This attribute is required, but it has no meaning on a set and is not significant in the design.

    When an internationalization key is defined for the label or description, the label or description is replaced with the value given by the key in the local bundle corresponding to the user’s locale. See Locale Bundles.

    items – A container for item elements.
    item – Represents an item. The attributes of item are the properties of the item it represents:
         id – The unique identifier of the item among all set and item IDs. This attribute is required.
         label – The item’s name, visible to users. If the label is missing, the Ad Hoc Editor displays the id.
         description – The optional description of the item, visible as a tooltip on the item name in the Ad Hoc Editor.
         labelId – The internationalization key for the label in the Domain’s locale bundles.
         descriptionId – The internationalization key for the description in the Domain’s locale bundles.
         resourceId – A reference to the column on which the item is based. This attribute is required because it defines the connection between what the user sees and the corresponding data in the data source. The resourceId has the form table_ID.field_ID. When the item refers to a column in a join tree, the resourceID corresponds to jointree_ID.table_ID.field_name because the field ID in a join tree includes the table ID.
         dimensionOrMeasure – Corresponds to the Field or measure setting in the user interface. Its possible values are Dimension (equivalent to field) or Measure. By default, all numeric fields are treated as measures in the Ad Hoc Editor. This attribute is optional and necessary only when overriding the default behavior, for example to make a numeric item explicitly not a measure. See Terminology.
         defaultMask – A representation of the default data format to use when this item is included in a report. The possible values depend on the type attribute of the column referenced by the resourceId. See the table below.
         defaultAgg – The name of the default summary function (also called aggregation) to use when this item is included in a report. The possible values for the defaultAgg depend on the type attribute of the column referenced by the resourceId. The following table gives the possible data formats and summary functions based on the column type. The appearance columns show the equivalent setting in the properties table of the Display tab:

    Field Type

    Default Data Formats

    Default Summary Functions

    Attribute Value

    Appearance

    Attribute Value

    Appearance

    Integer

    #,##0
    0
    $#,##0;($#,##0)
    #,##0;(#,##0)

    -1,234
    -1234
    ($1,234)
    (1234)

    Highest

    Lowest

    Average

    Sum

    DistinctCount

    Count

    Maximum

    Minimum

    Average

    Sum

    Distinct Count

    Count All

    Double

    #,##0.00
    0
    $#,##0.00;($#,##0.00)
    $#,##0;($#,##0)

    -1,234.56
    -1234
    ($1,234.56)
    ($1,234)

    Date

    short,hide
    medium,hide
    long,hide
    medium,medium

    3/31/09
    Mar 31, 2009
    March 31, 2009
    Mar 31, 2009 23:59:59

    DistinctCount Count

    Distinct Count
    Count All

    All others

    Not allowed

     

    The following example shows the use of the itemGroup and item elements to represent the sets and items from Example of Creating a Domain. The design file was exported from the Domain Designer.

    <itemGroups> ... <itemGroup id="users1" label="Account Rep" description="Primary account       representative" labelId="" descriptionId="" resourceId="JoinTree_1">  <items>   <item id="first_name" label="First Name" description="Given name"      labelId="" descriptionId="" resourceId="JoinTree_1.users1.first_name"/>   <item id="last_name" label="Last Name" description="Surname or family name"      labelId="" descriptionId="" resourceId="JoinTree_1.users1.last_name"/>  </items> </itemGroup> <itemGroup id="opportunities" label="Opportunity" description="Sales opportunity"       labelId="" descriptionId="" resourceId="JoinTree_1">  <items>   <item id="date_entered1" label="Date" description="Date opportunity opened"      labelId="" descriptionId="" defaultMask="short,hide"      resourceId="JoinTree_1.opportunities.date_entered"/>   <item id="amount" label="Amount" description="Estimated contract Amount"      labelId="" descriptionId="" defaultMask="$#,##0;($#,##0)"      defaultAgg="Average" resourceId="JoinTree_1.opportunities.amount"/>   <item id="probability" label="Probability" description="Chance of closing the      contract" labelId="" descriptionId=""      resourceId="JoinTree_1.opportunities.probability"/>[/code]
     [/code]                    

    Labels and descriptions may contain any characters, but the ID property value of both itemGroup and item elements must be alphanumeric and not start with a digit.


    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...