(Show Contents)

Restrict a paginated report using restrictions to complex SQL queries

To use restrictions to complex SQL queries in paginated reports, you define the paginated report .axvw as usual, using SQL for complex queries.

Place “${restriction.sql}” where the console restriction should go. This macro instructs the dataSource to take any applied parsed restriction and format it into a set of SQL clauses.

<dataSource id="dsCASumCls3ByBldgRep">
   <table name="activity_log" role="main"/>
   <table name="bl" role="standard"/>
   <table name="csi" role="standard"/>
   <sql dialect="generic">
    SELECT activity_log.bl_id,
      bl.name,
      hierarchyIds,
       csi_description,
       count(*) ${sql.as} vf_num_itm,
       (SUM(activity_log.cost_estimated)) ${sql.as} sum_cost_estimated,
      (SUM(activity_log.cost_est_cap)) ${sql.as} sum_cost_est_cap,
      (SUM(activity_log.cost_to_replace)) ${sql.as} sum_cost_to_replace
     FROM activity_log, bl,
        (SELECT csi_id,
          hierarchy_ids ${sql.as} hierarchyIds,
          description ${sql.as} csi_description
        FROM csi
       WHERE hierarchy_ids like '%|%|%|%|%'
      ) ${sql.as} csi
   WHERE activity_log.bl_id = bl.bl_id
     AND activity_log.csi_id = csi.csi_id
     AND activity_log.activity_type = 'ASSESSMENT'
     AND activity_log.cond_priority &lt;&gt; 0
     ${restriction.sql}
   GROUP BY activity_log.bl_id, bl.name, hierarchyIds, csi_description
   </sql>
   <field table="activity_log" name="bl_id" dataType="text"/>
   <field table="bl" name="name" dataType="text"/>
   <field table="activity_log" name="hierarchyIds" dataType="text"/>

You assemble the restriction from the view console values, and supply this restriction when calling the paginated report view:

var restriction = new Ab.view.Restriction();
restriction.addClause('activity_log.status’, 'OPEN%', "LIKE");
restriction.addClause('bl.name’, '%quarters%', "LIKE");
View.openPaginatedReportDialog(
     'ab-my-paginated-report-using-sql.axvw',
     { ‘dsCASumCls3ByBldgRep’ : restriction } );

The sql.restriction macro would evaluate to:

( activity.log.status LIKE ‘OPEN%’ AND bl.name LIKE ‘%quarters%’ )