Exago Inc. FAQs

The following REST Web Service API endpoint paths are available. All calls require the following headers:

Content-Type: application/json

Accept: application/json

Authorization: {type} {authstring}

/rest/Batch v2018.1.1+

POST

/rest/Sessions

POST

GET

/rest/Sessions/{sid}

GET

PATCH

DELETE

All of the following calls require the Session ID URL parameter : sid={sid}

Example

GET /rest/Settings?sid={sid}

/rest/DataSources

GET

/rest/DataSources/{Id}

POST

GET

PATCH

/rest/Entities

POST

GET

/rest/Entities/{Id}

GET

PATCH

DELETE

/rest/Entities/{Id}/Fields

GET

/rest/Entities/{Id}/Fields/{Field Id}

GET

PATCH

/rest/Folders/{Name}

POST

DELETE

/rest/Folders/Rename

POST

/rest/Functions

POST

GET

/rest/Functions/{Id}

GET

PATCH

DELETE

/rest/Joins

POST

GET

/rest/Joins/{Id}

GET

PATCH

DELETE

/rest/Parameters

POST

GET

/rest/Parameters/{Id}

GET

PATCH

DELETE

/rest/Reports/List

GET

/rest/Reports/Execute/{Type}

POST

/rest/Roles

POST

GET

/rest/Roles/{Id}

GET

PATCH

/rest/Roles/{Id}/DataObjectRows

GET

PATCH

/rest/Roles/{Id}/Entities

GET

PATCH

/rest/Roles/{Id}/Folders

GET

PATCH

/rest/Roles/{Id}/Settings

GET

PATCH

/rest/ServerEvents

GET

/rest/ServerEvents/{Id}

GET

DELETE

/rest/Settings

GET

PATCH

Sessions are the means by which REST persists changes through multiple API calls. A REST session allows for you to easily encapsulate a group of user-specific changes. Most REST changes will only affect the current session, with folder changes being the exception. However, once an API session is committed and Exago is launched, a user will be able to make such changes as their role permits.

Note: All requests require basic request headers. In the following examples, headers are omitted forclarity.

Entering the API

A POST /Sessions call should always be the first made when entering the API. This creates a session, which contains a Session Id and an AppUrl.

Session Id

The Session Id is a GUID which uniquely identifies theinstance of Exago you're working in.

Example:

3c04c155-3317-4dab-a2ee-09572e3a8e44

This Id is passed as a URL parameter to all other method calls.

AppUrl

The AppUrl is a string which encodes the session information in a format readable by the host application.

Example:

ExagoHome.aspx?d=fk7hInneiNI7yUR1n8fCjjbTuIdTWgQrj8AaUlzO8l7K5JMaf7DhbZxzaSAXthP3bWLA%2fzG4AtXApO6pGoBpzQ%3d%3d&showerrordetail=true

The AppUrl consists of three components: the Home Page, an alphanumeric string, and the ShowErrorDetail URL parameter.Retrieving the AppUrl via GET /Sessions/{Id} should always be the last call made when finishing in the API.

Once you have committed your API changes and want to launch Exago, append the AppUrl to the end of your Exago base URL and launch the full URL string in a browser:

http://yoursite.com/exago/ExagoHome.aspx?d=fk7hInneiNI7yUR1n8fCjjbTuIdTWgQrj8AaUlzO8l7K5JMaf7DhbZxzaSAXthP3bWLA%2fzG4AtXApO6pGoBpzQ%3d%3d&showerrordetail=true

An AppUrl can only be used once. Launching an API session will permanently close it, and apply yourcustom variablesinto a new user session in the host application.

Session JSON

The Sessions endpoint allows for creation and deletion of sessions and access to some general settings. It also allows you to specify and configure the action that the session will take when launched. To launch a report in the full user interface, it needs to be set in Sessions as the output report.

Sessions are represented as JSON objects with the following properties:

Name

Type

Writable

Description

Id

string

no

The unique Id of this session ("sid")

AppUrl

string

no

The AppUrl of this session; changes to reflect any modifications made to this session in the API

Page

string

yes ("ExagoHome")

The Exago home page

ApiAction

enum

yes ("Default")

Api Action, to take when this session is launched

ExportType

enum

yes

Export Type, if ApiAction is 'ExecuteReport'

ShowTabs

boolean

yes (true)

Whether the tab bar should be shown

ShowErrorDetail

boolean

yes (true)

Whether detailed error messages should be shown

ReportSettings

Report

yes

A report with optionalsorts & filtersif ApiAction is 'ExecuteReport' or 'EditReport'

ApiKey

string

no

Used when instantiating the JavaScript Api, if applicable.

Example:

{

"AppUrl": "ExagoHome.aspx?d={alphanumeric}&showerrordetail=true",

"Id": "{sid}"

"Page": "ExagoHome",

"ApiAction": "ExecuteReport",

"ExportType": "Html",

"ShowTabs": true,

"ShowErrorDetail": true,

...

}

Report JSON

If you want to edit or launch a report, specify a report in theReportSettings property. Reports are represented as JSON objects with the following properties:

Name

Type

Writable

Description

Id

string

no

The unique Id of the this report

ReportPath

string

yes

Full path from root folder to this report

SortsResource

Sorts

yes

Anysorts to apply to this report

FilterItems

array of Filter

yes

Any filters to apply to this report

IsError

boolean

no

Whether there were any validation errorswhen loading thisreport

ErrorList

array of ReportValidationErrorType

no

If IsError == true, a list of errors encountered when loading this report. Possible Errors

Example:

"ReportSettings": { "Id": "ad0f442e-275b-48a0-893f-0b1255ee7880",

"ReportPath": "Reports\\Product Sales Report",

"SortsResource": null,

"FilterItems": [], "IsError": false, "ErrorList": null

}

Note:Windows file paths are delineated by double-backslashes: \\

Sorts

Sorts can be added to the report, either in addition to, or replacing its existing sorts. This only affects the current session, and does not edit the base report. The collection of sorts to be added is represented as a JSON object with the following properties:

Name

Type

Writable

Description

ReplaceFlag

string

yes (false)

Whether to replace all existing report sorts

SortItems

array of Sort

yes

List of sorts to apply to this report

Example:

{ "ReplaceFlag": false,

"SortItems": [

{

"Id": "Products.ProductName",

"Asc": false

},

{

"Id": "Categories.CategoryName",

"Asc": false

} ...

]

}

Sort

Each sort in the SortItems property is represented as a JSON object with the following properties:

Name

Type

Writable

Description

Id

string

required

Data field to sort on; format: "EntityName.ColumnName"

Asc

boolean

yes (false)

Whether sort direction is ascending

Example:

{

"Id": "Products.ProductName",

"Asc": false

}

Filter

Filters can be added tothe report in addition to its existing filters. This only affects the current session, and does not edit the base report. Each filter to be added is represented as a JSON object with the following properties:

Name

Type

Writable

Description

Name

string

required

Data field to filter on; format: "EntityName.ColumnName"

EntityName

string

yes

Entity name to filter on (deprecated)

ColumnName

string

yes

Column name to filter on (deprecated)

DataType

enum

yes ("string")

Data Field Type

Operator

enum

yes ("EqualTo")

Filter Operator Type

Values

array of strings

yes

Values to filter with

AndFlag

boolean

yes (true)

Whether this filter should be AND-ed with the next (versus OR-ed)

Prompt

boolean

yes (false)

Whether this filter should prompt the user for a value (Advanced or Express Reports)

GroupWithNext

boolean

yes (false)

Whether this filter should be grouped with the next (Advanced or Express Reports)

Note:Filter values that are DateTime strings must be able to be parsed by the .NET method DateTime.Parse using the current thread culture.

Example:

{

"Name": "Categories.CategoryName", "DataType": "string", "Operator": "OneOf",

"Values": [

"Beverages", "Seafood", "Produce"

], "AndFlag": true, "Prompt": false, "GroupWithNext": false

}

Create a Session

POST /rest/sessions

Available parameters

Name

Type

Description

config

string

Specify which config file to create the session with. Format: CustomConfigFile

Caution: All config files must usethe same Temp path.Otherwise, you may experience errors whenloading sessions.

Using curl

curl http://{webservice}/rest/sessions -X POST ^

-d "{'Page':'Reporting','ApiAction':'Home','ShowTabs':false}"

Example response

Status: 201 Created

Location: /{webservice}/rest/Sessions/{sid}

{

"AppUrl": "Reporting.aspx?d={alphanumeric}",

"Id": "{sid}",

"Page": "Reporting",

"ApiAction": "Home",

"ExportType": null,

"ShowTabs": false,

"ShowErrorDetail": true,

"ReportSettings": {

"ReportPath": null,

"SortsResource": null,

"FilterItems": null

}

}

Caution: Currently, if a new config .xml file and temp path are passed via a POST session, the API session .enc file will first be written to the original temp path of the default config file rather than the newly assigned path. This limitation prevents this action from being properly completed. However, a new config .xml file may still be passed with the default temp path, allowing this limitation to be bypassed.

Show a Session

GET /rest/sessions/{sid}

If the session is launched, the session object is deleted and can no longer be viewed.

Note:Unlike other resources, the {sid} is passed as part of the endpoint string, not as a URL parameter.

Using curl

curl http://{webservice}/rest/sessions/{sid} -X GET

Example response

Status: 200 OK

{

"AppUrl": "Reporting.aspx?d={alphanumeric}",

"Id": "{sid}",

"Page": "Reporting",

...

}

Edit a Session

PATCH /rest/sessions/{sid}

Onlysupply the properties to be edited.If the session is launched, the session object is deleted and it can no longer be edited.

Note:Unlike other resources, the {sid} is passed as part of the endpoint string, not as a URL parameter.

Using curl

curl http://{webservice}/rest/sessions/{sid} -X PATCH ^

-d "{'Page':'CompanyHome'}"

Example response

Status: 200 OK

{

"AppUrl": "Reporting.aspx?d={alphanumeric}",

"Id": "{sid}",

"Page": "CompanyHome",

...

}

Delete a Session

DELETE /rest/sessions/{sid}

If a session is deleted, the AppUrl can no longer be used. If the session is launched, the session object is deleted and it can no longer be edited.

Note:Unlike other resources, the {sid} is passed as part of the endpoint string, not as a URL parameter.

Using curl

curl http://{webservice}/rest/sessions/{sid} -X DELETE

Example response

Status: 204 No Content

Launch a Report with Sorts and Filters

POST /rest/sessions

Set the ApiAction to ExecuteReport, and specify a report, with any optional sorts and filters, in the ReportSettings property. If you want to save the report to a file, set the ExportType to the desired file type.

Using curl

curl http://{webservice}/rest/sessions -X POST ^

-d @sessionSettings.txt

sessionSettings.txt

"{'ApiAction':'ExecuteReport','ExportType':'html','ReportSettings':{'ReportPath':'Sales\\Products','FilterItems':[{'Name':'Categories.CategoryID','DataType':'Integer','Values':["123"]}],'SortsResource':{'ReplaceFlag':true,'SortItems':[{'Id':'Categories.CategoryID','Asc':true}]}}}"

Response

Status: 201 Created

Location: /{webservice}/rest/Sessions/{sid}

{

...

"ApiAction": "ExecuteReport",

"ExportType": "Html",

...

"ReportSettings": {

...

"ReportPath": "Sales\\Products",

"SortsResource": {

"ReplaceFlag": true,

"SortItems": [

{

"Id": "Categories.CategoryID",

"Asc": true

}

]

},

"FilterItems": [

{

"Name": "Categories.CategoryID",

"DataType": "Integer",

"Values": ["123"]

}

] }

}

To launch the report in Exago, take the AppUrl, and append it to the URL to your Exago installation to create the full URI path:

http://{website}/{exago}/{AppUrl}

Example

http://yoursite.com/reporting/homepage?d={alphanumeric}&showerrordetail=true

There are many variables in the APIs that require a specific type of value from a set of values. These sets of values are called enumerated types, and each value in a type is called an enumerator, or enum. Enumerated types are no more than a set of named values, which act as Constants in the API.

When an API variable requires an enum, it must be set to one of the constants that are defined in the specified enumerated type. For example, if a variable requires a Report Type enum, it accepts the values Advanced, Express, Dashboard, Chained or ExpressView. Other values will cause an invalid type error.

Some API variables are not enumerated types, but may require a set of constants regardless. These constants have no numeric equivalent.

The following article is a reference guide that lists the most important enumerated types and lists of constants that are used throughout the API.

Caution: Numeric values should not be used as enum types as these values are not constants.

ApiAction

Enum Name

Numeric Value

Description

Default

0

If a report is loaded, this is equivalent to ExecuteReport. Otherwise, this is equivalent to Home.

Home

1

Open the home page.

ExecuteReport

2

Execute the loaded report (specify format with wrExportType).

EditReport

3

Open the loaded report in the appropriate editor.

NewAdvancedReport

4

Open the New Advanced Report Wizard.

NewReport

4

Deprecated: Use NewAdvancedReport instead.

NewCrossTabReport

5

Open the New CrossTab Report Wizard.

NewExpressReport

6

Open the New Express Report Wizard.

NewDashboardReport

7

Open theDashboard Designer for a new dashboard.

NewExpressView

8

Open the ExpressView Designer for a new ExpressView.

ScheduleReport

9

Open the Schedule Report Wizard for the loaded report.

ScheduledReportsManager

10

Open the Scheduled Report Manager.

Cache Visibility Level

Enum Name

Numeric Value

Description

User

1

Cache visibility set at the UserId parameter level

Company

2

Cache visibility set at the CompanyId parameter level

Global

3

Global cache visibility

Code Language

Note: Each value has multiple names.

Enum Name

Numeric Value

Description

c# cs csharp

0

C Sharp

js javascript

1

JavaScript

vb visualbasic

2

Visual Basic

Data Source Type

Enum Name

Numeric Value

Description

MsSql

0

Microsoft SQL Server

MySql

1

MySQL

ODBC

2

ODBC

Postgres

3

PostreSQL

Oracle

4

Oracle

DB2

5

IBM DB2

Informix

6

IBM Informix

Assembly

7

.NET Assembly

WebService

8

Web Service

File

9

Excel file

MsOlap

10

OLAP

Data Field Type

Enum Name

Numeric Value

Description

String

0

String

Date

1

Date, i.e.day, month, year

DateTime

2

Date & time

Time

3

Time, i.e. hour, minute, second (am/pm)

Integer

4

Integer number

Decimal

5

Decimal number

Float

6

Floating-point number

Bit

7

Bit

Guid

8

Globally (universally) unique identifier, aka. UUID

Image

9

Image

Currency

10

Currency

Data Object Type

Enum Name

Description

Assembly

.NET Assembly method

File

Excel file

Function

User-defined function

Procedure

Stored procedure

SqlStmt

Custom SQL statement

Table

Data table

View

SQL view

WebSvc

Web Service method

MdxStmt

MDX query (OLAP)

DashboardLayoutFit

Enum Name

Numeric Value

Description

All

0

All tiles are resized horizontally and vertically to fit on the canvas

Same as selecting Scale to Screen in the Dashboard Designer Canvas Format menu

Width

1

Tiles are resized horizontally to fit within a specified canvas width

Same as selecting Specify Height in the Dashboard Designer Canvas Format menu

None

2

The tiles are not resized. The canvas has a specified height and width

Same as selecting Specify Height and Width in the Dashboard Designer Canvas Format menu

ExportType

Enum Name

Numeric Value

Description

Html

0

Run in the browser, using the appropriate viewer.

Excel

1

Export as an Excel file.

Pdf

2

Export as a PDF file.

Rtf

3

Export as an RTF file.

Csv

4

Export as a CSV file.

Word

5

Export as a Word file.

Default

6

Default type specified by theloaded report; if not specified, default type specified by the current config.

Filterable Type

Enum Name

Description

All

Field supports all filter types

True

Field supports all filter types

None

Field cannot be filtered

False

Field cannot be filtered

Dynamic

Field supports only dynamic (interactive) filters

Static

Field supports only static (report) filters

Filter Operator Type

Enum Name

Numeric Value

Description

EqualTo

0

Data value is equal to the filter value

NotEqualTo

1

Data value is less than the filter value (number, date)

LessThan

2

Data value is less than or equal to the filter value (number, date)

LessThanOrEqualTo

3

Data value is greater than the filter value (number, date)

GreaterThan

4

Data value is greater than or equal to the filter value (number, date)

GreaterThanOrEqualTo

5

Data value is not equal to thefilter value

StartsWith

6

Data value starts with the filter value (string, number)

NotStartsWith

7

Data value does not start with the filter value(string, number)

EndsWith

8

Data value ends with the filter value(string, number)

NotEndsWith

9

Data value does not end with the filter value(string, number)

Contains

10

Data value contains the filter value(string, number)

NotContains

11

Data value does not contain the filter value(string, number)

Between

12

Data value is between the two filter values

NotBetween

13

Data value is not between the two filter values

OneOf

14

Data value is equal to one of the filter values

NotOneOf

15

Data value is not equal to any of the filter values

FolderStatus

Enum Name

Description

Exists

This folder exists

DoesNotExist

This folder does not exist

Created

This folder was successfully created

Deleted

This folder was successfully deleted

Renamed

This folder was successfully renamed

Join Type

Enum Name

Description

Inner

Inner join

LeftOuter

Left outer join

RightOuter

Right outer join

FullOuter

Full outer join (left outer join + right outer join)

Join Relation Type

Enum Name

Description

OneToOne

One-to-one relationship

OneToMany

One-to-many relationship

Parameter Type

Enum Name

Numeric Value

Description

String

0

String

Date

1

Date, i.e.day, month, year

Integer

2

Integer number

Decimal

3

Decimal number

Range Limit

Enum Name

Description

None

Disable range limiting SQL.

LimitOffset

SELECT ... LIMIT rangeSize OFFSET offsetRow

OffsetFetch

SELECT ... OFFSET offsetRow ROWS FETCH NEXT rangeSize ROWS ONLY

RowNumberWithTop

SELECT TOP endingRow ROW_NUMBER() Over(Order By (...)) as wrRowNum ... as wrRowNumbered WHERE wrRowNumbered.wrRowNum BETWEEN startingRow AND endingRowField

RowNumber

SELECT ROW_NUMBER() Over(Order By (...)) as wrRowNum ... as wrRowNumbered WHERE wrRowNumbered.wrRowNum BETWEEN startingRow AND endingRowField

RowNumberNoAlias

SELECT ROW_NUMBER() Over(Order By (...)) as wrRowNum ... WHERE wrRowNum BETWEEN startingRow AND endingRowField

Report Type

Enum Name

Numeric Value

Description

Advanced

0

Advanced Report (CrossTab Reports are considered Advanced Reports)

Standard

0

Deprecated: Use Advanced instead.

Express

1

Express Report

Dashboard

2

Dashboard

Chained

3

Chained Report

ExpressView

5

ExpressView

ReportValidationErrorType

The types of report validation errors that can happen. Not all validation error types are applicable to all report objects.

Enum Name

SortFormulaSyntaxError

FilterFormulaSyntaxError

DataObjectNotFound

SortDataFieldNotFound

FilterDataFieldNotFound

LinkedDataFieldNotFound

MinMaxFilterDataFieldNotFound

JoinDataObjectNotFound

JoinNotFound

JoinDataFieldNotFound

ChartDataFieldNotFound

MapDataFieldNotFound

CellDataFieldNotFound

RowGroupNameNotFound

RowGroupFormulaNameNotFound

ChartCellIdNotFound

MapCellIdNotFound

MergedCellsAcrossSections

CrossTabIdNotFound

CrossTabCellIdNotFound

ColumnSortByFieldNotFound

ChildReportNotFound

ExpressViewColumnMissingEntity

ExpressViewColumnMissingField

ExpressViewGroupMissingField

ExpressViewFilterMissingField

ExpressViewChartMissingField

EtlReportDesignerModeNotEtl

EtlReportTypeNotAdvanced

EtlDataTypeNotSet

EtlDataTypeNotFound

EtlReportFormatInvalid

EtlCellBlank

EtlHeaderCellValueDuplicate

EtlPossibleDataTypeError

EtlCellSpan

None

ReportParameterNameUsed

ScheduleManagerViewLevel

Enum Name

Numeric Value

Description

User

1

Filter schedules by current UserId parameter

Company

2

Filter schedules by current CompanyId parameter

All

3

Show all schedules

TreeShortcut

Enum Name

Numeric Value

Description

Default

0

The report will take the default action defined in the system's configuration.

Run

1

The report will run in the Report Viewer

Export

2

The report will be exported

The toolbar contains the buttons and menus used to modify the report. Modifications can include aesthetic formatting, inserting formulas and images, linking reports, and much more.

Your system administrator has the ability to hide or show specific menu options and toolbar items. Some items may not be available to you.

Google Maps

Advanced Report Designer toolbar in v2019.2+

Advanced Report Designer toolbar

pre-v2019.2

The toolbar begins with the Settings dropdown menu sometimes referred to as the Cog Wheel menu. This menu controls changes, such as renaming and filtering, that affect the entire report. All other buttons on the toolbar require that a cell (or cells) in the design grid be selected.

Settings menu

Rename: Allows you to change the name and folder where the report will be saved.

Description: Allows you to enter or change the description of the report.

Categories: Allows you to change the Categories which are included on the report. The article on Categories has more information.

Sorts: Allows you to change the Sorts on the report. The article on Sorts has more information.

Filters: Alows you to change the Filters on the report. The article on Filters has more information.

Template: Allows you to setup document templates and forms. The article on Templates has more information.

Options: Allows you to change behavior of the report. The article on Options has detailed information about each option.

General: Allows you to change things such as report export types, filter execution windows and page formatting. Review the General Options article for full details.

Report Viewer: Allows you to change things such showing the report grid, the toolbar and interactive filters and sorts in the report viewer. Review the Report Options article for full details.

Advanced: Allows you to change advanced properties of the report.

Joins: Allows you to define how categoires on a report are related to one another. The article on Joins has more information.

Parameters: Allows you to define reusable system variables, called Parameters, on the report. The article on Report Level Parameters has more information.

Show Generated SQL: Allows you to view the generated SQL query which retrieves the data used on the report. The article on Show Generated SQL has more information.

Saving Reports

The report can be saved by clicking the Save icon. The report will also be saved anytime it is executed.

Undo/Redo

Any action on a report can be undone by clicking or pressing CTRL + Z. Undone actions can be redone by clicking or pressing CTRL + Y.

Font & Alignment Options

The text of each cell can be formatted using dropdown menus and buttons in the toolbar. A cell or multiple cells must be selected for these tools to be used.

Font

To change the font, use the searchable dropdown menu. The font names appear in the style that they represent.

Text size can be controlled using the up and down arrows on font size spinner.

The Bold, Italics and Underline icons make the selected font bold, italicized, and underlined, respectively. Clicking the icon will turn it slightly gray to indicate the function is turned on.

Color

To change the text color, click the Foreground Color icon to show the foreground color selector. Select a color or enter an RGB value. Hexadecimal HTML color codes may be added in the Hex textbox, or decimal values may be entered into each of the R, G and B textboxes ranging from 0-255. Click the Reset button to revert to the default color. A preview of the active foreground color appears below the Foreground Color icon.

To change the cell background color, click the Background Color icon to show the background color selector. Select a color or enter an RGB value. Hexadecimal HTML color codes may be added in the Hex textbox, or decimal values may be entered into each of the R, G and B textboxes ranging from 0-255. Click the Reset button to revert to the default color. A preview of the active background color appears below the Background Color icon.

To change the text color, click the Foreground Color icon to show the foreground color selector. Select a color or enter a hexadecimal RGB value. Click the Clear icon to revert to the default color. A preview of the active foreground color appears below the Foreground Color icon.

To change the cell background color, click the Background Color icon to show the background color selector. Select a color or enter a hexadecimal RGB value. Click the Clear icon to revert to the default color. A preview of the active background color appears below the Background Color icon.

Color picker

Note: The colors on the bottom row of the new color picker can be set by a system administrator on a system-wide basis or they may be available for user preference.

Color picker

Alignment

Text can be aligned to the left, center, right or justified in the cell using the respective horizontal alignment icons. First, click the Horizontal Alignment Selector icon, and then choose from Left, Right, Center or Justified alignment. The horizontal alignment selector icon will change to show the currently selected alignment mode.

Text can be aligned to the top, center, or bottom in the cell using the respective vertical alignment icons. First, click the Vertical Alignment Selector icon, and then choose from Top, Middle or Bottom alignment. The vertical alignment selector icon will change to show the currently selected alignment mode.

Clicking on the Wrap Text icon will cause text longer than the width of a cell to wrap to new lines instead of being cut off by the cell's boundary.

Text can be aligned to the left, center, right or justified in the cell using the respective horizontal alignment icons.

Text can be aligned to the top, center, or bottom in the cell using the respective vertical alignment icons.

The Wrap Text icon will cause text longer than the width of a cell to wrap to new lines instead of being cut off by the cell's boundary.

Formatting Cells

Cells can be formatted in the Cell Format Window. To open the window, first click on a cell and then click the Format Cell icon. The window has three tabs: Number, Border, and Conditional.

Comprehensive cell formatting information including how to use each of the three tabs can be found in our Cell Formatting article.

Cell formatting can be copied using the Format Paintbrush tool. Select the cell you want to copy, click the Format Paintbrush

Merge/Split Cells

Multiple cells can be merged together by first selecting the cells and then clicking the Merge Cells icon. This is helpful when adding a chart to a report or when creating a heading, for example.

Conversely, merged cells may be split back up by selecting the cell to be split and the clicking the Split Cells icon. Only cells which have been merged can be split.

AutoSum

To quickly get a total on a Data Field, place the field in a Report or Group Footer and click the AutoSum icon. Alternatively, a sum can be created with the aggSum or Sum functions. See Formulas for more information.

Note: Do not use AutoSum on a cell with an aggregate formula such as aggSum.

Images

An image from your computer can be added to a cell using the Insert Image icon.

Functions

Complex calculations can be done using Formulas. A formula can be added to a cell by keying it in manually or using the Formula Editor. To open the Formula Editor click the Formula Editor icon.

Detailed information about formulas and how to use them can be found in the Formulas Section of the product documentation.

Chart Wizard

A chart visualization may be added to the report by first clicking on a cell and then clicking on the Chart Wizard icon to launch the chart wizard. Full details about chart types and how to use the chart wizard can be found in the article on Charts.

Gauge Wizard

A gauge visualization may eb added to the report by clicking on a cell and then clicking on the Gauge Wizard icon to launch the gauge wizard. Full details about gauges and how to use the gauge wizard can be found in the article on Gauges.

Suppress Duplicates

You can suppress duplicate values of a Data Object from being displayed. Select the cell and click the Suppress Duplicates icon.

For example, the two reports below are identical, except the secondimage has suppressed duplicates forthe customer column.

Duplicates are not suppressed

Duplicates are suppressed

CrossTab Wizard

A CrossTab Report can be added to the report by clicking on the CrossTab Wizard icon.

Linked Reports (aka Drilldowns)

A "subreport" can be linked to data on the current report by creating a link or drilldown to another report. Click on a cell and then click the Link Reports icon to link a report to that cell. Review the Drilldowns article for full details on how to create a report link (drilldown).

Linked Action Event

Action Events are custom programs which can be activated by certain actions on a report. This is an advanced feature. Consult with your system administrator about which action events are available in your system. To add an Action Event to a cell, first click the cell and then click the Linked Action Event icon.

GeoChart Wizard

GeoCharts are a way to visualize geographical report data. To insert a GeoChart into a cell, first select the cell and the click on the GeoChart Wizardicon.

GeoCharts are a legacy feature, and have been replaced with the Google Maps feature. More information about the GeoCharts feature can be found in the GeoCharts article.

Google Maps Wizard

Google Maps are a way to insert interactive maps in a report to visualize geoghraphical report data. To insert a Google Maps visualization into a cell, first select the cell and then click on the Google Maps Wizard icon.

More information about using this feature can be found in the article.

Run Report

Run Report button

Clicking on the Run Report button will execute the report and display the result in the Report Viewer.

Export

Export PDF button

Export file types button

To export the report as a downloadable file, click on the Export PDF button. You can also click on the More icon to select from the additional exportable file types.

The system administrator can make only certain file types available, so the menu may look differently depending on how your installation is configured.

Data Objects (a.k.a. "Entities") are the manner by which Exago views and accesses the tables, views, procedures, etc., from the Data Sources. Data objectsrepresent the structure of thedata, but the actual data is only accessed at report run-time.

Note. All requests require Session Id URL parameter and basic request headers. In the following examples, headers are omitted forclarity.

Data Object JSON

Data Objects are represented as JSON objects with the following properties:

Name

Type

Writable

Description

Id

string

required-create

The unique Id of this data object

Name

string

required

The display name ("alias") of this data object

Schema

string

yes

The schema of this data object

CategoryName

string

yes

The Category group of this data object

DataName

string

required

The name of this data object in its data source

DataSourceId

integer

required

The Id of the data sourceof this data object (see Data Sources )

DataType

enum

yes ("Table")

Data Object Type

SqlStatement

string

yes

The custom SQL of this data object if it is of type SqlStmt

Parameters

array of strings

yes

Any parameters for stored procedure, .NET Aassembly or Web Service API calls.

KeyColumns

array of strings

yes

The unique key fields of this data object

TenantColumns

array of Tenant Column

yes

The tenant fields of this data object

FilterDropdownObject

Filter Dropdown

yes

The filter dropdown object of this data object

Example

{

"Id": "Employees_0",

"Name": "Employees",

"Schema": "dbo",

"CategoryName": "",

"DataName": "Employees",

"DataSourceId": "0",

"DataType": "Table",

"SqlStatement": "",

"Parameters": [],

"KeyColumns": ["EmployeeID"],

"TenantColumns": [

{

"Column": "EmployeeID",

"Parameter": "UserId"

}

],

"FilterDropdownObject": {

"FilterDbName": "Employee_List",

"FilterDataSourceId": -1,

"FilterObjectType": "view",

"FilterSchema": "",

"FilterSqlStmt": ""

}

}

Tenant Column JSON

Tenant Columns are represented as JSON objects with the following properties:

Name

Type

Writable

Description

Column

string

required

The tenant data field

Parameter

string

required

The tenant parameter

Example

"TenantColumns": [

{

"Column": "EmployeeID",

"Parameter": "UserId"

}

]

Filter Dropdown JSON

A Data Object's FilterDropdown is represented as a JSON object with the following properties:

Name

Type

Writable

Description

FilterDbName

string

required

The name of this data object in its data source

FilterObjectType

enum

yes

Data Object Type

FilterSchema

string

yes

The schema for this data object

FilterSqlStmt

string

yes

The custom SQL for this data object if it is of type SqlStmt

Example

"FilterDropdownObject": {

"FilterDbName": "Employee_List",

"FilterObjectType": "view",

"FilterSchema": "",

"FilterSqlStmt": ""

}

ListData Objects

GET /rest/entities

List all the data objects in the current configuration. Output is an array of objects, each representing an individual dataobject.

Name

Type

Description

Id

string

The unique Id of this dataobject

Name

string

The display name ("alias") of this dataobject

Using curl

curl http://{webservice}/rest/entities?sid={sid} -X GET

Example response

Status: 200 OK

[

{

"Id": "Customers_0",

"Name": "Customers"

},

{

"Id": "Employees_0",

"Name": "Employees"

},

...

]

ShowData Object

GET /rest/entities/{Id}

Show the properties of the data object specified by its Id.

Using curl

curl http://{webservice}/rest/entities/{Id}?sid={sid} -X GET

Example response

Status: 200 OK

{

"Id": "Employees_0",

"Name": "Employees",

"Schema": "dbo",

"CategoryName": "",

"DataName": "Employees",

"DataSourceId": "0",

"DataType": "Table",

...

}

Create Data Object

POST /rest/entities

Requires a DataNameor a custom SqlStatement. One or more KeyColumns are required for most data types.

Using curl

curl http://{webservice}/rest/entities?sid={sid} -X POST ^

-d @newDataObject.txt

newDataObject.txt

"{'Id':'Employees_1','Name':'Employees','Schema':'dbo','DataName':'Employees','DataSourceId':0,'KeyColumns':['EmployeeID']}"

Example response

Status: 201 CreatedLocation: /{webservice}/rest/Entities/Employees_1

{

"Id": "Employees_1",

"Name": "Employees",

"Schema": "dbo",

"CategoryName": "",

"DataName": "Employees",

"DataSourceId": "0",

"DataType": "Table",

"SqlStatement": "",

"Parameters": [],

"KeyColumns": ["EmployeeID"],

"TenantColumns": [],

"FilterDropdownObject": null

}

Edit Data Object

PATCH /rest/entities/{Id}

Onlysupply the properties to be edited.

Using curl

curl http://{webservice}/rest/entities/{Id}?sid={sid} -X PATCH ^

-d "{'Name':'Staff List'}"

Example response

Status: 204 No Content

DeleteData Object

DELETE /rest/entities/{Id}

Using curl

curl http://{webservice}/rest/entities/{Id}?sid={sid} -X DELETE

Example response

Status: 204 No Content

Data Field JSON

Data fields for each object are represented as JSON objects with the following properties. Theactual data in the fields is not accessible via REST. Data fields cannot be created or deleted.However, some metadata for existing fields can be edited.

Name

Type

Writable

Description

Id

string

no

The unique namefor this data field

Name

string

yes

The display name for this data field

Type

enum

yes

Data Field Type

IsFilterable

bool (pre-v2017.2)

yes

