Venn Set View
The Venn Set view enables you to view and edit basic information about an overall Venn Set. Venn Sets are used to combine multiple queries of the same shape into one merged dataset. Each Venn Set creates an underlying SQL Wrapper [see here] for retrieving the data and interacting with it throughout the LemonEdge platform.
Venn Set Features
Venn Sets enable you to merge multiple queries together in an easy and intuitive manner. Venn Sets can query all entities and all their fields, along with all associated permissions and history, and even includes the ability to query any underlying SQL Wrapper, allowing Venn Sets to merge other Venn Sets too.
Tip
This last feature allows you to easily build up a standard library of SQL Wrappers, using Datasets, that any user can then easily incorporate in to a Venn Set without having to expose them to any of the underlying working of your data structure. They can simply merge datasets together to see the data the way they wish to.
Each Venn Set is responsible for creating a single set of data in the shape you require, and ensuring all data being merged into this single set conforms to the same shape. This combined single set of data is accessible through a SQL Wrapper the Venn Set 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 Venn Set with custom sql wrappers.
Note
Venn Sets are not responsible for creating data in any format you require. Rather they are responsible for merging existing sets together. You can use SQL Wrappers, or Datasets to create underlying queries of data in any way you like that you can then combine together using Venn Sets.
Venn Set Properties
Venn Sets have the following top level properties:
| Name | Description |
|---|---|
| [Required] [Key] Name |
The unique user friendly name for this venn set |
| [Required] [ReadOnly] [Automatically Maintained] SQL Wrapper |
The link to the SQL Wrapper that allows you to execute this venn set, 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 venn set. |
| Description | A user friendly description of this venn set |
Commands
Note
A Venn Set has the standard set of commands and functions when being viewed from a grid. See our standard grid functionality.
Tip
Venn Sets can be exported and imported as packaged xml files to transfer configuration across systems. The export includes the SQL Wrapper, and any other Venn Sets/Data Sets/SQL Wrappers that are referenced within it, all protected using our standard versioning. Again this is available from the standards grid commands.
Venn Sets 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.
Parameters
By default all Datasets, Venn Sets, 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 Venn Set have the same functionality as SQL Wrapper Parameters [see here]. Specifically for Venn Sets 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.