In this video, we'll go over calculated fields for the Dashboard.
This can be helpful If your underlying data source does not include all required fields.
Just add a calculated field for additional data.
For example, we can create a new calculated field called Extended Price.
Which is the Unit Price multiplied by the quantity of items.
I already have a dashboard with a grid that displays a list of products.
We also have a Bar Chart showing profits by segment.
Let’s take a look at the underlying data source.
Go to the Data Source ribbon tab and click the Edit button.
In the Query Editor, click Run Query Builder and use the Preview Results button to see
the underlying data.
You can see that our data source contains the UnitPrice, Quantity, and Discount fields,
which can be used to calculate sales values.
Close the Preview window and the Query Builder; then, click Finish in the Query Editor.
To create a new calculated field, right-click the field list and select Add Calculated Field.
In the Expression Editor, you can create an expression that will be used to compute field
values.
Go to Fields and double-click the UnitPrice field to add it to the expression.
Multiply this field by Quantity.
Then, multiply the result to (1 – the Discount) to take into account discount values.
Note that the Expression Editor contains a list of Operators with descriptions.
You can also use predefined constant values such as True or False.
Also, you can use the Parameters group to include dynamic dashboard parameters to the
calculated field.
You can learn more about Parameters in this video.
Click OK to finish creating the calculated field.
Right-click it, select Rename, and give it a new name (“Sales”).
The Field Type option allows you to specify the type of values returned by this field.
Let’s change its type from Double to Decimal, which is more appropriate for financial calculations.
Right-click the field again and select Edit Expression.
You will see that the expression is wrapped to the ToDecimal conversion function.
If you need to, you can find math conversion functions under the Math group.
Switch to String and you will find the ToStr function that allows you to convert values
to corresponding strings.
Finally, click Cancel and drop the Sales field to the Grid columns.
Now let's look at how to use calculated fields
for displayed values.
In this case, calculated fields should be evaluated on a summary level.
For instance, our data source contains the Profit field, which can be used along with
the created Sales field to calculate a margin.
But in this case we need to divide the sum of profit values by the sum of sales values
to get a correct result.
Let’s take a look at how to do this.
Add a new calculated field.
In the Expression Editor, select Logical functions and double-click the iif function.
In the first argument, we will check whether or not the sum of sales is equal to zero.
Note that the Sum aggregate function is used to calculate the sum of sales values.
As a second argument, divide the sum of profit by the sum of sales.
Finally, specify the third argument as zero.
Click OK.
Rename the created field to Margin and drop it to the second pane of the Chart . It'll
display margins by the customer segment.
You can see that a summary type of this field cannot be changed because its data is already
aggregated on a visual level.
Not like the Profit measure which is bound to the underlying data source and allows you
to change its summary type.
Finally, click Format and change the Format type to Percent to display margins as percentages.
And that's it for this video, to learn more about our dashboards, make sure to check out
the documentation on our website.
Thanks for watching, and thank you for choosing DevExpress.