Whether this field is filterable

const (v2017.2+)

Filterable Type

IsVisible

boolean

yes

Whether this field is visible

List Data Fields of a Data Object

GET /rest/entities/{Id}/fields

List all the data fields in the data object specified by its Id. Output is an array of objects, each representing an individual data field.

Name

Type

Description

Id

string

The unique Id of this datafield

Name

string

The display name of this data field

Using curl

curl http://{webservice}/rest/entities/{Id}/fields?sid={sid} -X GET

Example response

Status: 200 OK

[

{

"Id": "Address",

"Name": "Address"

},

{

"Id": "BirthDate",

"Name": "Date of Birth"

},

{

"Id": "EmployeeID",

"Name": "ID Number"

},

{

"Id": "FirstName",

"Name": "First Name"

},

{

"Id": "LastName",

"Name": "Last Name"

},

...

]

Show Data Field

GET /rest/entities/{Id}/fields/{Field Id}

Show the properties of the data field specified by its Id, of the data object specified by its Id.

Using curl

curl http://{webservice}/rest/entities/{Id}/fields/{Field Id}?sid={sid} -X GET

Example response

Status: 200 OK

{

"Id": "LastName",

"Name": "Last Name",

"Type": "String",

"IsFilterable": true,

"IsVisible": true

}

Edit Data Field

PATCH /rest/entities/{Id}/fields/{Field Id}

Onlysupply the properties to be edited.

Using curl

curl http://{webservice}/rest/entities/{Id}/fields/{Field Id]?sid={sid} -X PATCH ^

-d "{'Name':'Surname'}"

Example response

Status: 204 No Content

In the design grid, you can:

Add and delete rows, columns, and sections.

Enter data fields, text, and formulas.

Drag and drop fields into different sections, rows, or columns.

Interacting with the Report Viewer

Sections

Sections dictate how the data appears in a report. There are five types of sections: page, report, details, group, and repeating group.

Page Header & Page Footer

The rows in the Page Header section appear at the top of every page of a report, and the rows in the Page Footer section appears at the bottom of every page of a report. Typically, the Page Header section is used to designate column headers for a report, and the Page Footer section isused to display the page number and/or confidentiality notices for a report.

Page Headers and Page Footers are not intended to perform calculations or display data fields. For this reason, a Page Header populated with a data field will only return the first line of datain that field; a Page Footer will return only the last line of data.

Note: If you are printing a report, remember that Excel outputs do not have pages. Page Headers will appear only once at the beginning of the report. Reports run via the Report Viewerwill display Page Headers similarly unless Simulate PDF is checked in the Options menu.

Report Header & Report Footer

The rows in the Report Header appear at the beginning of a report. Typically, these rows display the title of a report. The rows in the Report Footer appear at the end of a report. Typically, the Report Footer displays grand totals and summary information for the report.

Detail

The Detail section is the main section of most reports. When the report is executed, the Details Section creates a row for each element in the Data Categories. For example, if the Detail section contains the Data Field Orders.OrderId, the report will display each Order Id on a separate row.

Group Header & Group Footer

Group Header/Footer sections require a sort on a Data Field. The rows in a Group Header section will appear above the Detail section for each unique value of the sorted Data Field. Typically, Group Header sections are used to display data as labels. For example, a report may contain a Group Header on Orders.OrderDate and display Orders.OrderId in the Detail section. The output would display each date with orders that occurred on that date below them.

The rows in a Group Footer section will appear below the Detail section for each unique value of the sorted Data Field. Typically, Group Footer sections are used to calculate subtotals. For example, a report may contain a Group Footer on Orders.OrderDate which displays the number of orders made on each date.

Note: Group Header/Footer sections can also be set to display rows for each value of a formula instead of a Data Field. (Ex. The report may be sorted on the Data Field Orders.OrderDate, but the report should show subtotals for each month. A Group Footer on the formula =Month({Orders.OrderDate}) will display rows containing subtotals for each month.)

Repeating Groups

Repeating Groups require a sort on a Data Field. Repeating Groups have their own header, detail, and footer subsections. Repeating Groups should only be used when the data has multiple one-to-many relationships and each should be rendered completely before the other.

For example, each Professor can teach multiple classes and advise multiple students. For each professor you want to see all the classes they teach and then all the students they advise.

Using Sections

Sections can be added, deleted, modified, moved, and assigned shading.

Adding Sections

Click anywhere in the Section Column.

Hover your mouse over Add Section, then select the type of section you would like to add.

Deleting Sections

In the Section Column, click on the section you want to delete.

Click Delete Section.

Modify Sections (Group Header/Footers and Repeating Groups only)

In the Section Column, click on the section you want to modify.

Click Modify Section. This will bring up a Modify Group Section Menu.

Select from the dropdown the desired Data Field for the group to use.

Click OK.

Section Shading

In the Section Column, click on the section that you want to Shade.

ClickSection Shading. This will bring up a menu.

Click Newto add a color to the shading.

Click the color box to select a color or enter a hex value.

Click OK.

Columns and Rows

Columns and rows of cells can be added, modified, or removed as described below.

Columns

To select a group of columns, hold the SHIFT key and then click the beginning and ending column.

Non-contiguous columns can be selected by holding the CTRL key and clicking the desired columns.

A column can be resized by dragging its right edge horizontally.

Clicking on a column (or selected group) will display a menu where you can:

Insert a new column.

Delete the selected column.

Set its width to be identical with the other selected columns.

Hide the selected column.

Set Column Info to make the label the column and/or make it sortable within the Report Viewer.

Sorting by Columnswithin the Report Viewer

While viewing reports in the Report Viewer, a user can click the bar at the top of the report to sort by a column. For Express Reports, this is handled automatically but must be enabled for Advanced and CrossTab Reports.

Note: Column Sorts are applied after any sorts defined in the Sorts menu.

To make a column sortable:

Click on the column and select Column Info... from the menu.

Provide the column with a label that will appear in the Interactive Report Viewer Dock.

From the Sort dropdown select the Data Field to be used for sorting, or provide a formula by clicking the Formula Editor icon.

Click on the column again to set a default sort direction.

Rows

To select a group of rows, hold the SHIFT key, then click the top and bottom rows of the area you wish to select.

Non-contiguous rows can be selected by holding the CTRL key and clicking the desired rows.

A row can be resized by dragging its bottom edge vertically.

Clicking on a row (or selected group) will display a menu where you can:

Insert a new row.

Delete the selected row.

Set the row'sheight to be automatically controlled.

Suppress the row from appearing on the report.

Insert a page break.

Cells

Cells are the containers for all the information in a report. Cells may contain data fields, formulas, text, images, charts, or links to other reports.

In application v2019.2+ cells containing data fields will have a small color-coded triangle appear in the top-left corner of the cell. Cells containing data fields from the same category will have the same color. In the figure below, the cells with fields from the Products category have a maroon triangle and the cells with data fields from the OrderDetails category have a dark green triangle.

Color coding of data fields in cells

To enter text into a cell, double-click the cell, and a text field will appear.

To select cells, either click or use the arrow keys.

Groups of cells can be selected by holding the SHIFT key and clicking on another cell. All the cells in-between the two will be selected.

Non-contiguous cells can be selected by holding the CTRL key and clicking the desired cells.

A cell can be copied by holding the CTRL key, then dragging and dropping it into a new cell.

Adjacent cells can be merged and unmerged using the Merge Cells and Split Cell icons in the Toolbar.

Using Page Breaks

For a Page Break to occur at the beginning of each element of a Data Field, place a page break on the top row of Group Header Section for that Data Field. See Sections for more detail on Group Header Sections.

Creating Collapsible Rows

A Group Section can be set to display as collapsed by default on HTML export. This causes the contents of the section to be suppressed and individually expandable for each change in Header. Collapsible rows are supported in the standard or Interactive HTML viewer. Non-HTML export formats will ignore Collapsible Rows.

Left-click on an arrow next to a Collapsible Row to expand or collapse the group.

Right-click on an arrow next to a Collapsible Row to see additional display options:

Expand will expand the selected top-level group. This elicits the same behavior as left-clicking an arrow.

Collapse will collapse the selected top-level group. The state of the sub-groups is preserved.

Expand Group will expand the selected top-level group and all sub-groups within that group.

Collapse Group will collapse the selected top-level group and all sub-groups within that group.

Expand All will expand every group and sub-group on the page.

Collapse All will collapse every group and sub-group on the page.

To create a collapsible row, click on the desired row number in a Group Header Section and select Collapse Rows in the dropdown menu.

Properties of a Collapsible Row

Collapsible Rows have the following properties when exported to HTML:

Collapsible Rows display as collapsed whenever the Report is exported or altered using Interactive HTML.

Note: Collapsed or expanded states cannot be saved to the Interactive HTML User Report preferences.

Page Breaks below a collapsed row are ignored. Expanding a collapsed group will not alter the Report pagination.

The contents of a collapsed group are searchable by the HTML search tool.

See for more information on the Interactive Report Viewer.

Some options need to be loaded by the web server at runtime. XML files at the root of the Exago host install path (appSettings.configand web.config), Web Service path (appSettings.config), and Scheduler Serviced install paths (eWebReportsScheduler.exe.config) allow for these additional options to be configred.

To apply a setting, add an XML key in the <appSettings /> node with "key" and "value" parameter pairs, in the following form:

<add key="key" value="value" />

The following article is a collection of all the possible app settings, and descriptions for their use. In general, you should not use any of these settings unless you have been specifically directed by an Exago Inc. staff member. Keys and values are case sensitive.

Web Application

web.config

sessionState

cookieless v2018.1+

Enable or disable Cookieless Sessions. Possible values

True

False

timeout & mode

Editing these attributes is not supported.

appSettings.config

sessionStorage

Determine which method to use to store session information for the user's configuration. See Using "In-Memory" Session Storage for details.

Possible values:

Diff (default) - Store the "diff" between the user's config and the base config.

InMemory - Store the entire effective config in session.

inputSanitizationMethod

Determine how aggressively to sanitize user-input data, such as report name, description, etc., against script attacks.

Possible values:

Safe (default) - Sanitizes against a blacklist of html tags and attributes.Less likely to alter user input.

Aggressive - Sanitizes against a whitelist of html tags. Potentially more harmful to user input. As of v2016.3.7+ the whitelist located at {webAppInstallDir}\Config\Other\tagwhitelist.json

ExagoConfigPath

Specifies the location of the WebReports.xml config file when storing the config in a Cloud (Azure/Amazon) location.See Installing Exago on Azure for details.

ShowErrorDetail

Set to false to disable the ability to append ?ShowErrorDetail=trueto an Exago URL to see the full text of error messages. See See Full Error Details for details.

Possible values:

False - Show the generic error text. Overrides the URL flag.

enableantiforgery v2016.2.12+

Enable protection for Cross-Site Request Forgery (CSRF) attacks.

Possible values:

True - Enable anti-forgery protection.

MinCompactionInterval (v2016.2.12+)

At specified intervals during .NET Garbage Collection, Exago will attempt to compact thelarge object heap in order to reduce memory consumption by eliminating fragmentation. This will only happen in a .NET 4.5.1+ environment.

Possible values:

Any positive integer including 0 - The minimum number of minutes between compaction cycles. 3 is the default.

-1 -Disable compaction.

SecurityProtocol v2016.3.4+

Specify which security protocols the application should use. Multiple values areseparated by commas (,).

Possible values (one or more):

Ssl3 - SSL 3.0

Tls - TLS 1.0

Tls11 - TLS 1.1

Tls12 - TLS 1.2

Tls13 - TLS 1.3 (.NET v4.6+)

Monitoring v2017.1+

Encompasses several keys determining which monitoring data is collected by the application. See Configuring Monitoring for details.

useSecurityToken v2018.1+

Enable two-factor authentication for Cookieless Sessions (the web server must enforce a secured connection).

Possible values:

True - Enable security token.

LoadAssemblyInExternalDomain v2018.2+

Load Assembly Data Sources in an external domain, as opposed to the application domain. If enabling this feature, it should also be set in the Scheduler's eWebReportsScheduler.xml and eWebReportsScheduler.exe.config files.

Possible values:

True - Enable

False (default) - Disable

useWkHtmlToImage v2019.1.5+

Revert to using the older WkHtmlToImage library for exporting visualizations. Only set to true if you are experiencing an incompatibility with Puppeteer.

In v2019.2 this flag has been changed to default to False, as Puppeteer is the default visualization rendering library in that version.

In application versions pre-v2019.2 the default visualization rendering library is wkHtmlToImage and this flag is set to True by default.

Possible values:

True Use the WkHtmlToImage library for visualization rendering

False Use Puppeteer library for visualization rendering

Scheduler Service

eWebReportsScheduler.exe.config

configFile

Specify the path and filename of the scheduler config file. Typically used in a multi-scheduler configuration where it would be desirable to maintain a global config file for all of them. If not specified, the scheduler will look for the config file in the default location.

MinCompactionInterval v2016.2.12+

At specified intervals, during .NET Garbage Collection, Exago will attempt to compact thelarge object heap in order to reduce memory consumption by eliminating fragmentation. This will only happen in a .NET 4.5.1+ environment.

Possible values:

Any positive integer including 0 - The minimum number of minutes between compaction cycles. 3 is the default.

-1 -Disable compaction.

Monitoring.CollectExecuteReportUsage v2017.1+

Specify whether to collect data about the report executions handled by this scheduler.

Possible values:

True - Enable collecting report execution data.

inputSanitizationMethod

Determine how aggressively to sanitize user-input data, such as report name, description, etc., against script attacks.

Possible values:

Safe (default) - Sanitizes against a blacklist of html tags and attributes.Less likely to alter user input.

Aggressive - Sanitizes against a whitelist of html tags. Potentially more harmful to user input. As of v2016.3.7+ the whitelist located at{schedulerInstallDir}\tagwhitelist.json

useWkHtmlToImage v2019.1.5+

Revert to using the older WkHtmlToImage library for exporting visualizations. Only set to true if you are experiencing an incompatibility with Puppeteer.

In v2019.2 this flag has been changed to default to False, as Puppeteer is the default visualization rendering library in that version.

In application versions pre-v2019.2 the default visualization rendering library is wkHtmlToImage and this flag is set to True by default.

Possible values:

True Use the WkHtmlToImage library for visualization rendering

False Use Puppeteer library for visualization rendering

Web Service

appSettings.config

ExagoRest

Add this key to enable use of the REST Web Service API.

Possible values:

True - Enable the REST Web Service API.

Security Protocol v2016.3.4+

Specify which security protocols the application should use. Multiple values areseparated by commas (,).

Possible values (one or more):

Ssl3 - SSL 3.0

Tls - TLS 1.0

Tls11 - TLS 1.1

Tls12 - TLS 1.2

Tls13 - TLS 1.3 (.NET v4.6+)

There are twodifferent ways to use the Exago APIs to perform report execution. This guidediscusses the main differences and provides examples for both types in each API.

API Action is the most comprehensive way to run executions, and supports all types of reports. This is the only way to run composite reports such as Dashboards and Chained Reports. This method launches an Exago session into the browser via URL, and thus usually requires the use of an Iframe. This also means that all interactive Report Viewer or ExpressView (v2016.3+) features are supported.

GetExecute executes reports on the back-end and returns bare HTML, JSON, CSV, or binary data. This only supports simple report types, Advanced, Express, and CrossTab Reports. Using this method you do not have to launch any visible instance of Exago for the user, and can simply use it as a calculation engine.

Note: GetExecute was previously referred to as Direct Execution.

Note: GetExecute methods are not supported by Remote Execution in versions pre-v2019.2.

Overview

Launch Method

Supported Report Types

.NET Supported Output Types

SOAP Supported Output Types

REST Supported Output Types

API Action

Redirect browser frame to Exago session URL

All types

Interactive Report Viewer, Dashboard Viewer, ExpressView designer, or PDF, RTF, Excel, CSV

GetExecute

Data returned directly to calling method

Advanced, Express, Crosstab reports, ExpressViews

HTML, CSV, PDF, RTF, Excel, JSON

HTML, CSV, PDF, RTF, Excel

HTML, CSV, PDF, RTF, Excel, JSON

API Action

API sessions in Exago have a property called action type, which determines what part of Exago should be launched when the session is opened. Action types include executing a report, loading a report into the editor, loading a report into the scheduler, opening a section of the UI, etc.

Note. API Action is also referred to as GetUrlParamString, because this is the general term for the methods which return the session redirect URL.

To tell the session to execute a report, set the action type to ExecuteReport.

Actions which load reports, such as Execute or Edit, work on the active report object. This is another property that must be set. This is done differently for each API: details are in the included examples.

NOTE. For security reasons, always set the action type and the active report explicitly. Although setting an active report defaults to execute, if a report fails to load and an action has not been specified, Exago will launch into the full UI. This could cause users to have unintended levels of access.

Once you've finished setting the session variables, call GetUrlParamString().This finalizes the session and creates a single-use URL string. This is done differently for each API; details are in the included examples. The URL is used to direct a browser window or iFrame to an Exago instance where thespecified action takes place. The user can then interact with the report like normal.

See the following sections for examples. Variable names and arguments are placeholders.

.NET

First create an API session and load a report object.

// a .net api object is a specific session; methods act on that session

WebReports.Api.Api netApi = new WebReports.Api.Api(appPath);

Report myReport = (Report)netApi.ReportObjectFactory.LoadFromRepository(reportPath);

Set the desired output type.

myReport.ExportType = wrExportType.Html;

Then save the report object back to the API.

netApi.ReportObjectFactory.SaveToApi(myReport);

Set the API action to execute.

netApi.Action = wrApiAction.ExecuteReport;

Finally, call GetUrlParamString to get the session URL.

// note: this terminates the session

string url = netApi.GetUrlParamString(homePage);

REST

When usingthe REST API, the initialization call creates a session ID string, which is a required URL parameter in all subsequent method calls. Note that the session URL is generated immediately, and altered dynamically as modifications are made to the session.

To use REST, create the session and pass the session variables. Take note of the sessionId and UrlParamString.

POST /sessions

Payload:

{

"Page":homePage,

"ApiAction":"ExecuteReport",

"ExportType":"Html",

"ReportSettings":

{

"ReportPath": reportPath

}

}

Response (some params omitted):

{

"sid":sessionId,

"AppUrl":urlParamString

}

SOAP

When usingthe SOAP API, theinitialization call creates a session ID string, which is a required parameter in all subsequent method calls.

First create an API object and initialize an API session.

// a soap api object is not a specific session; it's an accessor for the methods

WebServiceApi.Api soapApi = new WebServiceApi.Api();

// initialize a session and save the id to memory

string sessionId = soapApi.InitializeApi();

Set the active report.

// all methods require the session id parameter

soapApi.ReportObject_Activate(sessionId, reportPath);

Set the API action to execute.

soapApi.SetAction(sessionId, (int)wrApiAction.ExecuteReport, null);

Finally, call GetUrlParamString to get the session URL.

string url = soapApi.GetUrlParamString(sessionId);

GetExecute

There are four provided GetExecute methods. Each return a different data representation of the report. Not every API supports every data type; see the Overview for details.

GetExecuteHtml: Typically used for web viewing

GetExecuteCsv: Plain-text format readable by spreadsheet applications

GetExecuteData: Byte array of binary data

GetExecuteJson(v2016.3+): Typically used for asynchronous client-server communication

Since GetExecute does not requireloading the Exago UI, callingGetUrlParamString is not required. Report interactivity is also not supported when executing reports via the GetExecute functions. However, the following Server Events are triggered by GetExecute in order to ensure that the data returned accurately reflects the report design:

OnReportExecuteStart

OnReportExecuteEnd

OnAfterReportExportSuccess

See the following sections for examples. Variable names and arguments are placeholders.

.NET

First create an API session and load a report object.

// a .net api object is a specific session; methods act on that session

WebReports.Api.Api netApi = new WebReports.Api.Api(appPath);

Report myReport = (Report)netApi.ReportObjectFactory.LoadFromRepository(reportPath);

Then call the appropriate GetExecute method for the desired data type.

string reportHtml = myReport.GetExecuteHtml();

REST

When using the REST API, the initialization call creates a session ID string, which is a required URL parameter in all subsequent method calls. Note that the session URL is generated immediately, and altered dynamically as modifications are made to the session.

First create an API session. Take note of the session ID.

POST /sessions

Response (some params omitted):

{

"Id":sessionId

}

Then execute the selected report. Supported types are HTML, CSV, PDF, RTF, Excel, JSON.

POST /reports/execute/{type}?sid="sid"

Payload:

{

"ReportPath":reportPath

}

Response (some params omitted):

{

"ExecuteData":rawReportData

}

SOAP

When using theSOAP API, the initialization call creates a session ID string, which is a required parameter in all subsequent method calls.

First create an API object and initialize an API session.

// a soap api object is not a specific session; it's an accessor for the methods

WebServiceApi.Api soapApi = new WebServiceApi.Api();

// initialize a session and save the id to memory

string sessionId = soapApi.InitializeApi();

Set the active report.

// all methods require the session id parameter

soapApi.ReportObject_Activate(sessionId, reportPath);

Then call the appropriate Report_GetExecute method for the desired data type. Supported methods are Report_GetExecuteHtml and Report_GetExecuteData.

string reportHtml = soapApi.Report_GetExecuteHtml(sessionId);

The Feature/UI settings allow administrators to hide various features in the user interface. As each heading indicates, settings may apply to specific report types or the entire application.

Available Report Types

These settings enable/disable report types.

Allow Creation/Editing of Express Reports

Enables/Disables the Express Report wizard.

Allow Creation/Editing of Advanced Reports

Enables/Disables the Advanced Report wizard and report designer.

Allow Creation/Editing of CrossTabs

Enables/Disables the CrossTab Report wizard and Insert CrossTab button in the report designer.

Allow Creation/Editing of Dashboards

Enables/Disables the Dashboard designer.

Allow Creation/Editing of Chained Reports

Enables/Disables the Chained Report wizard.

Allow Creation/Editing of ExpressViews

Enables/Disables the ExpressView designer.

ExpressView Settings

These settings only apply to the ExpressView designer.

Allow Editing ExpressView with Live Data

Allows users to make changes to ExpressViews while in Live Mode.

Note: We recommend setting this to False. Editing live ExpressViews will cause a large increase in database calls, and may reduce performance.

Fields Enabled in Data Fields Treev2017.1.2+

This setting controls whether users are allowed to add fields to an ExpressView that are not directly joinable to another field on the report.

All joinable fields (default): Users can add any fields with a join path to existing report fields.

Direct joins only:Users can only add fields with a direct join to an existing report field.

Note: As of v2018.1.23+, the join algorithm hasbeen modified for ExpressViews with Direct joins only enabled. The Disable Non-Joined Data Objects database setting is now taken into consideration when generating a join path, allowing these ExpressViews to properly analyze join weight and encompass constructed SQL.

Join Path Algorithm

Select the join path algorithm to use when running ExpressViews with multiple entities.

Standard (default): More performant in most cases.

Legacy:Select if you are experiencing issues with ExpressViews created in an older version.

ExpressView Tutorial

Enables an instructive tutorial guide using ExpressViews to guide new users through the process of building an ExpressView report. The user can opt out during the course of the tutorial.

ExpressView Hints

Enable context-sensitive hints when using different features in ExpressViews.

Express Report Designer Settings pre-v2019.2

These settings only apply to the Express Report wizard.

Show Styling Toolbar

Enables/Disables the styling tools in the Layout tab of the Express Report wizard.

Show Themes

Enables/Disables the Theme dropdown in the Layout tab of the Express Report wizard.

Show Grouping

Enables/Disables the grouping tools in the Layout tab of the Express Report wizard.

Show Formula Button

Enables/Disables the formula editor button in the Layout tab of the Express Report wizard.

Advanced Report Designer Settings

These settings only apply to the Advanced Report designer.

Show Chart Wizard

Enables/Disables the Insert Chart button in the Advanced Report designer. Set to False to disable users from creating or editing charts.

Chart Colors

Lists the values used for default chart colors. Hexadecimal values should be separated by commas (or semicolons).

Maximum Number of Chart Data Points

Upper limit on the number of data points visible on a chart. If the limit is exceeded, a warning will be displayed to the user. Charts with large numbers of data points could cause browser performance issues.

Default Chart Font

Specifies a default font for charts created in the Advanced Report designer. This setting can be overridden on a per-Report basis. Does not apply to Data Visualizations.

Show Geochart Map Wizard

Enables/Disables the Geochart Maps button in the Advanced Report designer. Set to False to disable users from creating or editing Geochart maps.

Note:Geocharts refers to the legacy maps feature, which was available in v2013.2+.

Note: The first time Show Map wizard is set to true a dialog appears prompting you to accept the terms of using the Google Charts Api. Type "I accept" in the first box and your full name in the second to accept the terms and enable mapping.

Geochart Map Key

Optional Google Maps license key for geochart permissions. License must contain the Google Maps Javascript API service. See Legacy Maps (Geocharts) for more information.

Note:Due to a change in Google's Maps API Terms of Service, if geocharting was enabled after June 2016, or if you had geocharting enabled before, but changed your host domain name after June 2016, you need a license key to use this feature.

Geochart Map Colors

List the values used for default Geochart map colors. Hexadecimal values or CSS color names should be separated by commas (or semicolons).

Show Google Map Wizard

Enables/Disables the Google Maps button in the Advanced Report designer. Set to False to disable users from creating or editing Google Maps.

Note:In order to use Google Maps, a license key must be obtained from Google, and a polygon file must be downloaded from our support site. See Google Maps for more information.

Google Map Key

License key for Google Maps permissions. This is required to use the new Google Mapping feature. License must contain the Google Maps JavaScript API and Geocoding API services. See Google Maps for more information.

Google Map Key (unlimited or JS API restricted)

License key for Google Maps permissions. Must contain either:

Google Maps JavaScript API and Geocoding API services, unlimited key.

Google Maps JavaScript API service, limited key with referrer URLs. If this is the case, supply a limited Geocoding API service key in the following field.

Note: When upgrading from a version prior to v2018.1, the value previously supplied for the Google Map Key will appear here.

Google Map Key (optional Geocode API restricted)

License key for Google Maps permissions. If the previous field contains a key limited to the JavaScript API service, supply a limited Geocoding API service key in this field, given server IP addresses. Otherwise leave this blank.

Google Map Colors

List the values used for default Google map colors. Hexadecimal values or CSS color names should be separated by commas (or semicolons).

Show Gauge Wizard

Enables/Disables the Insert Gauge button in the Advanced Report designer. Set to False to disable users from creating or editing gauges.

Gauge Colors

List the values used for default gauge colors. Hexadecimal values or CSS color names should be separated by commas (or semicolons).

Show Document Template

Enables/Disables the Document Template menu. Set to False to disable users from using the Document Template menu.

Show Document Template Upload Button

Set to True to allow users to upload Document Templates to the Report Path. Set to False to prevent users from uploading Document Templates.

Show Linked Report

Enables/Disables the Linked Report button in the Advanced Report designer. Set to False to disable users from creating Linked Reports.

Show Linked Report Fields

Enables/Disables the Fields selector tab in the Linked Report dialog.

Show Linked Report Formula

Enables/Disables the Formula editor tab in the Linked Report dialog.

Show Linked Action

Enables/Disables the Linked Action button.

Show Insert Image

Enables/Disables the Insert Image button in the Advanced Report designer. Set to False to disable users from inserting images.

Show Joins Window

Enables/Disables the Joins menu under Advanced Options. Set to False to disable users from modifying joins.

Show Advanced Joins

Enables/Disables additional options in the Joins menu. Set to True to enable advanced users to create, delete, and modify joins.

Advanced Joins Display v2017.3.1+

Select whether to show complex join options in the report Joins menu. Choose Complex to allow users to modify join operators and expressions, and allow conjoining clauses. Choose Standard to only permit joining data columns on equality.

Allow Category Aliasing v2017.3.1+

Select whether to allow data categories to be aliased in the report Categories menu. Enabling aliasing allows users to add categories multiple times on the same report. This may be necessary for some advanced join operations.

Show Events Window

Enables/Disables the Events menu under Advanced Options. Set to True to allow advanced users to apply Event Handlers for the report. See Server Events for more information.

Show Report-Level Parameters Window v2019.1.3+

Enables/Disables the Parameters menu under Advanced Options. Set to True to allow advanced users to view system-parameters and define their own report-level parameters for use within reports. See Advanced Options for more information.

Show SQL Window

Enables/Disables the Show Generated SQL menu under Advanced Options. Set to True to allow advanced users to generate and view the SQL that will be sent to relevant databases upon report execution. See Advanced Options for more information.

Note:The Show Linked Reports in New Tab flag has been replaced with the Linked Report Display setting as of v2017.3.

Show Linked Reports in New Tab pre-v2017.3

Specify how to display Linked Reports. Set to True to open Linked Reports in a new tab. Set to False to display Linked Reports in a floating window above the parent report.

Linked Report Display v2017.3+

Specify where to display drilldowns for linked reports.

Cursor:Window at the cursor

New Tab:New Exago tab

Center of screen:Window centered in the screen

Allow Grouping on Non-Sorts

Enables/Disables the group formula button in the Group Header/Footer menu. Enabling this will allow users to group on non-sort formulas.

Note: Grouping on non-sort formulas is deprecated and unsupported.

Allow Creation of Custom SQL Objects v2018.1+

Allow end-users to write custom SQL objects at the report level.

Warning:See this article before enabling Report-Level SQL.

Data Sources to Exclude from Custom SQL Creation v2018.1+

