Skip to main content

How do I use the DATASOURCE function in Klips? (+examples)

Updated

Datasource_ID numbers and pointers

The DATASOURCE function is used to indirectly refer to a data source field, specified by a pointer, in a specific data source, using the datasource_id (a unique 32-digit hexadecimal identifier). Learn how to find your datasource_id.

The pointer is entered as a literal string. For example, to refer to

  • column A in a tabular data source: "A:A"
  • column B in worksheet, Sheet1, in a Microsoft Excel workbook, "Sheet1,B:B"
  • /connections/person/last-name in XML/JSON data source: "/connections/person/last-name"

DATASOURCE examples

The DATASOURCE function works with the User Input Control component to allow you to cycle through different data references on a dashboard. 

A common use for the DATASOURCE function is to refer to columns of data in a data source with multiple sheets of data to return. In this example, a data source has a different sheet per product: Books, Magazines, Journals, DVDs and two columns per sheet: Quantity (A:A) and Billing (B:B).

  1. Build a User Input Control with an ARRAY that sets the Values for the drop-down list: ARRAY ("Books, Magazines, Journals, DVDs")
  2. Create a new variable and give it a value: a variable named product and a value named Books.
  3. Build a Table component using the DATASOURCE function and replace the pointer parameter with a variable and CONCAT the column of data, A:A, you want to return based on the variable:

DATASOURCE("ab12b32e9ee3c8037be930a4b7b8f123",CONCAT($product, ",A:A"))

 

Similarly, to select the last_name elements matching multiple values from a JSON hockey data source, you can use a User Input Control and create a variable. In this example, we use the variable, city: 

  1.  Use CONCAT to manipulate the XPath so you can add a variable.
  2. In the XPath, /player[team/name='Winnipeg']/last_name, replace "Winnipeg", with the variable (city).

CONCAT("/player[team/name='",$city,"']/last_name")

If city= Winnipeg, this formula returns /player[team/name='Winnipeg']/last_name.

If city= Montreal, this formula returns /player[team/name='Montreal']/last_name.

  1. Then use DATASOURCE to specify which data source (using the data source ID) to apply this reference to.

Related articles