This pattern illustrates how to create two different charts. One chart shows sales (calculated in US dollars) by country and the currency paid. The other shows sales by currency type, comparing the number of sales paid in US dollars versus the local currency. This pattern also provides a sample scenario to show how you can take common business requirements and quickly turn them into a report.
You'll notice that this pattern provides more than just an expression, it shows you the fastest way to build reports in Design Mode. To get the most out of this pattern, follow the steps in Create this pattern to learn how to build advanced reports using the latest low-code features.
Account managers at the Appian Retail company want to know how much of their year-to-date sales are generated by country and currency type. Although all sales are calculated in US dollars, some countries accept local currency as payment. For example, in Germany, sales occur in both US dollars and Euros, while sales in the United Kingdom only occur in pounds.
To better understand their customer demographic, account managers want to see two different charts:
The report will look like this:
This pattern uses data from the Appian Retail application, available for free in Appian Community Edition. To follow along with this pattern, log in to Appian Community to request the latest Appian Community Edition site.
If you do not see the Appian Retail application available in your existing Appian Community Edition, you can request a new Appian Community Edition to get the latest application contents available.
This pattern will use data from the following record types in the Appian Retail application:
Tip: In a rush? No worries, check out the AR_SalesByCountryAndCurrency interface available in the Appian Retail application to see the final result of this pattern.
This pattern contains two different charts. We'll break down the steps to create both in the following sections:
We’ll start this pattern by creating and setting up our interface. Since we’ll display two different charts in our report, we’ll use a Columns Layout to separate the two components.
To create and set up the interface:
Configure the following properties:
Property | Value |
---|---|
Name | Enter AR_SalesByCountryAndCurrencyPattern . |
Save In | Select the Rules & Constants folder. |
The interface currently looks like this:
Now we’ll start building our first chart.
The Sales by Country in US Dollars chart will display the year-to-date sales (calculated in U.S. dollars) generated by each country and the currency those sales were initially made in.
To display this information, we'll use a column chart with two different groupings: the first grouping will categorize all sales by country name, while the second grouping will categorize the sales in those countries by currency type.
The final chart will look like this:
First, we'll add a column chart to the interface and define our primary grouping and measure.
To create the column chart:
From Data Source, select RECORD TYPE and search for the Order record type.
Tip: Why are we using the Order record type?
When choosing the source of your chart, you want to select a record type that can easily reference all your related data. Since we will be using data from the Currency, Currency Rate, and Sales Region record types, the Order record type is our best starting point.
Sales by Country in US Dollars (YTD)
.The chart currently looks like this:
Next, we'll add a secondary grouping to our chart so we can see the different currencies used for payment in each country.
To display this information, we'll use the name field from the Currency record type.
To add a secondary grouping:
Use the dropdown to hover over the currencyRate > toCurrency relationship and select the name field. The field will display as currencyRate.toCurrency.name.
Tip: I see a toCurrency and a fromCurrency relationship. What's the difference between these two?
Both the toCurrency and fromCurrency relationships establish a relationship between the Currency Rate and Currency record types; however, they have different common fields in order to return different data. The toCurrency relationship uses the toCurrencyCode
field as the common field to convert dollars to the local currency. The fromCurrency relationship uses the fromCurrencyCode
field as the common field to convert the local currency into dollars.
The chart currently looks like this:
Notice that there is a secondary grouping labeled [Series 1]
. This means that there is a missing currency name for one of the values in our chart.
The reason for this is that US dollars is not listed as a currency name. Since we are sourcing data from the toCurrency relationship, the chart is only getting values for local currencies that will be converted into US dollars, so US dollars is excluded from this list.
Since we want US Dollars
to appear as a secondary grouping in our chart, we will create a new custom record field that replaces any any null currencies associated with an order with "US Dollars". Then, we'll use that new field as our secondary grouping.
We'll create our new custom record field on the Order record type.
We'll create a custom record field that evaluates in real-time so we can reference related data from the Currency record type.
Custom record fields that evaluate in real-time leverage a special set of functions called Custom Field functions. For this pattern, we'll use the Custom Field function called a!customFieldDefaultValue(), which allows us to replace null values with record fields, related record fields, or literal values.
To create the custom record field:
Copy and paste the following expression in the Expression dialog:
1
2
3
4
a!customFieldDefaultValue(
value: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate.relationships.{49d04656-5c49-4793-beab-acff7bf3ff9b}toCurrency.fields.{3355f795-4a22-413e-a1bc-5d22269e4ae4}name',
default: "US Dollars"
)
currency
.Now that we have our custom record field, let’s add it to the chart so the [Series 1]
label is replaced with US Dollars
.
To add the custom record field to the chart:
The final chart looks like this:
Next we'll create our second chart.
The Sales by Currency Type chart will display year-to-date sales by currency type, comparing sales numbers in US dollars versus the local currency.
To create this chart, we’ll group by currency name to see all local currencies. Then, we’ll add two measures to our chart: one calculating the total sales in US dollars, and another calculating the total sales in the local currency.
The final chart will look like this:
We’ll start by adding a bar chart component to our interface and defining a primary grouping and measure.
To create the bar chart:
Total Sales - US Dollars
.Sales by Currency Type (YTD)
.The chart currently looks like this:
Similar to the first chart, this chart has a missing value for "US Dollars".
However, unlike our first chart, we don't need to display "US Dollars" as a grouping. Since this chart is meant to display sales in each local currency compared to sales in US dollars, we'll add a second measure so account managers can quickly compare the sales numbers side-by-side.
As such, we can remove the [Category 1]
value from our chart.
To remove the [Category 1]
value, we’ll add another filter on the chart so that only orders that have a currency rate are returned.
We can easily filter our data by filtering directly on the relationship reference recordType!Order.relationships.currencyRate
.
You can use a record type relationship reference in the field parameter of a!queryFilter()
when the operator is set to "is null"
or "not null"
. This allows you to only return records that do or do not have any related records.
To filter out null categories:
Select Expression.
Note: You can only filter by a relationship in expression mode.
Update the expression with the following:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
operator: "between",
value: /* Year-to-Date */{ datetime(year(today()), 1, 1), now() }
)`,`
`a!queryFilter(`
`field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate',`
`operator: "not null"`
`)`
},
ignoreFiltersWithEmptyValues: true
)
The chart now only displays orders that have a currency rate of not null, which removes the [Category 1]
bar.
Right now, our chart only displays the total sales generated in US dollars. To display the amount of sales generated in each local currency, we’ll create a custom record field on the Order record type that converts sales in US dollars to sales in the local currency.
To calculate this, we'll create another custom record field that evaluates in real-time.
We'll use the a!customFieldMultiply() function to multiply the order total by the daily average currency rate. However, the averageRate
field will have a null value for US Dollars because there is no rate conversion between US Dollars and US Dollars. To prevent the function from returning a null value, we'll use the a!customFieldDefaultValue() function to replace null values with the total due in US Dollars.
To create this custom record field:
Copy and paste the following expression in the Expression dialog:
1
2
3
4
5
6
7
8
9
a!customFieldDefaultValue(
value: a!customFieldMultiply(
value: {
'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue',
'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate.fields.{10dd27ed-6d31-49be-835f-42aaf1e6fa93}averageRate'
}
),
default: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue'
)
totalInLocalCurrency
.In this last step, we'll add our new custom record field as a second measure in the chart.
To add the custom record field as a second measure:
Total Sales - Local Currency
.The final report now looks like this:
The resulting expression will look like this:
Note: To copy and paste this expression into an interface in the Appian Retail application, you must create the custom record field to replace null values and the custom record field to calculate currency conversion.
These record type references are specific to the Appian Retail application, so you will only need to replace the record field references if you're following along in a different environment.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
{
a!columnsLayout(
columns: {
a!columnLayout(
contents: {
a!columnChartField(
data: a!recordData(
recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
filters: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
operator: "between",
value: /* Year-to-Date */{ datetime(year(today()), 1, 1), now() }
)
},
ignoreFiltersWithEmptyValues: true
)
),
config: a!columnChartConfig(
primaryGrouping: a!grouping(
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{ae938f73-f61f-483d-8ca0-65d3c911abae}salesRegion.relationships.{0ea75675-bc93-491c-8cc2-3c74668c96d3}country.fields.{85846423-5eb5-46c0-ac1a-263e4f522be7}name',
alias: "name_primaryGrouping"
),
secondaryGrouping: a!grouping(
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{c7ed6acb-eba6-4a36-9558-1e30302ce33d}currency',
alias: "currency_secondaryGrouping"
),
measures: {
a!measure(
function: "SUM",
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue',
alias: "totalDue_sum_measure1"
)
},
sort: {
a!sortInfo(field: "totalDue_sum_measure1")
},
dataLimit: 100
),
label: "Sales by Country in US Dollars (YTD)",
stacking: "NORMAL",
showLegend: true,
showTooltips: true,
labelPosition: "ABOVE",
colorScheme: "CLASSIC",
height: "MEDIUM",
xAxisStyle: "STANDARD",
yAxisStyle: "STANDARD",
refreshAfter: "RECORD_ACTION"
)
}
),
a!columnLayout(
contents: {
a!barChartField(
data: a!recordData(
recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
filters: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
operator: "between",
value: /* Year-to-Date */{ datetime(year(today()), 1, 1), now() }
),
a!queryFilter(
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate',
operator: "not null"
)
},
ignoreFiltersWithEmptyValues: true
)
),
config: a!barChartConfig(
primaryGrouping: a!grouping(
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate.relationships.{49d04656-5c49-4793-beab-acff7bf3ff9b}toCurrency.fields.{3355f795-4a22-413e-a1bc-5d22269e4ae4}name',
alias: "name_primaryGrouping"
),
measures: {
a!measure(
label: "Total Sales - US Dollars",
function: "SUM",
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue',
alias: "totalDue_sum_measure1"
),
a!measure(
label: "Total Sales - Local Currency",
function: "SUM",
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{547d0dd0-730a-4c0b-8b25-e9347d5ac475}totalInLocalCurrency',
alias: "totalInLocalCurrency_sum_measure2"
)
},
sort: {
a!sortInfo(field: "totalDue_sum_measure1")
},
dataLimit: 100
),
label: "Sales by Currency Type (YTD)",
labelPosition: "ABOVE",
stacking: "NONE",
showLegend: true,
showTooltips: true,
colorScheme: "CLASSIC",
height: "MEDIUM",
xAxisStyle: "STANDARD",
yAxisStyle: "STANDARD",
refreshAfter: "RECORD_ACTION"
)
}
)
}
)
}
Sales by Country and Currency