Report Query

When the Report type is set to ItemType or Generic, the Report Query is the static AML query used to generate the data for the Report. The following attributes are very useful to help filter and control the results for your Report.

• select: the select attribute is used to define the set of properties you want returned from the query. This is a comma delimited list of property names (not the property label). It is identical to the select clause in SQL if you are familiar with relational database technology. You can gain significant performance improvements to your Reports by limiting the data returned from the query.

  The following example illustrates the select attribute by selecting the item_number, description, and cost properties from the Part ItemType:

  <Item action="get” type="Part” select="item_number,description,cost"/>

• order_by: the order_by attribute is used to order the results. The Report Tool offers the ability to sort the data in a variety of ways but sometimes you may want the data in a pre-sorted order. This is a comma delimited list of property names (not the property label). This is identical to the order by clause in SQL if you are familiar with relational database technology.

  The following example illustrates the select attribute by selecting the item_number, description, and cost properties from the Part ItemType:

  <Item
action="get”
type="Part”
select="item_number,description,cost”
order_by="item_number"/>

• page & pagesize: if you are performing a query for items and you know that there can be hundreds or thousands of items, you can limit the number of items returned by using the page and pagesize attributes. Together they control how many items to return (the pagesize) and which page to return.

  The following example illustrates the select attribute by selecting the item_number, description, and cost properties from the Part ItemType:

  <Item
action="get”
type="Part” select="item_number,description,cost”
order_by="item_number”
page="1"
pagesize="50"/>

• Relationships – often you want the relationship items in addition to the source item to generate a Report showing the items configuration. The query may contain the <Relationships> tag to hold the <Item> tags for the relationship items you want included in the query results. The <Item> tags can themselves include the same control attributes.

  The following example illustrates the Relationships tag to include the BOM relationships. Notice the select attribute for the BOM type Item defines the properties to be returned for the BOM relationship item plus the properties for the Part item referenced by the related_id property, which is defined within the parenthesis following the related_id property:

  <Item
action="get”
type="Part”
select="item_number,description,cost”
order_by="item_number">
<Relationships>
<Item
action="get”
type="Part BOM”
select="qty,ref_des,related_id(item_number,description,cost)"/></Relationships>
</Item>