Filter

A filter captures data from the user using search fields and reports back matching results from a database table using report fields. It can be defined similarly to below:

  <Filter>
    <ZName>ts_fil_purchaserecords</ZName>
    <DisplayName>Purchase Records</DisplayName>
    <Category>Reports</Category>
    <ShortDescription>Shows all purchases</ShortDescription>
    <LongDescription>
      <p>Shows all of the purchase records</p>
    </LongDescription>
    <BaseTable>
      <ZName>ts_t_purchaserecords</ZName>
    </BaseTable>
    <FilterAction>
      <ZName>ts_fa_newpurchaserecord</ZName>
    </FilterAction>

    <!-- SearchFields -->
    <SearchField>
      <ZName>ts_sf_purchaseid</ZName>
    </SearchField>

    <!-- ReportFields -->
    <ReportField>
      <ZName>ts_rf_purchaseid_edit</ZName>
    </ReportField>
  </Filter>

Grouped Filters

Basics

A grouped filter has the results grouped by one of the fields, in a similar fashion to "group by" in SQL. This can be achieved simply by adding "GroupBy" to the filter, and referencing the group by in the report fields using "ApplyToGroups" like so:

  <Filter>
    ...
    <ReportField>
      <ZName>grouping_rf</ZName>
    ...
      <ApplyToGroups>gb_group</ApplyToGroups>
    </ReportField>
    <GroupBy>
      <ZName>gb_group</ZName>
      <DisplayName>Grouping</DisplayName>
      <TargetField>grouping_rf</TargetField>
    </GroupBy>
  </Filter>

The GroupBy XMLs can also be nested, resulting in what is known as a 'tree-view' filter. By default this displays as a series of collapsed groups that can be expanded/collapsed using JS +/- buttons, each with a group header at the top.

This XML:

  <GroupBy>
      <ZName>gb_group</ZName>
      <TargetField>grouping_rf</TargetField>
      <DisplayName>Grouping</DisplayName>
      <GroupBy>
        <ZName>gb_group_level_2</ZName>
        <DisplayName>Group level 2</DisplayName>
        <TargetField>id_rf</TargetField>
      </GroupBy>
  </GroupBy>

Produces:

Additional Flags:

<ShowCount>true</ShowCount>

Added to a "GroupBy" to force the filter to use that level of grouping to calculate the filter count. For example, if you had 2 results for the top level group, with 3 results in each, which in turn also had 3 results, the filter count would say "2". Adding "ShowCount" to the bottom leve group will cause the count to be shown as "18".

<GroupExpanded>gb_group</GroupExpanded>

Added at the filter level and references a GroupBy, this will cause that group to be automatically expanded when the filter is run.

<ForceStandard>true</ForceStandard>

Added at the filter level, this flag will cause the filter to be displayed as a normal filter, without the "tree-view" JS. (See end for an example image of this)

<GroupHeadersAfter>true</GroupHeadersAfter>

Added at the filter level, this flag will cause the group headers to be displayed below the results in the groups.

Adding a totals row to a filter

An example of special functionality that can be added to a filter using groups, is adding a row to total the report fields to the bottom.

-Add a total report field, with the SQLName concat("Total"), and a display name the same as the column you wish the totals to line up with. Also 'ApplyToGroups', the group from the next step. This will display the word "Total" in the field, to mark the row as the total row.

<ReportField>
      <ZName>total_title_rf</ZName>
      <Field sequence="5">
        <ZName>total_title</ZName>
        <DisplayName>Mode</DisplayName>
        <SQLName>CONCAT("Total")</SQLName>
      </Field>
      <ApplyToGroups>group_by_total</ApplyToGroups>
</ReportField>

-Add a GroupBy, which groups by the report field from the previous step. This will put all the results in the same group. Inside the GroupBy, add another GroupBy which groups the results the primary key, e.g. id. Suggest adding "showcount" to the lowest level group.

<GroupBy>
      <ZName>group_by_total</ZName>
      <TargetField>total_title_rf</TargetField>
      <DisplayName>Branch Name</DisplayName>
      <GroupBy>
        <ZName>group_by_id</ZName>
        <TargetField>id_rf</TargetField>
        <ShowCount>true</ShowCount>
      </GroupBy>
</GroupBy>

-Add report fields for each column you want totalling. These should have an SQLName which sums that column's report field and the bottom level group applied. Base the field on the field it is summing so that the display names and sql types are the same:

<ReportField>
      <ZName>net_total_rf</ZName>
      <Field sequence="6">
        <ZName>net_total</ZName>
        <Base>net_sales</Base>
        <DisplayName>Net Sales</DisplayName>
        <SQLName>SUM(net_sales)</SQLName>
      </Field>
      <ApplyToGroups>group_by_total</ApplyToGroups>
</ReportField>

-Group Expand both the groups:

<GroupExpanded>group_by_total</GroupExpanded>
<GroupExpanded>group_by_till_id</GroupExpanded>

-Force standard:

<ForceStandard>true</ForceStandard>

-Add the totals to the bottom, rather than the top, if required:

<GroupHeadersAfter>true</GroupHeadersAfter>

Final look: