Data Objects

Data objects are the tables, views, methods, stored procedures, functions and custom SQL that you want to make accessible for reports.

All existing data objects are listed in the Main Menu under data.png Data. All data objects that are added or edited will be displayed in the dataobjects.pngObjects tab.

  • To add a new data object click dataobjects.pngObjects in the Main Menu then click the Add button.
Note: Data objects can be added quickly using Automatic Database Discovery.
  • To edit a data object either double click it or select it and click the Edit button.
  • To clone a data object select it and click the Clone button. A clone data object inherits all base attributes from its parent except its alias and ID, which must be entered manually in the settings. For more information please see the Cloning Data Objects section below.
  • To delete a data object select it and click the Delete button.
  • To save changes click the Okay or Apply button.

Each data object has the following properties. Properties that are required are marked with an asterisk.


Select the data object’s source from the first drop-down. In the second drop-down select a data object.

Note: This will display all the of the source’s tables, views, methods, stored procedures, and functions.
  • To add custom SQL click the Add Custom SQLcustomsql.pngbutton next to the Data Sources drop-down. For more details see Custom SQL Objects.
Note: The name of tables or views may not contain the following characters: { } [ ] , . %


The user friendly name for the data object. The alias will be displayed to end-users.

Note: An alias may not contain the following characters: @ { } [ ] , . %

Unique Key Fields*

The columns which uniquely identify a row.


The ‘folder’ used to group related data objects. Sub-categories can be created by entering the category name followed by a backslash then the sub-category name.


A unique value for the data object. IDs are required when creating multiple data objects with that have the same name but come from distinct data sources. IDs can also be used to optimize Web Service and .Net Assembly calls. For more information see Data Object IDs.

Note: While IDs are not a required property of data objects, it is highly recommended that they are used.


Parameters that are passed to stored procedures, table functions, Web Services or .NET assembly methods. Clicking in the drop-down will bring up a menu. Click the Add button and select the parameter from the drop-down list. For more information see Parameters, Stored Procedures and Web Services & .NET Assemblies.

  • Parameter values are passed in the order in which they are listed in the data object. It is critical to ensure that the order is correct.

Tenants Columns

Specify which columns contain tenant information and link the parameters accordingly.

  • This setting is used to filter data when multiple users’ information is held within the same table or view, and a column holds information identifying each user. Exago will only retrieve the rows where the column value matches the corresponding parameter.

Column Metadata

Specify any columns that should not be filterable, visible, or that should be read as a specific data type. See Column Metadata for more information.

Schema Access Type

Specify how Exago should retrieve the schema for the data object. There are three possibilities:

  • Default – Follow the global Schema Access Type setting in Other Settings.
  • Datasource – Queries the data source for the schema.
  • Metadata – Reads the schema from the stored metadata.
Note: For more information see Retrieving Data Object Schemas.

As of v2019.1+, the Schema Access Type defaults to Metadata. The optimizations made to the configuration architecture in this version alleviate the performance issues previously associated with loading large configuration files—metadata playing a primary role in increasing configuration file size. Exago now strongly encourages the full use of metadata in combination with the new configuration optimizations as this will reduce the frequency in which the application queries databases.

Note: For more information regarding the performance enhancements of v2019.1+, please see this article.

Filter Dropdown Object

Specify an alternative data object to be queried when a user clicks the value drop-down in the Filters menu. This setting is most likely to be used when the data object is a Stored Procedure, Web Service, or .Net Assembly that takes more than a few seconds to return data. In this scenario a table or view can be designated to increase performance.

Note: The Filter Dropdown Object must have a column with the same name as each column in the main data objects.

Suppress Sort and Filter (v2018.1+)

If this object is a programmable object (Stored Procedure or .NET Assembly Method), select whether to suppress the application sorting and filtering for report execution queries. Enable this if the programmable object uses Programmable Object parameters to do sorting and filtering in code, and application processing would be redundant and unnecessary. This can allow for better performance for programmable objects.

Note: If the application requires sorting/filtering in memory, it will not be suppressed. For instance, a report with this object has a cross-source join, an advanced join, a Cartesian join, or a formula sort or filter. Multiple tables from the same PDO can be joined with suppressed filtering & sorting if this setting is enabled for all of them.


Cloning Data Objects

As of v2019.1+, data objects may be cloned via the Admin Console. A cloned data object inherits all its based attributes from its parent except its alias and ID, which must be entered manually in order to create the clone.

To clone a data object, right-click it and select Clone.


Enter a new Alias and ID for the cloned object.


The cloned object will then appear in the Objects menu:


Functionality of Cloned Data Objects

  • A cloned data object is linked to its parent object. Any changes made to a parent data object will be reflected in its clone.
  • Deleting the parent data object will also delete its linked clone.
  • Clones do not inherit joins from their parent objects. Joins need to be manually created between clones and other data objects.
  • Clones cannot be distinguished from other data objects within the report designers. There is no visual or functional difference between a cloned data object and a normal object outside of the Admin Console.
  • A cloned data object cannot itself be cloned or duplicated.


Stored Procedures

Stored Procedures offer the ability to use high level code to modify the data set before it is sent to Exago.

Stored procedures must know what sorts and filters the user has set and whether to return the schema, a single column, or the entire data set.

To accomplish this:

  • Use the Call Type, Filter, Column and Sort Parameters in the Programmable Object Settings. These parameters will be passed from Exago to identically named parameters in the Stored Procedure.
  • Additional parameters may be passed by setting them in the data.png Data > dataobjects.png Objects tab.
Important: As noted above in the Parameters section, parameter values are passed in the order in which they are listed. It is critical to ensure that the order listed is correct.


Important Note for SQL Server:

SQL Server has an attribute called ‘FMTONLY’ that must be handled by all stored procedures.

'FMTONLY' has two possible values:

  • ON: The stored procedure will only return the column schema. However all IF conditional statements are ignored and all of the code will be executed. This setting will fail if the stored procedure contains any temp tables.
  • OFF: The stored procedure returns all of the data and the column schema. The stored procedure will correctly execute IF conditions.

The 'ON' setting will cause problems if there are IF conditions in the procedure; however, only using the 'OFF' setting will hurt performance if the Call Type Parameter in the Programmable Object Settings is not used.

The following example demonstrates how to use the Call Type, Column, Filter and Sort Parameters to maintain efficiency.

Note: For SQL Servers, FMTONLY is set to OFF.
ALTER PROCEDURE [dbo].[sp_webrpt_person]
@callType INT, --optional but should be implemented for efficiency
and dropdown support
@columnStr varchar(1000), --optional; used for limiting data for efficiency
@filterStr varchar(1000), --optional; used for limiting data for efficiency
@fullFilterStr varchar(1000), --optional; used for limiting data for efficiency
@sortStr varchar(1000) –-optional; may improve performance a bit if used
SET NOCOUNT ON --for performance reasons
SET FMTONLY OFF --force procedure to return data and process IF conditions

declare @sql varchar(2000)
declare @columnInfo varchar(1000)
declare @orderbyClause varchar(1000)
if @callType = 0 --return schema; don't need to return any rows
     set @sql = 'select * from vw_webrpt_person where 0 = 1'
if @callType = 1 --return all data for execution
    set @orderbyClause = ''
    if @sortStr is not NULL AND @sortStr <> 'null' set @orderbyClause = ' ORDER BY ' + @sortStr
    set @sql = 'select' + @columnStr + ' from vw_webrpt_person where ' + @filterStr + @orderbyClause
if @callType = 2 --return filter dropdown values; limit # rows to some value
     set @columnInfo = '[' + @columnStr + ']'
     set @sql = 'select top 100 ' + @columnInfo + ' from vw_webrpt_person where ' + @columnInfo + ' >= ' + @filterStr + ' and ' + @fullFilterStr + ' order by ' + @columnInfo



Table Value Functions

Table Value Functions can be used as data objects. Any available table value functions of a data source will be displayed in the data.png Extensions tab under functiontab.png Functions. Exago handles table value functions similar to views and tables except it will pass any parameters set in the data.pngData > dataobjects.pngObject tab or in the Programmable Object Settings.

For more information, see Table-Valued Functions.


Custom SQL Objects

Exago can use custom SQL as data objects. Parameters can be embedded in these SQL statements to enable you to change the statement at runtime.

To add or edit a Custom SQL Object click the Custom SQLcustomsql.pngbutton and a dialog box will appear.

Data Object Name

The name of the data object to be displayed in the Administration Console.

Data Source

The data source that will be sent the SQL.


Select the parameter you want to embed in the statements. Use the Add button to move the selected parameter into the SQL statement where your cursor is located. Parameters may also be added manually between @ symbols (ex. @userId@).

Use the Testsqltest.pngbutton to verify that the SQL statement is correct.

Press Okay to save the SQL statement or Cancel to close the dialog without saving.


Data Object Macros

Macros can be embedded in Custom SQL Objects to make them even more dynamic. Each macro allows for different SQL to be used according to the circumstances in which the data object is being called. Below are the details and examples of available macros.


(string trueCondition, string falseCondition)


Includes the trueCondition if a user is executing a report. Includes the falseCondition if otherwise.


SELECT * FROM vw_webrpt_optionee IfExecuteMode("WHERE [State] = 'CT'","")



(string dataObjectName, string trueCondition, string falseCondition)


Includes the trueCondition if dataObjectName exists inside the full Exago SQL statement to the data source. Includes the falseCondition if otherwise.


SELECT * FROM vw_webrpt_optionee IfExistReportDataObject("fn_webrpt_grant", "JOIN ON fn_webrpt_grant...", "")


Column Metadata

Column metadata refers to the properties of each column in the data objects. Normally Exago gets the metadata for each column directly from the data source, however, in some cases it may be helpful to override or add additional information to the metadata.

Note: Column metadata will override culture settings.

To modify the metadata of a column, select it and click the Add button or double click it. Enter a Column Alias or use the Data Type, Filterable, and Visible drop-downs to set the desired properties.

Click the Read Schema button to quickly create column metadata for each column in the data object. Alternatively, as of v2019.1+, metadata can be built in bulk across all of the existing data objects in a data source. For more information please see the Building Metadata in Bulk section below.

To remove metadata for a column, select it in the right panel and click thedelete2.pngDelete Row button.

To save changes to Column Metadata, click the Okay button. To discard the changes, click the Cancel button


The following properties of each column can be modified:

Column Alias

The name of the data field that the end-users see.

Column Description


Data fields can have description text added. If the data field is hovered over in a selection screen in the Report Designer, the description text will pop up:


Admins can add description text to data fields on an application-wide level. To do so, using the Admin Console, expand the dataobjects.png Objects tab, and double-click on the desired data object, or select it and press Edit. Then, in the object menu click on the Manage Metadatacolumnmetadatamenubutton.png button next to the Column Metadata field. This will open the Column Metadata dialog.

Double-click on the desired data field, or click-and-drag it to the Selected Columns pane, or select it and press the Add button. You have two options for adding description text: Using Plain Text or editing the Language File.

Plain Text

Hover-text can be added verbatim in the Column Description field. In-line HTML tags like <b> can also be used if desired. Press Okay when done, then Apply the change.

Language File


You can also add description elements to the language file, and reference them in the Column Description field.

In the language file, add new elements to the <AdminObjects> section using the following format:

<element id="uniqueIdentifier" tooltip="Description Text"></element>

HTML tags must be encoded like so:

  • Encode < as &lt;
  • Encode > as &gt;
  • Encode " as &quot;

For example, the following tooltip string encodes "<b>Description</b> Text", which displays as "Description Text."

<element id="uniqueIdentifier" tooltip="&lt;b&gt;Description&lt;/b&gt; Text"></element>

After adding the element to the language file, add the ID string to the Column Description field. Press Okay when done, then Apply the change.

Data Type

The type of data Exago should treat the data field as any of the following valid types:

  • String, Date, Datetime, Time, Int, Decimal, Image, Float, Boolean, and Guid


Whether this field can be used to filter reports. The available options are:

Field Used as a Report Filter Used as an Interactive Filter
All (True)

Yes, for the:

  • Advance Report designer
  • Express Report designer
  • ExpressView designer
  • Dashboard designer
  • Scheduler

Yes, for the:

  • Report Viewer
Dynamic (False) No

Yes, for the:

  • Report Viewer
Static (v2017.1.2+)

Yes, for the:

  • Advance Report designer
  • Express Report designer
  • ExpressView designer
  • Dashboard designer
  • Scheduler
None (v2017.1.2+) No No



If set to False, the data field will not be listed in the Sorts menu.

Admins can now indicate whether data fields should appear in the Sorts menu using the Sortable dropdown.

Note: This toggle does not prevent data fields from being sorted by. Users can still enter the data fields manually as a formula, or use the data fields within a sort formula.

Date Format String


The format of datetime data fields. Allows datetime data fields to be properly pulled out of generic string columns. Implemented in order to support datetime metadata for vertical tables.


If set to False, the data field will not be listed for users.

Sort and Group-By Value (v2016.3+)

Specify a custom formula by which columns should be sorted and grouped by the application.

This field allows admins to specify how columns should be sorted and grouped by the application. By default, Exago will sort (and group) columns based on the data in the column. You can use this metadata field to specify different data by which the column should be sorted.

Note: As of v2019.1.1+, Sort and Group-By Values are not honored when the data field is being used within a formula sort. This change was made to prevent unexpected behavior from occurring in these instances.

For example, you may have a custom column Employees.FullName like the following:

{Employees.FirstName} & ' ' & {Employees.LastName}

By default, Exago would sort this field on the full string. You may want to sort on just the LastName, instead. In Sort and Group-By Value, enter {Employees.LastName}, and the column will sort on LastName.

Another common example is sorting a Month field by the numeric representation of the month instead of the name. Since this value accepts any valid Exago formula (except aggregates), custom functions can also be used.

Note: The sort-and-group field must have a one-to-one relationship with the data field. Otherwise, unexpected behavior could occur.

Custom Columns


Custom columns are a way to add columns to Exago that don't exist in the database. This is completely transparent for the users; they can then use them like any other column. New data fields can be created from composite or interpreted data fields. You could even use a formula to create data from scratch. Admins often use custom columns to make popular formula sorts available on an application-wide level.

Admin Console

To add a custom column using the Admin Console, expand the dataobjects.pngObjects tab and double-click on the desired data object, or select it and press Edit. Then, in the Object menu click on the columnmetadatamenubutton.png button next to the Column Metadata field. This will open the Column Metadata dialog box.

Press the Add New button. Enter a name for your data field in the dialog box.

Data Type, Column Alias, and Column Value are required fields. In the Column Value field, press the formula button to bring up the Formula Editor.

Press Okay when done, then Apply the change.

Config File

To add a custom column by editing the config file, open the config file in a text or xml editor. Data objects are <entity> elements. Locate the entity and add a new <column_metadata> element:

<col_description>First and Last Name</col_description>
<col_value>{Employees_0.FirstName} &amp; ' ' &amp; {Employees_0.LastName}</col_value>
Note: Fields in bold are required.

<col_source>ExagoFormula</col_source> is static. This is the same for every <column_metadata>.

In <col_value> and <col_sortandgroupbyvalue>, data fields are identified by their ID, not their alias.

Acceptable values for <col_type>: string, date, datetime, time, int, decimal, image, float, boolean, guid, currency.

Save the config file when done, and restart the web server.


There are a lot of options for what kinds of data fields you can create:

Transform or interpret an existing data field:

  • Right({Employees.SocialSecurityNumber},4)
  • Month({Orders.OrderDate})

Combine multiple data fields together:

  • {Employees.FirstName} & ' ' & {Employees.LastName}

Create new data from scratch:

  • Random(0,65536)
    Uses a custom function

And much more!

Note: Custom columns cannot be used as filters or within aggregate formulas.


Retrieving Data Object Schemas

Many of the dialogs throughout Exago require schema information like column name, data type, and so on. To enhance performance, schema information can be stored as column metadata. Exago can then read the column metadata instead of querying the data source.

Note: While storing the schema as column metadata improves performance, updates to the column metadata will be required whenever columns are added, removed, or re-titled.

Building Metadata in Bulk

As of v2019.1+, the use of full metadata is strongly encouraged. In combination with the configuration optimizations that may be implemented in this version, the use of column metadata will reduce the frequency in which the databases are queried for schema information. Furthermore, to make the process of adding metadata simpler, this information can now be built in bulk through the Admin Console.

To build metadata in bulk, right-click a data object source under the dataobjects.pngObjects menu and click Bulk Metadata.


Building metadata in bulk on the Northwind data source

