Examples: Create Expressions and KPIs
/onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/examples-use-of-expressions-and-kpis
section
2024-07-04T17:39:28+02:00
# Examples: Create Expressions and KPIs
To illustrate the creation of expressions and KPIs, we will look at some examples.
First, we will create two expressions called *INCOME* and *COGS*.
We will then create a KPI called GROSS MARGIN % which uses these two expressions to show the gross margin in %.
Finally, we have an example where we create a KPI based on measures.
Create KPI based on Expressions
/onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/examples-use-of-expressions-and-kpis/combine-expressions-income-and-cogs-in-the-kpi-grossmargin
page
2024-08-07T11:22:23+03:00
# Create KPI based on Expressions
In this example, we create a KPI that shows gross margin in %. This KPI will be based on the expressions *INCOME* and *COGS*.
1. In the **Report Designer** menu, select **KPIs** and then **Manage KPIs**.
2. In the dialog that opens, click **New**.
3. In the next dialog, enter a KPI group name (in the example *Financial KPIs*). You can also add a description.
4. Click **Add** and enter a name of the KPI; *GROSS MARGIN %*. You may also enter a comment.
5. This KPI will be based on expressions, so make sure that the **Based on Expressions** option is selected.
Next, you must indicate which measure the expressions that will be included in the KPI are based on.
6. Click the **Edit measure** button in the *Measure* box.
7. Find and select the measure that the expressions *INCOME* and *COGS* are based on. In our case, this is **Net Amount** (under *General Ledger Transactions)*.
8. Click **OK** to confirm.
9. Click the **Edit** button in the **Formula** field.
The **KPI Editor** opens:
| | **Name** |
|-----|--------------------|
| 1 | Expressions |
| 2 | **Formula** area |
| 3 | **Operators** area |
The KPI *GROSS MARGIN %* will consist of the expression *INCOME* minus the expression *COGS* divided by *INCOME* to get the result in percent.
The formula will look as shown in the figure below:
10. To build the formula, perform the following steps:
a. In the **Operators** section, click **(** (left parenthesis) twice.
b. Drag the expression *INCOME* into the **Formula** area*.*
c. In the **Operators** area, click **\*** (multiply by) and type -1. This is to convert the income amounts that are negative in the ERP system into positives so that the entities that will be calculated are both positive in order to give the correct result.
d. In the **Operators** area, click **)** (right parenthesis).
e. In the **Operators** area, click **--** (minus).
f. Drag the expression *COGS* into the **Formula** area*.*
g. In the **Operators** area, click **)** (right parenthesis).
h. In the operation section, click **/** (divide by).
i. Drag the expression *INCOME* into the **Formula** area.
j. In the **Operators** area, click **\*** (multiply by) and type -1. (Again to convert the income amounts into positives.)
k. In the **Operator** section, click **)** (right parenthesis).
l. Click **OK** to confirm.
11. Click **OK** and then **Close** to exit the **KPI** dialog.
Create KPI based on Measure
/onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/examples-use-of-expressions-and-kpis/create-kpi-based-on-measure
page
2024-08-02T13:54:26+02:00
# Create KPI based on Measure
In this example, we create a KPI based on measures. This time, we will create a KPI that divides a *Line Amount* measure by a *Quantity* measure.
1. In the **Report Designer** menu, click **KPIs** and then click **Manage KPIs**.
2\. In the **KPI** dialog, click the **Financial KPIs** group that you created in the previous example.
3\. Click **Change**.
4\. Click the **Add** button to add a new KPI.
5\. In the **Name** field, enter a name. In this example, we have named the KPI *Average amount*.
6\. Select the **Based on Measure** option.
7\. Click the **Edit** button.
8\. In the **KPI Editor**, take the following steps:
a\. Open the **Sales** module, and the **Receivable Invoice Lines** folder.
b\. Drag the **Line Amount** measure into the **Formula** area.
c\. Click the **Divide by** button (/).
d\. Drag the **Quantity** measure into the **Formula** area.
e\. Click **OK**.
9\. In the **KPI** dialog box, click **OK**.
10\. In the **KPI group** dialog, click **Close**.
The KPI is now created and ready to use in reports.
Create expressions: INCOME and COGS
/onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/examples-use-of-expressions-and-kpis/create-expressions-for-income-and-cogs
page
2024-08-02T13:54:26+02:00
# Create expressions: INCOME and COGS
1. In the **Report Designer** menu, click **Expressions** and then **Manage Expressions**.
Expressions are organized in groups, so you must first create a group.
2\. Click **New** to create a new expression group. (Our two expressions *INCOME* and *COGS* (Cost of Goods Sold) will be added in this group.)
3\. In the **Expression Editor** dialog, enter an expression group name. (In the example we use *Financial Expressions*.)
4\. Click the **Add** button.
5\. Replace the default text *New expression* with the name of the first expression; *INCOME*.
6\. Click the **INCOME** expression.
7\. Locate your account dimension and drag it into the **Filters** area.
8\. Click the **Lookup** button and select the required accounts that should be part of the *INCOME* expression. (In this example, accounts 200:270.)
9\. Click the **Add** button again and name this expression *COGS*.
10\. Repeat the process of dragging your account dimension into the filter area and enter the relevant account information. (In the example, account 310.)
11\. Click **OK** to confirm and exit the dialog.
12\. Click **Close** to exit the **Expressions Groups** dialog*.*
