How-to Guide: Kloud Function in Excel

Overview

The Excel Kloud Function enables users to return a single piece of data to a cell, so that individual cells—from different data sources—can be dynamically manipulated in Excel.

If you’d like to pull a whole report, please see the Excel Kloud Array documentation.

Usage // Example of Basic Functionality

Step 1: Create a Report

In the Kloudio Portal, create a report that returns a single column.

Step 2: Create a Filter

Next, create a filter on a unique identifier and Preview to ensure a single line is returned.

Step 3: Verify a Single Row Result

Enter in a unique identifier into the filter to check that the result is a single row.

Step 4: Save the Report

Once the report brings back a single row, save the Report.

Step 5: Test in Excel

In Excel, select a cell and enter the Kloud function:

=Kloud("Report Name", "Column to Return", "Filter Column", "Value")

The selected cell which contains the function should have a single value in it.

NOTE: The Excel Kloud Function requires a report that returns a single row. This can be achieved by following the ‘basic functionality’ example outlined above. A more complex, multi-filter example is outlined below.

Usage // Example of Advanced Functionality

A more complex use case for the Excel Kloud Function is where multiple filters are required to retrieve a piece of data, or where a measure such as count or sum is desired.

The example below uses two reports. Each report consists of two tables joined together, and requires multiple filters and a measure to get a sum.

Step 1: Create a Report

Select two tables from the data warehouse and connect them with an inner join.

Step 2: Select the Dimensions

Select the relevant columns under Dimensions and Measures, and delete the rest.

Step 3: Create a Measure

To add a Calculated Field to sum up all the values for rows returned by the query either click “Add Calculated Field” in Measures, or select a Dimension and click “Convert to Measure”.

Once converted, select “sum” from the Aggregation drop down.

Step 4: Validate the Table

In this example, Previewing the Report without any Filter values produces a table that uses the Fiscalqtr and Account Parent to create a unique identifier. This is accomplished by using the sum of a field (Value2 in our example) to get a single value where otherwise there would be multiple.

Step 5: Validate the Filters

Enter the Filter values and verify that a single row is returned.

Step 6: Use Kloud Function in Excel

Confirm the filter query returns a single row and place it into a cell within Excel using the second parameter as the value to be returned.

Step 7: Parameterize the Function

The parameters passed in the function call can be parameterized themselves by referencing data from other cells. In addition, a full sheet can be made by selecting the cell and copying it to others while updating the parameter references.

Summary

The Kloud Function in Excel allows users to return a single piece of data to a cell, with each cell containing its own Kloud Function call. This enables Kloud Function to scale from simple use cases to those requiring multiple filters and measures.

If you have further questions about the Kloud Function in Excel, please contact us at support@kloud.io