Updating the Filter in a Pre-Edit Hook¶

A pre-edit hook can read the filter a user applied and return a modified one. This is useful when the editor should widen a user's selection behind the scenes - for example, when a user filters for the current quarter and the grid should also show the previous one.

The hook runs inside the editors/getdata service, so both the applied filter and the filtered rows are in scope. This page describes what is available at that point and works through an example.

What the editor receives¶

When a filtered editor loads, editors/getdata returns the filter to the client in two forms:

sasparams.FILTER_TEXT - the resolved WHERE clause, which the editor shows above the grid.
query - the filter's clauses as structured rows (variable_nm, operator_nm, raw_value), which the editor renders as the filter chips.

The response does not carry the filter key (FILTER_RK). That is an input to getdata, not an output - the editor already holds it in the URL (/editor/<table>/<FILTER_RK>), so there is nothing to return.

When the hook runs¶

getdata builds the filter, applies it, and sorts the result, all before the pre-edit hook runs. In order:

the WHERE clause is written to a temporary fileref, filtref;
work.OUT is populated with where %inc filtref - the filter is applied here;
the pre-edit hook runs;
FILTER_TEXT is read back out of filtref, and the response is streamed.

So the hook sits between "the rows are filtered" and "the filter is returned". Two consequences follow:

It can change what the editor is told the filter was, by rewriting filtref and, if the chips must match, work.query.
It cannot change which rows came back that way, because the data step has already run. To return different rows, the hook must re-read the table itself.

Take the value from the data¶

A hook that wants to extend a date filter needs the date the user is working with. It does not have to parse the filter text for it - the filtered rows are already in work.OUT, so the value can be read from there.

Example: widen a date filter to the previous quarter¶

The hook below is registered in MPE_TABLES.PRE_EDIT_HOOK for a table with a date column ORDER_DATE. When a user opens the editor with a filter that includes ORDER_DATE, it extends the filter to also cover the previous quarter, re-reads the table so the grid shows both quarters, and returns the widened filter text.

/* PRE_EDIT_HOOK: widen the user's ORDER_DATE filter by one quarter */

%if &filter_rk > 0 %then %do;

  /* the date the user is looking at, taken from the rows already returned */
  %let refdt=;
  proc sql noprint;
    select max(ORDER_DATE) into :refdt from work.out;
  quit;

  %if &refdt ne %then %do;

    /* the previous quarter, relative to that date */
    %let pq_from=%sysfunc(putn(%sysfunc(intnx(quarter,&refdt,-1,b)),date9.));
    %let pq_to  =%sysfunc(putn(%sysfunc(intnx(quarter,&refdt,-1,e)),date9.));

    /* the user's own clauses, as a standalone WHERE */
    %mp_filtergenerate(work.query,outref=userref)

    /* Data Controller's own clause - current-record validity, plus RLS */
    %mpe_filtermaster(EDIT,&orig_libds,
      dclib=&mpelib,
      filter_rk=-1,
      outref=ctlref,
      outds=work.ctlq
    )

    /* read each clause (which may span several lines) into a macro variable */
    %let usertext=;
    data _null_;
      infile userref end=eof;
      input;
      length t $32767;
      retain t;
      t=catx(' ',t,_infile_);
      if eof then call symputx('usertext',t);
    run;
    %let ctltext=;
    data _null_;
      infile ctlref end=eof;
      input;
      length t $32767;
      retain t;
      t=catx(' ',t,_infile_);
      if eof then call symputx('ctltext',t);
    run;

    /* ( user's filter OR previous quarter ) AND ( validity + RLS ) */
    filename newfilt temp;
    data _null_;
      file newfilt lrecl=32767;
      length f $32767;
      f='(( '!!trim(symget('usertext'))!!' ) OR (ORDER_DATE between "&pq_from"d and "&pq_to"d))'
        !!' AND ('!!trim(symget('ctltext'))!!' )';
      put f;
    run;

    /* re-read the table, so the grid shows both quarters */
    data work.out (drop=&var_txfrom &var_txto &var_processed &drop_rk);
      _____DELETE__THIS__RECORD_____="No";
      set &orig_libds;
      where %inc newfilt;
    run;

    /* keep the order the editor first showed */
    proc sort data=work.out;
      by &sortpk;
    run;

    /* hand the widened filter back to the editor */
    data _null_;
      file filtref lrecl=32767;
      infile newfilt;
      input;
      put _infile_;
    run;

  %end;
%end;

What the hook does¶

The reference date. work.OUT holds the rows the user's filter returned, so the latest ORDER_DATE on screen is read straight from it. Nothing has to be parsed out of the filter text.
The previous quarter. INTNX moves back one quarter from that date and returns its first and last day, formatted as SAS date literals (01APR2026 and 30JUN2026, for example).
The user's clause. work.QUERY is the filter's clauses as structured rows, and %mp_filtergenerate turns them back into a WHERE clause. This is the user's filter on its own, with no Row Level Security or validity clause mixed in.
Data Controller's clause. Calling %mpe_filtermaster with filter_rk=-1 produces the part of the filter that is not the user's: the current-record validity clause and any Row Level Security, or 1=1 when there is neither.
The combined filter. The two clauses are combined as ( user OR previous-quarter ) AND ( validity + RLS ), so the previous quarter is added to the user's selection without widening anything the user is not entitled to see.
The rows. work.OUT is rebuilt with the combined filter, using the same drop list as the data step getdata runs itself - the transaction and processing columns, plus &drop_rk, the surrogate key that getdata hides when a table is registered with RK_UNDERLYING - and sorted on &sortpk, because getdata sorts before the hook runs and does not sort again afterwards. On a bitemporal table getdata also deduplicates to the latest record per primary key (by &pk &var_busfrom); add that step too, or the re-read returns every temporal version rather than the current one.
The returned text. filtref is overwritten with the combined filter, so the clause the editor shows is the one that was actually applied.

What it looks like¶

The captures below are the editor for a table with an ORDER_DATE column, opened with a filter for the current quarter. In the first, no hook is configured, so the filter is the user's own and the grid holds one quarter of rows. In the second, the hook has run: the filter above the grid carries both quarters, and the rows have grown to match.

The editor with the user's own filter, one quarter of rows

The same editor after the hook widened the filter to the previous quarter

Row Level Security¶

The user's clause and Data Controller's clause have to be kept apart, because Row Level Security lives in the second one. Combining them as ( user OR previous-quarter ) AND ( validity + RLS ) keeps RLS applied to both branches. Wrapping the whole existing filter instead - ( existing OR previous-quarter ) - would let the previous-quarter rows through without their RLS condition, so do not take that shortcut.

The row cap¶

getdata refuses a result larger than DC_MAXOBS_WEBEDIT (500 by default) with Table is too big. Widening a filter widens the result, so a hook that adds a quarter can push a table over the cap. Check the count before handing the data back, or raise the option for that table in MPE_CONFIG.

Matching the filter chips¶

The clause above the grid comes from FILTER_TEXT, so overwriting filtref is what updates it. The filter chips are rendered from the query output instead, so if they must reflect the widened filter too, amend work.QUERY in the same hook - append the extra clause as a new subgroup with group_logic='OR' before the response is built.