SQLWrapper Results

SQL Wrapper Results is a dynamic grid that displays allows execution of, and displays all results for, any SQL Wrapper in the system including automatically generated ones such as those created from Datasets. This provides a consistent and easy method for viewing and interacting with results of data.

SQL Wrapper Result Features

The result grid is capable of interacting with, and displaying the results of any SQL Wrapper in the system.

The grid first dynamically adds columns (including hidden ones) according to the columns, and their order, as they are defined in the SQL Wrapper itself. The grid is created according to the following SQL Wrapper column features:

  • The columns appear in the order they are defined in the SQL Wrapper
  • The "Label" is used as the column header on the grid for each column
  • If the column holds a uniqueidentifier field (which would display as unknown codes to the user) and has a "Parent Index" holding a label property set then the system will display the label in the column instead of the code.
    If the Entity Type and Entity Property are set to the ID property of an entity in the system, then the label will appear as a hyper link that the user can click to open that item from
  • If the column is not marked as visible it will be included in the grid but hidden by default. Users can still make the column visible or include it in any exports of the data.
  • Any "Format" options will be applied to the display of the data
  • If the Footer Aggregate option is set the the grid will display grand totals for the column, and they will also appear on any group bars when data is grouped by any columns either by default or by the user dragging a column to group by it
  • If the Group Header of the column is set then the column will have that header as well above it's name as part of a larger group
  • If the Sub Group Header value is populated the column will have a Group Header, Sub Group Header, and Name, giving the grid 3 column header rows for the whole grid.
  • If you want the grid to automatically group by a column the GroupBy field specifies that. The grid will group all the data into sections for that column. You can next as many groups as you like and they will be nested in the order the columns appear in the SQL Wrapper column definition
  • If Freeze Up To is set for any column other than the first one, then the Freeze bar will appear beside that column. The user can always drag and move it to specify different columns being frozen when they scroll horizontally across the grid.
  • If Part Of Row Header is set then this columns value will appear in the row header tooltip that is shown beside the vertical scroll bar when scrolling through large amounts of rows quickly.
  • If the Is Default Search is set then this column will be automatically included in any search filter the user applies to the grid.

The properties for this grid appearance is described here.

Caution

As you are editing SQL Wrappers if you make changes to the column and they aren't immediately reflected in this Results grid, you can just close and re-open the SQL Wrapper to dynamically load those changes in.

Running The Results

You can easily run the results of the SQL Wrapper by clicking the

Run command.
This will first popup a parameter dialog with the parameters that are specified in the SQL Wrappers Parameters. The system will automatically apply the correct controls for the user to select values from. If you have setup default values or formulas, then they will be evaluated and entered by default for those parameter values.

The system will then execute and display the results in the grid. If the query returns more than 1000 rows, only the first 1000 will display and the grid will automatically display paging controls for moving through the data.

Tip

This significantly improves performance (even more so if the SQL Wrapper is an Inline Function) for running and downloading your results.

In fact all the standard functionality for grids is available for the result set of your SQL Wrapper in this grid too, including:

  • Sorting
  • Filtering
  • Grouping
  • Searching
  • Paging

Whenever you apply sorting, filtering or paging the system is translating that to correct SQL statements to improve performance and the data size being downloaded.

You can also open rows that have columns that have an Entity Type set with an Entity Property of ID. This means the system can uniquely identify an item in the LemonEdge platform from the data in that column/row. If only one column has this setup then open will automatically open the data in that row of that column as a full view. If multiple columns have that setup then the Open command has a sub display of possible columns you could want to use to open the data from. It will then open the selected one.

Note

This way the user has any easy mechanism to open data from the resulting query in the actual system itself to further drill in to it.

Exporting

Exporting is always available as an option in the SQL Wrapper Results Grid.

The export, like the Run command, will first prompt for any parameters setup in the SQL Wrapper. It will then popup a dialog box allowing you to filter the results however you like and with the option to select which columns you actually want returned.

When the task is complete you will be notified and at any point you can download those results and open them in csv format, in excel, in excel templates or however you would like to view them.

Important

The real power of the export functionality is the ability to filter the results and choose the columns included in the result set. This can be of immense benefit when your SQL Wrapper returns millions of rows. Now users can easily and efficiently filter the data anyway they like first and only include the columns they actually need to work with. This means one SQL Wrapper can server the requirements of multiple teams use cases.

UI Integration

The SQL Wrapper Results grid is a view you can utilise within any layout you are custom designing. As long as a SQL Wrapper is public you can drag that from the Layout Designer and incorporate the query into the standard view the user sees for any item. You can then configure the parameters to be context sensitive to the layout you are designing.

For instance a simple query that returns all roles a user is in, could be dragged and dropped in to a custom user layout. The parameter could then be linked to the user the layout is showing so whenever someone looks at a user with that layout they will see you sql wrapper running custom sql to show all the roles that user belongs to. But best of all it looks, and is, a consistent, indistinguishable and real part of the LemonEdge system.

You can also configure the main menu to link to a SQL Wrapper using the standard Export Data command. You can configure the properties of the button to run a specific SQL Wrapper that will open immediately as a result grid when the user clicks on it.

This provides an excellent and easy mechanism to have reports on a users menu that they can just run with a single click and work with immediately. This seamlessly integrates with other tools of our workflow technology too.