Tutorial 45: How to Build an Interactive Report

This tutorial describes how to build an interactive report using the interactive reporting features of the grid component.

The interactive reporting features of the grid allow the application users to:

  • sort a grid column
  • hide/show a grid column
  • move and change the display order of a grid column
  • resize a grid column
  • apply a search criteria to a grid column
  • apply aggregate functions to a grid column

You may click here to run the completed application.

Open Formspider IDE and click “New Application” under the “File” menu. The “New Application” dialog shows up. Enter “interactiveReport” as the name of the application and “HR” as its Datasource Schema name.  If you are using Formspider Online, you do not need to enter database schema name since your account is already configured to use your online database. Leave other fields empty and click “OK”. This creates the “interactiveReport” application with a default mainframe (called “mainFrame”) and a default panel (called “mainPanel”).

Creating The Model

Create the necessary Datasource Definition for your grid. Expand the Datasource Definitions accordion, select the “Datasource Definitions” node and click the “+” button. Alternatively, you may right click the “Datasource Definitions” node and select “New” from the pop up menu. The “New Datasource Definition” Dialog shows up. Enter “EMPLOYEES” as “Name” and select “Query” radio button from “Based On” section, this selection means that you will enter a SQL query to retrieve data from the HR schema.

Creating “EMPLOYEES” datasource definition

Select the “Query” node in the navigation tree on the left. Enter the following “SQL Statement” to the datasource definition:

select emp.employee_id,
       emp.first_name,
       emp.last_name,
       emp.email,
       emp.hire_date,
       emp.salary,
       dep.department_name
from employees emp,
     departments dep
where emp.department_id = dep.department_id

Entering Query for “EMPLOYEES” datasource definition

Click “OK” to save and close the “New Datasource Definition” dialog. This creates the “EMPLOYEES” datasource definition and the “EMPLOYEES1″ datasource. You will use the “EMPLOYEES1” datasource as the datasource of your grid.

Creating The User Interface

Double click the “mainPanel” under the “Panels” node of the “Containers” navigation tree to open it in the editor. Add a grid displaying “First Name”, “Last Name”, “Salary”, “Hire Date”, “Email” and “Department” information of the employees using “EMPLOYEES1” datasource that you have created recently. The panel XML looks like;

<panel>
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid name="grid" dataSource="EMPLOYEES1">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textLabel column="SALARY"/>
          </column>
          <column headerLabel="Hire Date">
            <textLabel column="HIRE_DATE" dateFormat="DD/MM/YYYY"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
          <column headerLabel="Department">
            <textLabel column="DEPARTMENT_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Press “Run on Web” button to run the application. Note that the interactive reporting features of the grid component are disabled by default.

By default, the interactive reporting features of the grid component are disabled

Sorting Grid Columns

The enableColumnSort attribute of the grid component allows you to enable or disable sorting functionality on all grid columns. Similarly, the sortable attribute of a grid column enables or disables sorting functionality on a specific grid column. When these two attributes are used together, the sortable attribute determines the sortable state of the grid column.

Limitations & Usage Notes

  • A grid column cannot be sortable if:
    • it’s not bound to a datasource column
    • it’s bound to datasource column with CLOB or BLOB datatype
    • it contains a radioButton component
  • If the fetch mode of the grid datasource is “Paging”, Formspider automatically re-queries it when a column is sorted.

Assume that as a business requirement, all grid columns expect “Department” should be sortable. To satisfy this requirement, edit “mainPanel” XML, set the enableColumnSort attribute of the grid component as “Y” and the sortable attribute of the “Department” column as “N”:

<panel>
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid name="grid" dataSource="EMPLOYEES1" enableColumnSort="Y">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textLabel column="SALARY"/>
          </column>
          <column headerLabel="Hire Date">
            <textLabel column="HIRE_DATE" dateFormat="DD/MM/YYYY"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
          <column headerLabel="Department" sortable="N">
            <textLabel column="DEPARTMENT_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again, note that the mouse cursor changes when it’s on the column header of a sortable column. You can sort all grid columns except “Department” by clicking on their headers.

You can sort all columns except “Department” by clicking on their headers

You can also perform sorting through the “Sort Ascending”/ ”Sort Descending” options existing on the interactive menu of the grid column header. To open the interactive menu of a grid column, click to the icon which appears at the right side of the grid column header when the mouse cursor is on this column header.

You can also perform sorting through the “Sort Ascending”/ ”Sort Descending” options existing on the interactive menu

Hiding/Showing Grid Columns

The enableColumnHide attribute of the grid component allows you to hide or show any grid column through the interactive column menu.

Limitations & Usage Notes

  • A grid column cannot be hidden if it’s the only visible column of the grid.
  • If an aggregate function is already used on any grid column, Formspider automatically re-queries the grid datasource when a grid column is hidden or shown.

Edit “mainPanel” XML and set the enableColumnHide attribute of the grid as “Y” to enable the column hide functionality:

<panel>
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid name="grid" dataSource="EMPLOYEES1" enableColumnSort="Y" enableColumnHide="Y">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textLabel column="SALARY"/>
          </column>
          <column headerLabel="Hire Date">
            <textLabel column="HIRE_DATE" dateFormat="DD/MM/YYYY"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
          <column headerLabel="Department" sortable="N">
            <textLabel column="DEPARTMENT_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again, open the interactive menu of any grid column and move your mouse cursor on the “Columns” option, note that a child menu listing all grid columns appears. Uncheck the “Salary” and “Hire Date” checkboxes to hide the “Salary” and “Hire Date” columns.

Uncheck the “Salary” and “Hire Date” checkboxes to hide the “Salary” and “Hire Date” columns

Moving Grid Columns

The enableColumnMove attribute of the grid component allows you to change the display order of any grid column by dragging and dropping it to an arbitrary position.

Edit “mainPanel” XML and set the enableColumnMove attribute of the grid as “Y” to enable column move functionality:

<panel>
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid name="grid" dataSource="EMPLOYEES1" enableColumnSort="Y" enableColumnHide="Y" enableColumnMove="Y">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textLabel column="SALARY"/>
          </column>
          <column headerLabel="Hire Date">
            <textLabel column="HIRE_DATE" dateFormat="DD/MM/YYYY"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
          <column headerLabel="Department" sortable="N">
            <textLabel column="DEPARTMENT_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again and move the “Salary” column between the “Email” and “Department” columns by dragging and dropping it between these two columns.

Drag the “Salary” column towards its new position

Drop the “Salary” column to its new position

Resizing Grid Columns

The enableColumnResize attribute of the grid component allows you to resize any grid column. When a grid column is resized, Formspider automatically adjusts (increase or decrease) the width of its neighbor column.

Edit “mainPanel” XML and set the enableColumnResize attribute of the grid as “Y” to enable the column resize functionality:

<panel>
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid name="grid" dataSource="EMPLOYEES1" enableColumnSort="Y" enableColumnHide="Y" enableColumnMove="Y" enableColumnResize="Y">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textLabel column="SALARY"/>
          </column>
          <column headerLabel="Hire Date">
            <textLabel column="HIRE_DATE" dateFormat="DD/MM/YYYY"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
          <column headerLabel="Department" sortable="N">
            <textLabel column="DEPARTMENT_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again, resize the “Last Name” column and increase its width towards the “Salary” column. Note that while the width of the “Last Name” column increases, the width of the “Salary” column automatically decreases.

Resizing “Last Name” column

While the width of the “Last Name” column increases, the width of the “Salary” column automatically decreases

Filtering Grid Columns

The enableColumnFilter attribute of the grid component allows you to apply a search criteria on any grid column. The filter types are listed below:

Filter Type Definition
Wildcard Filter Filtered column should contain the filter value
Exact Filter Filtered column value should be equal to the filter value
Range Filter Filtered column value should be greater and/or lesser than the filter value(s)


The availability of a filter type depends on the data type of the datasource column used in the grid column:

varchar2 number date clob blob
Wildcard Filter X - - - -
Exact Filter - X X - -
Range Filter - X X - -

Limitations & Usage Notes

  • A grid column cannot be filtered if:
    • it’s not bound to a datasource column
    • it’s bound to datasource column with CLOB or BLOB datatype
    • it contains a radioButton, checkBox, hyperLink, comboBox or htmlRenderer component
  • When a filter is applied to a grid column, Formspider automatically re-queries its datasource to apply the filter.
  • When a function is applied to a grid column, its filter cannot be changed.

Edit “mainPanel” XML and set the enableColumnFilter attribute of the grid as “Y” to enable the column filter functionality:

<panel>
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid name="grid" dataSource="EMPLOYEES1" enableColumnSort="Y" enableColumnHide="Y" enableColumnMove="Y" enableColumnResize="Y" enableColumnFilter="Y">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textLabel column="SALARY"/>
          </column>
          <column headerLabel="Hire Date">
            <textLabel column="HIRE_DATE" dateFormat="DD/MM/YYYY"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
          <column headerLabel="Department" sortable="N">
            <textLabel column="DEPARTMENT_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Adding Filters
Run the application again, open the interactive menu of the “First Name” column and move your mouse cursor on the “Filters” option, note that a child menu displaying a single filter field appears. Enter “v” and focus out from this field to apply the filter. Note that since “First Name” is a varchar2 column, a wilcard filter is applied and all employees whose first name contains the “v” character are listed.

Enter “v” to the filter field

Focus out from the field to apply the filter

Open the interactive menu of the “Salary” column and move your mouse cursor on the “Filters” option, note that a child menu displaying three filter fields and three icons (greater than, lesser than and equal) appears.

Enter 3000 as the value of the “greater than” filter field, note that when you focus out, the filter applies and the grid lists all employees whose first name contains “v” and whose salary is greater than 3000.

Enter 3000 as the value of the “greater than” filter field

Focus out to apply the filter

Enter 6000 as the value of the “lesser than” filter field, in this way, employees having a salary greater than 6000 are eliminated.

