Dataset Queryable Items View

Dataset Queryable Items allow you to specify the data you want to include in your query and the hierarchical relationships that tie the data together. The Items you can query in a Dataset come from 3 main sources; Entities, History and Queries themselves. The last one allowing you to re-query queries in a dataset.

Dataset Queryable Items Features

The items you can query in a Dataset are represented here in a hierarchical view that easily shows at a glance the structure of the data you are retrieving. This is manipulated using our standard TreeView for easily creating hierarchical structures. You can specify the relationships between different queryable items and how they join together. The Items you can query come from the following main sources:

  • Entities
    This allows you to query all entities in the system, and all their fields, including custom entities and those created through our API.
  • Audit History
    This allows you to query all of the audit history associated with any entity. The audit history itself contains the following main properties:
    • User Modified By
    • DateTime stamp
    • ChangeType
      • Update
      • Insert
      • Delete
    • Property Name
    • Old Value
    • New Value
    • Old Link Value
    • New Link Value
    • Queries
      This allows you to query any public SQL Wrapper in the system, allowing you to incorporate that set of data into your query whether it is created from a Dataset itself or a hand written SQL query.
Tip

Being able to query SQL Wrappers (which every Dataset creates one of anyway to retrieve their data [see here for more info]) allows you to easily build up a library of re-usable queries. These can be used by any user to get results but also to query themselves without having to understand any of your underlying data structure.

Note

Datasets are responsible for defining a single set of data, as such you can't create multiple Queryable Items that aren't related to each other. They must all be related in some way together (hence the hierarchical view) otherwise they are really independent sets of data. If you want to merge various sets of data together you can create those sets individually using DataSets and merge them using our other tools such as Query Groups or VennSets.

Dataset Queryable Items Properties

Whenever you create a Dataset Queryable Item the system pops up a dialog box allowing you to choose the properties of the item you want to query. This has the following properties:

Name Description
[Required]
Queryable Item Type		 The type of item you want to query. This can be an Entity, History, or Query.
Queryable Item The item of the specified type that you want to query
Queryable Item Option Some Queryable Items contain sub options. Permissions is an example of this. If you select a Permission item to query of the Entity type, this will display a set of options for which entity type permission you want to query
Replicate For All If the queryable item type is a type that has sub Queryable Item Options, such as permissions, then this indicates you want to replicate your query for every option and union the results together.
[Required]
Name		 This is the user friendly name of the queryable item as it will appear in the treeview.
This makes looking at the data structure very easy at a glance. It is also the name the user will see if they are able to open a row of data from this item type
Join Type If this Queryable Item is a child of any other Queryable Item, then this specifies the type of join this queryable item will have to the overall dataset:
  • Exclusive
    Equivalent to Inner Join in SQL
  • Inclusive
    Equivalent to Outer Join in SQL
  • CrossJoin
    Equivalent to Cross Join in SQL

You can always edit these properties for any Queryable Item by selecting the

Properties command.

Queryable Item Joins

Whenever you create a new Queryable Item that is a child of another Queryable Item the system will automatically evaluate possible relationships between those two item types and create appropriate default joins to relate the two items together.

You can always click on the Joins command to override and change these joins for any Queryable Item. You can join any Queryable Item to any other Queryable Item through properties and parameters (if the item has them). Joins have the following properties:

Name Description
[Required]
Source Data Set Queryable Item		 This is the item you want the selected Queryable Item to join to. In this description the selected Queryable Item is the Target of the join.
The drop down allows selection of any Queryable Item in the tree structure, or the global parameters of the Dataset itself.
[Required]
Source Queryable Item Field		 A property within the selected Source Dataset Queryable Item that you want to use to join to the selected Queryable Item.
If the Source is global parameters it will allow a selection from all your parameters. If the source is another queryable item it will provide a list of all fields that queryable item contains.
[Required]
Target Queryable Item Field		 This is the property on the selected queryable item that you want to use to join to the source queryable item.
This will list all fields against this queryable item, and if it has parameters (is a query queryable item) those will also be listed allowing you to pass values into the parameters as part of the join. Doing so will force the join to be an outer apply join.

You can add as many joins as you require here, link to as many different queryable items as required, and edit any default joins the system created to link the two queryable items together by default.

Commands

Note

A Dataset Queryable Item View has the standard set of commands and functions when being viewed from a tree view. See our standard tree view functionality.

Queryable Items also have the following custom commands:

  • Parameters
    Opens the popup dialog box allowing you to edit the properties of the Queryable Item such as the type and name. This is described in more detail above.
  • Joins
    Opens the popup dialog box allowing you to edit the joins of the Queryable Item. This is described in more detail above.