Data Objects are the tables, views, methods, stored procedures, functions and custom SQL that you want to make accessible for reports.
All existing Data Objects are listed in the Main Menu under Data. All Data Objects that are added or edited will be displayed in a Tab entitled Objects.
NOTE. Data Objects can be added quickly using Automatic Database Discovery.
Each Data Object has the following properties:
Select the Data Object’s Source from the first drop-down. In the second drop-down select a Data Object.
NOTE. This will display all the of the Source’s tables, views, methods, stored procedures, and functions.
NOTE. The name of tables or views may not contain the following characters: { } (curly braces), [ ] (square brackets), ',' (comma), '.' (period/full stop).
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: @ (at sign), { } (curly braces), [ ] (square brackets), ',' (comma), '.' (period/full stop).
The columns which uniquely identify a row.
The ‘folder’ used to group related Data Objects. Sub-categories can be created by entering the category name followed by a backslash then the sub-category name.
Ex. Sales\Clients
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.
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.
Specify which columns contain tenant information and link the parameters accordingly.
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.
Specify how Exago should retrieve the schema for the Data Object. There are three possibilities:
NOTE. For more information see Retrieving Data Object Schemas.
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 Drop-down Object must have a column with the same name as each column in the main Data Objects.
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:
SQL Server has an attribute called ‘FMTONLY’ that must be handled by all stored procedures.
'FMTONLY' has two possible values:
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
AS SET NOCOUNT ON --for performance reasons SET FMTONLY OFF --force procedure to return data and process IF conditions
declare @sql varchar(2000)
declare @columnInfo varchar(1000)
if @callType = 0 --return schema; don't need to return any rows begin set @sql = 'select * from vw_webrpt_person where 0 = 1' end
else
if @callType = 1 --return all data for execution
begin
set @sql = 'select' + @columnStr + ' from vw_webrpt_person where ' + @filterStr + ' order by ' +@sortStr
end
else
if @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 ' + @columnInfo
end
exec(@sql)
Table Value Functions can be used as Data Objects. Any available table value functions of a Data Source will be displayed in the Data Object menu 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.
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 Data Object click the ‘Custom SQL’ button and a dialog will appear.
The name of the Data Object to be displayed in the Administration Console.
The Data Source that will be sent the SQL.
Select the parameter you want to embed in the statements. Use the ‘Add’ button to move the selected parameter into the SQL statement where your cursor is located. Parameters may also be added manually between @ symbols (ex. @userId@).
Use the ‘TEST’ button to verify that the SQL statement is correct.
Press 'OK' to save the SQL statement or 'Cancel' to close the dialog without saving.
‘Macros’ can be embedded in Custom SQL Data Objects to make them even more dynamic. Each macro allows for different SQL to be used according to the circumstances in which the Data Object is being called. Below are the details and examples of available macros.
(string trueCondition, string falseCondition)
Description |
Will include the trueCondition if a user is executing a report. Will include the falseCondition otherwise. |
Example |
select * from vw_webrpt_optionee IfExecuteMode("where [State] = 'CT'","") |
(string dataObjectName, string trueCondition, string falseCondition)
Description |
Will include the trueCondition if dataObjectName exists inside the full Exago SQL statement to the data source. Will include the falseCondition otherwise. |
Example |
select * from vw_webrpt_optionee IfExistReportDataObject("fn_webrpt_grant", "join on fn_webrpt_grant...", "") |
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.
The following properties of each column can be modified:
The name of the Data Field that the end-users see.
The type of data Exago should treat the Data Field as (ex. DateTime).
If set to 'False' the Data Field will not be listed in the Filters menu.
(v2016.3+) If set to 'False' the Data Field will not be listed in the Sorts menu.
If set to 'False' the Data Field will not be listed for users.
(v2016.3+) Specify a custom formula by which columns should be sorted and grouped by the application. For more information see Column Metadata Additions.
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.
To remove Column Metadata for a column, select it in the right panel and click the 'Delete' button.
To save changes to Column Metadata, click the ‘OK’ button. Click the ‘Cancel’ button to discard changes.
(v2016.3+) Custom columns are a way to add columns to Exago that don't exist in the database. This is completely transparent for the users; they can then use them like any other column. New data fields can be created from composite or interpreted data fields. You could even use a formula to create data from scratch. Admins often use custom columns to make popular formula sorts available on an application-wide level. For more information, see Column Metadata Additions.
To add a custom column, open the Column Metadata dialog. Press the Add New button and enter a name for your data field in the dialog box.
Data Type, Column Alias, and Column Value are required fields. In the Column Value field, press the formula button to bring up the Formula Editor.
Press OK when done, then Apply the change.
Many of the dialogs throughout Exago require schema information (ex. column name, data type, etc.). By default these dialogs query the Data Sources for the schema. This process, however, may cause performance issues if the Data Sources take a considerable amount of time to return the schema.
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 retitled.
For Exago to retrieve schema information from Metadata:
NOTE. Alternatively this setting can be overwritten for individual Data Objects by setting the ‘Schema Access Type’ property.
NOTE. Other metadata options such as aliasing can still be utilized.
There are three ways in which you can utilize Data Object Ids.
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.
Providing Ids for all the Data Objects will avoid issues if the name of the underlying tables, views, or stored procedures, is changed.
Web Services, .Net Assemblies, and Stored Procedures comprise a group called Programmable Data 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:
Ex. 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 end end
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.