This article applies to the Admin Console > Data > Sources settings.

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

• To add a new data source click  Sources in the Main Menu and either:
  • click the Add icon at the top of the main menu
  • right-click and select Add from the context menu
• To edit a source either:
  • double click it
  • select it and click the Edit  icon at the top of the main menu
  • right-click it and select Edit from the context menu
• To delete a source either:
  • select it and click the Delete  icon at the top of the main menu
  • right-click it and select Delete from the context menu
• To save changes and new objects click the Apply or Okay buttons.

Data Source Properties

Each data source must have the following:

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
  • Oracle.ManagedDataAccess.Client
• 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

sudo ./installDevartMySql.sh /opt/Exago

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

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 General >  Programmable Object Settings section.

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 the connection verification icon 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 read Microsoft Excel workbooks and XML files as Data Sources.

Caution

Excel workbooks and XML files will have less speed, performance, and security of a database. Using Excel and XML files is recommended only if the dataset is small or if the information is only available in this format.

Excel

Each worksheet in the Excel file will be read as a separate table with the sheet name as the table's name. The top row will be read as the column headers, and the remaining cells will be read as the data. Do not leave any blank rows or columns.

Connection String Example

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

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>

File=C:\example.xml;Type=xml;

Data Source=SERVER1\MSSQLSERVER2014;Catalog=AdventureWorksDWAnalysis;

' (grave accent)

[] (brackets)

" (quotation marks)