Applicable Versions: 6.00.0016 and above


An open API query allows you to create your own bespoke assemblies to gather data from anywhere. For example, consider the scenario where I want to use current currency exchange rates in my dashboard. This article on codeproject gives code on how pull currency rates from the Yahoo! Finance portal. Using this, we create a simple C# .NET class that contains a public method getData that accepts a single string input parameter, and returns a DataTable. The name of the class is not important, however the name of the public method it contains must be getData;


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Zayko.Finance;

 

namespace DataPA.Utils.Data.Currency

{

  public class CurrencyDataProvider

  {

 

    public System.Data.DataTable getData(String configString)

    {

      System.Data.DataTable returnValue = null;


      var tokens = configString.Split(Convert.ToChar("|"));

      if (tokens.Length < 2) throw new Exception("Invalid currency definition. Should be in the format [FROM CURRENCY]|[TO CURRENCY]");

      if (CurrencyList.GetCodeIndex(tokens[0]) == -1) throw new Exception(tokens[0] + " is not a valid currency code");

      if (CurrencyList.GetCodeIndex(tokens[1]) == -1) throw new Exception(tokens[1] + " is not a valid currency code");

 

      CurrencyConverter cc = new CurrencyConverter();

 

      // Now build a list of the currency convertions we need the request

      IList<CurrencyData> list = new List<CurrencyData>();

      list.Add(new CurrencyData(tokens[0], tokens[1]));

 

      cc.GetCurrencyData(ref list);

 

      // OK, now let's convert the results to a dataset

      returnValue = new System.Data.DataTable();

      returnValue.Columns.Add("From Currency Code", typeof(String));

      returnValue.Columns.Add("From Currency", typeof(String));

      returnValue.Columns.Add("To Currency Code", typeof(String));

      returnValue.Columns.Add("To Currency", typeof(String));

      returnValue.Columns.Add("Rate", typeof(double));

      returnValue.Columns.Add("Trade Date", typeof(DateTime));

      returnValue.Columns.Add("Min Rate", typeof(double));

      returnValue.Columns.Add("Max Rate", typeof(double));

 

      foreach (CurrencyData cd in list)

      {

        System.Data.DataRow row = returnValue.NewRow();

        row["From Currency Code"] = cd.BaseCode;

        row["From Currency"] = CurrencyList.GetDescription(CurrencyList.GetCodeIndex(cd.BaseCode));

        row["To Currency Code"] = cd.TargetCode;

        row["To Currency"] = CurrencyList.GetDescription(CurrencyList.GetCodeIndex(cd.TargetCode));

        row["Rate"] = cd.Rate;

        row["Trade Date"] = cd.TradeDate;

        row["Min Rate"] = cd.Min;

        row["Max Rate"] = cd.Max;

        returnValue.Rows.Add(row);

      }

 

      return returnValue;

    }

  }

}


Once we have created our class, we must compile our assembly and install it along with any dependencies into the GAC on the target machine (for more details see How to: Install an Assembly into the Global Assembly Cache). Now we have the assembly installed, we can create the query. Follow these steps;

  1. Create a new query, dashboard or report.
  2. Give your query an appropriate name and description.
  3. Select the Open API query type
  4. Enter the name of your class (in my example I'll enter DataPA.Utils.Data.Currency.CurrencyDataProvider)
  5. Enter the configuration string (you can create required fields to prompt the user for values by pressing Insert Required Field).
  6. Press Next and Finish.