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")
In this Example: Report Name = "KloudFunctionReport" Column to Return = "product" **note: make sure to check the case in the sql query Filter Column = "company" Value = "Twintey" |
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