When Allow Creation of Custom SQL Objects is enabled, enter the data sources to exclude from Report-Level SQL. Write each data source in double quotation marks ("), and separate sources by a comma (,). Example: "Northwind","AdventureWorks".

Dashboard Report Designer Settings

These settings only apply to the Dashboard designer. If Show Dashboard Reports is false these settings will be ignored.

Prompt user for Parameters/Filters on Execution

Default setting indicating whether to prompt the user for filter and/or parameter values when executing a dashboard. The option can be overridden on an individual dashboard in the Options menu.

Show URL Item Button

Display/Hide the New URL item in the Toolbox of the Dashboard designer.

Allow Creation/Editing of Dashboard Visualizations

Display/Hide the New Data Visualization item in the Toolbox and the Data Fields of the Dashboard designer.

Use Sample Data for Dashboard Visualization Design

Set to True to use sample data while creating and editing Dashboard Visualizations. This will reduce the number of calls to the database. Set to False to query the Data Source for each change made while editing Dashboard Visualizations.

Visualization Database Row Limit pre-2017.2

Maximum number of rows returned on a queries made by Data Visualizations. This only applies to Tables, Views and Functions. Set to 0 to return all rows.

Refresh Reports/Visualizations on Dashboards Silently

Set to True to disable the refresh hourglass animation for timed automatic dashboard reloads.

Minimum Tile Width for Dashboard Reflow v2019.1+

Specifies the minimum width a tile will be drawn when Dashboard Reflow is enabled. Please see the Screen Fit and Scaling section of the Dashboard Designer article for more information.

Minimum Window Width for Dashboard Reflow v2019.1+

Specifies the minimum window size where Dashboard Reflow will begin to take effect. Please see the Screen Fit and Scaling section of the Dashboard Designer article for more information.

Common Settings

Default Designer Font

Specifies a default font for reports created in the Advanced Report wizard, Express Report wizard, Advanced Report designer, and Dashboard designer. This setting can be overridden on a per-Report basis.

End-users must have the selected font installed locally in order to display. Otherwise, Exago will default to Sans Serif. We suggest using a font-face CSS tag in your custom home page to tell the browser to download the font automatically:

@font-face {

font-family: 'Open Sans';

src: url('myFonts/OpenSans.ttf');

}

Default Designer Font Size

Specifies a default font size for reports created in the Advanced Report wizard, Express Report wizard, Advanced Report designer, and Dashboard designer. This setting can be overridden on a per-Report basis. Does not apply to CrossTabs.

Show Help Button

Enables/Disables the Help button in the top right corner of Exago. Set to False to disable users from accessing Context Sensitive help.

Custom Help Source

Specifies the URL that contains custom Context Sensitive Help content. See Custom Context Sensitive Help for more details.

Show Exports in Tab

Set to True to open PDF reports in a tab in Exago. Set to False to prompt the user to download the PDF.

Show IE Download Button

Set to True if Internet Explorer is not automatically prompting users to download PDF, XLS, RTF or CSV reports.

Show Join Fields

Enables/Disables any Data Fields that are used as Unique Keys or Joins. Set to False to hide all unique key and join Data Fields from users. To hide specific Data Fields see Column Metadata.

Show Grid Lines in Report Viewer

Sets the default output to show grid lines. This can be modified in the Options menu of the report designer.

Show Enhanced Tooltips

Sets the style of tooltips to display. If True, enhanced tooltips which support HTML will be displayed. If False, standard browser tooltips will be shown.

Save on Report Execution

Set to False to disable automatic saving of reports when executing from the report designer.

Save on Finish Press

Set to False to disable automatic saving of reports when finish button is pressed in a wizard.

Enable Right-Click Menus

Set to False to disable right click menus.

Enable Reports Tree Drag and Drop

Set to False to disable the dragging of reports and folders in the Main menu.

Show Report Upload/Download Options

Set to True to enable users to upload and download report files by right clicking on folders and reports. Default value is False.

Allow interactivity in Report Viewer

Set to False to disable Interactive Report Viewer capabilities, including: changing column width, styling output, and interactive filters.

Show Toolbar in Report Viewer

Specify if Report Viewer should display paging, search, and export options.

Auto - Exago will detect if the report only displays a single plage of content from the Report Footer Section. If so the HTML Toolbar will be hidden, otherwise it will show.

Show - The toolbar will always show.

Hide - The toolbar will never show.

Default interactive report viewer dock is open

Set to False to have the Interactive report Viewer Dock minimized by default.

Interactive report viewer default dock placement

Specify if the Interactive Report Viewer Dock should appear on the right or left of the default output.

Allow save to report design for report viewer

Set to False to prevent users from saving Interactive Report Viewer changes onto the report.

Maximum number of fields in a CrossTab header or tabulation source

Specify the maximum allowed fields in a CrossTab header or tabulation source. Note that adding a large number of data fields to a CrossTab will significantly increase the execution time of the report.

Use SVG for Application Icons v2016.3+

Set to true to enable Exago to use SVG (scalable vector graphics) icons instead of the default PNG icons for the UI elements. SVG icons look nicer on high-pixel density screens, but they may not be compatible with older web browsers.

Application Theme Selection v2016.3+

Choose from a selection of downloadable UI themes. See Application Themes for more information.

Show Data Fields Search Box v2017.2+

Enables/Disables the data field search tools in the sidebar of the ExpressView and Dashboard designers.

Note:We highly recommend setting Column Metadata, and setting Schema Access Type to Metadata for all available objects, before enabling this feature.

This reference guide describes the structure of the config file XML elements. Use this guide if you build or edit your config XML directly, without using the Admin Console.

Note: For the "settings" nodes within <general></general>, see Config File and API Setting Reference.

The config file's outer nodes are as follows:

<?xml version="1.0" standalone="yes"?>

<webreports></webreports>

All following nodes are within the <webreports> element.

Guide Reference Key

XML objects are represented in tables with the following format:

Node

Description

Type

Required

Node is the XML node name. Nodes in bold font are objects, which are represented in a below table.

Description is the Admin Console field. If there is no mapped field, or the field is implicit, the description is in italic font.

Type is the value type. If the type is a constant or enum, the possible values are listed below the table. Constants are case sensitive.

Required indicates whether the node requires a value. (1+) indicates that there can be multiple nodes.

Data Sources

Outer node: <datasource></datasource>

Node

Description

Type

Required

id

Unique Id

int

yes

name

Name

string

yes

dbtype

Type

constant

yes

dataconnstr

Connection String

string

yes

schema

Schema/Owner Name

string

no

odbcdelim

Column Delimiter(s)

string

no

Possible values for dbtype: mssql, oracle, msolap, odbc, websvc, assembly, mysql, file, postgres, db2, informix, cdata

Example

<datasource>

<id>0</id>

<name>Northwind</name>

<dbtype>odbc</dbtype>

<schema>dbo</dbtype>

<dataconnstr>DRIVER=SQLite3 ODBC Driver;Database=C:\Northwind.sqlite;</dataconnstr>

<odbcdelim>`</odbcdelim>

</datasource>

Data Objects

Outer node: <entity></entity>

Node

Description

Type

Required

id

Id

string

no

entity_name

Alias

string

no

db_name

Name

string

yes

sql_stmt

Custom SQL Object

string

no

category

Category

string

no

datasource_id

Id of data source

int

yes

object_type

Object type

constant

yes

schema_access_type

Schema Access Type

constant

no

key

Unique Key Fields

object

yes (1+)

param

Parameters

object

no (1+)

tenant

Tenant Columns

object

no (1+)

filter_dropdown

Filter Dropdown Object

object

no

column_metadata

Column Metadata

object

no (1+)

transform

Vertical table transform

object

no

affinity

(v2018.1+) Report or config affinity

constant

no

suppress_sort_filter

(v2018.1+)Suppress Sort and Filter

boolean

no

Possible values for object_type: table, view, procedure, function, assembly, sqlstmt, websvc, file

Possible values for schema_access_type: Metadata, Datasource

Possible values for affinity: Global, Report

key

Node

Description

Type

Required

col_name

Column name

string

yes

param

Node

Description

Type

Required

id

Parameter name

string

yes

tenant

Node

Description

Type

Required

col_name

Column name

string

yes

parameter_id

Parameter name

string

yes

filter_dropdown

Node

Description

Type

Required

db_name

Data object name

string

yes

sql_stmt

SQL statement

string

no

datasource_id

Id of data source

int

yes

object_type

Object type

constant

yes

Possible values for object_type: table, view, procedure, function, assembly, sqlstmt, websvc, file

column_metadata

Node

Description

Type

Required

col_source

Specify as custom col

constant

no

col_name

Column name

string

yes

col_description

Column Description

string

no

col_type

Data Type

constant

no

col_alias

Column Alias

string

no

visible

Visible

bool

no

filterable

Filterable

constant

no

sortable

Sortable

bool

no

col_sortandgroupbyvalue

Sort and Group-By Value

string

no

col_value

Column Value

string

no

Possible values for col_source: ExagoFormula

Possible values for col_type: string, date, datetime, time, int, decimal, image, float, boolean, guid, currency

Possible values for filterable: All, Dynamic, Static, None

transform

Node

Description

Type

Required

col_name

Data field names column

string

yes

val_name

Data values column

string

yes

datatype_name

Data types column

string

no

non_transform_col

Fields exempt from transform

object

no

non_transform_col

Node

Description

Type

Required

col_name

Data field name

string

yes

data_type

Data type

enum

no

Possible values for data_type: 0: string, 1: date, 2: datetime, 3: time, 4: integer, 5: decimal, 6: float, 7: bit, 8: guid, 9: image, 10: currency

Example

<entity>

<id>ODE</id>

<entity_name>OrderDetails</entity_name>

<db_name>OrderDetail</db_name>

<sql_stmt>SELECT *</sql_stmt>

<category>Order Information</category>

<datasource_id>1</datasource_id>

<object_type>table</object_type>

<key>

<col_name>OrderId</col_name>

</key>

<key>

<col_name>ProductId</col_name>

</key>

<param>

<id>count</id>

</param>

<tenant>

<col_name>CategoryName</col_name>

<parameter_id>count</parameter_id>

</tenant>

<filter_dropdown>

<db_name>Category</db_name>

<sql_stmt>SELECT *</sql_stmt>

<datasource_id>-1</datasource_id>

<object_type>table</object_type>

</filter_dropdown>

<column_metadata>

<col_source>ExagoFormula</col_source>

<col_name>Total</col_name>

<col_type>currency</col_type>

<col_alias>Total</col_alias>

<filterable>false</filterable>

<col_value>{ODE.Quantity}*({ODE.UnitPrice}-{ODE.Discount})</col_value>

</column_metadata>

<column_metadata>

<col_name>UnitPrice</col_name>

<col_type>currency</col_type>

</column_metadata>

</entity>

Joins

Outer node: <join></join>

Note: For entity names, use Id if set, otherwise use db_name.

Node

Description

Type

Required

entity_from_name

From object (left)

string

yes

entity_to_name

To object (right)

string

yes

join_type

Join Type

constant

yes

relation_type

Relation Type

constant

yes

weight

Weight

int

yes

joincol

Join Columns (deprecated in v2017.2)

object

yes (1+)

clause

Join Clause (v2017.2+)

object

yes (1+)

affinity

Join affinity (v2018.1+)

constant

yes

Possible values for join_type: inner, leftouter, rightouter, fullouter

Possible values for relation_type: 11: one to one, 1M: one to many

Possible values for affinity: Global, Report

joincol (deprecated in v2017.2)

Node

Description

Type

Required

col_from_name

From column (left)

string

yes

col_to_name

To column (right)

string

yes

clause (v2017.2+)

Node

Description

Type

Required

left_entity

From object (left) (v2017.3+)

string

no

left_side

From expression (left)

string

yes

right_side

To expression (right)

string

yes

conjunction

Joining conjunction

constant

yes

comparison

Comparison operator

constant

yes

left_side_type

From expression type

constant

yes

right_side_type

To expression type

constant

yes

level

Parenthesis nesting level

int

yes

left_entity overrides the base (implicit) from object entity_from_name for the specific join clause; this is necessary for some uses of the IN operator

Possible values for conjunction: AND, OR

Possible values for comparison: EQ (=), NE (<>), LT (<), GT (>), LE (<=), GE (>=), IN

Possible values for left_side_type, right_side_type: Column, Constant, SubQuery, Expression

Example

<join>

<entity_from_name>PRO</entity_from_name>

<entity_to_name>CAT</entity_to_name>

<clause>

<left_side>ProductId</left_side> <right_side>CategoryId</right_side> <conjunction>AND</conjunction> <comparison>EQ</comparison> <right_side_type>Column</right_side_type> <left_side_type>Column</left_side_type> <level>0</level>

</clause>

</join>

Parameters

Outer node: <parameter></parameter>

Node

Description

Type

Required

id

Name

string

yes

data_type

Type

constant

yes

value

Value

string

no

hidden

Hidden

bool

yes

prompt_text

Prompt Text

string

no

parameter_dropdown

Parameter Dropdown Object

object

no

Possible values for data_type: string, date, integer, decimal

parameter_dropdown

Node

Description

Type

Required

db_name

Database object name

string

yes

sql_stmt

SQL statement

string

no

datasource_id

Unique Id of data source

int

yes

object_type

Object type

constant

yes

value_field

Value Field

string

yes

display_value_field

Display Value Field

string

no

display_data_type

Display Type

constant

yes

sp_params

Stored proc parameters

string

no

Possible values for object_type: table, view, procedure, function, assembly, sqlstmt, websvc, file

Possible values for display_data_type: string, date, integer, decimal

Example

<parameter>

<id>count</id>

<data_type>integer</data_type>

<hidden>False</hidden>

<prompt_text />

<parameter_dropdown>

<db_name>Category</db_name>

<sql_stmt />

<datasource_id>1</datasource_id>

<object_type>view</object_type>

<value_field>Category_View</value_field>

<display_value_field />

<display_data_type>string</display_data_type>

<sp_params />

</parameter_dropdown>

</parameter>

Roles

Outer node: <role></role>

Node

Description

Type

Required

id

ID

string

yes

active

Active

bool

yes

rolegeneral

General settings

object

yes

security

Security settings

object

yes

rolegeneral

Note: Settings pertaining to Express Reports were removed from the Admin Console in v2019.2 but they remain available here in the config file. No other functionality was removed.

Node

Description

Type

Required

reportpath

Report Path

string

no

dateformat

Date Format

string

no

timeformat

Time Format

string

no

datetimeformat

DateTime Format

string

no

separatorsymbol

Numeric Separator Symbol

string

no

currencysymbol

Numeric Currency Symbol

string

no

readfiltervalues

Read Database for Filter Values

bool

no

dbtimeout

Database Timeout

int

yes

showgrid

Show Grid Lines in Report Viewer

bool

no

showcrosstabreports

Allow Creation/Editing of Crosstab Reports

bool

no

showdashboardreports

Allow Creation/Editing of Dashboard Reports

bool

no

showchainedreports

Allow Creation/Editing of Chained Reports

bool

no

showexpressviews

Allow Creation/Editing of ExpressViews

bool

no

allowexpressviewliveedit

Allow Editing ExpressView with Live Data

bool

no

showexpressreports

Allow Creation/Editing of Express Reports

bool

no

showexpressreportsgrouping

Show Grouping

bool

no

showexpressreportsformulabutton

Show Formula Button

bool

no

showexpressreportsstylingtoolbar

Show Styling Toolbar

bool

no

showexpressreportsthemes

Show Themes

bool

no

showadvancedreports

Allow Creation/Editing of Advanced Reports

bool

no

showschedulereports

Show Report Scheduling Option

bool

no

showschedulereportsmanager

Show Schedule Reports Manager

bool

no

schedulemanagerviewlevel

Scheduler Manager User View Level

constant

no

showschedulereportsemail

Show Email Report Options

bool

no

decimalsymbol

Numeric Decimal Symbol

string

no

languagefile

Language File

string

no

servertimezoneoffset

Server Time Zone Offset

int

no

showdashboardnewvisualizationbutton

Allow Creation/Editing of Dashboard Visualizations

bool

no

allowreportcustomsqlobjects

Allow Creation of Custom SQL Objects in Advanced Reports (v2018.1+)

bool

no

Possible values for schedulemanagerviewlevel: user, company, all

security

Node

Description

Type

Required

folders

Folder Security

object

yes

dataobjects

Data Object Security

object

yes

dataobjectrows

Data Row Security

object

no

includereportcustomsqlobjects

Allow User to View Report-Level Custom SQL Objects (v2018.1+)

bool

no

folders

Node

Description

Type

Required

include_all

Include All Folders

bool

yes

read_only

All Folders Read Only

bool

yes

allow_management

Allow Folder Management

bool

yes

folder

Folders

object

no (1+)

folder

Node

Description

Type

Required

name

Folder Name

string

yes

read_only

Read Only

bool

yes

propagate

Propagate status to sub-folders

bool

yes

dataobjects

Node

Description

Type

Required

include_all

Include All Data Objects

bool

yes

dataobject

Objects

object

no (1+)

dataobject

Note: For name, use Id if set, otherwise use db_name.

Node

Description

Type

Required

name

Data Object Name

string

yes

dataobjectrows

Node

Description

Type

Required

dataobjectrow

Filters

object

no (1+)

dataobjectrow

Note: For name, use Id if set, otherwise use db_name.

Node

Description

Type

Required

name

Data Object Name

string

yes

filter

Filter String

string

yes

Example

<role>

<id>Test</id>

<active>False</active>

<rolegeneral>

<reportpath>asraaweweweasd</reportpath>

<dateformat />

<timeformat />

<datetimeformat />

<separatorsymbol>,</separatorsymbol>

<currencysymbol>$</currencysymbol>

<readfiltervalues />

<dbtimeout>0</dbtimeout>

<showgrid />

<showcrosstabreports />

<showdashboardreports />

<showchainedreports />

<showexpressviews />

<allowexpressviewliveedit />

<showexpressreports />

<showexpressreportsgrouping />

<showexpressreportsformulabutton />

<showexpressreportsstylingtoolbar />

<showexpressreportsthemes />

<showadvancedreports />

<showschedulereports />

<showschedulereportsmanager />

<schedulemanagerviewlevel />

<showschedulereportsemail />

<decimalsymbol>.</decimalsymbol>

<languagefile />

<servertimezoneoffset>1</servertimezoneoffset>

<showdashboardnewvisualizationbutton />

</rolegeneral>

<security>

<folders>

<include_all>True</include_all>

<read_only>False</read_only>

<allow_management>True</allow_management>

<folder>

<name>Examples</name>

<read_only>False</read_only>

<propagate>True</propagate>

</folder>

</folders>

<dataobjects>

<include_all>False</include_all>

<dataobject>

<name>CAT</name>

</dataobject>

</dataobjects>

<dataobjectrows>

<dataobjectrow>

<name>CAT</name>

<filter>heyho</filter>

</dataobjectrow>

</dataobjectrows>

</security>

</role>

Functions

Outer node: <function></function>

Note: Functions and Filter Functions use the same xml syntax. Filter functions have weight > 0 (which indicates its order in the filter list).

Node

Description

Type

Required

name

Name

string

yes

description

Description

string

no

category

Category

constant

no

min_args

Minimum Number of Arguments (deprecated in v2017.2)

int

no

max_args

Maximum Number of Arguments (deprecated in v2017.2)

int

no

arguments_json

(v2017.2+) Arguments information

json object

yes

variable_arguments

(v2017.2+) Variable Argument Count

bool

yes

available_in

Formula or filter function

enum

yes

filter_return_type

Filter Type

constant

no

weight

List Order

int

yes

language

Language

constant

yes

program_code

Program Code

string

yes

disable_database_grouping

(v2019.1.5+)Prevent In-Database Grouping When Included in Detail Section

bool

yes

reference

References

object

no (1+)

namespace

Namespaces

object

no (1+)

Possible values for category: Aggregate, Operators, Logical, Date, Financial, Database, Arithmetic, String, Formatting, Other

Possible values for available_in: 0: Unavailable, 1: Formula, 2: Filter

Possible values for filter_return_type: date, string, integer, decimal

Possible values for language: CSharp, JavaScript, VB

reference

Node

Description

Type

Required

name

Reference name

string

yes

namespace

Node

Description

Type

Required

name

Namespace name

string

yes

arguments_json (v2017.2+)

A JSON-formatted array of objects with the following format:

Property

Description

Type

Required

Name

Name

string

yes

Required

Required

bool

yes

Description

Description

string

yes

Example

<function>

<name>IncrementBy</name>

<description>Count up by value steps.</description>

<category>Other</category>

<arguments_json>[{"Name":"counter","Required":false,"Description":"Number to increment by. Default: 1."}]</arguments_json> <variable_arguments>false</variable_arguments>

<available_in>1</available_in>

<filter_return_type>String</filter_return_type>

<weight>0</weight>

<language>CSharp</language>

<program_code>return Extensions.CustomFunctions.IncrementBy(sessionInfo, args);</program_code>

<reference>

<name>Extensions.dll</name>

</reference>

</function>

Server Events

Outer node: <serverevent></serverevent>

Node

Description

Type

Required

name

Name

string

yes

id

Unique Id

int

yes

event_type

Global Event

constant

yes

datasource_id

Id of assembly data source

int

no

func_name

Assembly function name

string

no

language

Language

constant

no

program_code

Program code

string

no

reference

References

object

no (1+)

namespace

Namespaces

object

no (1+)

Possible values for event_type: None, or see List of Server Events.

Possible values for language: CSharp, JavaScript, VB

reference

Node

Description

Type

Required

name

Reference name

string

yes

namespace

Node

Description

Type

Required

name

Namespace name

string

yes

Example

<serverevent>

<name>ExpandAllFolders</name>

<id>0</id>

<event_type>OnAfterLoadReportsList</event_type>

<language>CSharp</language>

<program_code>Extensions.ServerEvents.ExpandAllFolders(sessionInfo, (TreeNodeCollection)args[0]);

return null;</program_code>

<reference>

<name>Extensions.dll</name>

</reference>

<reference>

<name>WebReports.dll</name>

</reference>

<namespace>

<name>WebReports.UI.Controls</name>

</namespace>

</serverevent>

Action Events

Outer node: <actionevent></actionevent>

Node

Description

Type

Required

name

Name

string

yes

id

Unique Id

int

yes

event_type

Global Event Type

constant

yes

datasource_id

Id of assembly data source

int

no

func_name

Assembly function name

string

no

user_action

Event Type

constant

yes

language

Language

constant

no

program_code

Program code

string

no

uiaction

Assigned UI Item(s)

object

no (1+)

reference

References

object

no (1+)

namespace

Namespaces

object

no (1+)

Possible values for event_type: None, or see Global Action Events.

Possible values for user_action: None, Load, Click

Possible values for language: CSharp, JavaScript, VB

uiaction

Node

Description

Type

Required

element_id

Element Id

constant

yes

Possible values for element_id: See Identifying Actionable UI Elements.

reference

Node

Description

Type

Required

name

Reference name

string

yes

namespace

Node

Description

Type

Required

name

Namespace name

string

yes

Example

<actionevent>

<name>DoubleClick</name>

<id>0</id>

<event_type>OnDoubleClickReport</event_type>

<user_action>Click</user_action>

<language>CSharp</language>

<program_code>return Extensions.ActionEvents.DoubleClick(sessionInfo);</program_code>

<uiaction>

<element_id>RunReportBtn</element_id>

</uiaction>

<reference>

<name>Extensions.dll</name>

</reference>

</actionevent>

Custom Options

Outer node: <customoption></customoption>

Node

Description

Type

Required

option_id

Id

string

yes

option_type

Type

constant

yes

option_list_item

Option list item

object

no (1+)

Possible values for option_type: Int, Decimal, Bool, Text, List

option_list_item

Node

Description

Type

Required

option_list_item_id

List item Id

string

yes

Example

<customoption>

<option_id>Hello</option_id>

<option_type>List</option_type>

<option_list_item>

<option_list_item_id>World</option_list_item>

</option_list_item>

</customoption>

If you build your config files directly, without using the Admin Console, or if you change settings in the API, use this reference to map Admin Console settings to the relevant config file node or API key.

Note: For other config file elements, see Config File XML Reference.

Format

Setting Name : type

<config_file_node> (within the <general> element)

Api_Key (within the Api.SetupData.General namespace)

Tip: This document is presented in the order that settings appear in the Admin Console. Use Ctrl-F to find a specific setting in this document.

Main Settings

The Main Settings article includes detailed descriptions of each of these settings.

Report Path : string

<reportpath>

ReportPath

Temp Path : string

<temppath>

TempPath

Temp Cloud Service : string

<tempcloudservice>

TempCloudService

Language File : string

<languagefile>

LanguageFile

Temp URL : string

<tempurl>

TempUrl

Allow direct access to Exago (bypassing API) : bool

<allowhomedirect>

AllowHomeDirect

Allow Execution in Viewer : bool

<allowhtmloutput>

AllowHtmlOutput

Allowed Export Types - Excel : bool

<allowexceloutput>

AllowExcelOutput

Allowed Export Types - PDF : bool

<allowpdfoutput>

AllowPdfOutput

Allowed Export Types - RTF : bool

<allowrtfoutput>

AllowRtfOutput

Allowed Export Types - CSV : bool

<allowcsvoutput>

AllowCsvOutput

Default Output Type : constant

Possible values: excel, pdf, rtf, csv

<defaultoutputtype>

DefaultOutputType

Report Tree Shortcut : constant

Possible values: Run, Export

<reporttreeshortcut>

ReportTreeShortcutRun : bool

Active Role : string (v2019.1+)

<activeroleid>

ActiveRoleId

Culture Settings

The Culture Settings article includes detailed descriptions of each of these settings.

Date Format : string

<dateformat>

DateFormat

Time Format : string

<timeformat>

TimeFormat

DateTime Format : string

<datetimeformat>

DateTimeFormat

DateTime Values Treated As : constant

Possible values: date, datetime

<datetimetreatedas>

DateTimeTreatedAs

Numeric Separator Symbol : string

<separatorsymbol>

SeparatorSymbol

Numeric Currency Symbol : string

<currencysymbol>

CurrencySymbol

Numeric Decimal Symbol : string

<decimalsymbol>

DecimalSymbol

Numeric Decimal Places : int

<decimalplaces>

DecimalPlaces

Currency Decimal Places : int

<currencydecimalplaces>

CurrencyDecimalPlaces

Apply Numeric Decimal Places to General Cell Formatting : bool

<applygeneralformatdecimalplaces>

ApplyGeneralFormatDecimalPlaces

Apply General Currency Right Alignment : bool

<applygeneralcurrencyrightalignment>

ApplyGeneralCurrencyRightAlignment

Server Time Zone Offset : int

<servertimezoneoffset>

ServerTimeZoneOffset

Time Zone Name : string

<clienttimezonename>

ClientTimeZoneName

Feature/UI Settings

The Feature/UI Settings article includes detailed descriptions of each of these settings.

Allow Creation/Editing of Express Reports : bool

Note: This setting was removed from the Admin Console in v2019.2 but remains available here in the config file. No other functionality was removed.

<showexpressreports>

IsShowExpressReports

Allow Creation/Editing of Advanced Reports : bool

<showadvancedreports>

IsShowAdvancedReports

Allow Creation of Crosstab Reports : bool

<showcrosstabreports>

IsShowCrosstabReports

Allow Creation/Editing of Dashboards : bool

<showdashboardreports>

IsShowDashboardReports

Allow Creation/Editing of Chained Reports : bool

<showchainedreports>

IsShowChainedReports

Allow Creation/Editing of ExpressViews : bool

<showexpressviews>

IsShowExpressViews

Allow Editing ExpressView with Live Data : bool

<allowexpressviewliveedit>

IsAllowExpressViewLiveEdit

Show Data Fields Search Box : bool(pre-v2017.2)

<showexpressviewdatafieldssearch>

IsShowExpressViewDataFieldsSearch

Fields Enabled in Data Fields Tree : bool(pre-v2017.2)

<expressviewdatafieldsdisplay>

IsExpressViewDataFieldsDisplay

Fields Enabled in Data Fields Tree : bool(v2017.2+)

<showdatafieldssearch>

IsShowDataFieldsSearch

Show Styling Toolbar : bool

Note: This setting was removed from the Admin Console in v2019.2 but remains available here in the config file. No other functionality was removed.

<showexpressreportsstylingtoolbar>

IsShowExpressReportsStylingToolbar

Show Themes : bool

Note: This setting was removed from the Admin Console in v2019.2 but remains available here in the config file. No other functionality was removed.

<showexpressreportsthemes>

IsShowExpressReportsThemes

Show Grouping : bool

Note: This setting was removed from the Admin Console in v2019.2 but remains available here in the config file. No other functionality was removed.

<showexpressreportsgrouping>

IsShowExpressReportsGrouping

Show Formula Button : bool

Note: This setting was removed from the Admin Console in v2019.2 but remains available here in the config file. No other functionality was removed.

<showexpressreportsformulabutton>

IsShowExpressReportsFormulaButton

Show CrossTab Wizard : bool

<showcrosstabwizard>

IsShowCrosstabWizard

Show Chart Wizard : bool

<showchartwizard>

IsShowChartWizard

Chart Colors : string

<chartcolors>

ChartColors

Maximum Number of Chart Data Points : int

<maxnumberofchartpoints>

MaxNumberOfChartPoints

Default Chart Font : string

<defaultchartfont>

DefaultChartFont

Show Geochart Map Wizard : bool

<showmapwizard>

IsShowMapWizard

Geochart Map Key (optional) : string

<geochartmapkey>

GeochartMapKey

Geochart Map Colors : string

<mapcolors>

MapColors

Show Google Map Wizard : bool

<showgooglemapwizard>

IsShowGoogleMapWizard

Google Map Key : string (pre-v2018.1)

<googlemapkey>

GoogleMapKey

Google Map Key (unlimited or JS API restricted) : string(v2018.1+)

<googlemapjsapikey>

GoogleMapJSAPIKey

Google Map Key (optional Geocode API restricted) : string(v2018.1+)

<googlemapgeocodeapikey>

GoogleMapGeocodeAPIKey

Google Map Colors : string

<googlemapcolors>

GoogleMapColors

Show Gauge Wizard : bool

<showgaugewizard>

IsShowGaugeWizard

Gauge Colors : bool

<gaugecolors>

GaugeColors

Show Document Template : bool

<showpdftemplate>

IsShowPdfTemplate

Show Document Template Upload Button : bool

<showtemplateuploadbutton>

IsShowTemplateUploadButton

Show Linked Report : bool

<showlinkreport>

IsShowLinkReport

Show Linked Report Fields : bool

<showlinkreportfields>

IsShowLinkReportFields

Show Linked Report Formula : bool

<showlinkreportformula>

IsShowLinkReportFormula

Show Linked Action : bool

<showlinkaction>

IsShowLinkAction

Show Insert Image : bool

<showinsertimage>

IsShowInsertImage

Show Joins Window : bool

<showjoinswindow>

IsShowJoinsWindow

Show Advanced Joins : bool

<showadvancedjoins>

IsShowAdvancedJoins

Advanced Joins Display : bool(v2017.3.1+)

<showcomplexjoins>

IsShowComplexJoins

Allow Category Aliasing : bool(v2017.3.1+)

<showcategoryalias>

IsShowCategoryAlias

Show Events Window : bool

<showeventswindow>

IsShowEventsWindow

Show Report-Level Parameters Window : bool

<showreportparameterswindow>

IsShowReportParametersWindow

Show SQL Window : bool

<showsqlwindow>

IsShowSQLWindow

Show Linked Reports in New Tab : bool(pre-v2017.3)

<linkedreportsinnewtab>

LinkedReportsInNewTab

Linked Report Display : constant(v2017.3+)

Possible values: Cursor, NewTab, ScreenCenter

<linkedreportdisplay>

LinkedReportDisplay

Allow Grouping on non-Sorts : bool

<showgroupheadersformulabutton>

IsShowGroupHeadersFormulaButton

Prompt user for Parameters/Filters on Execution : bool

<dashboardpromptatexecution>

IsDashboardPromptAtExecution

Show URL Item Button : bool

<dashboardshowurlitembutton>

IsDashboardShowUrlItemButton

Allow Creation/Editing of Dashboard Visualizations : bool

<showdashboardnewvisualizationbutton>

IsShowDashboardNewVisualizationButton

Show Data Fields Search Box : bool

<showdashboarddatafieldssearch>

IsShowDashboardDataFieldsSearch

Use Sample Data for Dashboard Visualization Design : bool

<usesampledatafordashboardvisualizationdesign>

IsUseSampleDataForDashboardVisualizationDesign

Visualization Database Row Limit : int

<visualizationdbrowlimit>

VisualizationDbRowLimit

Refresh Reports/Visualizations on Dashboards Silently : bool

<silentdashboardrefresh>

IsSilentDashboardRefresh

Minimum Tile Width for Dashboard Reflow : int (v2019.1+)

<mintilewidth>

MinTileWidth

Minimum Window Width for Dashboard Reflow : int (v2019.1+)

<mindesktopwidth>

MinDesktopWidth

Default Designer Font : string

<defaultfont>

DefaultFont

Default Designer Font Size : int

<defaultfontsize>

DefaultFontSize

Show Help Button : bool

<showhelp>

IsShowHelp

Custom Help Source : string

<customhelpsource>

CustomHelpSource

Show Exports in Tab : bool

<showexportsintab>

IsShowExportsInTab

Show IE Download Button : bool

<showiedownloadbutton>

IsShowIeDownloadButton

Show Join Fields : bool

<showjoinfields>

IsShowJoinFields

Show Grid Lines in Report Viewer : bool

<showgrid>

IsShowGrid

Show Enhanced Tooltips : bool

<enhancedtooltips>

IsShowEnhancedTooltips

Save on Report Execution : bool

<saveonexecute>

IsSaveOnExecute

Save on Finish Press : bool

<saveonfinish>

IsSaveOnFinish

Enable Right-Click Menus : bool

<enablerightclickmenus>

IsEnableRightClickMenus

Enable Reports Tree Drag And Drop : bool

<enablereportstreedraganddrop>

IsEnableReportsTreeDragAndDrop

Show Report Upload/Download Options : bool

<showreportuploaddownloadoptions>

IsShowReportUploadDownloadOptions

Allow interactivity in report viewer : bool

<interactivehtml>

AllowInteractiveHtml

Show Toolbar in Report Viewer : constant

Possible values: Auto, Show, Hide

<showhtmltoolbar>

IsShowHtmlToolbar

Default interactive report viewer dock is open : bool

<defaultisdockopen>

DefaultIsDockOpen

Interactive report viewer default dock placement : constant

Possible values: Left, Right

<dockplacement>

IsEnableScheduling

Allow save to report design from the report viewer : bool

<executesavetodesign>

AllowExecuteSaveToDesign

Maximum number of fields in a crosstab header or tabulation source : int

<crosstabmaximumfields>

CrossTabMaximumFields

Use SVG for Application Icons : bool

<usesvgforappicons>

UseSVGForAppIcons

Application Theme Selection : string

<csstheme>

CssTheme

Join Path Algorithm : const(v2018.1+)

<expressviewjoinalgorithm>

ExpressviewJoinAlgorithm

Allow Creation of Custom SQL Objects : bool(v2018.1+)

<allowreportcustomsqlobjects>

IsAllowReportCustomSQLObjects

Data Sources to Exclude from Custom SQL Object Creation : string(v2018.1+)

<excludedatasourcesreportcustomsql>

ExcludeDataSourcesReportCustomSQL

Programmable Object Settings

The Programmable Object Settings article includes detailed descriptions of each of these settings.

Call Type Parameter Name : string

<calltypeparamname>

CallTypeParamName

Column Parameter Name : string

<columnparamname>

ColumnParamName

Filter Parameter Name : string

<filterparamname>

FilterParamName

Full Filter Parameter Name : string

<fullfilterparamname>

FullFilterParamName

Sort Parameter Name : string

<sortparamname>

SortParamName

Data Category Parameter Name : string

<datacategoryparamname>

DataCategoryParamName

Data Object ID Parameter Name : string

<objectidparamname>

ObjectIdParamName

DB Row Limit Parameter Name : string(v2018.1+)

<dbrowlimitparamname>

DbRowLimitParamName

DB Row Start Index Parameter Name : string(v2018.1+)

<dbrowrangestartparamname>

DbRowRangeStartParamName

DB Row End Index Parameter Name : string(v2018.1+)

<dbrowrangeendparamname>

DbRowRangeEndParamName

Filter Settings

The Filter Settings article includes detailed descriptions of each of these settings.

Show Group (Min/Max) Filters : bool

<showgroupfilters>

IsShowGroupFilters

Show Top N Filters : bool

<showtopn>

IsShowTopN

Allow New Filters at Execution : bool

<allowexecutenewfilters>

AllowExecuteNewFilters

Read Database for Filter Values : bool

<readfiltervalues>

IsReadFilterValues

Allow Filter Dependencies : bool

<allowdependantfilters>

AllowDependantFilters

Show Filter Description : bool

<showfilterdescription>

IsShowFilterDescription

Default Filter Execution Window : constant

Possible values: Standard, SimpleWithOperator, Simple, Custom

<defaultFilterExecutionWindow>

DefaultFilterExecutionWindow

Allow User to Change Filter Window : bool

<changeableFilterExecutionWindow>

IsChangeableFilterExecutionWindow

Include Null Values for 'NOT' Filters : bool

<includenotfilternullvalues>

IsIncludeNotFilterNullValues

Custom Filter Execution Window : string

<filterexecutionwindow>

FilterExecutionWindow

Database Settings

See Database Settings for more information.

Database Timeout : int

<dbtimeout>

DbTimeout

Database Row Limit : int

<dbrowlimit>

DbRowLimit

Row Limit Step Size : int(v2017.2+)

<dbrowlimitstepsize>

DbRowLimitStepSize

Disable Non-Joined Data Objects : bool

<detectjoinedobjects>

IsDetectJoinedObjects

Enable Special Cartesian Processing : bool

<enablespecialcartesianprocessing>

IsEnableSpecialCartesianProcessing

Aggregate and Group in Database : bool

<aggregateandgroupindatabase>

IsAggregateAndGroupInDatabase

Convert Filter and Sort Formulas to SQL : bool

<evaluateformulasindatabase>

EvaluateFormulasInDatabase

Scheduler Settings

The Scheduler Settings article includes detailed descriptions of each of these settings.

Enable Report Scheduling : bool

<enablescheduling>

IsEnableScheduling

Show Report Scheduling Option : bool

<showschedulereports>

IsShowScheduleReports

Show Email Report Options : bool

<showschedulereportsemail>

IsShowScheduleReportsEmail

Show Schedule Reports Manager : bool

<showschedulereportsmanager>

IsShowScheduleReportsManager

Show Schedule No End Date Option : bool

<showschedulenoenddate>

IsShowScheduleNoEndDate

Show Schedule Intraday Recurrence Option : bool

<showscheduleintradayrecurrence>

IsShowScheduleIntradayRecurrence

Scheduler Manager User View Level : constant

Possible values: User, Company, All

<schedulemanagerviewlevel>

ScheduleManagerViewLevel

Email Scheduled Reports : bool

<emailscheduledreports>

IsEmailScheduledReports

Enable Batch Reports : bool

<enablebatchreports>

IsEnableBatchReports

Show Schedule Delivery Type Options : bool

<showscheduledeliverytypeoptions>

IsShowScheduleDeliveryTypeOptions

Use Secure Scheduler Remoting Channel : bool

<usesecureremotingchannel>

IsUseSecureRemotingChannel

Schedule Remoting Host : string

<scheduleremotinghost>

ScheduleRemotingHost

Enable Remote Report Execution : bool

<executeremotely>

IsExecuteRemotely

Enable Execution Cache : bool

<executioncache>

UseExecutionCache

User Cache Visibility Level : constant(v2017.3+)

Possible values: Global, Company, User

<cachevisibilitylevel>

CacheVisibilityLevel

Enable Access to Data Sources Remotely : bool

<accessdatasourcesremotely>

IsAccessDataSourcesRemotely

Remote Execution Remoting Host : string

<synchronousremotinghost>

SynchronousRemotingHost

Custom Queue Service : string

<schedulerqueueservice>

SchedulerQueueService

Delete Schedules upon Report Deletion : bool

<deleteschedulesuponreportdeletion>

IsDeleteSchedulesUponReportDeletion

Default Email Subject : string

<reportscheduleemailsubject>

ReportScheduleEmailSubject

Default Email Body : string

<reportscheduleemailbody>

ReportScheduleEmailBody

Password Requirements (for pdf and excel documents) : string

<passwordrequirement>

PasswordRequirement

Custom Scheduler Recipient Window : string

<schedulerrecipientwindow>

SchedulerRecipientWindow

User Settings

The User Settings article includes detailed descriptions of each of these settings.

User Preference Storage Method : constant

Possible values: None, Cookie, ExternalInterface, ServerEvent

<userprefstorage>

UserPreferenceStorage

Startup Report(s) Replace Getting Started : bool

<userstartupreportoverwritegettingstarted>

UserStartupReportOverwriteGettingStarted

Maximum Number of Startup Reports : int

<userstartupreportmaxnum>

UserStartupReportMaxNum

Allow User Reports : bool

<userallowuserreports>

UserAllowUserReports

Other Settings

The Other Settings article includes detailed descriptions of each of these settings.

Excel Export Target : constant

Possible values: v2003, v2007, v2010

<excelexporttarget>

ExcelExportTarget

External Interface : string

<externalinterface>

ExternalInterface

Enable Paging in Report Viewer : bool

<htmlpaging>

IsHtmlPaging

Renew Session Automatically : bool

<renewsession>

IsRenewSession

Write Log File : bool(pre-v2017.2)

<writelog>

IsWriteLog

Write Log File : constant(v2017.2+)

Possible values: NONE, ERROR, INFO, DEBUG

<logginglevel>

LoggingLevel

Enable Debugging : bool

<debugging>

IsDebugging

Max Report Execution Time (minutes) : int

<maxjobexecutionminutes>

MaxJobExecutionMinutes

Maximum Age for Temp Files : int

<maxtempfileage>

MaxTempFileAge

Enable Web Service / Assembly Data Mapping : bool

<datamapping>

IsDataMapping

Limit Reports and Visualizations to One Category : bool

<onecategorylimit>

IsOneCategoryLimit

Run Aggregate Functions at Record Level by Default : bool

<isaggregaterecordlevel>

IsAggregateRecordLevel

Cache External Services : bool

<cacheservices>

IsCacheServices

Global Schema Access Type : constant

Possible values: Datasource, Metadata

<schemaaccesstype>

SchemaAccessType

Allow Multiple Sessions : bool

<allowmultiplesessions>

AllowMultipleSessions

Allow MD5 Hashing on FIPS server : bool

<allowmd5onfips>

AllowMD5OnFIPS

Write BOM to CSV Files (v2019.1.1+)

<writebomtocsv>

WriteBOMToCSV

'LoadImage' Cell Function Parameter Prefix : string

<loadimageprefix>

LoadImageFuncParamPrefix

Ignore Inaccessible Report Folders : bool

<ignoreinaccessiblereportfolders>

IgnoreInaccessibleReportFolders

User ID : string

<userid>

UserId

Password : string

<password>

Password

Debug Password : string

<debugextractionpassword>

DebugExtractionPassword

REST Key : string(v2017.3+)

<restkey>

RestKey

Exago Expiration Date : string

<expirationdate>

ExpirationDate

Custom Code Supplied by Exago : bool

<customcode>

CustomCode

License Key : string

<licensekey>

LicenseKey

Hidden Flags

These settings are not accessible in the Admin Console. See Hidden Flags for more information.

ExpressView Default Theme : string

<expressviewdefaultformattheme>

ExpressViewDefaultFormatTheme

Allow HTML in Scheduled Email Body : bool

<allowhtmlinscheduledemails>

AllowHtmlInScheduledEmails

Web Farm Support : bool

<webfarmsupport>

IsWebFarmSupport

Show Browser Out-Of-Date Warning : bool

<showbrowseroutofdatewarning>

IsShowBrowserOutOfDateWarning

Alias All Entities : bool(v2017.2+)

<aliasallentities>

AliasAllEntities

Safe Mode : bool(v2017.2+)

<safemode>

SafeMode

Allow Early Page Break : bool(v2017.3+)

<allowearlypagebreak>

AllowEarlyPageBreak

Web Request Keep Alive : bool (v2018.2+)

<webrequestkeepalive>

IsWebRequestKeepAlive

Web Request Protocol Version : string (v2018.2+)

<webrequestprotocolversion>

WebRequestProtocolVersion

Unsupported

These settings are not accessible in the Admin Console. Changing these settings is not supported.

Formula functions loaded : bool(v2017.2+)

<formulafunctionsloaded>

AreFormulaFunctionsLoaded

Date functions loaded : bool

<datefunctionsloaded>

AreDateFunctionsLoaded

Version Number : string

<versionnumber>

VersionNumber

Data Cloud Service : string

<datacloudservice>

DataCloudService

Window Height : int

<windowheight>

WindowHeight

Window Width : int

<windowwidth>

WindowWidth

Map Terms Signature : string

<maptermssignature>

MapTermsSignature

Join Transform Objects in Database: bool(v2018.1+)

<canjointransformobjectsindb>

CanJoinTransformObjectsInDb

Cache Configuration Data: bool(v2018.1+)

<cacheconfig>

IsCacheConfig

You may want to sort groups by the summary, or aggregate, calculation of each group. You can do this using Top/Bottom filters, which are available in v2017.1+.

Top/Bottom filters look for the highest or lowest values in a set, and then put those values in order. You can supply any arbitrary cell in the report in the Top/Bottom filter. So if you set the number of displayed values to "infinity", then no values are excluded, but the ordering still takes effect.

To sort by a summary calculation:

Ensure that your report has the appropriate aggregate formula in a group footer cell. The cell should return a numeric value in the report output, which you want to sort the groups by.

From theSettings menu, click Filters. Then click the Top/Bottom tab.

Check the Limit the report to the top/bottom values check box.

From the Top/Bottom list, select Bottom to sort in ascending order, or Top to sort in descending order.

In the # field, enter 2147483647. Why this number? We cannot enter, so instead we want to enter an arbitrarily large number.This is the largest number that can fit without causing a report error.

From the Value list, select the group footer cell with the aggregate formula.

If there is a For Each group, click theDelete icon to remove it.

Keep in mind that this is not a Sort from the sort menu. You cannot use this to make nested groups. This only affects the order in which a group of data is shown in the output. Because this is technically a Filter, this has precedence over the report sorts.

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 Admin Console under Column Metadata menu Data. All data objects that are added or edited will be displayed in the Objects tab.

To add a new data object click Objects 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.

Every data object has the following properties.

Note: Properties that are required are marked with an * asterisk.

Name*

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

Note: This will display all the of the sources tables, views, methods, stored procedures, and functions.

To add custom SQL click the Add Custom SQLbutton 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: { } [ ], . %

Alias*

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.

Category

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.

ID

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: By default all objects have an implicit object ID that is set behind-the-scenes. This field allows you to set the IDs explicitly. It is generally recommended to set explicit IDs for all data objects. As of version 2019.1.12, this field will display the implicit ID by default.

Parameters

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.

Tenant 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 filesmetadata 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, as 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 v2019.1+

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 > 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*/ASSET 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; no need to return any rows */begin set @sql = 'select * from vw_webrpt_person where 0 = 1'endelseif @callType = 1 /*return all data for execution */begin 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 + @orderbyClauseendelseif @callType = 2 /*return filter dropdown values; limit # rows to some value */begin set @columnInfo = '[' + @columnStr + ']' set @sql = 'select top 100 ' + @columnInfo + ' from vw_webrpt_person where ' + @columnInfo + ' >= ' + @filterStr + ' and ' + @fullFilterStr + ' order by ' + @columnInfoendexec(@sql)ss

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 Extensions tab under Functions. Exago handles table value functions similar to views and tables except it will pass any parameters set in the Data > Object 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 SQLbutton 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.

Parameter/Insert

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 Testbutton 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.

IfExecuteMode

(string trueCondition, string falseCondition)

Description

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

Example

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

IfExistReportDataObject

(string dataObjectName, string trueCondition, string falseCondition)

Description

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

Example

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 the Delete Row icon.

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.

Note: Column aliases may not contain the following characters: { } "

Column Description v2016.3+

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 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 Metadata 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 <

Encode > as >

Encode " as "

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

<element id="uniqueIdentifier" tooltip="<b>Description</b> 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

Guid

Currency

Filterable

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:

Advanced 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:

Advanced Report Designer

Express Report Designer

ExpressView Designer

Dashboard Designer

Scheduler

No

None (v2017.1.2+)

No

No

Sortable v2016.3+

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 v2019.1+

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.

Visible

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 v2016.3+

Custom Columns are a way to add columns to Exago that don't exist in the data source. This is completely transparent for the users; they can then use them like any other column. New data columns 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 Data > Objects group and double-click on the desired data object, or select it and click the Edit icon.

In the Object panel that opens, in the Column Metadata field,click on the Edit Metadata icon at the end of the line to open the Column Metadata dialog box.

Click the Add New button at the bottom left of the Column Metadata dialog box. Enter a name for the new column in the dialog box.

Data Type, Column Alias, and Column Value are required fields, the others are optional.

In the Column Value field, click the Formula Editor icon to open the Formula Editor and define the data for the new column. For example, to create a new Revenue column, you might enter the formula {OrderDetails.Quantity} * {OrderDetails.UnitPrice}.

Click Okay when done, then click Apply to save the new column to the configuration.

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 desired entity and add a new <column_metadata> element:

<entity> ... <column_metadata> <col_source>ExagoFormula</col_source> <col_name>FullName</col_name> <col_type>string</col_type> <col_alias>FullName</col_alias> <col_description>First and Last Name</col_description> <filterable>false</filterable> <col_value>{Employees_0.FirstName} & ' ' & {Employees_0.LastName}</col_value> <col_sortandgroupbyvalue>{Employees_0.LastName}</col_sortandgroupbyvalue> </column_metadata></entity>

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.

Examples:

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 Objects 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:

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.

For each data object open the Column Metadata menu.

Click the Read Schema button. A message will appear asking you to confirm you want to continue. Click Okay.

Click Okay to close the Column Metadata menu.

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 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

SET

NOCOUNT

ON

SET

FMTONLY OFF if @objectID = 'Produce' begin if @callType = 0 begin SELECT

ProductID,

ProductName,

SupplierID,

UnitPrice,

UnitsInStock

FROM

Products

WHERE

CategoryID = 1001

end

else if @callType = 1 begin SELECT

ProductID,

ProductName,

SupplierID,

UnitPrice,

UnitsInStock

FROM

Products

ORDER BY

ProductID

end

else if @callType = 2 begin SELECT

ProductID,

ProductName,

SupplierID,

UnitPrice,

UnitsInStock

FROM

Products

ORDER BY

ProductID

end

end if @objectID = 'Orders0' begin if @callType = 0 begin SELECT

OrderID,

OrderDate,

RequiredDate,

ShippedDate,

CustomerID

FROM

Orders

WHERE

CustomerID = 0

end

else if @callType = 1 begin SELECT

OrderID,

OrderDate,

RequiredDate,

ShippedDate,

CustomerID

FROM

Orders

ORDER BY

OrderID

end

else if @callType = 2 begin SELECT

OrderID,

OrderDate,

RequiredDate,

ShippedDate,

CustomerID

FROM

Orders

ORDER BY

OrderID

endend

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.

In the Administration Console edit the data object that contains the images. Open the 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.

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.

The Filter Settings panel provides control over what filter options are exposed to users and how the dropdowns within filters behave.

Extensions

The Filter Settings menu

The following Parameters can be set:

Show Group (Min/Max) Filters

Enables/Disablesthe Min/Max Filter menu. Set to False to disable users from using Min/Max filters.

Show Top N Filters v2017.1+

Enables/Disables the Top/Bottom Filters menu in the ExpressView and Advanced Report designer. Top N filters allow users to see the highest or lowest values for a data set. Set to False to disable users from using Top N filters.

Allow New Filters at Execution

Controls the creation of new filters when a user is prompted for a filter value at the time of report execution. Set to False to disable new filters from being created at execution.

Read Database for Filter Values

Enables/Disables filter drop downs to contain values from the database. Set to False only if retrieving the values for the dropdown will take more than a couple of seconds.

Allow Filter Dependencies

Causes filter drop downs to retrieve values dependent on the filters above them in the menu. Set to True to enable.

Note: This setting only works for Data Objects from databases and will not change drop downs from Web Services, .NET Assemblies, stored procedures, etc.

Note: Drop downs after an OR filter will not be dependent on previous filters.

Show Filter Description

Enables/Disables reports to have a description text for the filters menu. The filter description is set in the Description tab of the New Report Wizard or the Description menu. A help button will appear in the Filters menu and display the filter description when clicked.

Default Filter Execution Window

Determines the type of window that appears when prompting users for filter execution input:

Standard New reports display the standard filter execution window, allowing filters to be modified and new filters to be created.

Simple with Operator New reports display a simplified filter execution window that only allows the operator and value to be changed.

Simple without Operator New reports display a simplified filter window that only allows the value to be changed.

Allow User to Change Filter Window

Enables/Disables reports to change the type of filter execution window that is displayed.

Include Null Values for 'NOT' Filters

Indicates to include NULL values for filters with using the operators not equal or not one of.

Custom Filter Execution Window

Specifies a control or URL that contains Custom Filter Execution Window. See Custom Filter Execution Window for more details.

Restore All Default Date Filter Functions v2016.3+

Restores the default Filter Functions to the Extensions menu.

Restore All Default Formula Functions v2017.2+

Restores the default Custom Functions to the menu.

Drilldowns are reports linked from cells or charts as a means of exploring data points by "drilling down" into their background data. Use the Advanced Report Designer to add them.

The terms Linked Reports and Drilldowns are used interchangeably throughout the application and this document, but they refer to the same concept.

Note:Drilldowns work in the Report Viewer and Dashboard Viewer, but the linked data is not included in exported reports or dashboards.

link is being added

Drilling down from a column chart (top) and from a cell (bottom)

Drilldowns require a child report, which can be an Advanced, CrossTab or Express Report that contains the background data for the parent report. Drilldowns can be created on a Dynamic Cell or a chart by linking to the child report from the parent report. Clicking each cell value or chart series will filter the child report by its respective value before opening it. Depending on system configuration, the child report will open at the mouse cursor location, in the center of the screen or in a new report tab.

Dashboards as Child Reports

Beginning in v2019.2+, Dashboards can be drilled down to as child reports. Dashboard Drilldowns open in the standardDashboard Viewer in a new tab. There are a few special considerations when drilling down to a Dashboard:

Filters

When drilling down to a Dashboard, filters on the parent report will affect the Dashboard tiles differently.

Linking filters are filters created during the linking process. Interactive filters are filters created on a Dashboard, or from Report Viewer Options in the Report Designer.

Report Filters are filters created in the parent report using either the Report Wizard or the Filters menu of the Report Designer.

Tile Type

Linking Filter Behavior

Report Filter Behavior

Existing Advanced, Express or CrossTab Report

if the Filter Field is already on the report in the tile, or if it can be directly joined to the other Data Objects on the report, the filter is applied to the tile.

if the Filter Field is already on the report in the tile, the filter is applied to the tile. If not, the filter is not applied.

Existing ExpressView

if the Filter Field is already on the report in the tile, or if it can be directly or indirectly joined to the other Data Objects on the report, the filter is applied to the tile.

Visualization

if the Filter Field is already in the visualization in the tile, or if it can be directly or indirectly joined to the other Data Objects in the visualization, the filter is applied to the tile.

In the Dashboard Viewer's Filters Pane, all filters that are a result of the linking process except for non-prompting filters from the parent report are visible. These filters are:

Filters from the Fields tab of the Linked Report Wizard

Filters that are a result of the formula in the Formula Tab of the Linked Report Wizard

If the parent report is on a Dashboard, interactive filters from parent and parent-of-parent (aka grandparent) reports

Prompting filters from parent report

Note: Currently, the prompt window will not appear when drilling down into a child report that contains prompting parameters. However, this can be partially bypassed by adding the prompting parameter in a hidden cell on the parent report so that the user will be prompted at runtime for the parameter value.

Caution: Adding a drilldown to a cell may override or interfere with any linked Action Event on the cell. Adding a linked report to a chart may override some aspects of the chart's basic interactivity.

Since Drilldowns are themselves reports, they can have interactive sorts and filters, visualizations, be exported, and even have their own drilldowns.

Linking Types

Default Linking

By default, the Data Field in the parent report's linking cell filters the closest joined Data Field on the child report. The article on Joins has more information on data field joining. If there is no direct join path, then fields may be linked manually in the Fields tab of the Linked Report Wizard.

For example, a parent report links an Employees data field to a child report with an Orders category. The two categories are joined on Orders.EmployeeId >> Employees.Id. For each Employees row, its Employee.Id value filters the linked report down to the Order rows with matching Orders.EmployeeId values. Therefore, clicking on an Employee name in the parent report will drilldown to a list of orders that the employee made.

Orders linked report filtered by Employee Id

Tip: Linking filters do not cascade down to grand-children, or child reports of the linked report.

Manually Defining Link Fields

To link on different fields or categories than the default link, use the Fields tab of theLinked Report Wizard. The From fields from the parent report filter the To fields on the child report. The Fields tab is suitable for the following situations:

Tip: The From fields from the parent report filter the To fields on the child report.

The default join is not the desired link to use Example: Linking on relatedfields other than the Id field, such as "Region" Employees.Region >> Orders.ShipRegion

No join exists between the From and To categories Example: Categories have related fields but are not joined, such as Orders and Suppliers Orders.ShipCity >> Suppliers.City

The From and To categories are the same Example: Fields are related to other fields in the category, such as EmployeeXsupervises EmployeesY and Z Employees.Id >> Employees.ReportsTo

Conditionally Link with Formula

Note: This feature is disabled in system configuration by default. Contact your system administrator for assistance.

Use the Formula tab of the Linked Report Wizard to conditionally enable drilldowns on specific values. By default, once a drilldown is setup, all records on the report can drilldown to the child. By adding a Formula, only values meeting the condition will permit drilling.

The formula must return True or False. The formula is evaluated for each row in the parent report, and if the condition is not met, drilling down to the child report is not enabled.

For example, the following formula could be used to create a drilldown to a report showing a suppliers list if and only if there are items on order or the quantity in stock is less than the product's reorder level. Those products that do not meet that criteria cannot be drilled down.

Or({Products.UnitsOnOrder} > 1, {Products.UnitsInStock} <= {Products.ReorderLevel})

Therefore, only Queso Cabrales, Gorgonzola Telino, and Marscarpine Fabioli meet the criteria. Clicking on the other products will have no effect.

See the Formulas article for assistance with conditional formulas.

Adding Linked Reports

Add a new Advanced,CrossTab or ExpressReport that contains the drilldown data. This will become the linked child report.

Linked reports typically open in a small window, so the child report should be simple and concise. Avoid large fonts, a lot of static content, or making it too large in size. Thechild report's General Option for No Data Qualify Display Mode should be set to Show Report in order to show an empty drilldown instead of an error message.

In the parent report, select the cell to link then click the Link Reports icon in the toolbar. This will open the Linked Report Wizard.

The Linked Report Wizard

Select the desired child report. This will use default linking to drilldown to the child report.

To manually define link fields:

The Fields tab of the Linked Report Wizard

Select the From Category and To Category from their respective dropdowns.

Click the Add button for each set of linked fields to add. Use multiple linked fields to showonly the drilldown rows that satisfy all of the link conditions.

For each set of linked fields, select the From Field and To Field.

To conditionally link with formula:

The Formula tab of the Linked Report Wizard

Add a data field by dragging and dropping it into the Formulapane or double-clicking it. Or enter it manually using the following format: {DataCategory.DataField} Caution:Linked report conditional formulas support onlyone data field. If multiple data fields are used, all but the first will be ignored.

Add a Parameter by entering it manually using the following format: @ParameterName@.

Add a function by dragging and dropping it into the Formula box or double-clicking it, or enter it manually.

Click Okay to close the Linked Report Wizard.

Caution: Because the child is a separate report from the parent,if thechild report is moved to another folder, or the linked field is removed from the report, the link will be broken.

Modifying Linked Reports

Click the Link icon in the corner of the linking cell, or click the cell and then click the Link Reports icon in the toolbar.

Interact with the Linked Report Wizard just as if the (continue at step 2).

Deleting Linked Reports

Click the Link icon in the corner of the linking cell, or click the cell and then click the Link Reports icon in the toolbar.

Click theRemove Link icon.

Click Okay.

The following Report Designer toolbar items can be attached to a Click Action Event.

ID

Icon

Description

ReportOptionsBtn

Promise

Report Settings menu

SaveReportBtn

Save Report

DesignNewReportBtn

Design New Report

UndoBtn

Undo

RedoBtn

Redo

FormatCellsBtn

Cell Format window

FormatPaintbrushBtn

Format Paintbrush

BoldBtn

Bold

ItalicBtn

Italic

UnderlineBtn

Underline

UnderlineSelect

Underline type select

AlignTopBtn

Vertical Align - Top

AlignMiddleBtn

Vertical Align - Middle

AlignBottomBtn

Vertical Align - Bottom

MergeCellsBtn

Merge Cells

SplitCellsBtn

Split Cells

AlignLeftBtn

Horizontal Align - Left

AlignCenterBtn

Horizontal Align - Center

AlignRightBtn

Horizontal Align - Right

AlignJustifyBtn

Horizontal Align - Justified

WrapTextBtn

Wrap Text

AutoSumBtn

Auto Sum

EditFormulaBtn

Formula Editor

SuppressDuplicatesBtn

Suppress Duplicates

CrossTabWizardBtn

CrossTab Report Wizard

LinkedReportBtn

Linked Report/Drilldown

LinkedActionBtn

Linked Action

GoogleMapBtn

Google Maps Wizard

MapBtn

GeoCharts Wizard

ID

Icon pre-v2019.2

Icon v2019.2+

Description

ReportOptionsBtn

Report Settings menu

SaveReportBtn

Save Report

DesignNewReportBtn

Design New Report

UndoBtn

Undo

RedoBtn

Redo

FormatCellsBtn

Cell Format window

FormatPaintbrushBtn

Format Paintbrush

BoldBtn

Bold

ItalicBtn

Italic

UnderlineBtn

Underline

UnderlineSelect

Underline type select

ForegroundColorBtn

Foreground (text) color

BackgroundColorBtn

Background (fill) color

FontSize

Font Size

AlignTopBtn

Vertical Align - Top

AlignMiddleBtn

Vertical Align - Middle

AlignBottomBtn

Vertical Align - Bottom

MergeCellsBtn

Merge Cells

SplitCellsBtn

Split Cells

AlignLeftBtn

Horizontal Align - Left

AlignCenterBtn

Horizontal Align - Center

AlignRightBtn

Horizontal Align - Right

AlignJustifyBtn

Horizontal Align - Justified

WrapTextBtn

Wrap Text

AutoSumBtn

Auto Sum

InsertPictureBtn

Insert Picture

EditFormulaBtn

Formula Editor

SuppressDuplicatesBtn

Suppress Duplicates

CrossTabWizardBtn

CrossTab Report Wizard

LinkedReportBtn

Linked Report/Drilldown

LinkedActionBtn

Linked Action

ChartBtn

not available pre-v2019.2

Chart Wizard

GoogleMapBtn

Google Maps Wizard

MapBtn

GeoCharts Wizard

GaugeBtn

not available pre-v2019.2

Gauge Wizard

ExpressView v2019.1.12+

Beginning with v2019.1.12+, the following ExpressView toolbar items can be attached to a Click Action Event.

ID

Icon

Description

ExpressViewLiveDataButton

Live Data

Live Data

Start/Stop using live data

Example

The return value of the embedded JavaScript determines whether the result of clicking the button should take place.

If the Action Event returns True, then toolbar item action is cancelled

If the Action Event returns False, then the toolbar item action is carried out

If the action event implementation returns a, then the toolbar item action continues if and when that Promise resolves False.

string JsCode = @"(function() {

return !confirm('Continue?');

}())";

sessionInfo.JavascriptAction.SetJsCode(JsCode);

return sessionInfo.JavascriptAction;

Dashboards are a way to combine a several related reports into one unified viewing space. You can add preexisting reports to a Dashboard, but you can also create new ExpressViews and visualizations directly on the Dashboard itself. You can also add images, text, embed other web sites side by side with your data, add interactive filters which can work on multiple reports all at once, and export the Dashboard as a Chained Report from the Dashboard Designer.

Exporting Dashboards

The Dashboard Designer

Double-click a Dashboard to open it in the Dashboard Designer, or click the Menu icon and select Edit.

For more information about the types of content that can be added to a Dashboard, see the following links:

Adding Reports ExpressView Visualizations Interactive Filters

Exporting Dashboards

Grid and tiles

Each element on a Dashboard is a rectangular tile that can be resized and dragged to the proper location. Tiles are arranged onto a grid, and they will snap into place next to each other.

Adding tiles

To add a new tile to the Dashboard, drag the New Tile icon onto the dashboard grid. You can drag to an empty location to fill the space, to the side of an empty location to take up a portion of the space, or over another tile to place it adjacent and resize the other tile to fit. You can then drag the resizing handles for fine grained control over the tile size.

A new tile placeholder will be added.

Select what type of content should be on the tile:

New Visualization

Create a new ExpressView visualization in the tile. Choose between a chart or a tabular ExpressView. See ExpressView Visualizations for more information.

URL

Embed another web page inside the Dashboard. Enter the URL, or web address, to the textbox and click Finish.

Caution: Some web pages may not be embeddable.

Image

Upload an image from your computer. Drag the image onto the tile or click browse your files and locate the image.

Text

Enter text into a field that can be formatted and styled.

Filter

Add several styles of interactive filters, which can affect multiple reports on the Dashboard. Filter tiles will not display in Dashboard exports. See Interactive Filters and Exporting Dashboards for more information.

Existing Report

Drag an existing report onto the tile. See Adding Reports for more information.

Screen fit and scaling

Tiles can resize and adjust their positions automatically to fit different screen sizes. A Dashboard will automatically resize to fit on a large television or a smartphone screen, so there is no need to make multiple Dashboards for different screen sizes.

You can customize how Dashboards will scale to fit different screen sizes. Click the styleicon to open the Dashboard Style pane. The following Canvas Fit options are available:

All

Tiles will resize to fit the height and width of the screen (default).

Tiles must be larger than 3-grid units tall and/or 4-grid units wide or less to automatically resize.

Width

Tiles will resize to fit the width of the screen. Specify the Height of the Dashboard and whether or not to Automatically Reposition Tiles, which dynamically derives an optimal new layout for the Dashboard tiles using the screen size as input.

Tiles must be larger than 20 pixels tall or 4-grid units wide to automatically resize.

None

Tiles will not resize. Specify the Height and Width of the Dashboard.

Use the Snap to Grid icon to choose if tiles should snap to the grid lines.

Note: Legacy Dashboards default to Canvas Fit: None and snapping off.

You can also use the Dashboard Style pane to set the default options for the Dashboard background color, tile and tile header background colors, and tile border colors and widths. These settings can be overridden for individual tiles.

Managing tiles

Every tile has a formatted header, background color, and border. Some types of tiles can be set to resize differently than the dashboard canvas. Tiles have a menu with some additional options.

Tile menu

Select a tile, then click the tile menu icon to access some additional options for managing tiles.

You can Delete a tile from the Dashboard. You can Copy a tile then Paste it next to another. You can Expand a tile to temporarily fill the screen, then Collapse it back into place. And you can Refresh the data in reports and visualizations.

ExpressView visualizations can be saved as new ExpressViews by clicking Save as ExpressView. You will be asked to name the report, and will be taken into the ExpressView Designer.

Tile style

Select a tile, then click the Style tab to access the options for changing its appearance.

Click Tile Fit to choose how the tile scales fit to he screen. This option is not available for filters or ExpressView visualizations.

Click Tile Header to add header text to the tile. Then choose the text font and formatting.

Click Background and Border to change the background color of the tile and tile header, and the border color and style.

More information

See Adding Reports, ExpressView Visualizations, Interactive Filters, and for more information about their unique features and options.

Data Sources establish the connection between Exago and a database or a web service. Although typically only one database is used, Exago can join data from different sources into a single report.

Note:To utilize some types of data sources you may need to download and install the appropriate driver. Refer to Data Source Drivers below.

Creating, Editing or Deleting Data Sources

All existing data sources are listed in the Admin Console's Main Menu under Data. All the sources you are adding or editing will be displayed in theData > Sources tab.

To add a new data source click Sources in the Main Menu then click the Add CData Drivers icon.

To edit a data source either double click it or select the data source and click the Edit icon.

To delete a data source select it and click the Delete icon.

Click the Test Connection icon to verify the connection succeeds.

To save changes click the OkayorApplybutton.

Each data source must have the following:

Name

A name for the data source.

Type

The type of source being used. Valid types include:

Relational databases

SQL Server Micrsoft SQL Server

MySQL

Oracle

Postgres PostgreSQL

DB2 IBM db2

Informix IBM Informix

ODBC ODBC Driver

Non-relational databases

MongoDB (v2018.2+)

ElasticSearch (v2018.2+) ElasticSearch/ELK database (For more information see CData Drivers.)

Data warehouses

Redshift (v2019.1.11+)

Snowflake (v2019.1.9+)

Other

File XML or Excel file (For more information see Excel and XML Files.)

.NET Assembly .NET Assembly DLL (For more information see .NET Assemblies.)

SOAP Web Service Web Service (For more information see Web Services.)

MS OLAP OLAP (For more information OLAP and MDX Queries.)

Schema/Owner Name (blank for default)

Provide a default database schema for the data source.

Note:Only use this if you are using schema to provide Multi-Tenant security. For more details see Multi-Tenant Environment Integration.

Connection String

The method that is used to connect to the data source. Connection strings vary by type:

Type

Connection Strings

mssql, oracle, postgres, mysql and olap

Refer to connectionstrings.com for database connection strings.

websvc

Required parameters:

url The URL of the web service.

Optional parameters:

authentication Set to basic to utilize basic authentication through IIS. This will send the user ID and password as clear text (unless https is used).

uid User ID is passed to the web service.

pwd Password is passed to the web service.

assembly

Required parameters:

assembly The full path of the assembly name.

class The class name in the assembly where the static methods will be obtained.

file

Requires the physical path to the Excel or XML file and the file type. Example: File=C:\example.xls;Type=excel;

mongodb

For more information see CData Drivers

elasticsearch

For more information see CData Drivers

snowflake

For more information see CData Drivers

redshift

For more information see CData Drivers

Click the Test Connectionicon to verify the connection succeeds.

Click the Make Connection String Visible or Connection String Hide icons to show/hide the connection string in the Data Source tab.

Data Source Drivers

Below is a list and the associated links for recommended ADO.NET drivers for each type of data source.

SQL Server

No external ADO.NET driver needed

Oracle

ODAC1120320_x64 or newer

Oracle ODAC Connector

MySQL/MariaDB

dcmysqlfree.exe

Devart Connector

PostgreSQL

dcpostgresqlfree.exe

Devart Connector

DB2/Informix

5.exe or newer

IBM Data Server Driver Package

Elasticsearch, Redshift, Snowflake, MongoDB

CData ADO.NET drivers

Web Services and .NET Assemblies

Web Services and .NET Assemblies can be used as data sources. This is possible when the Web Service and .NET Assemblies underlying methods are setup as data objects.

An advantage of doing this is being able to use high-level language to manipulate the data being reported on at run-time. The main disadvantage is not being able to take advantage of the database to perform joins with other data objects; data from methods can still be joined, but the work to do this is done within Exago. For more information see Note about Cross Source Joins.

Parameters

Parameters are passed from Exago to Web Services and .NET Assemblies. Three types of parameters can be passed but only Call Type is required.

Call Type (required)

Integer that specifies what Exago needs at the time of the call. There are three possible values. You may specify the name of this parameter in the Programmable Object Settings of theGeneral section.

0 : Schema - returns a DataSet with no rows.

1 : Data - returns a full DataSet.

2 : Filter Dropdown Values returns data for the filter dropdown list. The Data Field being requested is passed in the column parameter. The filter type is passed in the filter parameter (see below).

Column, Filter and Sort Strings (optional)

To optimize performance Exago can pass user-specified sorts and filters to the Web Service or .NET Assembly. This process reduces the amount of data sent to Exago. If these parameters are not used, all of the data will be sent to Exago to sort and filter. Column, filter and sort strings are sent as standard SQL. You may specify the name of these parameters in the Programmable Object Settings of theGeneralsection.

Custom Parameter Values (optional)

Additional parameters can be specified to be sent to individual methods in the Data Object Menu.

Important:When a Web Service or .NET Assembly is first accessed it is compiled and kept in an internal cache within Exago. This is done in order to increase performance. Due to this internal cache, Exago will not be aware of any changes within the Web Service or .NET Assembly. If the service or assembly is subsequently changed, Exago will execute the prior compiled version. Thus, when you modify the Web Service or .NET Assembly reset the internal cache of Exago by clicking theconnection verificationicon of the Data Source or by restarting IIS.

Note:If an Exago .NET API application needs to access reports, which use an assembly data source. It must include a reference to the assembly WebReportsAsmi.dll.

SessionInfo (optional) (v2016.2+)

Session state variables. See SessionInfo for more information.

.NET Assemblies

It is important to note that when a connection string for .NET Assembly is set the class name must match the name of the class where the static methods will be searched. UNC or absolute paths may be used. Make sure that the assembly has read privileges for the IIS user running Exago. Below is an example of a .NET Assembly connection string:

assembly=\\MyServerName\MyShareName\MyAssembly.dll;class=Main

.NET Assembly methods must be static. Below is an example of a .NET Assembly method.

public class Main

{

public static DataSet dotnet_optionees(int callType, string columnStr, string filterStr, int myCustomParameter)

{

switch (callType)

{

case 0:

// return schema

case 1:

// return data

case 2:

// return filter values for dropdown

}

}

}

Method signature using SessionInfo (v2016.2+):

public class Main

{

public static DataSet dotnet_optionees(WebReports.Api.Common.SessionInfo sessionInfo, int callType, string columnStr, string filterStr, int myCustomParameter)

{

switch (callType)

...

}

}

Web Services

Web Services are accessed via SOAP. Below is an example of a Web Service connection string:

url=http://MyServer/MyWebService.asmx

Web services methods are similar to .NET Assembly methods with the following exceptions:

Methods do not need to be static

Methods must return a serialized XML string. The returned XML must follow the structure used by the C# method DataSet.GetXML. An example of XML format can be found in the following section.

Excel and XML Files

Exago can use Microsoft Excel and XML files as data sources. Remember though that Excel and XML files are not databases. Simply put, these data sources do not offer the speed, performance, or security of a real database. Using Excel and XML files is recommended only if your dataset is small or if the information is only available in this format.

Connection String

File=C:\example.xls;Type=excel;

Excel

Each worksheet in the Excel file will be read as a separate table. Each worksheets name will be read as the tables title. The top row will be read as the column header, and the remaining cells will be read as the data. Do not leave any blank rows or columns.

XML

The XML document must begin with the schema. After defining the schema the data must be placed into the appropriate tags. For reference see the working example below:

<?xml version="1.0" encoding="UTF-8"?>

<ExagoData>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="ExagoData">

<xs:element name="ExagoData" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="Call">

<xs:complexType>

<xs:sequence>

<xs:element name="CallID" type="xs:unsignedInt" minOccurs="0" />

<xs:element name="StaffID" type="xs:string" minOccurs="0" />

<xs:element name="VehicleUsed" type="xs:unsignedInt" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name="Staff">

<xs:complexType>

<xs:sequence>

<xs:element name="StaffID" type="xs:unsignedInt" minOccurs="0" />

<xs:element name="Rank" type="xs:string" minOccurs="0" />

<xs:element name="LastName" type="xs:string" minOccurs="0" />

<xs:element name="FirstName" type="xs:string" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

<Call>

<CallID>890</CallID>

<StaffID>134</StaffID>

<VehicleUsed>12</VehicleUsed>

</Call>

<Call>

<CallID>965</CallID>

<StaffID>228</StaffID>

<VehicleUsed>4</VehicleUsed>

</Call>

<Call>

<CallID>740</CallID>

<StaffID>1849</StaffID>

<VehicleUsed>2</VehicleUsed>

</Call>

<Staff>

<StaffID>134</StaffID>

<Rank>Captain</Rank>

<LastName>Renolyds</LastName>

<FirstName>Malcom</FirstName>

</Staff>

<Staff>

<StaffID>228</StaffID>

<Rank>Lieutenant</Rank>

<LastName>Brown</LastName>

<FirstName>Bill</FirstName>

</Staff>

<Staff>

<StaffID>1849</StaffID>

<Rank>Sergeant</Rank>

<LastName>John</LastName>

<FirstName>Pepper</FirstName>

</Staff>

</ExagoData>

OLAP and MDX Queries

Exago can query OLAP Data Sources using MDX Queries. OLAP Data Sources and Objects are identical to a regular data base type object, with the following exceptions.

OLAP Data Objects will always be MDX Queries written in the Custom SQL Object

Data Objects must have Schema Access Type set to Metadata and must have Column Metadata set for all fields.

Using Azure based SSAS

In order to use an Azure based SQL Service Analytic Service Database (SSAS) you need the most up to date ADOMD.NET driver, which is available from Microsoft. Then, in order to properly configure Data Object entities through joins, the following information must be provided:

The entity containing the primary key should be specified in the "From" section.

The entity containing the foreign key should be specified in the "To" section.

The IDs used in the join configuration must be included in the metadata fields and should be available in the design model.

ODBC Drivers

Exago can use ODBC drivers to connect to data sources. When connecting to an ODBC data source, an extra option will appear to set the Column Delimiters. The delimiter character depends on which type of data base you are connecting to.

Examples

MySql

' (grave accent)

MsSql, OLAP

[] (brackets)

DB2, Informix, Oracle, Postgres, Sqlite

" (quotation marks)

If you don't know which delimiter character to use, contact your database administrator.

Caution:If your data objects have spaces in their names, you must set the correct delimiter in order to access the data. Otherwise, improper SQL will be generated and you will see errors or erroneous data.

CData Drivers

As of v2018.2+, Exago allows for additional data source types through CData ADO.NET drivers. For more information, see the article on .

This article lists all the available keyboard shortcuts in the Web Application User Interface and describes their behavior in their applicable contexts.

Where a plus sign + appears between two keys, it means hold down the key before it and press the key after it. For example, Ctrl+V means hold down the Control key and then press the V key. Release both keys after pressing V.

Global

Ctrl+Down Arrow or Ctrl+Right Arrow Next tab

Ctrl+Up Arrow or Ctrl+Left Arrow Previous tab

Ctrl+Shift+V Show application version number

Ctrl+Shift+X Create debug package (only if debugging is enabled in system configuration)

Report / Data Tree

[ Toggle last open tree pane

Up Arrow Previous item

Down Arrow Next item

Delete Delete selected report

Enter Edit selected report

New Report Menu

Up Arrow Previous item

Down Arrow Next item

Enter Select item

Esc Close menu

Advanced Report Designer

Arrows Select cells

Ctrl+S Save report

Delete Delete cell contents

Ctrl+C Copy cell contents

Ctrl+X Cut cell contents

Ctrl+V Paste cell contents

Ctrl+Click Select multiple cells, rows, or columns

Shift+Click Select a range of cells, rows, or columns

Ctrl+Z Undo

Ctrl+Y Redo

Ctrl+B Bold cell text

Ctrl+I Italicize cell text

Ctrl+U Underline cell text

Tab Select next cell

Shift+Tab Select previous cell

Enter Open cell text editor / close cell text editor and apply changes

Esc Close cell text editor without applying changes / close wizard or modal window

Filters Menu

Ctrl+[ Add an opening parenthesis before the selected filter

Ctrl+] Add a closing parenthesis before the selected filter

Ctrl+Shift+[ Remove an opening parenthesis before the selected filter

Ctrl+Shift+] Remove a closing parenthesis before the selected filter

Report Joins Menu (Complex)

Ctrl+. Add parentheses around last clicked condition

Ctrl+, Remove parentheses from around last clicked condition

Formula Editor

Up Arrow Select previous item in search list

Down Arrow Select next item in search list

Right Arrow Select next argument in formula

Left Arrow Select previous argument in formula

Color Picker Menu

Enter Apply selected color and close picker

Esc Close picker without applying color

ExpressView

Page Up Scroll up

Page Down Scroll down

Ctrl+Home Start of report

Ctrl+End End of report

Ctrl+Arrows Scroll the report

N Next page

P Previous page

S Toggle sorts menu

F Toggle filters menu

O Toggle formatting menu

E Toggle options menu

V Toggle visualizations menu

C Toggle selected cell menu

Shift+V Show/hide visualization

Ctrl+R Refresh data

Ctrl+S Save ExpressView

Ctrl+F Show search report menu

Esc Close radial menu

ExpressView Search

Enter Find next result

Shift+Enter Find previous result

Esc Close menu

Dashboard Designer

E Open/close options menu pre-v2019.2, opens the Save menu in v2019.2

Ctrl+S Save

Esc Deselect all tiles

Arrow keys Moves the selected tiles

Delete Delete the selected tiles

Ctrl+A Select all tiles

Ctrl+C Copy selected tiles

Ctrl+V Paste copied tiles

F Toggle filters menu

O Toggle formatting menu

Dashboard Viewer

F Open/close the Filters pane

P Open/close the Parameters pane

We store our guides within this article as hidden attachments for use on our download page. If you found yourself here in error hoping to download our guides, you may do so on our download page.

Installationand Updating to Exago guides are already stored in their respective 'Application Installers' pages.

Aggregate Functionscalculate valuesbased on two factors: the input cell, which is usually but not always a data field, and the report section that the function is located, relative to the input. The location of the function determines which, and how many cell values will be sent to the input. For example, an AggCount() function in a Group Footer will countthe values in the group, but that same function in a Report Footer will countthe values for the entire report.

Beginning with v2019.2, Aggregate Functions can also be used in Group Headers.

Note:Aggregate Functions cannot be used in conditional formulas or ExpressView formula columns.

Note: Aggregate Functions are not capable of summarizing data within static sections of a report, such as the Page and Report Headers and Footers. For more information, please see the article on static sections.

Aggregating One-to-Many Relationships

As of v2016.3.8, Aggregate Functions have an optional argument that indicates how to count aggregates for one-to-many relationships named recordLevel. This argument indicates whether aggregation should occur at the record level or entity level. Pass True() to force aggregation to occur for every record, regardless of whether or not the record represents a unique instance of the entity that is being aggregated. This is called record level aggregation.

By default aggregation occurs only for every unique instance of the entity being aggregated, not for repeat values that occur from a one-to-many data relationship.

In application v2019.2+ by default aggregation occurs for all values in a one-to-many data relationship. To aggregate only for each unique instance, False() must be passed to force aggregation on the entity level. This default behavior can be changed by a system administrator.

In application versions pre-v2019.2 by default aggregation occurs only for every unique instance of the entity being aggregated, not for repeat values that occur from a one-to-many data relationship. This default behavior can be changed by a system administrator.

For example, in the two reports below, the Category Name column is being aggregated with the AggCount() function. The top figure aggregates on the entity level (recordLevel argument is False), and the bottom figure aggregates on the record level (recordLevel argument is True). Since the Category Name does not change, when aggregating by entity level, the count is 1. When aggregating on the record level, all records are considered for the aggregation and the count is 11.

Aggregating CategoryName on entity level (recordLevel is False)

Aggregating CategoryName on record level (recordLevel is True)

Most users can choose to ignore this option by simply omitting the argument.

In application v2019.2, an administrator can add their own aggregate functions to the application, called a Custom Aggregate Function. Consult with your administrator for more details about what functions are available when building reports.

AggAvg

Description

Returns the average of the values in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count:

Records: True

Entites: False (default)

Example

AggAvg({OrderDetail.Quantity}) returns the average quantity of sales orders.

AggCount

Description

Returns the number of unique entities in a Data Category.

Remark

The AggCount() function uses the Data Category, not the Data Field. For example, the function AggCount({Officer.Salary}) counts the number of Officers. You could replace Officer.Salary with any other field in the Officer Data Category and the function would still count the number of officers.

Optional second argument (v2016.3.8+) indicates whether to count:

Records: True

Entites: False (default)

Example

AggCount({Orders.ProductPrice}) returns the number of sales orders.

AggDistinctCount

Description

Returns the number of unique values in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count:

Records: True

Entites: False (default)

Optional third argument (v2018.1.8+) indicates whether to count null as a distinct value.

Count:True

Ignore:False (default)

Example

AggDistinctCount({OrderDetail.Quantity}) returns the number distinct quantities in an order.

AggMax

Description

Returns the maximum value in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count:

Records: True

Entites: False (default)

Example

AggMax({OrderDetail.Discount}) returns the largest discount.

AggMin

Description

Returns the minimum value in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count:

Records: True

Entites: False (default)

Example

AggMin({OrderDetail.Discount}) returns the smallest discount.

AggSum

Description

Returns the sum of the values in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count:

Records: True

Entites: False (default)

Example

AggSum({OrderDetail.Quantity}) returns the total quantity of units ordered.

RunningSum

Description

Returns a running total of the input cell.

Remark

The cell you want to sum.

Optional second argument of a Data Field or Category. The running sum will reset to 0 whenever there is a new value for this Data Field or Category.

Optional third argument (v2016.3.8+) indicates whether to count:

Records: True

Entites: False (default)

Examples

RunningSum({Employees.Salary}) returns running total of all the employees salary.

RunningSum({Employees.Salary}, {Employees.Region}) returns a running total of employees salary for each region.

RunningSum({Employees.Salary}, {Company}) returns a running total of employees salary for each Company.

AggMedian v2017.2+

Description

Returns the median, or the middle value, of a data set.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count:

Records: True

Entites: False (default)

Example

AggMedian({OrderDetail.Quantity}) returns the median order quantity.

AggMode v2017.2+

Description

Returns the mode, or the value that appears most often, of a data set.

Remark

Accepts data fields or cell references.

Optional second argument indicates whether to count:

Records: True

Entites: False (default)

Example

AggMode({OrderDetail.Quantity}) returns the mode of order quantity.

AggStandardDeviation v2017.2+

Description

Returns the standard deviation (SD) of a data set. SD is used to quantify the spread of the values in a data set. A lower SD indicates that values are close to the mean, and a higher SD indicates that values are more spread.

Remark

Accepts data fields or cell references.

Takes one, two, or three arguments:

The cell to get the standard deviation

Optional second argument indicates whether to calculate:

Sample-based standard deviation: "String"

Population-based standard deviation: "Population"

Optional third argument indicates whether to count:

Records: True

Entites: False (default)

Example

AggStandardDeviation({OrderDetail.Quantity}) returns the standard deviation of order quantities.

AggVariance v2017.2+

Description

Returns the variance of a data set. Variance is used to quantify the spread of the values in a data set, and is equal to the square of the standard deviation. Variance weighs outliers more heavily than standard deviation.

Remark

Accepts data fields or cell references.

Takes one, two, or three inputs:

The cell to get the variance

Optional second argument indicates whether to calculate:

Sample-based standard deviation: "String"

Population-based standard deviation: "Population"

Optional third argument indicates whether to count:

Records: True

Entites: False (default)

Example

AggVariance({OrderDetail.Quantity}) returns the variance of order quantities

You can exercise some fine grained control over the actual data that appears in the ExpressView. You can choose styling options, you can filter down the rows to appear, and you can change the order of rows in their respective sections.

Formatting and Style

Formatting allows you to specify a data type (e.g.number,date, text) for specific data fields, and choose how that data displays.

To change the data type for a field:

Click the Formatting and Style the article on aggregation icon in the pane on the right to open the Formatting page.

Click the data rows to format. They will highlight in blue.

Selecting data to format

In the pane on the right side of the screen, click the Data Formatheading to open it.

From the Format Type list, select a data type from the following options:

General

Format the data using the default settings for your environment. The application will assume the data type.

Number

Format the data as a number.

In the Decimal Places field, enter a number for how many decimal places to display. In the field to the right, enter a symbol to use as the decimal mark.

To show a delimiter every three digits, select Use 1000 Separator. Then, in the field to the right, enter a symbol to use as the delimiter.

To show a symbol before the number, indicating that this is currency, select Use Currency Symbol. Then, in the field to the right, enter the symbol to show.

To show a percent sign after the number, indicating that this is a percentage, select Append Percent Sign.

To show no value if the number is 0, select Blank When Zero.

To showthe negative symbol in front of negative numbers, select Show Negative Symbol.

To show parentheses around negative numbers, select Show Parenthesis.

To show negative numbers in a different color, select Color, then enter a hex value or use the color picker to choose a color.

Date

Format the data as a date, time, or date and time. You can choose which date and timecomponents to display, and how to show them.

Choosing a date/time format

Text

Do not apply any formatting to the data, and show it exactly as it appears in the database

If the data field cannot be formatted as a number or date, then selecting one of those options will have no effect on the appearance of the data.

Sorting

Sorting allows you to set the order that the data rows appear in each section. Click the Sorts icon to open the list of sorts. Every data columnin the ExpressView that is not a group column is automatically sorted.

You can choose which columns take precedence for sorting. The order of fields on the Sorts page is their order of precedence, from highest to lowest. Their order is also indicatedby a numberon the right of thecolumn headers: The lower the number, the higher the precedence.

To set the sort precedence of data fields, either:

On the Sorts page, drag fields up or down.

Use radial >down to give a field the highest precedence.

Dragging a sort to change its precedence

You can choose which direction to sort the data for each data field: ascending or descending. A field's sort direction is indicated by an arrow on the right of the column header: Up for ascending, down for descending.

To change a field's sort direction, either:

On the Sorts page, select asc for ascending, or desc for descending.

Click the column header to swap to the opposite direction.

Filtering

Filtering allows you to narrow the scope of your ExpressView by restricting the amount of data shown.

Standard Filters allow only rows whose values satisfy certain conditions to appear in the report.

Top N filters allow only the rows with the top or bottom values, for either data fields or data summaries, per group iteration to appear in the report.

Click the Filters icon in the pane on the right side of the screen to open the list of filters. The filters page has two tabs: Standard and Top/Bottom.

To show only the data that satisfies several conditions:

On the Filters page, click the Standard tab.

To add a data field tofilter, either:

Use radial >right on a data column or group.

Drag a data field from the Data Pane on the left side of the screen to the Meet all of the following conditions pane.

Note: This allows you to filter any accessible data field, not just those on the ExpressView.

Choose a filter operator from the list. See Filters for details.

Enter a filter value or values, or select them from the list.

Repeat steps 2-4 for every filter conditionthat the data must satisfy in order to show.

If you are viewing live data, click Apply Changes to apply the filters.

To show only the data that satisfies at least oneof several conditions:

On the Filters page, click the Standard tab.

To add a data field tofilter, either:

Use radial>right on a data column or group. Then drag the filter to the meet any of the following conditions pane.

Drag a data field from the Data pane to the meet any of the following conditions pane.

Note:This allows you to filter any accessible data field, not just those on the ExpressView.

Choose a filter operator from the list. See Filters for details.

Enter a filter value or values, or select them from the list.

Repeat steps 2-4 for every filter condition, of which the data must satisfy at least one in order to show.

If you are viewing live data, click Apply Changes to apply the filters.

Adding a field as a Standard filter

To show only the top or bottom values, for either data fields or data summaries:

On the Filters page, click the Top/Bottom tab.

Select the Limit the report to the top/bottom values check box.

Select either Top or Bottom, for whether you want to show the top or bottom values.

Enter a number for how many values you want to show.

Choose how you want to limit your data:

If you want to show the rows with the top or bottom data values for a field or group, select Values from the list.

If you want to show the groups with the top or bottom summary values for a parent group or the ExpressView, select one of the summary calculations, Sum, Avg, Min, Max, Count, or Distinct Count, from the list. See for more information.

Note:Only numeric data fields support Sum and Avg calculations.

Select the data field or group field to filter from the Of list.

Optional: To show the top or bottom values for each iteration of a group:

Click Add Group.

Select a group field from the For Each list.

Adding a Top/Bottom filter

Note: Formula columns are available beginning with v2018.1+ of the application.

Formulas can be used to create custom data columns in ExpressView. Formulas are calculated once per each row, based on the other data values in that row. For each row calculation, a formula value is returned for that row, which populate a new column of data. You can use these columns just like any others - format the data, add to a visualization, or group based on formula columns. They are treated the same as data columns.

To add a formula column to an ExpressView, open the Formulas Data Pane then click+ Add Formula at the top of the pane. A blank column will be added to the ExpressView, and theFormula Builderwill open.

In the right pane, theFormula section of the Selected Cellpage shows the available built-in functions and parameters. Hover over a function to see its description and an example of how to use it. To add a function or parameter to the formula, drag it from the Formula page to the Formula Builder window, type its name into the Formula Builder, or double-click the name of the function while the formula editor dialog is open. You can use theSearchfield in the to filter the functions by name.

Formula editor dialog

To add a data field to the formula, either:

drag it from the Data Pane to the Formula Builder window

type its name into the Formula Builder

double-click the field name while the Formula Builder is open

Note: Double-clicking the field names will place the fields wherever the cursor is in the formula editor at that time. Double-clicking enabled in v2018.2 and later.

Tip: To use a data field, function, or parameter as a function argument, drag it to the argument placeholder until the placeholder turns blue. Or click the placeholder and type the name of the desired argument.

Dragging a field to a function argument

Typing in theFormula Buildershows a list of functions, parameters, and data fieldsthat match the text. Click on an item, or use the up and down arrow keys, to highlight an item and see its description. To addthe selected item to the formula, press theEnterkey.

When you are finished, click theApply Changesicon at the top of the of the Formula Builder to save the formula.

To edit an existing formula column, click Selected Cell, open the Formula tab, then click the column to reopen the Formula Builder.

Seefor more details on building formulas.

There are a number of ways to interact with reports in the Report Viewer. Available options depend on how the report was designed and the type of content in it.

Tip:If the environment has Action Events, there may be more interactive features than are listed in this article.

Drilling Into Data

Some reports allow you to drilldown into cells or charts to see additional data related to the field selected. If a cell or chart has a drilldown, you can click on one of the values to drill down into the related data.

Drilldowns are actually other reports that you can interact with or even export to a file. Drilldowns may even have drilldowns of their own!

GeoChart

Drilling down into a data cell and a chart

Expanding Hidden Data

Some reports have hidden data that can be seen by expanding certain rows. Expandable rows are indicated by an arrow icon . Click on the icon to expand the row. Click the icon again to collapse it. Right-click the icon to open a menu to do the following:

Expand / Collapse expand or collapse the outer group

Expand / Collapse Group expand or collapse the group and any inner groups

Expand / Collapse All expand or collapse all outer and inner groups on the report

Expanding a data row

Visualization Features

Visualizations have a number of interactive features depending on the type of chart. For example, line charts and bar charts allow you to hide series, multi-axis charts allow you to turn axes on or off, and pie charts allow you to rotate the chart and pull out slices. Charts can also show some additional information if you hover over a point or series.

Toggling a chart series on or off

Drillingdown on Google Maps

On a Google Map, use the mouse wheel or click the Zoom in and Zoom outicons to change your view level. Drag the map to move the view in that direction. Double-click on a highlighted region to drill down into the location data.

On a, click on a highlighted region to drill down into the region. Click [Back] to return to the previous view.

Visualizations allow data to be showcased in a visual format. They allow data to be scanned quickly for patterns and trends. Charts can be easily made and customizedin ExpressViews, in as little as one click.

Report Viewer: Interacting with Reports

An ExpressView Visualization

To add a chart to an ExpressView:

Click the Show Visualization icon in the top toolbar. A bar chart is added and populated with the data from the ExpressView.

In the Visualizations pane, select the type of chart you want to use. The chart immediately swaps to that type. Choose from Bar and Column, Pie and Other Single-Series, Line and Area or Miscellaneous.

When Live Data Mode is on, click on a chart series to drill down into the data for that series. This filters the data columns to only the series you select.

You can add many more personal touches, small or large, to the visualizations you create. See the following sections fordetails on how to customize your visualizations.

Choosing Chart Data

When a visualization is first added, the application presets the chartfields to try to match the ExpressView data as closely as possible. This may not always match the data that you want the chart to use.

To see the chart data fields, click the Visualizations icon in the pane on the right side of the screen, then click the Data tab. There are two sections: Labels and Values.

Labels

The Labels field is a group of data values, each iteration of the group represented as a series on the chart. For example, using an Employees group field as the label field represents each employee as a series.

Several charts allow an additional labels field. This is useful if you have a nested group, which represents common series to be measured across several groupings. For example, to compare the number of sales per product per employee, you could add a nested Product field as a second label to the aforementioned employees chart.

To add a chart label, either:

Drag a data field onto the chartand release on the Add Label area.

In the Visualizations pane, click the Data tab.Either:

Drag a data field to the Labels area.

Click Add Label, then select a field from the Field list.

Drag a field onto the visualization or onto the Data tab to add it as a label

Values

The Values field is the data values to plot on the chart for each iteration of each labels group. For example, the number of sales per employee.

Several charts allow for multiple values fields. This is useful if you have common series to be measured across several groupings, and the values fields are in the same data row. For example, the number of sales and number of lost sales per employee.

To add a chart value, either:

Drag a data field onto the chartand release on the Add Value area.

In the Visualizations pane, click the Data tab.Either:

Drag a data field to the Values area.

Click Add Value, then select a field from the Field list.

Drag a field onto the visualization or onto the Data tab to add it as a value

Interactive Editing of ExpressView Visualizations

You can invert the data, change the Type, Theme, Legend Location, or Sort of ExpressView visualizations by simply right-clicking with the introduction of interactive HTML to ExpressViews in v2018.2.

Suggested Reading

Charts

ExpressViews can be styled in a variety of ways:

Click the Formatting and Style here icon in the right pane to make universal report styling changes such as Theme.

Click the Selected Section icon and choose a section of the report to make formatting changes.

Right-click a section of the report for interactive editing that includes formatting options and data structuring options such as sorting, grouping, and filtering. Learn more about interactive editing in ExpressView .

Premade Themes

If there are any available, you can select a premade theme to use for the ExpressView, or to use as a baseline for further customization. On the Formatting page, click the Theme tab. Then select a themefrom the Theme Selector list:

Legacy

This is the default theme.

Custom

This option indicates that you are not currently using a premade theme.

Selecting a premade theme overrides any custom styling you have.

Selecting a premade theme

Styling Data Cells

Cells, columns, group headers, and group footers can be styled using the ReportFormatting and Style pane, the Selected Section pane, or by right-clicking sections of the report. Each of the three options is used for different purposes:

Universal report styling changes such as theme, row shading, and group colors are made using the ReportFormatting and Style pane. See the following two sections for more details on row and group colors.

The Selected Section pane is used to make data format changes such as Date or Number format or to create formulas in a column. It can also be used to style multiple cells or sectionsat one time when used with the CTRL key. To use the multi-select feature, click the Selected Section icon, hold the CTRL key while clicking all of the desired sections, and then make changes in the right pane.

To make styling changes to independent sections, you can right-click specific areas and use interactive editing or you can click the Selected Sectionicon, click the appropriate section, and make changes in the right pane.

Changing data row colors

The background colors for the data rows can be customized, and you can set the pattern by which the colors alternate. This affects every data section in the ExpressView.

To set the background colors:

On the Formatting page, click the Row Shading tab. By default there are two colors which alternate every other row.

Choose the number of colors that you want to alternate between:

To make all rows the same color, click to delete all the colors except one.

To make rows alternate between more than two colors, click to add more colors.

Enter a hex value or use the color picker to set each color.

Choosing data row colors

Changing group colors

The headers and footers for group columns are prefixed by a different color depending on the level of grouping. These colorscan be customized, and you can set the pattern by which the colors alternate. This affects every group header and footer in the ExpressView.

To set the group level colors:

On the Formatting page, click the Group Colors tab. By default, nested groups alternate between four colors.

Choose the number of colors that you want to alternate between:

To make all group levels the same color, click to delete all the colors except one.

To make group levelsalternate between more than four colors, click to add more colors.

Enter a hex value or use the color picker to set each color.

Choosing radial menu colors

The data pane is organized into data categories, which you can expand to see their fields. Click the Choose Data icon to expand or collapse the data pane.

Viewing the available data fields

To locate a specific data field, type the name of the field into the Search Fields... field and press Enter. To see all the data fields, click to clear the search field.

You canshow only data fields of acertain typeby clicking one of the type icons under the Search field:

text

numeric

date/time

other

All available data categories are usable on an ExpressView. You do not have to worry about adding categories manually or thinking about join structure. Adding a data field is as simple as dragging it onto the Design pane. Thisturns it into a data column.Useradial>left to remove a data column.

An ExpressView can be exported either as a file or an Advanced Report. A copy of the ExpressView is used to export to an Advanced Report. This means that the original ExpressView remains available and the new Advanced Report can be edited independently of it.

Export to Advanced Report

ExpressView is a great starting point to quickly add fields to a report, before delving into some of the more advanced reporting capabilities. The ExpressView format is not compatible with the Report Designer, but you can export it to an Advanced Report and edit the copy.

To export an ExpressView to an Advanced Report:

Click the Settings icon at the top of pane on the right side of the screen.

Click the Create Advanced Report header to open it.

Enter a name for the report and select a folder where it will be stored.

Click Create Advanced Report button.

Note: Beginning in v2018.2, Advanced Reports converted from ExpressViews will retain reprinting of group headers by default if detail section spills to the next page.

Export to File

If you choose to save an ExpressView as a file, it be exported as PDF, RTF, CSV, or Excel. Each format has some advantages and disadvantages.

PDF is closest to the look of the ExpressView, and is suitable for printing and emailing. But if there is a large amount of data, you may have too many pages, or too large a file size.

RTF retains the basic look of the ExpressView, and can be opened in a word processor for any additional editing. But it may look different depending on the program it is viewed with, and it is not suitable for viewing large amounts of data.

CSV retains only the data, and none of the look of the ExpressView. It is best usedif you need to process a large amount of data in an accounting or analysis program.

Excel retains the data, visualization, and optionally, some styling. It is a good hybrid format if you have a lot of data, but you still need it in a visually presentable form.

Export Settings

You can make some customizations to the appearance that the exported files will take. With the Settings page open, click the Export Settings tab to see the available options:

Page Options affect the size which PDF, RTF and Excel exports will show on a screen and on print.

General Options allow you to restrict available export types, and choose a default type.

Other Options:

Include Setup Info: Choose whether to include some information about the fields, sorts, and filters in the body of the file.

Use Group Color Styling: Choose whether to include the group header coloringsin the output file.

'No Data Qualified' Mode: If the ExpressView returns no data, choose whether to render an empty file, or show a user message instead.

Flatten Groups in Excel and CSV: For Excel or CSV files, choose whether to automatically ungroup, or "flatten"all group columns into data columns. This may make the output more suitable for data analysis.

Keep Cell Styling in Excel: For Excel files, choose whether to show styling, such as font and row shading, or to show only the bare data.

DashboardReport Class

The DashboardReport class allows Dashboards to be manipulated from the host application. This class does not need to be instantiated, it should be retrieved using methods defined in ReportObjectFactory. The DashboardReport class is derived from the ReportObject abstract class.

Properties

A DashboardReport object has the following properties. Properties that appear as hyperlinks below require constants or enums which are documented in the Constants and Enumerators article.

AllowOutputCSV: a boolean value that when true enables the Dashboard to be exported as a CSV file

AllowOutuputHtml: a boolean value that when true enables the Dashboard to be exported as an HTML file

AllowOutputRTF: a boolean value that when true enables the Dashboard to be exported as an RTF file

AllowOutputPDF: a boolean value that when true enables the Dashboard to be exported as a PDF file

AllowOutputExcel: a boolean value that when true enables the Dashboard to be exported as an Excel workbook

Id: the unique ID of the Dashboard

ExportType : an ExportType value which sets the desired export types for the Dashboard

Description: the description text for the Dashboard

ReportTreeShortcut : a TreeShortcut value which sets the default action to take when the Dashboard is run from the Web Application user interface

Name: fully qualified name for the Dashboard

CanvasFormat.CanvasBackgroundColor: a string containing a hexadecimal HTML color code that sets the canvas background color

CanvasFormat.CanvasBackgroundOpacity: an integer value ranging from 0-100 representing the percent opacity of the canvas background. 0 means completely transparent, 100 means fully opague

CanvasFormat.CanvasFit : a DashboardLayoutFit value that sets the canvas fit option

CanvasFormat.CanvasHeight: an integer value specifying the height of the canvas in pixels. Used when CanvasFormat.CanvasFit is DashboardLayoutFit.None.

CanvasFormat.CanvasWidth: an integer value specifying the width of the canvas in pixels. Used when CanvasFormat.CanvasFit is DashboardLayoutFit.Width or DashboardLayoutFit.None.

CanvasFormat.DefaultTileBackgroundColor: a string containing a hexadecimal HTML color code that sets the default tile background color

CanvasFormat.DefaultTileBackgroundOpacity: an integer value ranging from 0-100 representing the default percent opacity of the tile background. 0 means completely transparent, 100 means fully opague

CanvasFormat.DefaultTileBorders: the following properties set the default tile border style

.ShowBorders: a boolean value that when true shows the borders, when false hides them. Default value is true.

.BorderRadius: an integer value that sets the roundness of the border corners. Default value is 3.

.UniformBorders: a boolean value that when true applies the UniformBorderColor and UniformBorderWeight to all four borders simultaneously. Default value is true.

.UniformBorderColor: a string containing a hexadecimal HTML color code that sets the default tile border color when UniformBorders is true. Default value is #ddd.

.UniformBorderWeight: an integer value that sets the width in pixels of the tile border when UniformBorders is true.

.TopBorderColor: a string containing a hexadecimal HTML color code that sets the default tile top border color when UniformBorders is false. Default value is #ddd.

.TopBorderWeight: an integer value that sets the width in pixels of the top tile border when UniformBorders is false.

.RightBorderColor: a string containing a hexadecimal HTML color code that sets the default tile right border color when UniformBorders is false. Default value is #ddd.

.RightBorderWeight: an integer value that sets the width in pixels of the right tile border when UniformBorders is false

.BottomBorderColor: a string containing a hexadecimal HTML color code that sets the default tile bottom border color when UniformBorders is false. Default value is #ddd.

.BottomBorderWeight: an integer value that sets the width in pixels of the bottom tile border when UniformBorders is false

.LeftBorderColor: a string containing a hexadecimal HTML color code that sets the default tile left border color when UniformBorders is false. Default value is #ddd.

.LeftBorderWeight: an integer value that sets the width in pixels of the left tile border when UniformBorders is false

CanvasFormat.DefaultTileHasShadow: a boolean value that when true enables tiles to have a slight shadow effect by default

CanvasFormat.DefaultTileTitleBackgroundColor: a string containing a hexadecimal HTML color code that sets the default tile header background color

CanvasFormat.DefaultTileTitleBackgroundOpacity: an integer value ranging from 0-100 representing the default percent opacity of the tile header background. 0 means completely transparent, 100 means fully opague

CanvasFormat.IsDashboardReflowEnabled: a boolean value that when true will make the tiles reposition each time the width of the canvas is changed. Leave unchecked if the tiles should maintain position when the canvas width is changed. Used when CanvasFormat.CanvasFit is DashboardLayoutFit.Width.

CanvasFormat.IsGridSnappingEnabled: a boolean value that when true will make the tiles snap to the grid

ReportItems: A list of ReportItem objects, each representing a report contained within the Dashboard. To find the index of a particular report on a Dashboard:

Enter the Dashboard Designer.

Press Ctrl+Shift+I.

Click on the desired report. The index will appear in the reports title bar.

Methods

A DashboardReport object has no methods.

ReportItem Class DEPRECATED

The ReportItem class represents a report that is contained within a compositereport such as a Dashboard.

Properties

A ReportItem object has the following properties:

Report: The fully qualified name of the report that this ReportItem represents.

Methods

The ReportItem object has the following methods:

void SetFilterValue(string filterName, wrFilterOperator filterOperator, List<string> filterValues)

Description

Set the value for a promptable filter that exists on this report

Remarks

The number of entries in filterValues depends on the filter operator.

void SetParameterValue(string parameterName, string parameterValue)

Description

Set the value for a promptable parameter that exists on this report

Select which data to use on the report. The left pane shows the data categories you can access. To see the fields in a category, select it, then click theView Category Fields icon.

What are data categories?

Data categories are tables of data, which are organized by rows and columns. Columns are also known as data fields. A row of data has entries for one or more columns in the category. When you add a data field onto a report you are seeing the information in one column of data for every row in the category.

For example, a data category for Employees could have columns for the first and last names of each employee, an identification number, and a home phone number. Each row represents a person, and each column contains a specific type of information such as Last Name or Phone Number.

Example of a data category for employee records

You add entire categories at a time to a report, but in the report view you select only the columns you want to see. When you add a data field to the report design, even though you only see one column, the rest of the table is still present behind the scenes. You will never lose the connections between items in each row, and you can always add more fields.

Adding categories

On the Categories page, add data categories to the report. Later on, you can select which fields you actually want to see in the report layout.

Dragging a category to the Category Name pane

Tip:As you add categories, unrelated categories will become unavailable.

SQL Categories (Advanced Users) v2018.1+

Note: This feature is available in application version v2018.1+ .

You may have the ability to define a custom data model for the report without needing to use the predefined data categories. For databases which support unique or unusual behaviors that are not supported in the main interface, you can use custom SQL to supplement or bypass the standard Categories, Sorts, Filters, and Joins. Only new reports, created with the Report Wizard, can have a custom SQL category. You cannot add a custom SQL category to an existing report.

Caution: Writing custom SQL requires knowledge of the underlying databases and their relevantSQL query language. It is only recommended for advanced users.

To add a custom SQL data category, click Add SQL. From theCustom SQL Objectwindow, addthe following:

Object Name- Unique name for the custom category. It cannot be the same as an existing category. It cannot contain white space or the following characters:

[ ] { } ., @

Data Source- Select the data source to retrieve the data from.

Not every data source you can access may support custom SQL categories.

Enter the full SQL statement in the code window. Note that this will be inserted into a subquery when it is sent to the database for processing.

Optional:Parametersare system variables that contain different values depending on factors such as the person running the report. To include parameters in the SQL statement, select them from the Parameters list then click Add. Or enter the parameter name surrounded by At Signs (@).

Tip: A custom SQL category can only be the sole category on a report. A report cannot contain multiple custom SQL categories, or a mix of custom SQL and standard categories. Therefore, to include multiple tables on a report with custom SQL, you must retrieve multiple tables and join them in the SQL statement. If field names conflict, you can alias them in the SQL statement, or else the application will append a number to the end to preserve uniqueness.

Click theTesticon to check if the SQL is valid.

When you have finished writing the SQL, click theUnique Key Fieldslist and select the unique keys for the category.

ClickOkaywhen done. If you have already sorted and filtered in the SQL statement, you can skip these menus.

Once added, you can edit theSQL category by clickingthe SQL iconnext to its name in the Categories window.

The Report Designer is the main editor for Advanced andCrossTab Reports. At your fingertips is a full suite of reporting and design tools with an interface that is familiar and easy to understand.

Toolbar article

The Advanced Report Designer

Cells

The Report Designer interface is based on cells, like a spreadsheet. Cells can contain text, images, charts, widgets, and formulas. More importantly, cells can contain multiple rows of data. A cell in a Detail section will expand into as many rows as are needed to show all the data. A cell in a Group section will repeat for every group. And a cell in a Page section will repeat for every page on the report.

A Detail cell repeats for every row in the "Employees" data category

All of this is dynamic, meaning that the report will adapt its look based on the data that is returned form the source. For more information on the different types of report cells, see Sections.

Toolbar

You can add a variety of content and styling to your report. Most of the features can be accessed by clicking the relevant icon in the toolbar. See the for all of the available features.

When assigning filters to an Advanced Report from the Report Designer, users have the option of assigning Group Min/Max filters to the report. Group Min/Max filters will cause the report output to display detail containing either the highest or lowest values in a field for either one group, multiple groups, or an entire data set.

Note: These operate differently from the Min() and Max() functions, which are used to manipulate specific data fields as part of formulas.

This tool is especially useful if you are only interested in viewing the highest or lowest values, such as the most recent hire date or highest revenue figure, in a given set. Group Min/Max filters are compatible with standard filters, and there is no limit to the number of group filters you may define.

To access Group Min/Max filters, navigate to the Filters menu from the Report Designer Settings menu and click the Group Min/Max tab in the upper right-hand corner of the menu.

To filter a Data Fields minimum or maximum value, either drag and drop the field to the Filter By panel, use the Add button, or double-click the field.

Specify Minimum or Maximum from the operator dropdown.

Use the Move Item Up and Move Item Down icons to change the filter priority.

To remove a filter, click the Delete icon.

Applying Group Min/Max Filters

We will explore the several ways of applying this type of filter using the below sample report.

With no Group Min/Max filtering, this report executes to the following:

Note: The following features are available only in v2016.2+.

Ignoring Other Groupings

To apply the filter to one group only, select a group from the dropdown menu and check the Ignore other groupings on report checkbox.

To best utilize this option, it is important to understand the difference between an inner group and an outer group. In the report designer, the topmost group (in this case, Orders.EmployeeID) is the outermost group and has first priority. The second group (in this case, Orders.CustomerID) is within the first and has second priority. Each subsequent group is nested into the previous one and grouped after the others.

If applying a Group Min/Max filter to an outer group, then checking the Ignore other groupings checkbox has no effect on the report output because the outermost group takes precedence anyway. If applying a Group Min/Max filter to an inner group, however, the button takes effect.

In our example, both Buchanan and Kinghave sold to customer BONAP. If we apply a maximum filter on order quantity for each Customer ID and leave the Ignore other groupings checkbox unchecked, the output shows the detail containing each customer's max quantity sale per employee.

Checking the Ignore other groupings checkbox, however, returns the customer's max quantity sale for the whole report, ignoring the grouping on Employee.

Due to the fact that BONAP's order of Spegesild from King is greater than its order of Pavolova from Buchanan, BONAP appears only once on the report. If BONAP had ordered the same quantity from both Buchanan and King, it would appear under both names, even with the Ignore other groupings checkbox checked.

Filtering the Entire Data Set

Selecting Entire Data Set from the group dropdown menu will disable the Ignore other groupings option and return the records containing the single maximum value for the selected field in the entire report.

In our example, selecting this option displays only BLONP's order from Buchanan because its quantity is the highest in the set.

As of v2017.1+, you can add Top/Bottom filters, also known as Top N filters, to a report.Top N filters allow you to filter data to only the rows with the top or bottom values, for either data fields or data summaries, per group iteration.

Top/Bottom tab of the Filters page

To show only the top or bottom values, for either data fields or data summaries:

On the Filters page, click the Top/Bottom tab.

Check the Limit the report to the top/bottom values checkbox.

Select either Top or Bottom, for whether you want to show the top or bottom values.

Enter a number for how many values you want to show.

Choose how you want to limit your data:

If you want to show the rows with the top or bottom data values for a field or group, select Values from the list.

If you want to show the groups with the top or bottom summary values for a parent group or the report, select one of the summary calculations, Sum, Avg, Min, Max, Count, or Distinct Count, from the list.

Note: Only numeric data fields support Sum and Avg calculations.

Select the data field or group field to filter from the Value list.

The sections of an Advanced Report define the appearance and pattern of the report. Specifically, sections determine how frequently their cell contents are repeated.

The appearance of a report is based on data that is not necessarily known at the time it is made. The data, and the relationships it describes, are dynamic. As such, tabular reports are usually not fixed designs. Instead, you are essentially describing how the report structures itself around a set of unpredictable data. Knowing which sections are suitable for different types of content is crucial for designing the best possible report.

Types of Sections

There are two general types of sections: Static and Dynamic. The difference is in the repetition of their cells.

The cells in a dynamic section repeat according to the data that is returned to the report. Dynamic sections are suitable for showing content related to that data. For example, cells in a Detail section repeat for every composite data row in the report. Therefore, it ismost suitable fordata fields, of which you will want to see each entry per row.

The cells in a static section do not repeat according to the report data. Static sections are best used for information that is not related to data rows. For example, you could use a Page Header to define column headers that describe the type of data in each field.

See the following topics, Static and Dynamic, for more information.

Manipulating Sections

A report can contain any variety of sections. Toadd, remove, or change sections, click a section title, to the left of the row numbers in the report grid.

conditional formatting

The Section menu

To add a section, hover overAdd Section, then choose the section to add. If you add a group section, choose which data category or field to group by.

To change the category or field for an existing group section, click Modify Section.

To change the order of sections, click Move Section Up or Move Section Down.

To delete a section, click Delete Section. This will delete every row in the section.

To add alternating background colors for each row in a section, click Section Shading. See Section Shading below for more information.

Static Sections

Sections categorized as static do not repeat according to the data in the report; although they may repeat by other means. These sections are best used for information about the report and about the data in the report. They can also be useful for boilerplate elements, such as logos, copyright notices or disclaimers.

A typical report may have a report header with the title of the report, a page header with labels for each column, a page footer that uses the PageNumber() functionto number the pages, and a report footer with summary information.

Page Header/Footer

Position of page sections

Page sections repeat for each page. Page breaks are usually caused when the data in dynamic sections overflows the length of a page. The length of pages is determined by the Page Size and Orientation lists in the Report General Options window. The data on each page is consistent between the paged output types.

You can add page breaks manually by clicking the row number to add a break, then selecting Page Break from the menu. Page breaks added for arow in a dynamic sectionwill make a new page every time the data changes.

Page breaks are indicated by a dashed line on the row number

Tip: Excel and CSV output types have no pages. Page sections function the same as Report sections for these output types.

Use these sections for information to repeat on every page, such as the title of the report, column headers, and company logos. You can also use the PageNumber() functionto number each page of the report.

Even though page sections repeat, they are not considered dynamic sections because their repetition does not directly depend on the data. Thus they are not suitable for displaying data fields. Since there is no way of knowing ahead of time which data will appear on which page, these sections are not suitable for summarizing data with aggregates or visualizations.

Report Header/Footer

Position of Report Header and Footer sections

Report header and footer sections appear onceeach. Use these sections for introductory and concluding information.

The report footer is where you would summarize all of the report data with aggregate formulas and visualizations.

Dynamic

Sections categorized as dynamic repeat according to the data in the report. These sections are best used for displaying and grouping data from data fields. Dynamic sections comprise the bulk of most tabular reports. Charts, maps, and gauges must reference data fields indynamic sections.

The key for knowing how to use dynamic sections is knowing how the data will repeat in relation to the rest of the report.

Detail

Position of Detail section

The rows in the Detail section repeat for each data row retrieved from the database. By default, there are exactly enough Detail rows to show all the data fields in all the categories on the report.

Detail sections are typically used for showing the actual data values. This is the mainarea of display for the bare report data.Aggregate formulas usually reference cells in the Detail section.

You can use duplicate suppression to limit the number of rows to only visible data fields. You can also use report filters to limit the rows by certain criteria.

Users with advanced knowledge of the data relationships can also use advanced join logic to limit the Detail rows to only the relevant relationships.

Group Header/Footer

Position of Group sections

Group sections are the primary way in which data is grouped into "buckets" that each share a unique attribute. Detail rowsare arranged into the groups they belong to. The common attribute is defined by a preexisting sort. In order to make a group, first add a sort in the Sort window.

For example, if you wanted to group a set of rows by each Product, first add a sort on the Products.ProductName field.

A sort is required for a group

The main difference between the header and footer is that the data comes after the header, but before the footer.

When you add a group section, you are asked which sort to base the group on. For each sort, you can choose to group on either the sort field or the sort category.

If you group on a field, the group breaks at each new value of the field.

If you group on a category, the group breaks at each new instance of the category's unique identifier.

The group window appears when adding a group section

For example, instead of grouping by each Product, you may want to instead group by product quantity, so that products that are low or out of stock are grouped together. First, add a sort on the Products.Quantity field, then group by that field.

You can have multiple group sections, if you wanted to add additional levels of stratification to the report. Each group requires a corresponding sort.

Tip: The order of the sorts has an effect on the way the data is arranged. Data is sorted by the first field, then any ambiguities are sorted by the second field, then any remaining ambiguities are sorted by the third, and so on. In general, you should order group headers by their associated sorts, and group footers in inverse order.

Repeating Group

Position of Repeating Group sections

Repeating groups are a way to organize and display data that contain multiple one-to-many relationships. Each repeating group has data from "many" category, grouped by the "one" category. The following diagrams demonstrate how this works.

Two categories, X and Y,with a one-to-many relationship from X to Y, will display like so in a Detail section.

Tip: Category X has duplicates suppressed - this is similar to grouping on X.

Each X has 1 or more Y

If there is athird category, Z, with a one-to-many relationship from X to Z, there is not necessarily a relationship between Y and Z. So there may be blank rows with only Y or only Z.

Each X has 1 or more Y, and1 or more Z

There are two potential solutions. Ifthere is a relationship between Y and Z, then you can use Advanced Joins to impose additional constraints so that only the rows with both Y and Z are shown. For more information, see Joins.

On the other hand, there may be no relationship between Y and Z, only between X and Y, X and Z. Or there may be a relationship, but it does not matter for this data set. You can use repeating groups to ignore that relationship and simply organize the data by X instead.

To do so, create two repeating groups for X, and put Y in the first, and Z in the second:

Organizing Y and Z into repeating groups for X

Each X repeats twice: once for Y, once for Z

Each repeating group section has its own group header, detail, and group footer. These follow the same principles as their general purpose equivalents, but within the repeating groups. For example, to calculate aggregate data for Y for each X, use an aggregate formula in the footer for the repeating group with the Y data.

Reprinting Group Headers

As of v2018.2+, you have the option to reprint Group Header rows at the top of the page if the detail section of any given group spans multiple pages. This option is only available on Group and Repeating Group Header sections. To reprint a Group Header row when its detail section continues onto another page, click the row number in the Report Designer and select Repeat Row. If the Header has been set to repeat, two blue lines will display next to the row number.

Section Shading

Section shading allows you to specify alternating background colors for repeating elements in a section. Alternating colors applies to Detail and Group sections; for other sections, only one color can be used.

To add section shading:

Click a section title to open a menu for that section. Select Section Shading.

Click New to add a new shading color.

For each shading color, select a color with the color picker, or enter a hexadecimal color code.

Use the upMove Item Up andMovie Item Down icons to rearrange the order of the colors.

Click the Delete icon to remove a shading color.

Click Okay.

Adding two alternating background colors to a Detail section

Tip: Section shading is overridden by the cell background color and by .

Caution:The Joins dialog is recommended for advanced users only.

Joins describe how the Categories on a report are related to each other. When two categories are joined, a field in the first category is associated with a field in the second category. Wherever a value in the first category's field matches a value in the second category's field, that value's rows from each category come together to form a composite row. The table produced by all the composite rows is the resulting data that appears on the report.

For example, take the following categories, Orders and Products. The Orders.ProductId field corresponds with the Products.Id field. When the categories are joined from Orders.ProductId to Products.Id, the rows are connected wherever those two fields have matching values.

Sections

Categories joined on Orders.ProductId >> Products.Id

The result of this join is the following composite rows. These categories have a one-to-one relationship, because each row in the "left" category joins at most one row in the "right" category.

Joined categories.Products.Id is omitted.

Tip: Categories could be joined along more than one set of fields; composite rows are formed only when all sets have matching values.

For two categories to be co-present on a report, there must be a join path between them. They are either directly joined, or there is a path through one or more intermediate categories. You do not have to configure joins manually as they already exist in the environment. However, if you want to learn how to add or adjust joins on a per-report basis, the following sections explain the options that are available.

Join Types

The join that was previously described is the most common type of join, an inner join. When an inner join is applied, rows in either category that have no matching row in the other are excluded from the resulting table. However, you may not want to exclude these rows. To do so, you can change the type of join to an outer join.

For example, the row in the Products category with Products.Id = 12 has no matching row in the Orders category. With an inner join, this row is excluded from the output. If you want to see the Products rows that have no matching Orders row, you can change the join type.

On the toolbar Settings menu, select Advanced and then Joins to open the Joins dialog. select the Products data that does not have Orders data checkbox.

This changes the join between these categoriesto a left outer join, because all rows from the left category are included. The following rows result:

Joined categories with all Products rows. Products.Id is omitted.

Similarly, selecting the Orders data that does not have Products data checkbox changes the join to a right outer join, which includes all rows from the right category. Selecting both checkboxes includes all rows from both categories; this is a full outer join.

Relationship Types

There are two types of join relationships: one-to-one and one-to-many.

In the previous example, the relationship between the categories is one-to-one, because each row in the left category joins at most one row in the right category. Some categories have a one-to-many relationship, where each row in the left category joins zero or more rows in the right category.

A one-to-many relationship from categories X to Y is represented in the following diagram:

Each X is joined to one or more Y

Reports with a single one-to-many join are well suited to grouping by the left category. The data in these reports is generally well-formed and understandable.

However, when a report has multiple categories with one-to-many joins, data can appear more disorganized and confusing. For example, the following diagram represents data from three categories, X, Y, and Z, where the relationships between X to Y and X to Z are both one-to-many:

Each X has 1 or more Y, and 1 or more Z

Because Y and Z are not directly related to each other, there are many rows with only Y or only Z. This can cause the report to be significantly larger, and to be difficult to read and interpret. This occurs even with inner joins, the most restrictive type, because by default there is no logic that deals with the relationship between Y and Z.

Read on for different ways of improving the structure of a report with multiple one-to-many joins.

Cartesian Processing

You could fill the blank spaces with supplementary data by disabling Special Cartesian Processing from the Joins dialog. Blank cells are filled in with data that is repeated directly from the previous row. The following diagram demonstrates how this works:

Disabling Special Cartesian Processing

The shaded cells represent data that has been repeated from the previous row. This can make the report more readable. However, this approach poses a problem: Blank cells indicate a lack of a relationship between two fields, so filling in these spaces with artificial data can obfuscate any relationship between Y and Z. This can decrease the accuracy of the report.

There are better ways to improve the readability of such a report without sacrificing accuracy:

Use repeating groups to show the X to Y and X to Z relationships in entirely separate sections. This is suitable if any relationship between Y and Z is irrelevant or nonexistent. See for more information.

Hide some or all of the rows which do not have data for both Y and Z. This is suitable if you want to highlight an implicit or indirect relationship between Y and Z.

Modifying Joins

The Joins dialog shows all direct and implicit joins on the report. Direct joins can be added, modified, or removed from the report.

Create a New Join

Select From and To categories.

Click Add.

Click Add Condition then select From (left column) and To (right column) fields.

Tip: If there are multiple conditions, only the rows that satisfy all the conditions are joined.

Click Okay

Click Okay

To restore the default joins, click the Recreate button, then click Okay.

Delete a Join

To remove conditions, click the Delete icon next to the condition to delete.

Modify a Join

Click the Edit icon next to the join to edit.

Add, remove, or modify conditions.

Click Okay.

To remove a join, click the Delete icon next to the join to delete, then click Okay.

Advanced Joins

You may be able to specify join conditions that are more complex than column equality.

Note: Advanced Joins cannot be applied across different data sources.

Type

Instead of joining between two columns, one or both sides of the join may instead be an arbitrary expression, constant, or SQL sub-query that you specify.

To change the expression type for one side of a join condition, select one of the following from the Type list:

Value: One or more constant values separated by commas

Expression: Formula or calculation

SubQuery: SQL query

Then enter the value in the Value field.

Operator

As opposed to the default equality (=) operator, which joins fields from the left expression to matching fields from the right expression, a join condition can use one of several alternative operators instead. For example, the inequality (!=) operator joins fields on the left to non-matching fields on the right. To do so, select one of the alternative operators ;from the Operator list:

!= not equal to

> greater than

>= greater than or equal to

< less than

<= less than or equal to equal

IN: match one or more values, either specified or calculated from a subquery

Grouping

When a join has two or more conditions, you can specify how the conditions should be met as a group in order for the join to take effect.

To specify that either one of two conditions will satisfy part of the clause, select OR from the Conjunction list for the first condition of the two.

To add parentheses around two conditions, select the Group checkbox for the first condition of the two.

You can preview the full join clause in the Summary field.

Use the Categories window to select which data to use on the report. The left pane shows the data categories you can access. To see the fields in a category, select it and click the View Category Fields Joins icon.

If the report has a custom SQL category then it cannothave any other categories. You can click the SQL icon to edit the SQL statement. See SQL Categories for more information.

What are Data Categories?

Data Categories are tables of data, which are organized by rows and columns. Columns are also known as data fields. A row of data has entries for one or more columns in the category. When you add a data field onto a report you are seeing the information in one column of data for every row in the category.

For example, a data category for Employees could have columns for the first and last names of each employee, an identification number, and a home phone number. Each row represents a person, and each column contains a specific type of information such as last name or phone number.

Example of a data category for employee records

You add entire categories at a time to a report, but in the report view you select only the columns you want to see. When you add a data field to the report design, even though you only see one column, the rest of the table is still present behind the scenes. You will never lose the connections between items in each row, and you can always add more fields.

Dragging a category to the Category Name pane

Relationships Between Categories

Note: This information is intended for advanced users.

In the data source, data categories are joined to other categories by associating uniquely identifying data fields from one category to matching data fields in another. This means that if a row's identifying field matches one or more rows in a joined category, then those rows connect to an entire row or group of rows, which have their own separate data fields.

Only joined data categories, which are described as having a relation, can be added to the same report. This is why some categories may become unavailable as you add others. But data categories, even if they are not related to each other, may both be related to another category. If you add that category, then you can add both those categories, because there is now a join path between them.

For more information on how categories are related to each other, see .

Suppressing Duplicates

Note: This information is intended for advanced users.

Be judicious when adding data categories. If you find that your report has unexpected duplicate values or empty rows, the cause is most likely that you have a one-to-many join to a category that you are not using.

For example, this report has Employees and Orders categories. There is a one-to-many join from Employees to Orders, indicating that each Employee row is joined to one or more Order rows. Even though we are not using Orders on the report design, there are duplicate Employees because our join setup causes us to have a row for each Order, instead of each Employee.

Unexpected duplicate Employee values

There are several ways to eliminate these duplicates. You can suppress duplicates for the Employees category, which will show blank rows for consecutive duplicates. In the Categories window, select the Suppress Duplicates check box for the Employees category.

Duplicate-suppressed category

You can alsosuppress duplicates for the cell, which will hide unnecessary duplicate rows. Select the cell and click the Suppress Duplicates icon. If a field from Orders is on the report, the behavior will be the same as suppressing duplicates for the category.

Duplicate-suppressed cell

Or, if you do not think you will need the Orders category, remove it from the report. In the Categories window, click the Delete Category icon next to Orders to remove the category. You can always add it again later if needed.

Sorting is the process of ordering data rows by a certain sequence. For each available data category, you can choose which data field should be used to sort the rows. Fields can be sorted in ascending or descending order. The way in which rows are sorted depends on the type of value in the field:

Numeric Ascending Formula Editor Lower valuesHigher values Descending

Date Ascending PastFuture Descending

Text Ascending AZ Descending

Sorting a category by Department

A report can have multiple sorts. This can be useful when you want your highest precedence sort to affect a data field where the values for multiple rows may be the same.

For example, imagine a large company with many employees. There could be multiple people with the last name Buchanan. With only a sort on LastName, you do not know how all the people with the last name Buchanan will be ordered amongst themselves. If this matters, then you can add a second sort on, say, the FirstName field, so that people with the same last names will be ordered by their first names.

Adding Sorts

In the Sorts window, add data fields to sort. The precedence of the sorts starts with the highest row and moves down the list. Drag the rows up or down to change the precedence.

Dragging a field to the Sort By pane

Relationship Between Sorts and Groups

Sorts are a prerequisite for making Groups. Sorting puts data in order so that data rows which share common values for the sort field are next to each other. This is essentially what grouping does as well. Grouping simply takes those common values, pulls them out of the rows, and makes Sections for each group of rows which share that value. Sorts tell the report how you want to your data to be grouped.

Grouping a category by the Department sort

Tip: Set the sort precedence so that nested groups are in order of their grouping level. The outermost group should have the highest precedence, with the next levels following in order. If the precedence is set incorrectly, it could result in inconsistent data groups.

Sort Formulas

Note: This information is intended for advanced users.

You can sort by a formula instead of a data field. This allows you to have finer and more specific control over your groups.

If you do not have a single data field as a unique key, you can use a sort formula to sort on a concatenation of two fields instead. For example, EmployeeId plus TerritoryId fields:

={EmployeeTerritories.EmployeeId} & {EmployeeTerritories.TerritoryId}

Or if your sort field would generate too many groups, you can sort on a piece of the field instead. For example, you could group on only the month and year component of a date field.

=Date(Year({Employees.HireDate}),Month({Employees.HireDate}),1)

To add a sort formula, click Add Formula, then use the to make a composite field to sort on.

The following options are available in the General Options dialog:

Cell Formatting

Report Options dialog

General Options

Allow Execution in Viewer: Allow or prevent the report from being run in the Report Viewer. If unchecked, this report must be exported to be viewed.

Include Setup Info: Select Top or Bottom to display the data categories, sorts, and filters at either the beginning or end of the report. Select None to not show this information on the report.

Allowed Export Types: Enable or disable output types for the report.

Default Export Type: Specify the default output type for the report. If Default the export type is determined by the system's configuration.

Report Tree Shortcut: Specify whether the Run icon for the report in the report tree runs the report in the Report Viewer or exports it in the default export type.

Filter Execution Window: Select which type of Filter menu displays when running the report with prompting filters:

Default: The type of filter window is determined by the system's configuration.

Standard: Display the standard filter window.

Simple with Operator: Display a simplified filter window that only allows the filter operator and value to be changed.

Simple without Operator: Display a simplified filter window that only allows the filter value to be changed.

Always Show Filters in Report Viewer: Show the Filter menu and allow changes to be made to the filters every time the report is run, even if there are no Prompting Filters on the report.

No Data Qualify Display Mode: Select what to display if no data qualifies for the report.

Show Message: Display the standard no data qualified message in a dialog box.

Show Report: Display the Page Header, Page Footer, Report Header, and Report Footer sections of the report. Any cells containing Data Fields will not be displayed.

Excel Options

Suppress Formatting: Check the checkbox to only include the data in an Excel export, without the formatting.

Show Grid: Check the checkbox to show grid lines between cells in an Excel export.

Freeze Rows: Freeze the top number of rows when exporting to Excel, so that the rows stay anchored to the screen when scrolling through the report.

Freeze Columns: Freeze the left number of columns when exporting to Excel, so that the columns stay anchored to the screen when scrolling through the report.

Page Options

Page Size: Select the page size of the report output.

Page Orientation: Specify whether the report runs in Portrait or Landscape orientation.

Fit to Page Width: Check the checkbox to force the report to fit to the width of the page.

Report Viewer Options

The following options are available in the Report Viewer Options window:

General

Show Grid: Check the checkbox to show grid-lines between cells in the Report Viewer.

Simulate PDF: Check the checkbox to have the report appear as if it were on a PDF page when viewed. When unchecked, the report still is paginated but does not have the formatting of a PDF exported report.

Allow Hide/Show Columns in Report Viewer: Check the checkbox if the ability to show or hide individual columns is available in the Report Viewer. If checked, headers will appear at the top of the page. When right-clicked, users will have the ability to hide or un-hide columns. If unchecked, the headers are not present and the columns may not be changed.

Note: Setting theEnable Paging in Report Viewer setting in theAdmin ConsoletoFalse restricts the interactive HTML hide/show column option.

Show Toolbar in Report Viewer: Select whether to show or hide the interactive toolbar in the Report Viewer.

Default: the toolbar will be displayed based on the system's configuration

Auto: the toolbar will be hidden if the Report Footer appears on the first page. Otherwise, the toolbar will be shown.

Show: always show the toolbar

Hide: always hide the toolbar

Report Row Step Limit: Override the default step size for incremental loading (with a smaller value). This option is only available if Incremental Loading is enabled in your environment.

Filters

Filters tab of the Report Viewer options dialog

To allow users to select additional interactive filters in the Report Viewer, add data fields as filters. These filters are accessed by clicking on theOpen Interactive Features icon on the left hand side of the screen in theReport Viewer.

Interactive Features pane opened in the Report Viewer showing a Product Name filter, adding a Category filter and Sorts

For each filter you can select the following options:

Title: Enter text to appear in place of the data field name.

Type: Specify the type of ability the user has to choose filter values:

Single Choice: A dropdown menu with all possible filter values. Users can choose one value.

Multiple Choice: A check list with all possible filter values. Users can choose multiple values.

Single Slider: Users can choose one value by sliding a point along a scale.

Range Slider: Users can choose multiple values between two points on a scale.

Value Sort Direction: Whether the filter values should display in ascending or descending order.

Filter Value Format: Select how the filter values should display. The controls here work similar to the controls.

Initially Display Filter on Panel: Check the checkbox if the filter should appear automatically when the report is run. If unchecked, the user will need to add the filter each time the report is run.

Sorts

Sorts tab of Report Viewer options dialog showing two Category sorts

To allow users to change existing sorts from ascending or descending in the Report Viewer. These sorts are accessed by clicking on theOpen Interactive Features icon on the left hand side of the screen in theReport Viewer.

Display Sorts in Report Viewer: Select whether the user can see and modify sorts in the Report Viewer.

Title: Enter a user-friendly name for the sorting category which will be displayed in the Report Viewer.

Sometimes you may only want to see a portion of the rows in a data category, rather than its entirety. Filters allow you to narrow the scope of your reports by restricting the amount of data by specified criteria.

For example, imagine a category containing a row for every single sale your company has ever made. You can use a filter to limit the report to only sales from the past month. This also has the benefit of speeding up your reports.

There are three types of filters available in the Filters window:

Standard

Group Min/Max

Top/Bottom

This article covers Standard Filters, refer to the respective articles for Group Min/Max and Top/Bottom filters.

Standard Filters

Standard filters limit data by only showing rows where the values for a field meet a certain condition.

Adding Standard Filters

In the Filters window, add Data Fields to filter.

Formulas in ExpressViews

Dragging a field to the Filter By pane

For each data field, select a condition. When the report is run, the field value for each row is checked against the condition for that field. Only the rows where the field satisfies the condition will show on the report.

To create a filter condition:

Select an operator from one of the following options. This is the condition used to match the data values to your specified filter value or values. Some operators are only available for some data types.

The following table describes the filter operators and their applicable data types:

Condition

Type

Filter Values

Matching Values

Not-Matching Values

Equal To (=)

Any

July 4, 2016

July 4, 2016 July 4, 2016 12:00 AM

July 4 July 4, 2016 2:00 PM

Not Equal To (!=)

Any

July 4, 2015

July 4 July 4, 2016 2:00 PM

July 4, 2016 July 4, 2016 12:00 AM

Less Than (<)

Number, Date/Time

July 4, 2016

July 3, 2016

July 5, 2016

Less Than or Equal To (<=)

Number, Date/Time

July 4, 2016

July 3, 2016 July 4, 2016

July 5, 2016

Greater Than (>)

Number, Date/Time

July 4, 2016

July 5, 2016 July 4, 2016 5:00 PM

July 3, 2016

Greater Than or Equal To (>=)

Number, Date/Time

July 4, 2016

July 4, 2016 July 5, 2016 July 4, 2016 5:00 PM

July 3, 2016

Starts With

Text, Number

203

2035550224

8458081120

Not Starts With

Text, Number

203

8458081120

2035550224

Ends With

Text, Number

224

2035550224

7188044606

Does Not End With

Text, Number

224

7188044606

2035550224

Contains

Text, Number

555

2035550224

2038081120

Does Not Contain

Text, Number

555

2038081120

2035550224

Is Between

Any

1, 4

1, 1.5, 3, 4

0.999, 5

Is Not Between

Any

1, 4

0.999, 5

1, 1.5, 3, 4

Is One Of

Any

1, 2, 3, 4

1, 2, 3, 4

1.1, 9

Is Not One Of

Any

1, 2, 3, 4

1.1, 9

1, 2, 3, 4

Enter a filter value or values, or select them from the list of existing values. Type into the filter field to search for data values to filter. Click the Settingsicon to select whether to search for values that either Start With or Contain the typed text (v2017.3+).

Choosing a filter value

Optional: If you have multiple filters, you can choose how they should be grouped. See Grouping Filters Together for more information.

Optional: If you want the report to prompt the user to enter a filter value when the report is run, check the Prompt For Value checkbox.

Prompting for Values

If you want to let users select their own filter values when they run the report, you can set filters to Prompt For Value. In the Report Options, you can choose whether to let users change the operators and delete filters.

You do not need to enter a value for prompting filters. But if you do, it is entered as the default value for the filter in the prompt dialog.

Formula Filters

As of v2018.2, the application supports using formulas in the string of a filter, allowing for much more powerful filter statements.

Previously filter statements would be limited to statements containing one Data Object. Consider the following example using data categories from the sample Northwind data set, which compares the OrderDate against a single date.

{Orders.OrderDate} > 01/01/2015

Now, Data Objects may be used in a formula when doing a filter operation. For example, calculating the revenue of an order line item by multiplying {OrderDetails.Quanity} by {OrderDetails.UnitPrice} and then applying a filter to select only those line items whose revenue is greater than $300.

{OrderDetails.Quantity}*{OrderDetails.UnitPrice} > 300

Advanced Report Designer

In the Advanced Report Designer, formula filters are available in the Filters tab. Clicking the Add Formula button in the lower left will open a Formula Editorwindow, which will allow for the creation of a formula to use within the filter.

Tip: Refer to Formulas section for details about creating and editing formulas and using the Formula Editor.

Report Filters window in Advanced Report designer with the Add Formula button

ExpressView Designer

In the ExpressView Designer, adding a formula filter requires that the formula exist as a column on the report.

Note: Any objects needed for a formula in an ExpressView must also exist as their own column on the report as well.

Add Formula button in the ExpressView designer

Once the formula is created as a column, select the Filters pane and drag the formula column over to the Filters pane to begin creating a filter on the formula. For more information about building formulas in an ExpressView, see the article on .

Note: Certain function are available in the Formula Editor that are not compatible with the context of formula filters, such as CellValue(), FilterValue(), Hyperlink, LoadImage() and PageNumber().

Grouping Filters Together

When a data row is checked against the report filters, the values for each of its data fields are checked against all of the filters for the data fields. By default, it must satisfy every filter condition to show on the report. The combined statement used to filter the data rows is shown in the Summary field.

Example of a combined filter statement

You can specify that a data row needs only to satisfy one filter, or a selection of filters, to show on the report.

To specify that a data row needs to satisfy any one of several filters:

On the Filters page, drag the filter rows next to each other.

Select the first filter.

Select OR With Next Filter. You should notice that in the Summary field, the And between this filter and the next has changed to Or.

Repeat steps 2-3 for every filter except the last.

You can group filters together, to specify that a data row can match either one group of filters, or another group of filters, to show on the report.

To specify that a data row needs to satisfy any one of several groups of filters:

On the Filters page, drag the filter rows such that the filters are nearest to their group mates.

Select the first filter in the first group.

Select Group With Next Filter. You should notice that in the Summary field, there are now parentheses around this filter and the next.

Repeat steps 2-3 for every filter except the last in the first group.

Select the last filter in the first group.

Select OR With Next Filter.

You should notice that in the Summary field, the And between this filter and the next has changed to Or.

Repeat steps 2-6 for every group of filters. Skip step 6 for the last filter in the last group.

Nesting Filter Groups

Filter groups can be nested arbitrarily. This allows for more detailed control over grouping. The Summary field shows the actual statement used to filter the data. You can make arbitrary groupings by inserting parentheses manually at locations in the statement. Use the following keyboard shortcuts:

Ctrl + [ to add an opening parenthesis before the selected filter

Ctrl + ] to add a closing parenthesis after the selected filter

Ctrl + Shift + [ to remove an opening parenthesis from before the selected filter

Ctrl + Shift + ] to remove a closing parenthesis from after the selected filter

You can use an Advanced Report to dynamically fill out fields in Templates and forms.

Report Options

Template report with repeating data fills a set of form templates

To use a report to fill out a template:

Editing of fields in exported PDF templates

Enter field data in cells on the report. For repeating data:

Repeating cells mapped to static template fieldsmake a new instance of the template for each repeat.

Repeating cells mapped to limited-repeating template fields make a new instance of the template each time the number of values exceeds the limit. Note: This is often undesirable. Ensure that there are appropriate constraints on your data and enough fields to fit it all.

Repeating cells mapped to unlimited-repeating template fields never make a new instance of the template. Overflowing data is cut off.

On the toolbar Settings menu, select Template to open the Report Templates dialog. Select an existing template or click the Upload button to upload a new one.

For each template field:

Select a report cell containing text or data. Images, visualizations, and other widgets are not supported.

Leave it blank. Any bookmarked text shows as-is in the output.

Select whether or not to allow editing of that field in the exported PDF file using the Allow Edit checkbox.

Click OK.

To remove a template from a report:

From the Template dialog, select the blank option from the template list.

Click OK.

Tip: Template reports must be exported in the same file type as the template. Review the General Section of the article for details on how to control report export types.

Making Templates

The process for making templates differs between the three supported types: PDF, Word, and Excel; as do the available features.

Caution: Close the template file before running or saving a report that uses it.

PDF Templates

PDF templates support static fields and limited-repeating fields. PDFs are convenient for preexisting and standardized forms, such as for government or businesses.

To make a PDF template:

Open a PDF in a PDF editor program, such as Adobe Acrobat or PDFescape.

Add form fields where you will insert report data. For fields where text may span multiple lines, select the multi-line property.

Give each form field a unique name, as follows:

For static fields, which appear only once per template instance, use any name, with the exception of the format reserved for repeating fields.

For repeating fields, which are mapped to consecutive values in a repeating cell, use the following naming format: Name.0, Name.1,... Where Name is shared by the repeating fields, and 0 maps to the first value, 1 maps to the next value, and so on.

Save the PDF. Then upload it to the report.

Microsoft Word Templates

Word templates support static fields, limited and unlimited-repeating fields, and conditional suppression.

Tip: Supported file types are .doc, .docx, and .rtf. Report templates exported as "RTF" will save to the original file type.

To make a Word template:

Open a document in Microsoft Word or a compatible document editor.

Add text where you want to insert report data.

Select the text, or in the case of repeating fields, a region of text, and insert a bookmark.

Employee Information example showing that Bookmarks in Word display as grey brackets surrounding text

Give each bookmark a unique name, as follows:

For static fields, which appear only once per template instance, use any name, with the exception of the formats reserved for repeating and conditional fields.

For limited-repeating fields, which are mapped to consecutive values in a repeating cell, use the following naming format: FieldName_0, FieldName_1, ... Where Name is shared by the repeating fields, and 0 maps to the first value, 1 maps to the next value, and so on.

For unlimited-repeating fields, which are mapped to all the values in a repeating cell, use the following naming format: RepeatForEach_FieldName WhereFieldName is a unique name. A RepeatForEach section in templating follows similar structure and purpose to grouping within reports. It is used to organize information under repeating groups within templates, arranging related information on the same page and separating groups with page breaks.

Important: Bookmark formatting for unlimited-repeating fields must follow specific spacing requirements. The opening bookmark bracket should be placed one line before the repeating field section begins, and the closing bookmark bracket should be placed one line after the repeating field sections ends. Please refer to the Employee Information example above for a visual reference. If this formatting is not followed, an "itemStart and itemEnd must be contained in one text body" error may be thrown.

Optional: To conditionally show or hide text:

Select the text and add a bookmark with the following naming format: KeepIF_FieldName Where FieldName is a unique name.

In the report, map this field to a cell with a formula that returns 1 if the text should be shown, and 0 if the text should be hidden. Example:

=If({Products.ProductName} = "Chai", 1, 0)

Save and close the file. Then upload it to the report.

Microsoft Excel Templates

Excel templates work differently than other types. Templates are used to fill Excel columns with report data. This is useful for passing data to Excel charts, pivot tables, and macros.

To make an Excel template:

Open an Excel file in Microsoft Excel or a compatible spreadsheet editor.

The first worksheet is used for dynamic report data. Repeating cells are mapped to columns in the worksheet. For each column where you will add report data, enter a unique name to the topmost cell in the column. All following cells must be empty.

Formatting an Excel file to be used as a template

Save the file. Then upload it to the report.

The Cell Format Window allows you to customize how data values will display, add custom borders, and add formatting that will only show if a condition is met.

Number

If a cell has a numeric, date, or time value, then you can use Number formatting to choose how the value should appear on the report. For example, you could add a dollar sign ($) to monetary values and separate each three digits to make values easier to read.

application parameters

Numeric values with currency styled formatting

The following options for Number formatting are available:

General

Format the data using the default settings for your environment. This is the default option. The application will assume the data type based on the value.

Number

Format the data as a number, currency, or percentage.

Optional: Choose how the number displays:

In the Decimal Places field, enter a number for how many decimal places to display. Then, in the field to the right, enter a symbol to use as the decimal mark.

To show a delimiter every three digits, check the Use 1000 Separator checkbox. Then, in the field to the right, enter a symbol to use as the delimiter.

To show a currency symbol before the number, check the Use Currency Symbol. Then, in the field to the right, enter the symbol to show.

To show a percent sign (%) after the number, check the Append Percent Sign (%) checkbox.

To show no value if the number is 0, check the Blank When Zero checkbox.

To show a minus sign (-) in front of negative numbers, check the Show Negative Symbol checkbox.

To show parentheses ( ) around negative numbers, check the Show Parenthesis checkbox.

To show negative numbers in a different color, enter a hexadecimal color code in the Color field or use the color picker to choose a color.

Date

Format the data as a date, time, or date and time.

Optional: Choose which date and time components to display, and how to show them. Either select one of the patterns from the Date/Time Format list, or enter a custom pattern using the following variables:

Variable

Description

Result for sample date of "Sept-2-1907 5:08:04 PM"

d

day of the month, from 1 to 31

2

dd

day of the month, from 01 to 31

02

ddd

day of the week, abbreviated name

Mon

dddd

day of the week, full name

Monday

M

month, from 1 to 12

9

MM

month, from 01 to 12

09

MMM

month, abbreviated name

Sept

MMMM

month, full name

September

y

year of the century, from 0 to 99

7

yy

year of the century, from 00 to 99

07

yyyy

year, from 0001 to 9999

1907

h

hour using a 12 hour clock, from 1 to 12

5

hh

hour using a 12 hour clock, from 01 to 12

05

H

hour using a 24 hour clock, from 0 to 23

17

HH

hour using a 24 hour clock, from 00 to 23

17

m

minute, from 0 to 59

8

mm

minute, from 00 to 59

08

s

second, from 0 to 59

4

ss

second, from 00 to 59

04

t

A/P

P

tt

AM/PM

PM

Text

Does not apply any formatting to the data, and show it exactly as it appears in the database.

Border

Alter the width and color of the cell borders. To set a color for a cell border, enter acolor codeor select a color from the picker. To set the width of the border, enter a pixel value, or use the arrows to make the border thicker or thinner.

To set all the cell borders to the same color and width, check the Make Borders Fixed checkbox.

Tip: If gridlines are enabled for the Report Viewer, then cell borders will show in addition to the gridlines.

Choosing border colors and widths

Conditional Formatting

A conditional format allows you to format a cell according to its output data. The cell and text styles can depend on its data value, and you can even conditionally hide rows or entire sections. This can be useful for highlighting certain values in a data set, such as outliers from a trend.

Conditional Formatting uses a formula to set the condition. The formula must evaluate to True or False. If True, the formatting will be applied otherwise it will not. Conditional formulas are often based on data in the cell, but they can also be based on other cells, data fields, or other information about the report.

Example of a formula that evaluates to True or False

To set or modify the format of a cell based on a conditional formula:

Conditional format tab of Format Cells dialog

Click Add to create a new condition.

From the Action list, select an action to occur if the condition is met. If applicable, select an attribute for the action from the Attribute list.

Action

Attribute

Foreground Color

Enter a hexadecimal color code or select a color using the color picker which will change the foreground (text) color of the chosen cell.

Background Color

Enter a hexadecimal color code or select a color using the color picker which will change the background color of the chosen cell.

Font Family

Select a font to change the text to.

Font Size

Enter a font size, in ems, to change the font size to.

Bold

no attribute

Italic

no attribute

Underline

no attribute

Horizontal Alignment

Choose from Left, Right, Center or Justify to which the text in the cell will change to.

Vertical Alignment

Choose from Top, Middle or Bottom to which the text in the cell will change.

Suppress Row

no attribute

Suppress Section

no attribute

Page Break

no attribute

Tip: Hexadecimal color values may be upper or lowercase and may contain the # prefix character, but it is not required.

Click the Formula Editor icon to enter a formula for the condition. The formula must evaluate to True or False. The Action will be applied to the report when the formula evaluates to True.

To use the value of the current cell in the formula, use the function CellValue(). Click Cell Value to insert CellValue() into the formula.

A cell can have multiple conditional formats, each of which is a separate row in the Conditional tab. If two or more overlap, the lower condition takes precedence. Click the Move Row Up and Move Row Down icons to reorder the precedence of the conditions.

A cell with multiple conditional formats

A cell with multiple conditional formats

Using Formulas as Conditional Formatting Colors v2019.2+

As of v2019.2, in addition to selecting a static color with the color selector, a formula may be used to change the colors of a cell when conditional formatting.

A formula which returns a value convertible to a hexadecimal color code can be entered as the Attribute when the Action is Foreground Color or Background Color. When using this option, conditional formatting color properties can be determined at runtime instead of report design time.

One application of this feature is to read color values from a data source and use that color data to apply formatting to a cell.

Color Selector with Set by Formula button

Clicking on the Color Selector's Set by Formula button will open the standard Formula Editor. All formula elements available in conditional formulas are available, including CellValue(), references to other cells, and Data Objects. Aggregate functions are not available in attribute formulas.

Tip: Hexadecimal color values may be upper or lowercase and may contain the # prefix character, but it is not required.

If the formula returns null or an empty string, the conditional formatting will not be applied. If the formula returns a value that is not convertible to a hexadecimal color code, an error message will be displayed in the cell.

Examples

This example uses conditional and logical functions to combine multiple conditions into one clause.

If(And(CellValue()>=1000, CellValue()<10000), "#FFFFFF", If(CellValue()>=10000, "#ECECEC", "#00000"))

This example uses an application parameter named ConditionalColor to select a color to apply:

=@ConditionalColor@

This example simply refers to a Data Field that contains a hexadecimal color value to apply:

{Products.ProductColor}

A few examples of valid hexadecimal color codes:

#FFF

#c0c0c0

ff4c00

F90

The ExpressView Designer is a data discovery and reporting tool that simplifies grouping, sorting, filtering, and aggregating data with a drag-and-drop interface. A chart can be added with a single click, and ExpressViews can be styled andsaved asPDF, RTF, CSV, or Excel files.

The ExpressView Designer

Making an ExpressView

ExpressViews allow you to quickly see data and make reports without concern for the minutiae of old-fashioned report building.

To make an ExpressView:

Click the Create New Report icon and select ExpressView.

The ExpressView designer is divided into the Data pane on the left, and the Design pane in the middle. The Data pane comprises all of your accessible data categories, containing groups of related data fields. Click the Choose Dataicon to expand or collapse the Data pane.

Expand a category by clicking the arrow icon. This shows the fields in that category.

Drag a field onto the Design pane to add it to the ExpressView. This expands the field into a data column. It will only show placeholder data initially.

Dragging a field onto the Design pane

Continue to add fields as desired. Fields can be added or removed at any time.

Note: As you add fields, unrelated fields will become unavailable. To learn about how fields relate to each other, see Joins.

When you are satisfied with your data selection, click Live Data to populate the ExpressView with data. You can now page through your data using the navigation iconsin the toolbar.

Truncated results displayed This icon and message indicates that the report has only returned a partial data set. There are fewer detail rows than the full set, and aggregate formulas and visualizations apply only to the data that was returned to the report. To get more data, click the icon and select either:

Generate +number to get the next number of data rows and add them to the existing report.

Generate All to get the full data set.

Click the Save icon to save the ExpressView. In the Settings window, enter a Name and select a folder where it should be stored. Then click Save Report Info.

These steps illustrate how quick it is to make a tabular report from scratch using ExpressView. But ExpressViews can be more than just basic reports. You can make groups, charts, calculations, and customize the look of the report. And it is all designed to be easy to use. The articles in this sectionwill describe how to use these powerful features.

About the Radial Menu

Throughout this article you will see references to a menu called the Radial Menu. The Radial Menu is a menu of options for each data column and group, which is accessed by clicking the coloredRadial iconon the top left of the column or group.

Using the radial menu

Each radial menu has four options, arranged in a circle around the center. Whenyou are prompted to select aradial menu option, you will be asked to "use radial>direction". Direction is one of the four cardinal directionscorresponding with one of the four options: left, right, up, down.

For example, "use radial>left" means to open the radial menu and select the left option. To do this, you have two options:

Click the radial icon, move your cursor left, then click again.

Click-and-hold the radial icon, drag your cursor left, then release.

To close the radial menu without selecting an option, click in the center.

Interactive Editing

With the introduction of interactive editing to ExpressView in v2018.2, you can customize your report by simply right-clicking on the desired section while in design or live mode.

Right-click options include all of the capabilities of the radial menu, conditional filters, and formatting options such as text alignment, font type, and font color.

The technology stack below provides a specification of the different products and solutions supported for an Exago BI implementation.

Web Server

Windows (.NET Framework)

IIS

Linux (Mono), see list of supported distributions

Apache

Nginx

Azure App Service (.NET Framework)

Server Distribution

Web Farm + State Server

Web App + Remote Execution Servers

Web Farm + State Server + Remote Execution Servers

(+ Distributed Scheduler Servers)

Data Sources

Arbitrary (out-of-box)

ODBC

.NET Assembly (custom)

SOAP Web Service (custom)

Excel

XML

ADO.NET (out-of-box)

SQL Server

ADO.NET Free Driver

MySQL

Oracle

Postgres

Informix

DB2

ADO.NET Paid Driver (CDATA)

MongoDB

ElasticSearch

Snowflake

Redshift

Partial Support (out-of-box)

MSOLAP/MDX

Note: Please see the Data Sources article for more information.

Report Storage

File System

Folder Management (custom)

.NET

SOAP

Azure File/Blob Storage

Note: Please see Report and Folder Storage/Management for more information.

Schedule Storage

File System

Scheduler Queue (custom)

.NET

Configuration Storage

File System

Azure Blob Storage

Application Integration

Server-side

.NET API

REST API

SOAP API

Client-side

JavaScript

IFrame

Note: Please see the Introduction to Integration article for more information.

Client Browsers

IE11

Edge

Firefox (Desktop & Mobile)

Chrome (Desktop & Mobile)

Safari (Mac & Mobile)

Extensibility

Custom Functions

.NET

JavaScript (server-side)

Server Events

.NET

JavaScript (server-side)

Action Events

.NET (server-side) + JavaScript (client-side)

JavaScript (server- & client-side) [Windows only]

Note: Please see An Overview of Exago Extensions for more information.

To configure the Scheduler Service, edit the file eWebReportsScheduler.xml in the folder where the Scheduler Service was installed.

The following settings are available:

Note: Settings that can be true or false are case sensitive and must use lower case.

smtp_server The SMTP server used by the Scheduler Service to email reports.

smtp_enable_ssl Set to true to enable SSL.

smtp_user_id The user id that is used to login into the SMTP server.

Note: If the user id is set to a null or empty value or "DefaultCredentials", the value automatically defaults to the user's current credentials. For anonymous emailing see the Credential-less SMTP section below.

smtp_password The password that is used to login into the SMTP server. This value is only considered when using the user id as an SSL credential.

Note: If the password is set to a null or empty value, the value automatically defaults to the user's current credentials.

smtp_from The 'From' email address used in the report emails.

smtp_from_name The 'From' name used in the report emails.

error_report_to The email address to send error reports to.

channel_type tcp or http must match the setting of the Remote Host in the Admin Console Scheduler Settings.

port The port number of the .NET remoting object used to communicate with Exago; this should also be entered in the Admin Console Scheduler Settings.

working_directory The directory where scheduled documents and temporary files are written. The default setting [INSTALLDIR]working creates a working folder in the Scheduler Service location.

default_job_timeout The maximum number of seconds any report execution is allowed. If an execution reaches a maximum number of seconds an email will be sent to the address specified under error_report_to.

Note:This setting is outdated. Usemax_job_execution_minutesinstead. Do not use both, as this could result in inconsistent timeout behavior.

report_path A path to specify where to save reports when 'Email Scheduled Reports' is set to false in the Admin Console. For more details see Saving Scheduled Reports to External Repository.

sleep_time The time interval (in seconds) used for polling for scheduled reports to execute.

simultaneous_job_max The maximum number of report executions that can occur simultaneously. This setting is based on the resources available of the server where the Scheduler Service is installed.

logging Logging is on by default. To turn logging off, set to OFF (in all-caps). To configure logging, edit the Logging Settings in the eWebReportsScheduler.exe.config file.

flush_time The number of hours that a completed, deleted, or aborted job will be saved for viewing in the schedule reports manager. Set to 0 to flush jobs immediately upon completion. Set to -1 to disable automatic flushing.

sync_flush_time The flush time for synchronous (non-scheduled remote) jobs.

email_addendum Text that will be added at the end of email body. Use \n to insert lines.

external_interface This is optional and overrides the value set in the admin config. The advantage of setting the value here is that the existing scheduled reports that have a previous external interface value will take the new value. For more details see External Interface.

abend_upon_report_error This controls how the scheduling service should proceed if an error occurs while loading or executing a report. The default true will stop the running the schedule and set the status to 'Abended'. Set to false to continue running the schedule and maintain the status as 'Ready'.

ip_address Binding IP address for the Scheduler Service. Most commonly used when the server has multiple Network Interface Cards (NICs).

encrypt_schedule_files Set to true to encrypt the files created by the scheduling service. All existing schedules will be encrypted the next time the service is started.

max_temp_file_age The number of minutes between each "flush" of the temp files created by the scheduling service. The default is 1440 minutes (24 hours).

Note: Making this value too low may result in errors as temp files are used during report execution and for interactive HTML capabilities when using remote execution. It is not recommended setting this value any lower than 60 minutes. Execution cache files will not be flushed.

email_retry_time In the case an email fails to send, the number of minutes to wait before retrying to send the email. After five failed attempts the schedule will set itself to 'Aborted'. The default is 10 minutes.

max_job_execution_minutes (v2016.2.12+) Maximum amount of time (in minutes) to run an execution job before timing out.If the job times out, the schedule will be marked as 'Aborted'.

Note: Do not set both this setting anddefault_job_timeoutat the same time. This could result in inconsistent timeout behavior.

secure_channel(v2016.3+) Set to true to allow receipt of encrypted data from hosts. The setting Use Secure Scheduler Remoting Channel must be true in the admin config.

security_protocol (v2016.3.4+) Specify which security protocol(s) thescheduler should use. Possible values:Ssl3, Tls, Tls11, Tls12, Tls13 (.NET v4.6+). Separate multiple values with commas (,).

service_name_tag For manualinstallation of Scheduler Services usingVisual Studio installutil.exe, this field is appended to the end of the service name. Usefulfor installing multiple services on the same server. installutil.exe must be in the same folder as the scheduler configuration file.

Example XML File

<?xml version="1.0" encoding="utf-8" ?><eWebReportScheduler><smtp_server>smtp.office365.com:587</smtp_server><smtp_enable_ssl>true</smtp_enable_ssl><smtp_user_id>[email protected]</smtp_user_id><smtp_password>SMTP_Password</smtp_password><smtp_from>[email protected]</smtp_from><smtp_from_name>Exago Scheduler</smtp_from_name><error_report_to></error_report_to><channel_type>tcp</channel_type><port>2022</port><working_directory>[INSTALLDIR]working</working_directory><default_job_timeout>3600</default_job_timeout><sleep_time>15</sleep_time><simultaneous_job_max>1</simultaneous_job_max><logging>debug</logging><flush_time>-1</flush_time><sync_flush_time>0</sync_flush_time><email_addendum></email_addendum><external_interface></external_interface><report_path>[INSTALLDIR]</report_path><abend_upon_report_error>true</abend_upon_report_error><ip_address></ip_address><security_protocol></security_protocol><encrypt_schedule_files></encrypt_schedule_files><max_temp_file_age>1440</max_temp_file_age><email_retry_time>10</email_retry_time><queue_service></queue_service></eWebReportScheduler>

Starting and Changing Scheduler Services

The Windows Service will have to be manually started for new installations of the Scheduler. Starting the service will create the working directory as set in working_directory described above.

To start the scheduler open Windows Services. Double click on 'ExagoScheduler' and the Properties menu will appear. Click Start.

If any changes are made to the configuration (detailed above) the service must be stopped and restarted for the changes to take effect.

Credential-less SMTP(v2018.1+)

Within Scheduler Configuration, the values for <smtp_user_id>, <smtp_password>, <smtp_from>, and <smtp_from_name>, which constitute the required SMTP credentials for non-anonymous emailing, cannot be removed or left blank. Otherwise, these values will be reset to their default or throw an error when attempting to send an email.

However, credential-less SMTP servers can now be used to send anonymous emails by making the following changes within the eWebReportsScheduler.xml file. This file is located in the Scheduler Service's installation directory.

To set up anonymous report emailing:

Set <smtp_user_id> to "NoSmtpCredentials"

Set <smtp_password> to any non-null value

Any scheduled report using these credentials will be sent anonymously when connected to an SMTP server that does not require credential authorization.

As of v2016.1, Exago introduced a new powerful feature to scheduling: the Scheduler Queue. The Scheduler Queue is a custom-built application library that sits between the Exago core application and any number of Scheduler Service instances and handles how schedule traffic is managed. The Scheduler Queue is completely optional, but configurations with multiple scheduler instances for which load balancing is a priority are ideally suited to making use of this feature.

Background

The way in which Exago has historically handled report scheduling and the default behavior without using a queue, is the following.

For this discussion, it's important to define some terms:

A Schedule represents all of the information that a Scheduler Service needs to execute a report. A Schedule contains information such as report name, time and frequency of execution, and how to handle the result. This information is usually stored as an XML file in a repository. Schedules can be accessed from the .NET API using the ReportScheduler class.

Each Schedule contains some interpreted data that tells a Scheduler Service when to run it. This information is called a Job. Jobs can also be stored separately from Schedules. Jobs can be accessed from the .NET API using the QueueApiJob class.

The process whereby a Scheduler Service runs a report at a specified time and emails or saves the information is called an Execution.

Within the host application, all Scheduler Service instances are listed in the configuration XML file:

Downloads

When a Schedule is created in the Web Application, the host application sends the job to Scheduler Services starting with the first and moving through the list ("round-robin" style). The queried Scheduler Service stores the Schedule XML in a local working directory. This acts as a repository for the Scheduler Service's unique set of jobs.

From this point, each Scheduler Service works independently. The host application has no idea what happens to schedules after they are sent out successfully. Likewise, the Scheduler Services have no more communication with the host application with regard to report execution.

Note: Schedules can be viewed and edited from the Web Application using the Schedule Manager, but this is essentially a combined front-end for the Scheduler Services' existing files. If a Scheduler Service is offline its schedules do not appear in the list (there will be a warning message). The Schedule Manager has no impact on the host application.

Scheduler Services periodically scan their repository for job execute times. If a job is ready and the current time is equal to or past the execute time, the Scheduler Service knows to run the job. The Scheduler Service will perform its duty and then alter the schedule XML file to indicate success or failure and the next execute time.

This default behavior may be adequate for most cases, but there can be issues. In particular, the Scheduler Queue sets out to solve the following two issues that can arise in default configurations: Load Balancing and Unexpected Outages.

Load Balancing

Ideally, unoccupied Scheduler Services would receive new jobs. This way, stacks of unexecuted data do not build up on individual Scheduler Services, leading to unbalanced load and potential time loss. But the host application has no idea which schedulers will be busy when, and no idea how long jobs will take to run. The randomness of round-robin job assignment could cause jobs to build up inordinately on one Scheduler Service.

Outages

Once the host application sends out a schedule, as far as it's concerned, it's finished. If a Scheduler Service goes offline unexpectedly the host has no recovery function. The job will simply be delayed until the Scheduler Service is restarted. There is also no function to move schedules from one Scheduler Service to another.

How the Queue Works

The Scheduler Queue is a custom .NET or Web Service library which aims to handle scheduling in a more robust manner. It's important to note that the queue is entirely customizable. You are only required to implement the applicable methods; how you do so is up to you. The following section will describe a typical setup which can improve load balancing and help resolve some common issues with multiple Scheduler Services. Later on, we provide a pre-built example that can be used as-is with minimal modifications or altered as you see fit.

The queue sits in between the Exago host application and any number of Scheduler Services and handles logic for all scheduler requests and maintenance.

Architecture diagram

The host applications and Scheduler Services all make calls to the queue at certain points during their runtime. In particular, Scheduler Services will call the queue on three occasions: upon service startup, periodically while running, and when a job's status is changed. The host application calls the queue for various maintenance tasks related to schedule creation and populating the Schedule Manager. For now, we'll focus on the relationship of Scheduler Services to the Scheduler Queue and how it can aid a typical multi-scheduler configuration.

When Scheduler Services are configured to use the queue, their behavior changes somewhat.

Note: In the default configuration, Scheduler Services store their unique schedules in a local working directory, from which jobs are queried for execution.

Scheduler Services now periodically query the queue, which has instructions (GetNextExecuteJob) for assigning jobs. (The query time defaults to 15 seconds, but is configurable). In a typical setup, the queue pulls from a central repository of stored schedules. In order to prevent duplication, Scheduler Services lock the queue so that only one may access it at a time. Additionally the Scheduler Queue sets a job's status to "running" while it's active, so that other Scheduler Services know to ignore it. (The provided example also saves a temporary file in the job repository to indicate which Scheduler Service is handling a running job).

Note: Scheduler Scervices still use a local working directory for temporary files.

This has several advantages. First, Scheduler Services are no longer responsible for a unique set of schedules. This prevents outages from causing excessive missed executes. Only one job will ever be hung per Scheduler Service as it will be responsible for only one job at a time. If a Scheduler Service goes offline in the middle of a job, the Scheduler Queue can be used to gracefully handle incomplete jobs (this is not present in the provided example ).

Next, jobs are now distributed much more evenly between the Scheduler Services. Jobs will only be assigned to available Scheduler Services. Finally, since this allows us to control what data is being sent and received to the Scheduler Services and the file system, any custom load balancing solution can be implemented.

Set Up

Setting up the Scheduler Queue is a multi-part process which depends on the desired configuration. We'll discuss some constants and some potential variations.

Write the Scheduler Queue

First we need to write the Scheduler Queue. This is discussed in more detail in the Example section. This can be a .NET Assembly or a Web Service, and it can be part of another library.

All the following methods must be implemented in the Scheduler Queue interface:

public static string[] GetJobList(string viewLevel, string companyId, string userId)

Called from the Exago Web Application to populate the jobs in the Schedule Manager.

public static string GetJobData(string jobId)

Called from the Exago Web Application Schedule Manager to get the full job XML data.

public static void DeleteReport(string reportId)

Called from the Exago Web Application when a report is deleted.

public static void RenameReport(string reportId, string reportName)

Called from the Exago Web Application when a report is renamed.

public static void UpdateReport(string reportId, string reportXml)

Called from the Exago Web Application when a report is updated.

public static void Flush(string viewLevel, string companyId, string userId)

Called from the Exago Web Application Schedule Manager in response to a click on the Flush icon.

public static void Start(string serviceName)

Called from Scheduler Services to indicate when a specific service starts.

public static string GetNextExecuteJob(string serviceName)

Called from the Scheduler Services to return the next job to execute.

public static void SaveJob(string jobXml)

Called from both the Scheduler Service and the Exago Web Application to save the job. This method is called when a schedule is added, updated, completed, killed, etc.

The QueueApi and QueueApiJob helper classes have been added to the API to facilitate writing the Scheduler Queue. You'll need to reference the WebReports.Api.Scheduler namespace. QueueApiJob wraps a Job object and a variety of useful methods for managing jobs. The QueueApiJob class will be used extensively in the following example.

Configuration

The host application config and each Scheduler Service config must contain the path to the Scheduler Queue assembly or Web Service class in the following format:

Assembly=Path\To\Assembly.dll;class=Namespace.Class

The path may be set by one of these methods:

Using the Admin Console by setting the General > Scheduler Settings > Custom Queue Service field.

Setting the Custom Queue Service field in the Admin Console

By setting the field <schedulerqueueservice> in the config file,

By setting the field Api.SetupData.General.SchedulerQueueService via the .NET API at runtime.

In each Scheduler Service, set the field <queue_service> in the config file.

Next, determine how schedules are accessed. A common solution uses a database to optimize lookup speed. The queue only needs to know the Job ID (filename), Next Execute Time, and the Running status to determine which schedules to run.

Job ID

Next Execute Time

Running?

String

DateTime

Boolean

If you're using folder management, you can implement the those methods in the queue assembly (see Report and Folder Storage/Management for more information).

Examples

We provide the following two examples for reference.

Basic Example

The first is a basic example designed to showcase how the Scheduler Queue works. It is not suited for use in a production environment. However, it can be quickly compiled and used for testing, with minimal setup.

This example uses a directory for schedule storage and fully implements the Schedule Manager. It supports unlimited scheduler services, and implements simple versions of load balancing and error recovery.

Download the example here. To compile, set the QueueDirectory global variable, rename the file with a .cs extension, and add it to a Visual Studio project.

Production Example

The second example, generously provided by SofterWare, is a full-fledged production-ready implementation of the Scheduler Queue. This demonstrates how the Scheduler Queue can use a database for schedule storage, which has significant performance advantages over using a file system.

This Scheduler Queue generates schedule data dynamically and on-the-fly, implements advanced tenanting, and uses a Server Event to implement custom emailing behavior. Note that temporary execution files must still be written to the file system.

Exago Inc. clients may download this example from the page. This example will require significant customization for your environment.

This code was originally created by Dave Killough and SofterWare, Inc. SofterWare has released it for Exago customer use in August 2017.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NON-INFRINGEMENT. IN NO EVENT SHALL THE COPYRIGHT HOLDERS, ORIGINAL COPYRIGHT HOLDERS, OR ANYONE DISTRIBUTING THE SOFTWARE BE LIABLE FOR ANY DAMAGES OR OTHER LIABILITY, WHETHER IN CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Execution Caching allows the data from a report execution to be saved so that reports can be viewed more quickly and with less stress to the data source. The frequency and schedule of data source updates is customizable for each report.Updates can be scheduled for off-hours in order to better balance system load. Caching can significantly reduce data source calls for reports that are accessed frequently. Large or data-intensive reports, which can take a long time to run, especially benefit from caching.

Note: Execution caching is currently incompatible with the Scheduler Queue.

Enabling Execution Caching

Execution Caching uses the Scheduler Service and the scheduling user interface to cache the data and set the refresh intervals. When caching is enabled for a report, a caching schedule is created for that report which determines how often the data is refreshed. Scheduler Services need to be enabled for the user or users who will be setting the caches; however, users who only need to view the cached reports do not need to have scheduling enabled.

To enable execution caching, set the following admin console setting to True:

( Scheduler Settings ) Enable Execution Cache

Cache Visibility Settings

The userId and companyId parameters must exist and have default values assigned to them in order to be able to enable execution caching. These parameters are used to assign visibility permissions to cached data.

The following administrative settings that affect access to some schedule recurrence options will also affect the recurrence options available in the execution caching window: ( Scheduler Settings )Show Schedule No End Date Option ( Scheduler Settings )Show Schedule Intraday Recurrence Option The following administrative setting will affect the view level options that a user has when assigning permissions for an execution cache: ( Scheduler Settings ) Scheduler Manager User View Level (pre-v2017.3)( Scheduler Settings ) User Cache Visibility Level (v2017.3+)

The cache data is stored in the scheduler temporary directory, however cache files will not be deleted by the scheduler's routine cleanup.

Setting Cached Reports and Refresh Intervals

To set up caching for an advancedreport:

Caution: Role-based filters and column-based tenancy apply based on the user who creates the cache.Users who can view the cache will see data filtered by the creator's role and tenancy, not the viewer.

From the companyId parameter Report Options menu, click Options > Execution Caching.

Select theEnable execution caching check box.

Choose which users will be able to see the cached data:

Note: Not every option may be available.

User: Users will only see cached data that they have created. When another user runs the report, it will execute against the database instead of the cache.

Company: Users will see cached data that has been created by anyone with the same . When a user from outside the company runs the report, it will execute against the database instead of the cache.

Global: The report has one data cache which is visible for all users.

Choose how often the cache is updated:

Enter a time of day in the Schedule Time field, or use the picker to select a time.

Optional: To update the cache multiple timesper day, select the Repeat Every check box and enter a time for how often it should repeat.

Optional: To repeat until a certain time of day, enter that time in the Until field, or use the picker to select a time.

Choose which days of the week the cache is updated. Select either:

To repeat every day or every number of days, click Every __ day(s) and enter a number of days for how often it should repeat.

To repeat every Mon, Tues, Wed, Thurs, and Fri, click Every weekday.

Click OK.

When execution caching is enabled for a report, the cache is not created immediately. To run the report, you can manually create the cache by clicking the refreshicon, or you can wait until the first scheduled update completes. The cache can always be updated manually by clicking this icon.

Cache schedules are visible in the schedule manager, with the label (cache). Caches can be edited or deleted using the schedule manager or the advanced report designer.

Viewing a report cache in the schedule manager

Editing cached reports

Editing a cached report invalidates its cache. It cannot be run until the cache is updated. A missing or invalid cacheis indicated by the message Cache is invalid, bothin the report description and in the report tree menu.

Valid and invalid report cache indicators

It is recommended that you fully design a report first, so that you canmanually verify the output,before enabling the cache.

Aggregates are calculations performed on a group of data rows. For example, counting the number of rows in a group is an aggregate calculation. In Exago, this is done by using an aggregate formula in the Group Footer or Report Footer section of a report.

Supported database aggregate functions are AggCount(), AggDistinctCount(), AggSum(), AggAvg(), AggMax(), and AggMin().

Exago calculates aggregates by individually pulling each detail row from the data source, and then applying a formula. For small reports this is usually acceptable, but in reports with large numbers of rows, where you don't need to see the row data, performance can be reduced.

Most databases have the ability to calculate aggregates themselves. This tends to be much faster than performing the calculations in memory. The Database Aggregation feature allows Exago to push these calculations to the database. This allows Exago to retrieve only the summary records from the database instead of all the individual data rows. This can mean a significant performance boost especially for reports containing large amounts of data.

Enabling Database Aggregation

Note: Database Aggregation is enabled by default in Exago v2019.1+.

Database Aggregation can be enabled (or disabled) by either:

changing the Admin Console's General > Database Settings > Aggregate and Group in Database setting

by editing the <aggregateandgroupindatabase> node in the config file.

A value of True enables Database Aggregation, False disables it.

Warning: You must see the following section to ensure that aggregates for One-to-Many joins are calculated correctly.

Identifying One-to-Many Joins

All One-to-Many joins must be identified as such in the application configuration. Exago calculates aggregates based on the unique key relationship between categories. But some databases calculate aggregates by simply counting the rows. This means Exago must know which joins are one-to-many, so that it can decide whether it is acceptable to push the calculation to the database, or whether it must do the calculation in the application.

Note:The Data Source Metadata Discovery tool cannot identify whether joins are One-to-Many. By default it sets all joins as One-to-One. If you used this tool to autofill your Objects and Joins, you must take these precautions before enabling Database Aggregation.

Admin Console

To set your joins using the Admin Console, expand Data > Joins, and then double-click on the Join, or select the Join and click the Edit Execution Caching icon. This will open the Joins tab.

In the Relation Type drop-down menu, select One To Many. Then click Apply or Okayto save your changes. Do this for each relevant Join.

Config File

To set joins by editing the config file, open the file in a text or XML editor. Each join is a <join> element. In each relevant join element, locate the <relation_type> field and change the value from "11" (one-to-one) to "1M" (one-to-many). Save the config and restart the web server.

Exceptions

Database Aggregation will not work on every report. In the following cases aggregation occurs in memory even if Database Aggregation is enabled:

ExpressViews (pre-v2017.3)

Reports with enabled (v2017.3.19+)

Reports with more than one data source

Reports with an aggregation on the One side of a One-to-Many join

Reports with a visible Detail section or cells referencing a hidden Detail section

Reports with Formula Sorts or Custom Columns

Reports containing an AggDistinctCount() formula (pre-v2019.1.3)

Reports containing a CrossTab Report

Aggregate formulas with a non-data field argument, such as another formula

AggAvg() needs to be in the innermost group if there are multiple groups or a group and a report footer

Reports with one or moreInteractive Filters, Column Sorts, Group Min/Max Filters, Top N Filters,Linked Report Formulas, CrossTabs, or TransformedVertical Tables

The Exago Administration Console serves as a user interface to set up and save administrative preferences. Use the Administration Console to create and modify:

Data :Establish how to connect to data sources and determine what data should be exposed to users. Configure system-wide variables called Parameters.

General :Modify global settings of Exago to enable/disable features.

Roles :Create and modify security Roles for individuals or groups of users.

Extensions

Functions :Create and modify custom functions to make calculations on reports.

Aggregate Functions : Create and modify custom aggregate functions.

Filter Functions : Create and modify custom filter functions

Server Events :Create and modify custom code that is run when reports execute.

Action Events : Create and modify custom code that activates when a certain condition in the application is met.

Custom Options :Create and modify custom options that can be set on reports.

The Administration Console creates two configuration files: an XML file called WebReports.xml and an encrypted XML file called WebReports.xml.enc. These files are created and saved in the Config sub-directory of Exago's installation location. The encrypted file cannot be edited with the Admin Console. It is recommended to use this as the live version of the config in a production environment. Copy WebReports.xml toa secure backup, and then delete WebReports.xml from the Config directory.

Important: A backup copy WebReports.xml.backup is no longer created.

Accessing the Administration Console

Once Exago is installed, navigate with a web browser to http://Your Server/Exago/Admin.aspx.

It is recommended to protect access to the Admin Console by setting values for the General > Other Settings > User ID, Password and Confirm Password parameters.

Navigation

The Administration Console consists of two sections. On the left is the Main Menu and on the right are Tabs that can contain menus to create and modify Data Sources, Data Objects, Parameters, Roles, and other settings.

Main Menu

Through the main menu you can:

Create Data Sources, Data Objects, Joins, Parameters, Roles, and Custom Functions.

Edit settings for: Data, Roles, Functions, and General features.

Delete Data Sources, Data Objects, Joins, Parameters, Roles, and Functions.

Click the Collapse icon to hide (or restore) the main menu.

Tabs

The right section of theAdmin Console is made up of tabs containing menus to create and modify administrative settings.

To save the changes made in a tab clickApply or Okay.

Tabs can be closed with or without saving by clicking theClose icon to the right of the tab name.

Tabs may be arranged by clicking and dragging them as desired.

Creating Additional Configuration Files

As part of the integration of Exago you may want to create alternative configuration files in addition to WebReports.xml. Additional configuration files can be utilized in two ways:

If entering Exago directly, the configuration file to be used is specified in the Custom Styling.

When entering through the API the configuration file to be used is specified in the API Constructor Methods.

To create additional configuration files:

Navigate to the Administration Console in a web browser.

Append "?configFn=NewConfigFile.xml" to the URL replacing NewConfigFile with the desired configuration file name.

Click in the URL bar and press enter.

Important: Please review the ETL Licensing article for information regarding purchasing and licensing.

Within Exago, ETL (Extract, Transform, Load) functions as a scheduled process capable of compiling a collection of data from disparate data sources, transforming this data, and finally writing the cleansed data back to a target database. It performs these processes all while using Exagos built-in capabilities of sorting, grouping, filtering, aggregation, and so on.

ETL will ultimately make the process of reporting off of this data more performant and user-friendly. For example, if a data warehouse is physically separate from Exago's implementation, ETL can be configured to consolidate relevant information on a local database, allowing for lower latency and faster reporting.

Note: Currently, ETL Jobs are only capable of writing to databases supporting SQL (MSSQL, MySQL, PostgreSQL, Oracle, and DB2 are supported).

With the addition of ETL to Exago, two sections have been added to the Admin Console:

ETL Settings: located within the General section, where corresponding settings can be defined and the ETL Schedule Manager can be accessed.

ETL Jobs: located within the Data section, where ETL Jobs can be created and edited.

ETL Settings

An ETL Job is similar to an Advanced Report in terms of its setup, design, and scheduling, and is stored as a standard report, rather than as a data source. This is intended to simplify the implementation of ETL within Exagofor example, if folder management is implemented for reporting, then the same folder management scheme can be used to store ETL Jobs.

In order to create ETL Jobs, an ETL Transforms Folder must be defined.

ETL through .NET API

The ETL Settings menu

The ETL Transforms Folder is the parent folder where the particular ETL Transformsare stored. This folder has been configured to hide the ETL Transformsthat are used to generate ETL data, preventing users from accessing these objects outside of the Admin Console.

ETL Schedule Manager

The ETL Schedule Manager is also located within the ETL Settings menu. By clicking the Show ETL Schedule Manager button, scheduled ETL Jobs can be viewed and managed.

The ETL Schedule Manager

The ETL Schedule Manager functions identically to the standard report Schedule Manager. Within the manager, scheduled ETL Jobs can be tracked, edited and deleted.

ETL Jobs

ETL Jobs are the foundation of ETL processes within Exago. These jobs collect and compile the data from specified data sources, structure and cleanse the compiled data, and define where and how often this data is written to the target database.

A list of ETL Jobs

Creating ETL Jobs

To create a new ETL Job, highlight the ETL Jobs section of the Data menu in the Admin Console and click Add, then insert the following information:

Job Name: The name that identifies the particular ETL Job.

Target Data Source: Select the data source where the data will be written to.

Note: A Target Data Source must be selected before an ETL Transformcan be edited.

Tip: The Target Data Source can be set to write to any data source regardless of the data sources it is compiling data from, allowing for the construction of separate data sources for reading a writing data.

Transform: The transform that is created to generate the written data.

This information will automatically populate within the Transformdialog after a new ETL Transformis created. For more information on creating ETL Transforms, refer to the ETL Transform Designer section below.

If an existing ETL Transformis being used to create a new ETL Job, the transform may be selected from the Transformdialog.

Note: Only transforms located within theETL Transforms Folder setting may be added as Transforms.

Data Object Name: Define the name of the data table that the data will be written to. Special characters such as ! @ # $ % ^ and [space] cannot be used.

If a new data table is defined as the Data Object Name, it will be created and written to upon the initial execution of the ETL Job.

If a pre-existing data table is defined, it will rewrite any existing data already present in that table. For more information on writing to pre-existing data tables, see the Clearing Existing Data section below.

Execute: Immediately executes the ETL Job once, writing all data to the specifieddata source.

Options:

Clear Existing Data on Execute: If enabled, the existing data on the target data table will be cleared before eachETL Job executes. For more information please see the Clearing Existing Data section below.

Scheduling: ETL Jobs utilize the Scheduler within Exago. By selecting Execute on a Schedule, scheduling settings can be adjusted for when the ETL process is executed. These settings include:

Schedule Time: The time of day that the process is executed.

Repeat Every: Whether or not, how often, and how long the process executes after the set time.

Recurrence Pattern: The daily interval that the process executes.

Example

Clearing Existing Data

IfClear Existing Data on Execute is disabled, data will accumulate with eachETL Jobexecution and no data will be overwritten. However, if Clear Existing Data on Execute is enabled, ETL Jobswill rewrite existing data tables every time they are executed. Due to this fact, administrators should be cautious when writing to pre-existing data tables.

In order to mitigate the accidental rewriting of data, whenever an existing data table is added to the Data Object Namefield, a warning icon will appear notifying you that a table with this name already exists, and that writing to this data table will rewrite all data.

A Data Object Name set to a pre-existing data table

When executing an ETL Job on a pre-existing data table:

Pre-existing Columns:

Pre-existing columns and corresponding column metadata will not be deleted from the data table.

Data associated with pre-existing columns that exist in the ETL Transformwill be deleted and rewritten with new data.

Data associated with pre-existing columns that do not exist in the ETL Transformwill be deleted and rewritten with null values.

New Columns:

New columns and corresponding column metadata specified within the ETL Transformwill be added to the data table.

Data associated with new columns will be written to the data table.

The visual example below shows a possible outcome of an ETL Job rewriting the data of a pre-existing data table:

Pre-existing data table (top) and ETL Transform data (bottom)

The data table after being rewritten by the ETL Job

ETL Transform Designer

The ETL Transform Designer functions similarly to theAdvanced Report Designer. In fact, it is an Advanced Report designer in ETL Mode, which removes some options that would not be necessary for the purposes of ETL transforming (e.g., visualizations).

To open the ETL Transform Designer, click the New or Edit button, which changes dynamically based on whether or not an existing ETL Transformis selected in the Transform dropdown.

To edit an existing ETL Transform, a pre-existing transformmust be selected prior to opening the ETL Transformdesigner.

Editing an existing ETL Transform

To create a new ETL Transform, leave the dropdown blank and open the ETL Transformdesigner, or select Create New Transformfrom the dropdown.

Creating a new ETL Transform

Creating an ETL Transform

In theName tab, specify a name and select a folder.

Next, in the Categories tab, select the categories that are to be included within the transform.

As with Advanced Reports, Sorts and Filters may be added; however, these are not required.

Next, in the Layout tab, the layout of the transform may be adjusted.ETL Transforms must have a single report header, which is used to set the names of the columns being written to the target database, and a single visible detail section, which is used for writing data to each column. This limitation exists in order to replicate standard database table formatting and prevent different sources of data from writing to the same data columns.

The detail row may consist of a single visible detail section, a report footer, or a grouped header or footer; however, multiple detail rows can exist within the ETL Transforms as long as they are suppressed. This functionality allows data in suppressed rows to be referenced in visible cells.

Tip: See the Grouping and Aggregation in ETL Transforms section below for more details.

An ETL Transform may then be executed in order to verify that the proper data is produced. A partial data set with a step size of 1,000 will be generated in order to optimize execution time; however, the step size may be incrementally increased if a larger sample size is desired. To return to the ETL Transform Designer, click the Back icon located in the top left corner.

Finally, click the Finish button. The newly created transform's name will then appear in the Transformdropdown.

Note: As with Advanced Reports, any specifications made during the initial creation of the transform may be changed in the Report Options after creation.

ETL Transform Formatting

Once the ETL Transform is created, there are many formatting options available for use, such as:

Cell references

Formulas

Grouping

Filters

Other aesthetic formatting options, such as italicizing and coloring font, are also available for use within the ETL Transform Designer.

Note: Currency symbols and other aesthetic formatting options will not be written to the target database. These formatting options have only been included within ETL transforming to allow the application of user-friendly designs when testing the transform.

Proper Formatting of an ETL Transform

Unlike the Advanced Reports, however, there are specific formatting requirements for ETL Transforms.

The formatting requirements for ETL Transforms include:

One visible report header row that contains target object column names.

One visible data row in either a detail, group footer, or report footer section that contains target data.

At least one visible column.

All columns must have a specified ETL Data Type. For more information, see the ETL Data Types section below.

An example of a properly formatted ETL Transform

If these formatting requirements are not met, the transform will not be able to function properly as a data source, and thus, will not be allowed to be executed.

If there is an attempt to execute an improperly formatted ETL Transform, the following popup will block the transform execution:

Note: The transform may still be saved even if it is improperly formatted.

Data Cleansing

Within an ETL Transform, data can be cleansed through the application of filters and filtering by formula.

Useful applications of filters for data cleansing include:

Creating a limited subset of data

Removing null values

Removing or censoring sensitive information

ETL Data Types

For each column within the ETL Transform, a Data Type must be defined. ETL Data Types will be automatically defined based on the information provided in the ETL Data Types Dictionary. This information specifies metadata for the type of data value that will be written to the target database.

To manually select a Data Type, right-click a column within the transform and select a Data Type from the ETL Data Type menu.

An example of possible ETL Data Types

Once a Data Type is defined, the type will appear in parentheses in the column label to indicate that the typing has been changed.

If a Data Type is not set for a column, then the ETL Job will not be able to execute. The following warning message will appear when attempting to save an ETL Transform containing a column with a blank Data Type:

The possible selection of Data Types is manually adjusted in the ETL Data Types section within the dbconfigs.json file. For more information see the ETL Data Types Dictionary section below.

Grouping and Aggregation in ETL Reports

Within ETL Transforms data can be grouped and aggregated. This allows for groups and aggregated data to be created and written to the target database prior to report execution, which will ultimately allow reports based on these ETL Jobsto process and execute this data at higher speeds.

Tip: Reporting off of pre-aggregated and grouped data is often more performant than aggregating in the report.

ETL Data Types Dictionary

There is a plethora of possible data types that can be written to different types of databases. For example, within SQL Server there are 12 different string data types including char, varchar(max), ntext, varbinary, and so on. To make all of the possible data types available for selection as a Data Type could be incredibly overwhelming and redundant.

In order to mitigate confusion and simplify the list of selectable data types within the ETL Transform, the ETL Data Types dictionary has been added. The ETL Data Types dictionary allows only the specified data types to be selectable within the ETL Transform Designer. This file can be manually adjusted to add, edit or remove specific data types, allowing for either a complete set or limited subset of data types to be defined.

The ETL Data Types dictionary is located within the dbconfigs.json file, which stores the settings information for each particular database. Within this file, which is located in the UI > Config > Other directory, data types can be added, edited, or removed using the following information:

Numeric Key: The unique identifier for each data type. This also specifies the order in which it will appear in the dropdown menu.

Title: The title, or friendly name, of each data type is the text that appears in the ETL Data Type menu.

Value: The actual name of the data type as it is to be referred to and processed within the database.

Quoted: A Boolean value determining whether or not the value should be surrounded with quotes when being written to the database. If set to true, the value will insert quotes when writing the value to the database; if false, quotes will be omitted.

Metadata Defaults: A list of data types that the Data Type could assume in the target database including: String, Date, Datetime, Time, Int, Decimal, Image, Float, Boolean, Guid, or Currency. This information allows for the automatic detection and population of Data Type information in columns create in ETL Transform.

The formatting of each dictionary entry should be as follows:

"<numKey>": { "Title": "<title>", "Value": "<value>", "Quoted": <true/false>, "MetadataDefaults": ["<list of metadata types>"] }

Example

An example of an ETL Data Types dictionary containing four data types

Important: The information detailed in the ETL Data Typesdictionary should be consistent among each dbconfigs.json file located in both the Web Application and any managed schedulers.

Creating a Data Object from an ETL Job

Creating a Data Object from an ETL Job is a simple but separate process. These processes were separated to prevent data objects from being created unnecessarily from ETL Jobs that are being used solely for compiling, cleansing, and writing data (e.g., for use outside of Exago BI).

Creating a data object from an ETL Job follows the same process as creating a standard data object.

In the Admin Console, highlight the Objects section in the Data menu and click Add.

Select the data source that the ETL Job references and then select the table that is created by the ETL Transform.

Next add an alias for the data object.

Select the Unique Key Fields.

Add the Category for the data object to be grouped in.

Tip: A Category specifically for ETL data objects can be created for organizational purposes.

Enter a unique Id for the data object.

Optionally add Parameters, Tenant Columns, a Description, and Column Metadata.

Select a Schema Access Type.

Optionally select a Filter Dropdown Object and set the Suppress Sort and Filter option.

Click Apply.

This newly created data object can now be used like any other standard data object within Exago.

ETL Server Event

For information on the ETL Server Event, please see the Global Event: OnEtlExecuteStart article.

ETL through .NET API

For information on how to use ETL through the .NET API, please continue to the article.

The following article walks through the installation process forLinux systems.

Supported Distributions

Red Hat Enterprise Linux 7

SUSE Linux Enterprise Server 12

CentOS 7

Fedora 21-25

Debian 8+

Ubuntu 14+

Warning: Exago is incompatible with SELinux.

Requirements

Exago is not compatible with mono versions installed from system repositories. It is highly recommended that only official mono-project repositories are used or that the Exago installer install mono.

Mono

Important: These are the only versions of mono that are compatible with Exago.

mono 4.2.2.30 or 5.10.1.20

Optional: mono-basic, which provides support for VB.NET

Apache 2.4+

mod-mono

NGINX

mono-fastcgi-server4

The Exago installer can automatically download and install supported versions of mono, mod-mono or mono-fastcgi-server4.

Installation

Apache or NGINX must be installed prior to installing Exago.

The Exago Linux Installer can be used to install the Exago Web Application, Web Service API, and Scheduler Service. It can also install mono, mod-mono or mono-fastcgi-server4. Use the following steps to install Exago on Linux:

Note: Apache or NGINX must be installed prior to installing Exago. If changing from one or the other, verify the dependencies (mod-mono or mono-fastcgi-server4) are installed.

Navigate to the Downloads page, select a build, and then use the Linux Download option. Decompress the download:

tar zxvf ExagoInstaller_vX.X.X.X.tgz

Then change to the newly created Installer directory and run installExago.shas root:

sudo ./installExago.sh

The installer can be run in guided or silent mode. The Linux distribution and the type and version of web server software will be detected automatically.

Silent Installation with Parameters

Usage:

[-d <install path>] [-m <TRUE|FALSE>] [-i <WEBAPP|WEBAPI|SCHEDULER>] [-y] [-h]

-d <Install Path>

Default is /opt/Exago

-m <TRUE|FALSE>

Whether or not to install Mono

-i <WEBAPP|WEBAPI|SCHEDULER>

Which component(s) to install

-a <Web App URL Alias>

Default is /Exago

-s <Web Service URL Alias>

Default is /ExagoWebApi

-y

Do not prompt for final verification before installing

-h

Show this help screen

Examples:

Install the Exago Web Application and Web Service API into /opt/Exago

./installExago.sh -d /opt/Exago -m TRUE -i WEBAPP -i WEBAPI

Full installation without prompting for final verification before installation start

./installExago.sh -d /opt/Exago -m TRUE -i WEBAPP -i WEBAPI -i SCHEDULER -y

Guided Installation

Specify an install path when prompted. Default is/opt/Exago.

If the proper versions of mono (and mod-mono) are not present in your distribution's package repository then the Exago installer can be used to download and install the correct versions. If so, the mono repositories will be added to the package manager's repository list so that they can be updated in the future.

Select which components to install:

Web Application

Web Service API

Scheduler Service

Note: It is possible to install any components at a later time by running installExago.sh again.

See either Apache or Nginx for web server configuration details.

Apache

If Apache is detected, the installer will additionally do the following:

Download and install mod-mono, if it is not already present.

Generate an Apache configuration file exago.conf in the Apache site path.

Set read/write permissions for the current Apache user on the install paths.

Config - Permissions and path set automatically

Temp - Requires read and write permissions

Reports - Requires read and write permissions

MapCache - Requires read and write permissions

Start and Test Install

When installed on a server with Apache, Exago will automatically start at the completion of the installation process.

Point your browser to the Home Page to verify that your installation was successful. By default this is http://<YourServer>/Exago/ExagoHome.aspx

If you encounter problems at any point, please file a Support Ticket.

Continue to Folder Configuration below to continue with the installation.

NGINX

NGINX proxies incoming and outgoing requests to a running instance of Exago using a fastcgi module that is installed during the installation process.

mono-fastcgi-server4 is a prerequisite for Exago to run on Nginx.

Configure NGINX

The required configuration is created in a separate site file located at /etc/nginx/sites-available/exago. The site file is not enabled by default.

Example

#Exago conf file for fastcgi-mono-server4

server {

listen 80;

listen [::]:80;

server_name _;

root /var/www;

location /Exago/ {

include /etc/nginx/fastcgi_params;

root /opt/Exago;

access_log /var/log/nginx/exago.log;

fastcgi_param SERVER_NAME $host;

fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;

fastcgi_param PATH_INFO "";

fastcgi_pass 127.0.0.1:9000;

}

location /ExagoWebApi/ {

include /etc/nginx/fastcgi_params;

root /opt/Exago/WebServiceApi;

access_log /var/log/nginx/exago_ws.log;

fastcgi_param SERVER_NAME $host;

fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;

fastcgi_param PATH_INFO "";

fastcgi_pass 127.0.0.1:9001;

}

}

To enable the site file, link it to /etc/nginx/sites-enabled:

sudo ln/etc/nginx/sites-available/exago/etc/nginx/sites-enabled/exago

Or include the configuration in another running site configuration file.

Note: Make sure that the default port does not conflict with another running site. If it does, you will see a warning when reloading Nginx:

nginx: [warn] conflicting server name "Exago" on 0.0.0.0:80

Then reload Nginx to refresh the configuration:

sudo nginx -s reload

Start and Test Install

When installed on a server with NGINX, this script created during installation needs to be started manually or configured to run at startup:

<Web App Install Dir>/bin/startExago.sh

Substitute <Web App Install Dir> with the directory where the Exago Web Application was installed. By default, this location is /opt/Exago

If the Web Service API was installed, this script created during installation needs to be started manually or configured to run at startup:

<Web Service API Install Dir>/bin/startWebService.sh

Substitute <Web Service API Install Dir> with the directory where the Web Service API is installed. By default, this location is /opt/Exago/WebServiceApi.

Point your browser to the Home Page to verify that your installation was successful. By default this is http://<YourServer>/Exago/ExagoHome.aspx

If you encounter problems at any point, please file a Support Ticket.

Continue to Folder Configuration below to continue with the installation.

Folder Configuration

The Config sub-folder of the Exago installation has read and write permissions set by default and the default path/opt/Exago/Configis preferred.

Create a folder for storing reports. This folder needs to be accessible from the web server, but is not required to be on the web server. The report folder can reside on any server accessible by Exago, provided a mount point is accessible on the Exago server.

Important: Do not create the reports folder within the Exago application structure. This can cause sessions to crash when report folders are created or deleted within Exago.

Set the Report folder's read and write permissions to 775.

sudo chmod 775 Reports

(Apache) Set the default ownership to the specific <apache user>:<apache group>

In the Admin Console, specify the location of the report folder in General > Main Settings > Report Path

Note: The group ownership on the reports directory is not mandatory, and can be changed to have other group ownership as needed for access outside of Exago.

Default UMASK for files written by Exago is 027 and can be changed by updating the MonoUnixUmask option in the generated exago.conf Apache configuration file.

The recommended path for the Temp folder is /opt/Exago/Temp.

Set the Temp folders read and write permissions to 775.

sudo chmod 775 /opt/Exago/Temp

(Apache) Set the default ownership to <apache user>:root

In the Admin Console, specify the location of the temp Folder in General > Main Settings > Temp Path

Set the MonitoringService folder's read and write permissions for the Apache user to 775, and set the default ownership to <apache user>:root.

Scheduler and Monitoring Services

The Scheduler installer for Linux has been updated for v2019.1+. Refer to the Installing the Scheduler Service on Linux (v2019.1+) article for more information.

See Scheduler Configuration and Setting Up Monitoring to configure the respective services correctly. If necessary, configure the Scheduler and Monitoring services to run at startup.

Start Monitoring Service

If the Web Application was installed, the Monitoring Service is also installed, but is disabled by default. This script created during installation needs to be started manually or configured to run at startup:

<Web App Install Dir>/MonitoringService/startService.sh

Substitute <Web App Install Dir> with the directory where the Exago Web Application was installed. By default, this location is /opt/Exago