Enter 6000 as the value of the “lesser than” filter field

Employees having a salary greater than 6000 are eliminated

Enter 7000 as the value of the “equal” filter field, note that when you focus out, the “greater than” and “lesser than” values are automatically cleared and only the employee having a salary equal to 7000 is listed.

Enter 7000 as the value of the “equal” filter field

Employee having a salary equal to 7000 is listed

Open the interactive menu of the “Hire Date” column and move your mouse cursor on the “Filters” option, note that a child menu displaying “After”, “Before” and “On” options appears. In fact, these options have exactly the same functionality with the “greater than”, “lesser than” and “equal” filter fields that are displayed for a number column.

“After”, “Before” and “On” options

Removing a Filter

You can remove a filter either by deleting its value or unchecking the “Filters” checkbox existing on the interactive column menu.

Applying Functions on Grid Columns

The enableColumnFunctions attribute of the grid component allows you to apply an aggregate function on any column. When a function is applied on a column, Formspider automatically evaluates all remaining visible grid columns as the members of the group by clause.

The availability of the functions depend on the data type of the datasource column used in the grid column:

varchar2 number date clob blob
Count X X - - -
Sum - X - - -
Average - X - - -
Min - X X - -
Max - X X - -
Percent of Total Count X X - - -
Percent of Total Sum - X - - -

Limitations & Usage Notes

  • A function cannot be applied on a grid column if:
    • it’s not bound to a datasource column
    • it’s bound to datasource column with CLOB or BLOB datatype
    • it contains a radioButton, checkBox, hyperLink, comboBox or htmlRenderer component
  • When a function is applied to a grid column, Formspider automatically re-queries its datasource to apply the function.

Edit “mainPanel” XML and set the enableColumnFunctions attribute of the grid as “Y” to enable this functionality:

<panel>
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid name="grid" dataSource="EMPLOYEES1" enableColumnSort="Y" enableColumnHide="Y" enableColumnMove="Y" enableColumnResize="Y" enableColumnFilter="Y" enableColumnFunctions="Y">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textLabel column="SALARY"/>
          </column>
          <column headerLabel="Hire Date">
            <textLabel column="HIRE_DATE" dateFormat="DD/MM/YYYY"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
          <column headerLabel="Department" sortable="N">
            <textLabel column="DEPARTMENT_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again, assume that you aim to find the maximum salary for each department, among the employees who are hired in 2006 . To achieve this:

  • Open the interactive menu of the “Hire Date” column, select 01/01/2006 from the “After” filter option and 31/12/2006 from the “Before” filter option to filter all employees who are hired in 2006.
  • Open the interactive menu of the “Salary” column and hide “First Name”, “Last Name”, “Hire Date” and “Email” columns. Then, select “Max” from the “Functions” section, note that the maximum salary for each department is calculated.

Filter data, hide necessary columns and select “Max” from the “Functions” section

The maximum salary for each department is calculated

Removing a Function
You can remove a function by unchecking the “Functions” checkbox existing on the interactive column menu.

Removing Grid Interactions

It’s possible to remove all filters, functions and sorting interactions that are applied to a grid using the api_component.clearGridInteractions API.

In your datasource schema, create a package called “interactiveReport_pkg” and open your newly created “interactiveReport_pkg” package in your favorite PL/SQL Editor. Add a procedure named “clearGridInteractions” and ensure that the procedure is exposed in the package specification.

procedure clearGridInteractions is
begin
  api_component.clearGridInteractions('mainPanel.grid');
end;

In Formspider IDE, expand the “Actions” accordion, select the “Actions” node, click the “+” button to create a new action. Alternatively you may right click the “Actions” node and select “New” from the pop-up menu. The “New Action” dialog shows up.

Enter “clearGridInteractions” as the action name and “interactiveReport_pkg.clearGridInteractions” as the procedure. Click “OK” to save your action.

Creating “clearGridInteractions” action

Open the “mainPanel” in the editor. Add a button labeled “Clear Grid Interactions” having a buttonPress event triggering the “clearGridInteractions” action, the panel XML should look like:

<panel>
  <tableLayout>
    <row height="20">
      <cell childWidth="150">
        <button label="Clear Grid Interactions">
          <events>
            <buttonPress action="clearGridInteractions"/>
          </events>
        </button>
      </cell>
    </row>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid name="grid" dataSource="EMPLOYEES1" enableColumnSort="Y" enableColumnHide="Y" enableColumnMove="Y" enableColumnResize="Y" enableColumnFilter="Y" enableColumnFunctions="Y">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textLabel column="SALARY"/>
          </column>
          <column headerLabel="Hire Date">
            <textLabel column="HIRE_DATE" dateFormat="DD/MM/YYYY"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
          <column headerLabel="Department" sortable="N">
            <textLabel column="DEPARTMENT_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again, randomly sort and apply functions and filters on grid columns. Note that when “Clear Grid Interactions” is pressed, the grid datasource is re-queried and all interactions are removed.