Exago will then process the information for each data object under that data source and add metadata information for each field within these objects. The Schema Access Type of each object will automatically be set to Metadata for each object.

A new Bulk Metadata tab will open in the Admin Console displaying the processing information.


The metadata information that has been processed and added to each data object may then be edited or removed in the Column Metadata menu.

Please note, however, that if metadata has not been added to or is not enabled for a data object, the following warning will appear next to the data object in the Admin Console. This warning serves to further encourage the use of metadata and notify the system administrator that this data object will query the database each time it requires schema information.



Building Metadata Manually

In versions prior to v2019.1, data sources are queried for schema information by default. This process, however, may cause performance issues if the data sources take a considerable amount of time to return the schema. To avoid these performance issues, metadata may be added manually via the Column Metadata menu.

For Exago to retrieve schema information from the metadata:

  1. In Other Settings, set Schema Access Type to Metadata. This will force Exago to get all schema information from the metadata for all data objects.
Note: Alternatively this setting can be overwritten for individual data objects by setting the Schema Access Type property.
  1. For each data object open the Column Metadata menu.
    1. Click the Read Schema button. A message will appear asking you to confirm you want to continue. Click Okay.
    2. Click Okay to close the Column Metadata menu.
    3. Press Okay or Apply to save the data objects.
Note: Other metadata options such as aliasing can still be utilized.


Data Object IDs

There are three ways in which you can utilize data object IDs.

Adding Multiple Data Objects with the Same Name

IDs are used distinguish data objects that have the same name but come from different data sources. When adding multiple data objects with the same name, make sure each data object has a unique ID.

Avoiding Issues from Changes to Object Names

Providing IDs for all the data objects will avoid issues if the name of the underlying tables, views, or stored procedures, is changed.

Calling a Single Web Service/.Net Assembly/Stored Procedure

Web Services, .Net Assemblies, and Stored Procedures comprise a group called Programmable Objects. These objects can retrieve parameters from Exago and the host application in order to control what data is exposed to the user.

Generally for Web Services and .Net Assemblies each data object calls a distinct method. Similarly each Stored Procedure is its own data object. By using data object IDs a single method/stored procedure can be called. This method can then return data or schema based on the data object ID.

To call a single Web Service/.Net Assembly/Stored Procedure:

  • Provide a name for Data Object ID Parameter Name in Programmable Object Settings
  • Create a method/procedure in your Service/Assembly/Procedure that utilizes the object ID parameter to return the appropriate data/schema.
  • For each data object:
    • Select dataobjects.png Object in the Main Menu and click the Add button
    • Select the single Service/Assembly/Procedure
    • Provide an Alias and an ID for the object
    • Select the key columns
    • Click Okay or Apply to save the object.

Example: This stored procedure uses the object ID parameter (@objectID) to return different data/schema information for different object IDs.

ALTER PROCEDURE "dbo"." Exago_Example"   @callType INT,  @objectID nvarchar(max)  AS  
FMTONLY OFF    if @objectID = 'Produce'   begin      if @callType = 0      begin          SELECT
   CategoryID = 1001      
else if @callType = 1      begin          SELECT
else if @callType = 2      begin          SELECT
end  if @objectID = 'Orders0'   begin      if @callType = 0      begin          SELECT
CustomerID = 0      
else if @callType = 1      begin          SELECT
else if @callType = 2      begin          SELECT


Reading Images from a Database

Exago can read images from a database and load them directly into a cell of a report. When images are stored in a database as a binary string there are two ways that Exago can load them into a report.

  1. In the Administration Console edit the data object that contains the images. Open the Column Metadata menu and for the image column set Data Type to Image. Next, simply place the data field containing the images into the desired cell of a report. Upon execution the images will be loaded into the cell.
  2. Place the data field that contains the images into the LoadImage function. Upon execution Exago will interpret the binary and load the images into the cell.

Hidden Article Information

Article Author
Alexander Agoado
created 2015-12-30 17:32:54 UTC
updated 2019-06-13 20:15:38 UTC

main menu, data, automatic database discovery, stored procedures, table value functions, sql objects, column metadata, retrieving data object schemas, data object ids, custom, sort by, group by, sortable, column, metadata, description, visible, filterable,
Have more questions? Submit a request