Restrict grouped data by date criteria
Restrict grouped data by date criteria
Technologies > User Interface Add-Ins > Grouping Views> Grouping DataSource with Restriction
Use a Single Parameter in a Date Restriction
In this example, clicking on a row in the left panel restricts the summarized data in the right panel. The right panel contains a parameter
year
, which is used in a sql restriction specifying that the year should fall between the
ls.date_start
and
ls.date_end
values:
<parameter name="year" dataType="number" value="1990"/>
<restriction type="sql"
sql="${sql.yearOf('ls.date_start')} <= ${parameters['year']} AND ${sql.yearOf('ls.date_end')} >= ${parameters['year']}" />
The
year
parameter is set to the selected year using
addParameter()
when the user clicks on a row link:
groupingDsRestrict_grid1_select_onClick: function(row, action) {
var year = row.getRecord().getValue('afm_cal_dates.year');
this.groupingDsRestrict_grid2.addParameter('year', year);
this.groupingDsRestrict_grid2.refresh();
Use Two Parameters in a Date Range Restriction
The panel on the left uses a separate restriction in the grouping data source to define a year range:
<parameter name="yearStart" dataType="number" value="1990"/>
<parameter name="yearEnd" dataType="number" value="2010"/>
<restriction type="sql"
sql="${sql.yearOf('cal_date')} >= ${parameters['yearStart']} AND ${sql.yearOf('cal_date')} <= ${parameters['yearEnd']}" />
The restriction above retrieves the year value from the
afm_cal_dates.cal_date
field using the
${sql.yearOf()}
binding expression and restricts according to a year range. The year range is set using parameters
yearStart
and
yearEnd
, which could be modified by the client using the
addParameter()
function using values derived from, for example, a restriction console:
this.groupingDsRestrict_grid1.
addParameter
('yearStart', yearStart);
this.groupingDsRestrict_grid1.
addParameter
('yearEnd', yearEnd);
this.groupingDsRestrict_grid1.refresh();
Use parameterized restrictions