Support FAQ > Using Visual KPI > Can standard Excel expressions/equations be used in the Visual KPI Designer?
Microsoft Excel supports hundreds of functions, equations and variable types. Since Visual KPI Designer is built as an add-in to Excel, it is natural to assume that you can use any of the Excel functions when building equations for an Attribute like “Actual” or “High High.” However, since the Visual KPI Designer is the front-end authoring-only tool for Visual KPI, this assumption would be wrong. Here is how it really works when you use a Microsoft Excel function in Visual KPI:
- You can use any Excel equation to build complex value results in any cell of Visual KPI Designer, and you will see the results in the cell. You can save the spreadsheet using the Excel Save command and all of your Excel functions are preserved in the cells.
- Once you save the results to the Visual KPI Server (using the Save button in the Document Actions pane) ALL Excel calculations are converted to the VALUE in the cell and saved in Visual KPI as the resulting number, string, date, etc.
- Visual KPI immediately reads back the result and overwrites the equations in the cells with the VALUES that were just stored in the Visual KPI database.
So how can you make functions and equations in Visual KPI which are executed at run-time by the Visual KPI Server? Easy! Use the built-in equations and functions supported by Visual KPI and be sure to format the cells as an Equation, which is any text contained in angle brackets <> as illustrated below. While Visual KPI does not support ALL of the Excel functions, we support many typical use cases for derived or calculated values in real-world scenarios. Most calculations in Visual KPI are simple math, or relatively short “IF THEN” evaluations and we support these through a built-in standard expression parser. Check out the examples below and see if your needs are met by one of these “patterns.”
Example Equations (for use in any standard attribute, either with or without a data reference in that attribute):
<[Tagname] + 10>
<[Tag1] / [Tag2]>
<[Tagname] * [Actual]/[Max]>
Let us know if you have any questions at email@example.com.
Last updated on February 19, 2013 by Transpara