The Dataset column view allows you to specify precisely which columns you would like returned in your Dataset. This allows you to indicate if columns should be hidden, ordered, aggregated, etc. in an intuitive and easy to follow way.
Dataset Column Features
The columns displayed here are the actual columns that will be returned by your resulting query. Datasets are responsible for writing an actual SQL Wrapper to perform the query specified in the Dataset configuration. The columns specified here are directly the columns that will also be visible in the SQL Wrapper itself [see here for more info].
Columns have column properties, behaviour properties (such as aggregate options), and grid display properties.
You can also create custom calculation columns that perform custom sql you want embedded in the calculation.
All of these columns then construct the shape of the data returned by this dataset. Most importantly the system completely understands all aspects of the parameters, query and return columns so the dataset can be completely integrated into the platform as if it's a standard created view.
Creating Dataset Columns
Whenever you have a Queryable Item selected you will be able to use the "New SQL Type Wrapper" command to add fields from that QueryableItem to the columns of this dataset.
When you click the command (only available if you have a Queryable Item selected) the system will popup a dialog box listing all the available fields within that Queryable Item. You can multi-select as many fields as you would like to add and hit Ok. The system will automatically add all those fields as columns to the dataset in the correct order.
It will also automatically configure the columns to have the correct type, and settings, depending on what the field you added is. For instance it will set up the Name, Entity Type, Entity Property Name, Enum Property Name, SQL Type and other properties for you.
This makes it incredibly easy to just add the fields you want from the QueryableItems you have selected into the set of column results.
Custom Calculations
You can also create custom calculation fields. To do this you can select
Add Calculated Field, instead of new sql type wrapper.
This will create a new column that doesn't have fields auto-populated. You will need to specify the name, any entity type/property relationships, and the SQL Type (defaults to nvarchar(50)).
You can then select the
Edit Calculation command when you have that column selected. This will popup a sql editor that allows you to enter any custom sql for this column that you like.
You can refer to any other column in your sql using the format {ColumnName}. So for instance if you have two columns called TotalA and TotalB you can create a custom sql calculation field with the sql being: IsNull({TotalA}, 0.0) + IsNull({TotalB}, 0.0) Which will sum both columns together to give you a grand total.
You must make sure that the sql you enter has a return type that matches the type you specified for the column itself. If it doesn't your dataset will fail to return results and will throw an error.
It's important to note that this functionality is also restricted and certain keywords if used (such as 'drop', etc) will result in an error and the dataset will fail to run. You should restrict this functionality to only the root account with the correct permissions.
Dataset Column Properties
The columns of a dataset, are the same as the columns of a SQL Wrapper [see here for more info], and have the following properties:
Name
Description
[Unique] [Required] Name
The name of this column. This is the name used as column headers in csv exports, and is the mnemonic you can use to refer to this columns value in custom sql formulas.
Column User Friendly Label
This is the column name the user sees when the results are displayed in a grid, or other presentation format.
[Key] Index
The order this column will appear in the result set. This can be moved up and down using the standard grid commands.
Parent Index
This column is hidden by default but can be made visible to edit if needed. The parent index is used for columns that display labels for unique identifiers. If you have a column that holds a unique identifier key, you can set the label of that column to have a parent index of the uniqueidentifier column. The system will then know how to display the label in the grid for the user rather than a list of uniqueidentifiers the user won't be able to recognize.
Description
A user friendly description of this column
Visible
Indicates if this column is visible by default when the results are displayed in a grid. The user can always make the column visible if it is hidden, and will always see the column in result exports too.
Exclude From Result Set
Indicates this column is not actually to be included in the result set. Can be used to refer to the column itself in custom calculations but make sure it isn't included in the actual results as a column itself.
Aggregate
Indicates this column should have its results aggregated. Can be:
None
Average
Count
Max
Min
Sum
If this is anything but None, then all other columns that have an aggregate of None will be used as part of the Group By for the result set.
Has Custom Aggregate
Indicates this column has a custom calculation that applies a custom sql clr aggregate function to the results. Use the Edit Calculated Field command to edit the custom calculation to call your clr sql aggregate function. This refers to SQL Functions (which define the clr sql functions that can be used) and is only available as an option if the AllowCLRSQLFunctions flag in the GlobalEnv table is set to 1. All other non-aggregated columns will be set to group by in the result set just as they would if an Aggregate value other than None had been selected.
EntityType
The type of entity this column has data that originates from.
Entity Property Name
The property of the Queryable Item that this column is populated from
Enum Property Type
If this property is an enum type this holds the type of enum to be used for displaying in grids.
Format
Applies any custom formatting you want to the result when displayed in a grid. See here for more info.
Footer Aggregate
Indicates when displayed in a grid that this column should be totaled automatically and displayed on the grid itself. This will also happen when the data is arbitrarily grouped by the user.
Group Header
Indicates this column should belong to a header group when displayed in a grid
Sub Group Header
Indicates this column should belong to a sub header group within a group header when displayed in a grid
Group By
When displayed in a grid this indicates this column should automatically be grouped in the grid
Freeze Up To
By default the first column is frozen when displayed in a grid. Here you can specify a different column that should be frozen when scrolling through the grid horizontally.
Part Of Row Header
When scrolling vertically the scroll bar displays a row header quick view. This indicates if this column should be part of that template.
Is Default Search
Indicates when the user searches the grid that this column should be included in that search by default
SQL Type
Indicates the actual underlying sql type this column holds
Max Length
The max length to use if this column is a text type such as nvarchar. If blank it is the equivalent to nvarchar(max).
Precision
If this is a decimal sql type this holds the precision amount as in decimal(18,2)
Scale
If this is a decimal sql type this holds the scale amount as in decimal(18,2)
Commands
A Column has the standard set of commands and functions when being viewed from a grid. See our standard grid functionality.
As a Column has a sequence the standard grid allows you to move the items up and down changing their order.
The Column grid also has the following custom commands:
Add Calculated Field Used to add a new calculated field to the set of result columns. Described in more detail above.
Edit Calculated Field Used to edit the sql of a calculated field in the set of result columns. Described in more detail above.
