Dataset View
The Dataset view enables you to view and edit the basic information about an overall Dataset. Datasets are used to easily query any information within the entire LemonEdge platform and return those results in a format you want. Each Dataset create a SQL Wrapper [see here] for retrieving the data and interacting with it throughout the LemonEdge platform.
Dataset Features
Datasets enable you to query all the underlying data in the LemonEdge platform in an easy and intuitive manner. Datasets can query all entities and all their fields, along with all associated permissions, all associated history, and even includes the ability to re-query queries you've created with Datasets themselves.
Tip
This last feature allows you to easily build up a standard library of Datasets that any user can easily report from and use Datasets to re-query however they like without having to expose them to any of the underlying workings of your data structures.
Each Dataset is responsible for creating a single set of data in any shape you require. That data is accessible through a SQL Wrapper the Dataset is responsible for automatically maintaining. This allows you visibility into how everything is being designed for your query, but also to utilise the deep integration into the platform SQL Wrappers allow - including combining your dataset with custom sql wrappers.
Note
If you are trying to union, or otherwise merge, multiple sets of data together you can create each set of data using a Dataset and utilise our other reporting tools such as Query Runners or Venn Sets for combining them all.
Dataset Properties
Datasets have the following top level properties:
| Name | Description |
|---|---|
| [Required] [Key] Name |
The unique user friendly name of this dataset |
| [Required] [ReadOnly] [Automatically Maintained] SQL Wrapper |
The link to the SQL Wrapper that allows you to execute this dataset, and otherwise interact with it throughout the whole LemonEdge platform. This is automatically created and maintained by the system. You can leave it blank when creating a dataset. |
| Description | A user friendly description of this dataset |
| Top | Indicates the dataset should always only return the following number of rows |
| Skip | Indicates the dataset should skip the specified number of rows (requires an order in the Columns) before returning the remaining amount |
| Distinct | Indicates all rows returned must be distinct removing any duplicate values |
Commands
Note
A Dataset has the standard set of commands and functions when being viewed from a grid. See our standard grid functionality.
Tip
Datasets can be exported and imported as packaged xml files to transfer configuration across systems. The export includes the SQL Wrapper, and any other Datasets that are sub-queries, all protected using our standard versioning. Again this is available from the standards grid commands.
Datasets also have the following custom commands:
Public/Private
This toggles the Dataset between being publicly viewable by everyone and private to just this user. Only public Datasets can be integrated into the LemonEdge platform as custom display grids, and viewable by other users.
Execute And Export Query Data
This executes the Dataset you've designed and pops-up and parameters you need to enter first. It then executes the query as a task service allowing you to download the results whenever into excel, csv, etc.
This is a shortcut to actually performing the same operation on the SQL Wrapper itself. see here
Parameters
Parameters allow you to define any additional parameters you would like this Dataset to have. These parameters can be mandatory, hidden with automatic formulas for values, and used throughout your query to filter/etc.
Note
By default all Datasets, and SQL Wrappers, in the system have the following set of system parameters:
- Account - The account id to run this query under. Not visible to the user, can't be bypassed and is automatically populated by the system at run time.
- Canvas - The canvas this query is to run within. Not visible to the user, can't be bypassed and is automatically populated by the system at run time depending what canvas the user is operating in.
- Team - The team to use to run this query for permission checking. Not visible to the user, can't be bypassed and is automatically populated by the system with the team the user is currently running in
- As Of - A date/time to run this query "as of" in the past. Visible to the user, is optional, and allows them to run the query using the current data or the data as of any point in the past.
The parameters for your Dataset have the same functionality as SQL Wrapper Parameters [see here]. Specifically for Datasets they have the following properties:
| Name | Description |
|---|---|
| [Required] Name |
The user friendly name for this parameter |
| Description | A user friendly description for this parameter that is shown as a tooltip to the user |
| SQLType | The underlying sql type for this parameter in the function or procedure it writes for you. This should match the underlying sql type of the field itself if you are mapping it to one. i.e. if this holds an ID to a record then it should be of type uniqueidentifier. |
| IsNullable | Indicates if the field can be left blank with no input from the user |
| Max Length | If the field is a type of text field (nvarchar, etc) this indicates the max length for that field. Leaving this blank would be the equivalent of varchar(max) - presumably unnecessary for a text input parameter though. |
| Precision | If this field is a decimal this holds the precision value. i.e decimal(16,8) |
| Scale | If this field is a decimal this holds the scale value. i.e. decimale(16,8) |
| Visible | Indicates if this parameter should be visible to the user. By default some system parameters are not visible, such as those for permissions. This gives you the ability to make a field invisible and auto populate the value through formulas. |
| EntityType | If this field maps to a field in the system you can specify the entity type of that field here. Doing so will mean the system will understand automatically how to display a control for this parameter. For instance if you select Tasks as Entity Type and ID as Entity Property Name, the system will provide a popup of all available tasks for this parameter. |
| Entity Property Name | The property this field maps to within the selected EntityType. |
| Format | Applies any formatting to the display of this control. [see here for more info] |
| [Required] [Key] Param Name |
The unique parameter name. Must begin with an '@' symbol. |
| Required | Indicates if the user must enter a value for this parameter |
| Default Value | Provides a hardcoded default value for this parameter |
| Formula Value | Provides a formula that can be run at execution time to determine a default value for this parameter |
This is displayed in a popup grid to the user, and has all the standard commands available to all standard grids including the ability to move the parameters up/down in order.
Warning
Note: The standard default parameters should always remain at the top in the system order of Account, Canvas, Team, As Of Date.