Skip to Content

Wiki source code of 01. Crosstab Reporting Guide

Last modified by Chris Weston on 06/11/2025, 14:23
Hide last authors
Chris Weston 1.1 1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
4
5 This guide has been written to outline the key steps need to create a number of reports and while the examples have been written against demonstration retail data, the methodology is easily transferable to other data sets.
6
7 When creating a report, the preview at the bottom of the screen will reflect the current layout of the report against a limited number of records. Clicking this preview will also reveal styling options to tailor the reporting output.
8
9 = Basic Reports =
10
11 When the [[Crosstab>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2c\. Crosstabs.WebHome]] functionality is initially accessed, the default **Total Type** is set to Count, where a simple record count is performed. This can be verified by clicking **Run Report** with no options set, where the report will simply display number of records:
12
13 [[image:Screenshot 2019-03-12 at 11.15.06.png]]
14
15 When creating [[Crosstab>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2c\. Crosstabs.WebHome]] reports, it is important to remember that fields that require aggregations should not be used as rows or columns and should instead be created as totals. With more than one total and only rows specified as the axis, the totals effectively become the columns.
16
17 == Report Containing a Single Row and Single Total ==
18
19 The following report displays the number of transactions by Region Name:
20
21 [[image:Screenshot 2019-03-12 at 11.20.40.png]]
22
23 This has been created by selecting a single **Row** in the **Axes** tab:
24
25 [[image:Screenshot 2019-03-12 at 11.21.29.png]]
26
27 A single total with the default Count **Total Type** was used:
28
29 [[image:Screenshot 2019-03-12 at 11.24.11.png]]
30
31 As the **Total Label** has been left blank, the total has been automatically named ‘Count()’. While this does not have any meaningful impact on this simple report, labelling totals will simplify the management of more complex reports.
32
33 (% style="box-sizing: border-box; font-family: Roboto, ~"Helvetica Neue~", Helvetica, Arial, sans-serif; font-weight: 400; line-height: 1.1; color: rgb(68, 68, 68); margin-top: 23px; margin-bottom: 11.5px; font-size: 20px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: 0.1px; text-align: start; text-indent: 0px; text-transform: none; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" %)
34 == Report Containing Multiple Rows in Sorted, Filtered Groups ==
35
36 (% style="box-sizing: border-box; margin: 0px 0px 1em; color: rgb(102, 102, 102); font-family: Roboto, ~"Helvetica Neue~", Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: 0.1px; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" %)
37 This report displays the top 5 sales within a grouping hierarchy drillable by Product Department and Product Group, notice that the number of sales for these 5 results are a subset of International Cuisine's total:
38
39 (% style="box-sizing: border-box; margin: 0px 0px 1em; color: rgb(102, 102, 102); font-family: Roboto, ~"Helvetica Neue~", Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: 0.1px; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" %)
40 [[image:1705328936083-685.png||height="297" width="453"]]
41
42 (% style="box-sizing: border-box; margin: 0px 0px 1em; color: rgb(102, 102, 102); font-family: Roboto, ~"Helvetica Neue~", Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: 0.1px; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" %)
43 The report has been created using (% style="box-sizing:border-box; font-weight:bold" %)**Rows**(%%) 'Product Department', 'Product Group' and 'Product Name', in the (% style="box-sizing:border-box; font-weight:bold" %)**Axes**(%%) tab:
44
45 (% style="box-sizing: border-box; margin: 0px 0px 1em; color: rgb(102, 102, 102); font-family: Roboto, ~"Helvetica Neue~", Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: 0.1px; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" %)
46 [[image:1705329031381-317.png||height="202" width="372"]]
47
48 (% style="box-sizing: border-box; margin: 0px 0px 1em; color: rgb(102, 102, 102); font-family: Roboto, ~"Helvetica Neue~", Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: 0.1px; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" %)
49 Row settings have been changed to **Drillable Hierarchy** with **Row Axis Labels** & **Show Row Numbers **both enabled.
50
51 (% style="box-sizing: border-box; margin: 0px 0px 1em; color: rgb(102, 102, 102); font-family: Roboto, ~"Helvetica Neue~", Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: 0.1px; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" %)
52 A single total with the default Count **Total Type** was used. Sort > **Row Sort** has been set to **Descending** and Filter > **Filter **has been set to **Top Value **with Type** **of **Cell** and Filter Value to **5**:
53
54 (% style="box-sizing: border-box; margin: 0px 0px 1em; color: rgb(102, 102, 102); font-family: Roboto, ~"Helvetica Neue~", Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: 0.1px; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" %)
55 [[image:1705329698888-622.png||height="341" width="632"]]
56
57 (% style="box-sizing: border-box; margin: 0px 0px 1em; color: rgb(102, 102, 102); font-family: Roboto, ~"Helvetica Neue~", Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: 0.1px; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" %)
58 [[image:1705329778029-670.png||height="298" width="648"]]
59
60 == Report Containing Single Column and Single Total ==
61
62 The following report displays the number of transactions for each method of payment:
63
64 [[image:Screenshot 2019-03-12 at 12.01.51.png]]
65
66 This has been created by selecting a single **Column** in the **Axes** tab:
67
68 [[image:Screenshot 2019-03-12 at 12.02.34.png]]
69
70 A single total with the default Count **Total Type** was used, as detailed in the previous example.
71
72 == Report Containing Basic Total ==
73
74 Creating reports that contain basic totals, such as Average or Sum, is a very simple task that can be created on an ad hoc basis using a number of user-friendly drop-down lists.
75
76 The following report displays the average basket value for each region:
77
78 [[image:Screenshot 2019-03-12 at 12.08.23.png]]
79
80 This has been created by selecting a single Row in the Axes tab:
81
82 [[image:Screenshot 2019-03-12 at 11.21.29.png]]
83
84 A single total with the **Total Type** set to Average has been used. Once the **Total Type** has been set to anything other than Count or Calculation, the **Measure** drop-down list becomes available, where the fields available to use in totals are displayed. If the required fields do not appear in this list, please contact the system administrator.
85
86 In this example, the **Value Format** has been set to #0.00 and a **Value Prefix** of £ has been added to denote monetary value:
87
88 [[image:Screenshot 2019-03-12 at 12.10.33.png]]
89
90 Finally, the column total has been hidden as it does not display meaningful information when returning averages in the report. This is achieved by clicking the **Cog** icon in the **Axes** tab in the Columns section and selecting **Column Totals Hidden**:
91
92 [[image:Screenshot 2019-03-12 at 14.48.36.png]]
93
94 == Report Containing Standard Calculation ==
95
96 The following report displays the average basket value for cash transactions against each region:
97
98 [[image:Standard Calc.png]]
99
100 This has been created by selecting a single **Row** in the **Axes** tab:
101
102 [[image:Single Row.png]]
103
104 As with the previously detailed report, the **Total Type** has been set to Average. However, rather than selecting a field from the **Measure** drop-down list, select Calculation.
105
106 Once Calculation has been selected, click … to open the Calculation Builder. Add the following:
107
108 **CASE WHEN MOP = 'Cash' THEN Basket_Value END**
109
110 A standard calculation will perform the statement on a row-by-row basis. For each row, the MOP (method of payment) field is scanned for the value ‘Cash’. When true, the Basket Value value is stored. Once the calculation has been run against every row, the Average **Total Type** is applied.
111
112 [[image:Average Basket Value (Cash).png]]
113
114 Finally, the row axis labels have been enabled to add headers onto the report. This is achieved by clicking the **Cog** icon in the **Axes** tab in the **Rows** section and enabling **Row Axis Labels**:
115
116 [[image:RAL.png]]
117
118 == Report Containing Cell Calculation ==
119
120 Cell Calculations behave very differently to Standard Calculations.
121
122 Rather than apply the calculation on a row-by-row basis, values from existing totals are used, permitting the use of pre-aggregated values in calculations.
123
124 A Cell Calculation can only be performed when at least one other total has been created in the report.
125
126 The following report displays the total number of transactions, the number of transactions paid via card and the percentage of transactions paid via card per region:
127
128 [[image:CC.png]]
129
130 This has been created by selecting a single Row in the Axes tab:
131
132 [[image:Single Row.png]]
133
134 Multiple totals have then been created.
135
136 The first total has the **Total Type** set to Count and has been labelled ‘No. of Transactions’:
137
138 [[image:No of Transactions.png]]
139
140 The second total has been labelled ‘No. of Transactions (Card Payments)’ with the **Total Type** set to Sum, as each row needs to be added up, and the following calculation logic applied:
141
142 **CASE WHEN MOP = 'Credit Card' || MOP = 'Debit Card' THEN 1 END**
143
144 Using two pipes (**||**) in the calculation performs the OR function, permitting more than one value to be specified. In this instance, both ‘Credit Card’ and ‘Debit Card’ values are of interest to represent all card-based transactions:
145
146 [[image:No of Transactions CP.png]]
147
148 The third total, labelled ‘% of Card Transactions’, has the **Total Type** set to calculation and the following calculation logic applied:
149
150 **'No. of Transactions (Card Payments)'/'No. of Transactions'*100**
151
152 This logic calculates the percentage value from the two previously created totals. Please note that calculations are handled sequentially when the **Run Report** tab is clicked. This means that the first two totals must run before the Cell Calculation.
153
154 This report can be taken a step further by hiding the first two totals and only displaying the percentage value. Simply enable the **Hide Row Total** option for the first two totals and enable the **Row Axis Labels** option in the Axes screen. This will display the following report, where the underlying logic for all three totals is still present, but only the Cell Calculation total value is displayed:
155
156 [[image:of Card Transactions.png]]
157
158 == Report Containing DrillThru ==
159
160 For calculations in a report, users are not able to drill through to the underlying records without translating the calculation logic into syntax that can be parsed in the Query screen.
161
162 To ensure that users navigate to the correct underlying values, the **DrillThru** value is used in calculations.
163
164 The following report displays the number of cash transactions per region:
165
166 [[image:No of Cash Trans.png]]
167
168 This has been created by selecting a single **Row** in the **Axes** tab and enabling the **Row Axis Labels** setting:
169
170 [[image:Region Name.png]]
171
172 A single total has been created, labelled ‘No. of Cash Transactions’, with the **Total Type** set to Sum, the **Value Format** set to Sum, the **Value Format** set to #,~#~#0 and the following calculation logic applied:
173
174 **CASE WHEN MOP = 'Cash' THEN 1 END**
175
176 [[image:Cash Trans Calc.png]]
177
178 When this report is run and a value is clicked, notice how the generated query does not factor in the calculation when drilling through to the underlying records:
179
180 [[image:66657.png]]
181
182 Amend the calculation to the following:
183
184 **CASE WHEN MOP = 'Cash' THEN DrillThru(1) END**
185
186 The following query is now generated when clicking the same value in the report:
187
188 [[image:13522.png]]
189
190 When the calculation logic is wrapped in the **DrillThru** value, it can then be translated to the required query syntax. In this example the ‘1’ value represents the calculation logic, instructing the total to include the rows in the sum.
191
192 == (% class="folderContents folderContents folderContents level0 level1 level2 reportNameText" id="folderContents98f49d5ddb254ca3bca86e51645f4994" style="display:block" title="public\Test Pack\01 Crosstab Reporting Guide" %)Report Referencing Specific Cells(%%) ==
193
194 The following report displays the number of transactions for each method of payment by region. It also includes a cell/Total Calculation called ‘Central and East Store Cards’ which demonstrates the row and column reference functions which include results of both the London & Midlands and East region's store card sales:
195
196 [[image:1705079377839-775.png||height="280" width="770"]]
197
198 You will notice that due to referencing specific cells from the crosstab, the context is independent of the row/column axis. This is done using the cell/Total Calculation formula below:
199
200 **Sum(rows('London'),columns('Store Card'),0)
201 +
202 Sum(rows('Midlands and East'),columns('Store Card'),0)**
203
204 With **Region Name** selected as a single **Row **with totals** **hidden selected under the row settings**, **and **MOP** as the column in the **Axes** tab:
205
206 [[image:1705078214884-282.png||height="271" width="368"]]
207
208 MOP values selected in the column axis are **'Cash' **& '**Store Card**' to filter down to just these results:
209
210 [[image:1705079255295-732.png||height="521" width="489"]]
211
212 In the **Totals** tab, (% style="-webkit-text-stroke-width:0px; background-color:#ffffff; box-sizing:border-box; color:#666666; font-family:Roboto,~"Helvetica Neue~",Helvetica,Arial,sans-serif; font-size:13px; font-style:normal; font-variant-caps:normal; font-variant-ligatures:normal; font-weight:bold; letter-spacing:0.1px; orphans:2; text-align:start; text-decoration-color:initial; text-decoration-style:initial; text-decoration-thickness:initial; text-indent:0px; text-transform:none; white-space:normal; widows:2; word-spacing:0px" %)**'No of Transactions'**(% style="-webkit-text-stroke-width:0px; background-color:#ffffff; color:#666666; display:inline !important; float:none; font-family:Roboto,~"Helvetica Neue~",Helvetica,Arial,sans-serif; font-size:13px; font-style:normal; font-variant-caps:normal; font-variant-ligatures:normal; font-weight:400; letter-spacing:0.1px; orphans:2; text-align:start; text-decoration-color:initial; text-decoration-style:initial; text-decoration-thickness:initial; text-indent:0px; text-transform:none; white-space:normal; widows:2; word-spacing:0px" %) is entered using the (% style="-webkit-text-stroke-width:0px; background-color:#ffffff; box-sizing:border-box; color:#666666; font-family:Roboto,~"Helvetica Neue~",Helvetica,Arial,sans-serif; font-size:13px; font-style:normal; font-variant-caps:normal; font-variant-ligatures:normal; font-weight:bold; letter-spacing:0.1px; orphans:2; text-align:start; text-decoration-color:initial; text-decoration-style:initial; text-decoration-thickness:initial; text-indent:0px; text-transform:none; white-space:normal; widows:2; word-spacing:0px" %)**Total Type**(% style="-webkit-text-stroke-width:0px; background-color:#ffffff; color:#666666; display:inline !important; float:none; font-family:Roboto,~"Helvetica Neue~",Helvetica,Arial,sans-serif; font-size:13px; font-style:normal; font-variant-caps:normal; font-variant-ligatures:normal; font-weight:400; letter-spacing:0.1px; orphans:2; text-align:start; text-decoration-color:initial; text-decoration-style:initial; text-decoration-thickness:initial; text-indent:0px; text-transform:none; white-space:normal; widows:2; word-spacing:0px" %) set to the default **Count** option. The(%%) cell/Total Calculation formula mentioned above can be entered in the calculation box and labelled as** 'Central and East Store Cards'**:
213
214 [[image:1705078716311-819.png||height="575" width="260"]]
215
216 == (% class="folderContents folderContents folderContents level0 level1 level2 reportNameText" id="folderContents98f49d5ddb254ca3bca86e51645f4994" style="display:block" title="public\Test Pack\01 Crosstab Reporting Guide" %)Report Referencing Min/Max Column Totals(%%) ==
217
218 The following report displays the number of transactions for each method of payment by region. It also includes two cell/Total Calculations called 'Region Minimum' & ‘Region Maximum’ which demonstrates the **ColumnMin()** and **ColumnMax()** reference functions:
219
220 [[image:1705081202113-982.png||height="276" width="846"]]
221
222 You will notice that due to referencing min & max column values from the crosstab, the context is independent of the row/column axis. This is done using the cell/Total Calculation formulas below:
223
224 **ColumnMin( 'No. of Transactions' )
225 ColumnMax( 'No. of Transactions' )**
226
227 With **Region Name** selected as a single **Row **with totals** **hidden selected under the row settings**, **and **MOP** as the column in the **Axes** tab:
228
229 [[image:1705078214884-282.png||height="271" width="368"]]
230
231 MOP values selected in the column axis are **'Cash' **& '**Store Card**' to filter down to just these results:
232
233 [[image:1705079255295-732.png||height="521" width="489"]]
234
235 In the **Totals** tab, (% style="-webkit-text-stroke-width:0px; background-color:#ffffff; box-sizing:border-box; color:#666666; font-family:Roboto,~"Helvetica Neue~",Helvetica,Arial,sans-serif; font-size:13px; font-style:normal; font-variant-caps:normal; font-variant-ligatures:normal; font-weight:bold; letter-spacing:0.1px; orphans:2; text-align:start; text-decoration-color:initial; text-decoration-style:initial; text-decoration-thickness:initial; text-indent:0px; text-transform:none; white-space:normal; widows:2; word-spacing:0px" %)**'No. of Transactions'**(% style="-webkit-text-stroke-width:0px; background-color:#ffffff; color:#666666; display:inline !important; float:none; font-family:Roboto,~"Helvetica Neue~",Helvetica,Arial,sans-serif; font-size:13px; font-style:normal; font-variant-caps:normal; font-variant-ligatures:normal; font-weight:400; letter-spacing:0.1px; orphans:2; text-align:start; text-decoration-color:initial; text-decoration-style:initial; text-decoration-thickness:initial; text-indent:0px; text-transform:none; white-space:normal; widows:2; word-spacing:0px" %) is entered using the (% style="-webkit-text-stroke-width:0px; background-color:#ffffff; box-sizing:border-box; color:#666666; font-family:Roboto,~"Helvetica Neue~",Helvetica,Arial,sans-serif; font-size:13px; font-style:normal; font-variant-caps:normal; font-variant-ligatures:normal; font-weight:bold; letter-spacing:0.1px; orphans:2; text-align:start; text-decoration-color:initial; text-decoration-style:initial; text-decoration-thickness:initial; text-indent:0px; text-transform:none; white-space:normal; widows:2; word-spacing:0px" %)**Total Type**(% style="-webkit-text-stroke-width:0px; background-color:#ffffff; color:#666666; display:inline !important; float:none; font-family:Roboto,~"Helvetica Neue~",Helvetica,Arial,sans-serif; font-size:13px; font-style:normal; font-variant-caps:normal; font-variant-ligatures:normal; font-weight:400; letter-spacing:0.1px; orphans:2; text-align:start; text-decoration-color:initial; text-decoration-style:initial; text-decoration-thickness:initial; text-indent:0px; text-transform:none; white-space:normal; widows:2; word-spacing:0px" %) set to the default **Count** option. The(%%) cell/Total Calculation formulas mentioned above can be entered in the calculation box and labelled as** **'Region Minimum' & ‘Region Maximum**' **respectively:
236
237 [[image:1705081698817-110.png||height="722" width="331"]]
238
239 = Variance Reports =
240
241 Variance reports are a common reporting requirement and are often used for comparing period-to-period or year-to-year sales.
242
243 == Report that Compares Sales ==
244
245 The following report compares the variance between the regional sales for the previous year compared to the current year, then changes the text colour based on an increase or decrease in sales. The Current Year figure is red if the total is less than the previous year, and green if the total is more than the previous year:
246
247 [[image:Conditional formatting comparing sales.png||height="213" width="312"]]
248
249 This has been created by selecting a single **Row** in the **Axes** tab:
250
251 [[image:Single Row.png]]
252
253 Multiple totals were then created.
254
255 The first total has been labelled ‘Previous Year Sales’ with the **Total Type** set to Sum, the **Value Format** set to #,~#~#0.00, a **Value Prefix** of £, the **Hide Row Total **and **Hide Total in Table **options enabled and the following calculation logic applied:
256
257 **CASE WHEN ToYear(Transaction_Date) = ToYear(TODAY) -1 THEN Line_Price END**
258
259 For each row, this will extract the year from the Transaction Date field and determine if it matches the current year when the report is run, denoted by ‘TODAY’, minus one year. When matching, the Line Price is added to the total.
260
261 [[image:PYS.1.png||height="481" width="941"]]
262
263 Clicking the **Copy** icon for a total will create an exact copy below. This is especially useful when the display options, such as **Value Format**, have already been set.
264
265 The second total has been labelled ‘Current Year Sales’ with the **Total Type** set to Sum, the **Value Format** set to #,~#~#0.00, a **Value Prefix** of £ and the following calculation logic applied:
266
267 **CASE WHEN ToYear(Transaction_Date) = ToYear(TODAY) THEN Line_Price END**
268
269 This uses the same methodology as the previous total, but does not hide the output and only adds the Line Price value to the sum when the year part of the Transaction Date field matches the current year when the report is run.
270
271 [[image:CYS.1.png||height="511" width="939"]]
272
273 The third total is a Cell Calculation labelled ‘Variance’ with the **Total Type** set to Calculation, the **Value Format** set to #,~#~#0, a **Value Suffix** of %, the **Hide Row Total **and **Hide Total in Table **options enabled and the following calculation logic applied:
274
275 **('Current Year Sales'/'Previous Year Sales')*100**
276
277 This divides the second total value from the first and multiplies the output by one hundred to derive the variance percentage value.
278
279 [[image:Variance Total.1.png||height="601" width="941"]]
280
281 This total will not be displayed in the final output. Instead, the values will be used to drive the conditional formatting for the 'Current Year Sales' total.
282
283 In the **Conditional Formatting** tab of the 'Current Year Sales' total, the **Source Value** drop-down list controls the total value that will be used. By default, this will be set to the current total.
284
285 In this example, the 'Variance' total has been selected from the **Source Value** drop-down list for two conditions. The first condition will change the the text colour to red when the total is less than 100, while the second condition will change the text colour to green when the total is greater than or equal to 100.
286
287 Finally, the **Scope** drop-down list controls which portion of the report will be styled based on when the conditions are met. In this example, **Row Totals Displayed** has been selected.
288
289 [[image:Conditional Formatting using different source value.png||height="642" width="942"]]
290
291 = Row Total Reports =
292
293 For reports that require a separate total for each row, Row Totals are used.
294
295 In most cases, reports that use Row Totals will use placeholders as axis to structure the output rather than actual fields from the Index. Placeholders act as row labels that do not perform any filtering, with this instead handled at calculation level.
296
297 == Report Containing Row Totals ==
298
299 The following report displays a summary of key metrics across the different regions:
300
301 [[image:RT1.png||height="98" width="508"]]
302
303 This has been created by selecting the Saved entry for the **Row** and a single entry for the **Column** in the **Axes** tab:
304
305 [[image:RT2.png]]
306
307 The Saved entry, located at the bottom of the list, allows placeholder values to be added to the report. Click the … button for the saved entry to display the Values popup. Enter the name of the required placeholder in the textbox and click **Add** to save the entry. Click **Apply** to complete the process.
308
309 [[image:RT3.png||height="179" width="369"]]
310
311 Multiple row totals were then created.
312
313 With the **Total Type** drop-down list set to Row Total, the **Measure** drop-down list will automatically select Row Total Calculations. The drop-down list below can then be used to select each row that will be used in the report.
314
315 [[image:RT4.png||height="198" width="258"]]
316
317 With the rows selected, a separate calculation can now be written against each entry. Click the … icon for each row to specify the logic that will be used:
318
319 [[image:RT Basket ID.png]]
320
321 The first total for the ‘No. of Transactions’ row simply uses **1** as the calculation logic. This provides a row count, which represents the total number of transactions in the Index.
322
323 The second total for ‘No. of Returns’ uses the following logic:
324
325 **CASE WHEN Has_Returns = '1' THEN 1 END**
326
327 For every row, the Has_Returns field is scanned for the value ‘1’. When true, the row is added to the row count that will represent the number of returned items in the Index.
328
329 Finally, third total for ‘Average Sale Price’ uses the following logic:
330
331 **Average( Line_Price )**
332
333 This simply averages the Line_Price value for every row. More mathematical functions can be found in the Calculation Builder **Functions** drop-down list.
334
335 As the first two totals are displaying row counts and the third total is displaying an average of monetary value, the **Value Format** will need to be different. When using row totals, this can be handled separately for each total via the **Style** icon: [[image:Style Icon.png||height="16" width="27"]]
336
337 For the third total, the **Value Format** and **Prefix** can then be set accordingly:
338
339 [[image:RT5.png||height="241" width="282"]]
340
341 Finally, when creating row total reports, it is important to consider the validity of row and column totals. In this example, there are two row counts and an average monetary value – summing these values together will not produce any meaningful output. In this report, they will be hidden.
342
343 This is achieved by clicking the **Cog** icon in the **Axes** tab in the **Rows** section and clicking **Row Totals Hidden**:
344
345 [[image:RT6.png]]
346
347 The same can be applied to the column totals by clicking **Column Totals Hidden**:
348
349 [[image:RT7.png]]
350
351 If the logic used does dictate that row or column totals are required, ensure that the **Calculate Row Total From Sum of Displayed Values** is enabled in the **Totals** tab. This will sum the on-screen values rather than taking the count from the unfiltered underlying Index.
352
353 = Row Calculation Reports =
354
355 Not to be confused with row totals, row calculations allow blank rows to be repurposed to contain calculations between other rows in a report.
356
357 == Report Containing a Row Calculation ==
358
359 The following report displays the number of transactions for each method of payment. It also includes a row calculation called ‘Bank Card Sales’ which includes data from both debit card and credit card sales:
360
361 [[image:9a.png||height="243" width="180"]]
362
363 This has been created by selecting a single **Row** in the **Axes** tab:
364
365 [[image:09b.png]]
366
367 To add the new value to the report, click the … icon for the field and navigate to the **Groups** tab before creating a group for each individual value. In addition, create a blank group titled ‘Bank Card Sales’:
368
369 [[image:09c.png]]
370
371 The default Crosstab behaviour is to hide rows that do not have any attached data. To override this behaviour and ensure that the empty row is displayed, enable the **Lock Values On Axis** option.
372
373 In the **Totals** tab, a new **Row Calculations** tab will now become available that allows a calculation to be written against the empty row:
374
375 [[image:09d.png]]
376
377 The following logic has been applied:
378
379 **ROW('Credit Card')+ROW('Debit Card')**
380
381 This will simply sum the output from both the ‘Credit Card’ and ‘Debit Card’ rows.
382
383 = Formfield Calculation Reports =
384
385 Using filters is an effective way to reduce the record count to a cohort of interest that not only focuses the output, but also optimises the loading time by reducing the number of records that are used in calculations.
386
387 Rather than filtering the data for the entire report, it is also possible to use filters as referenceable parameters.
388
389 == Report Containing a Formfield Calculation ==
390
391 The following report displays the month-to-date (MTD), year-to-date (YTD) and last year’s month-to-date sales for each region:
392
393 [[image:10a.png]]
394
395 Despite the filters being set to June 2014, the use of formfield calculations results in the ‘YTD’ and ‘MTD Last Year’ totals not being filtered to only June 2014 data.
396
397 To produce this report, two filters named Transaction Year and Transaction Month are required. Importantly, the **Ignore** option must be enabled for each filter under the **Advanced Options**. This stops the filters having any effect on the report unless specified in a calculation.
398
399 The report has been created by selecting a single **Row** in the **Axes** tab:
400
401 [[image:Single Row.png]]
402
403 Multiple totals have then been created.
404
405 The first total has the **Total Type** set to Sum, **Measure** set to Calculation and has been labelled ‘MTD’. The following calculation logic has been applied:
406
407 **CASE WHEN ToMonth( Transaction_Month ) = ToMonth( FORMFIELD^"Transaction Month" ) && ToYear( Transaction_Year ) = ToYear( FORMFIELD^"Transaction Year" ) THEN Line_Price END**
408
409 In this statement, to define the month-to-date sales, every row is scanned to determine if the Transaction Month value matches Transaction Month filter and if the Transaction Year value matches the Transaction Year filter. These values are then summed and output into the report.
410
411 The **FORMFILED^** syntax is used to reference filters in the calculation builder.
412
413 **When referencing a filter using the FORMFIELD^ syntax, double quotes must be used.**
414
415 **[[image:10b.png]]**
416
417 The second total has the **Total Type** set to Sum, **Measure** set to Calculation and has been labelled ‘YTD’. The following calculation logic has been applied:
418 \\**CASE WHEN ToMonth( Transaction_Month ) BETWEEN 1 AND ToMonth( FORMFIELD^"Transaction Month" ) && ToYear( Transaction_Year ) = ToYear( FORMFIELD^"Transaction Year" ) THEN Line_Price END**
419
420 In this statement, to define the year-to-date sales, every row is scanned to determine if the Transaction Month field is between January and the Transaction Month set in the Filter and if the Transaction Year field match.
421
422 The **ToMonth** function returns a numeric value (January = 1, February = 2 etc.) and the **BETWEEN** statement uses 1 (January) as the starting point of the range. These values are then summed and output into the report.
423
424 [[image:10c.png]]
425
426 The third total has the **Total Type** set to Sum, **Measure** set to Calculation and has been labelled ‘MTD Last Year’. The following calculation logic has been applied:
427
428 **CASE WHEN ToMonth( Transaction_Month ) = ToMonth( FORMFIELD^"Transaction Month" )
429 && ToYear( Transaction_Year ) = ToYear( FORMFIELD^"Transaction Year" ) - 1
430 THEN Line_Price END**
431
432 This statement uses the same logic as the first total, but importantly includes **-1** after the **ToYear** syntax. This instructs the system to take the output and then display the results for the previous year.
433
434 [[image:10d.png]]
435
436 == Variance Report Containing a Formfield Calculation ==
437
438 The following report compares the average profit for a filtered date range against the whole dataset for each region and method of payment. The variance is then dynamically output to denote whether the filtered range value is greater than or less than the value across the entire Index:
439
440 [[image:11a.png]]
441
442 To produce this report, a date range filter named Transaction Date is required. Importantly, the **Ignore** option must be enabled for this filter under the **Advanced Options**. This allows end-users to select a custom range when running the report that only impacts the ‘Filter Range Average Profit’ total. A second droplist multi filter for MOP has been added to further focus the output.
443
444 The report has been created by selecting a single **Row** and single **Column** in the **Axes** tab, with **Row Totals Hidden** and **Column Totals Hidden** selected from the corresponding **Cog** menu:
445
446 [[image:11b.png]]
447
448 Multiple totals have then been created.
449
450 The first total has the **Total Type** set to Average, **Measure** set to Calculation and has been labelled ‘Filter Range Average Profit’. The following calculation logic has been applied:
451
452 **CASE WHEN Transaction_Date BETWEEN FORMFIELD^"Transaction Date"^LOW AND FORMFIELD^"Transaction Date"^HIGH THEN Line_Price - Cost_Price END**
453
454 In this statement, the profit is derived for every transaction where the transaction date falls between the date range set in the filter panel. The profit is calculated by subtracting the cost price (the amount the product cost the business) from the line price (how much the product was sold for).
455
456 The **FORMFIELD^"Transaction Date"^LOW** logic refers to the from value in the range filter and the **FORMFIELD^"Transaction Date"^HIGH’** logic refers to the to value in the range filter.
457
458 [[image:11c.png]]
459
460 The second total has the **Total Type** set to average, **Measure** set to Calculation and has been labelled ‘Total Average Profit’. The following calculation logic has been applied:
461
462 **Line_Price-Cost_Price**
463
464 This calculates the profit value, as detailed for the previous calculation, for every row and averages the output. This total is not impacted by the Transaction Date filter, and instead provides a benchmark figure for every row of data that the filtered total can be compared against.
465
466 [[image:11d.png]]
467
468 The third total has the **Total Type** set to Calculation and has been labelled ‘Variance’. The following calculation logic has been applied:
469
470 **CASE WHEN 'Filter Range Average Profit' < 'Total Average Profit' THEN 1 ELSE 0 END**
471
472 This cell calculation simply outputs a 1 when the first total is less than the second total, and a 0 when the opposite is true. These values provide the logic for the conditional formatting, and enabling the **Suppress Total in Table** option will hide the values:
473
474 [[image:11e.png]]
475
476 Save the current report and import the following images:
477
478 [[image:GreenUP.png]] [[image:RedDOWN.png]]
479
480 In the Conditional Formatting tab for the third total, the following conditions can now be created:
481
482 [[image:11f.png]]
483
484 The first condition has been set to **Equal to 1** and the **Scope** set to Totals. The **Edit Style** button can then be used to modify the output, where the **Image** option can be used to select the ‘Red Down Arrow’ image.
485
486 The second condition has been set to **Equal to 0** and the **Scope** set to Totals. The **Edit Style** button can then be used to modify the output, where the **Image** option can be used to select the ‘Green Up Arrow’ image.
487
488 When end-users view the report and change the Transaction Date filter, the report will now dynamically output the corresponding image when the filtered value is greater than or less than the total average value.
489
490 = Saved Query Reports =
491
492 Utilising the flexibility of saved queries in Crosstab reports provides a number of ways to output cohorts of interest to not only optimise the rendering time, but to maintain the integrity of a published report.
493
494 == Report Using a Saved Query as a Hidden Filter ==
495
496 The following report displays the number of transactions that have been processed for the current week for each region and method of payment:
497
498 [[image:12a.png]]
499
500 Rather than using a filter or the query bar the reduce the records, a hidden saved query has been used to drive the report at axis level. Due to the sequential nature of how a Crosstab report is run, the record count is reduced before any calculations are run, significantly optimising the render process. Furthermore, using queries in this manner maintains the integrity of the report as the syntax cannot be viewed or modified.
501
502 The dynamic saved query used in the report will return all rows from the current week and uses the following syntax:
503
504 **+Transaction_Date:["FIRST_DAY_OF_WEEK" TO "TODAY"]**
505 \\The report has been created by nesting a **Row** below the Saved option and a single **Column** in the **Axes** tab:
506
507 [[image:12b.png]]
508
509 For the Saved entry, clicking the … button allows the saved query to be selected by clicking the **Select Reports** button:
510
511 [[image:12c.png]]
512
513 To hide the saved query from the reporting output, click the **Visibility** icon in the **Axes** tab:
514
515 [[image:Screenshot 2019-04-01 at 12.39.26.png||height="36" width="157"]]
516
517 This does not remove the saved query or stop the data from being filtered, it simply hides the heading when the report is run.
518
519 == Report Using a Saved Query as Axes ==
520
521 Using a saved query in a Crosstab Axis will filter the data and display as the name of the saved query. To change this default behaviour and display the axis label as the description field rather than the name field, you can apply the **Use** **Description** Checkbox.
522
523 [[image:Screenshot 2022-06-10 at 16.14.05.png||height="406" width="396"]]
524
525 When using saved queries with dynamic dates, you can also specify dynamic syntax to display in the row/column axes.
526
527 To do this, use the following structure **{<point in time>, <format of date>}**. Any text outside the { } will be hard coded. For example, if today was Friday 10/12/2021 then **W/C {FIRST_DAY_OF_WEEK, dd/MM/yyyy} **would return the axis label as **W/C 10/12/2021**.
528
529 If required, additional logic can be added to the description to control the dynamic output. In this example, instead of using { }, the dynamic output is defined using the following structure:
530
531 **'{<point in time>, <format of output>}' **- Note the single quotes around the statement compared to the simple structure shown above.
532
533 For example, to have a different output for February, you could use the following code:
534
535 **CASE WHEN CurrentMonth = 02 THEN '{LAST_DAY_OF_MONTH-1YEAR-1MONTH,MMM YY}' ELSE '{LAST_DAY_OF_MONTH-1MONTH,MMM YY}' END**
536
537 === Reporting Example ===
538
539 The following six-month rolling report displays a count of transactions grouped by month. Rather than selecting static month values, saved queries with dynamic descriptions have been used to drive the report. This means that when the report is opened next month, the month values will update automatically:
540
541 [[image:13a.png]]
542
543 First, six saved queries were created. Each month value is driven by a saved query with the following syntax:
544
545 **Transaction_Date:["FIRST_DAY_OF_MONTH-6MONTHS" TO "LAST_DAY_OF_MONTH-6MONTHS"]**
546
547 Simply change the ‘-6MONTHS’ values to ‘-5MONTHS’, ‘-4MONTHS’ etc. to create a number of queries looking at a different month:
548
549 [[image:13b.png]]
550
551 In the above examples, simple dynamic descriptions have also been added for every query.
552
553 The report has been created using a single **Row** and a single **Column**, with the Saved entry selected, in the **Axes** tab:
554
555 [[image:13c.png]]
556
557 Using the Saved column entry, a number of saved queries can be selected.
558
559 These are loaded into the report using the **…** icon for the Saved entry:
560
561 [[image:13d.png]]
562
563 Ensure the **Use Description** option has been enabled. The report will then refer to the saved query’s dynamic description rather than the static query name. If there is a blank description, the name will be used instead.
564
565 With the **Total Type** set to the default Count option, the report will simply count the records that are returned from each query. The dynamic query syntax ensures that the report will automatically update to reflect a rolling six-month period, negating the requirement to update reports every month.
566
567 Dynamic descriptions have the ability to use **CASE **or **IF **statements to select different dynamic dates depending on a condition. In this example, we may want to hold off shifting the months forward until working day 2 to allow for more time to review the previous month in full, against the one six months prior.
568
569 (((
570 (((
571 (% class="small" %)CASE
572 )))
573 )))
574
575 (((
576 (((
577 (% class="small" %)WHEN TODAY <= FIRST_WORKING_DAY_OF_MONTH THEN
578 )))
579 )))
580
581 (((
582 (((
583 (% class="small" %)FORMAT(LAST_DAY_OF_MONTH-2MONTH,'MMM YY')
584 )))
585 )))
586
587 (((
588 (((
589 (% class="small" %)ELSE
590 )))
591 )))
592
593 (((
594 (((
595 (% class="small" %)FORMAT(LAST_DAY_OF_MONTH-1MONTH,'MMM YY')
596 )))
597 )))
598
599 (((
600 (% class="small" %)END
601 )))
602
603 Use the above **CASE **statement to replace the description within the **-1 Months** query to enable this behaviour. Do the same, but adjusting for the number of months, for the subsequent queries.
604
605 Note: you will need to add some additional days to the (% class="small" %)FIRST_WORKING_DAY_OF_MONTH to simulate the first working day before and after your current day of the month. For example, on the 15 Jan 2024 (%%)you would use (% class="small" %)FIRST_WORKING_DAY_OF_MONTH+13DAYS & FIRST_WORKING_DAY_OF_MONTH+14DAYS to test both conditions.
606
607 (((
608 Ensure that when using **CASE **or **IF **statements, the option to (% id="xabDescCalc" style="display:inline-block" %)**Evaluate calculation**(% style="display:inline-block" %) is** **enabled.
609 )))
610
611 (((
612 [[image:1705342472236-141.png||height="500" width="579"]]
613 )))
614
615 == Report Using a Saved Query with Field Comparisons ==
616
617 Saved queries can make use of the same functions available when writing query syntax, the following example compares values between two columns.
618
619 Save the following queries run against the EPOS Index:
620
621 (% cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:none; width:1024px" %)
622 (% style="height:1.0pt" %)|(% nowrap="nowrap" style="background-color:#f79646; background:#f79646; border-style:solid; border-width:1.0pt; width:106.1pt" width="141" %)(((
623 (% style="margin-bottom:.0001pt; padding:0cm 5.4pt 0cm 5.4pt" %)
624 (% style="background-color:#f79646; color:white; height:1pt; line-height:115%" %)**Name**
625 )))|(% nowrap="nowrap" style="background-color:#f79646; background:#f79646; border-left:none; border-style:solid; border-width:1.0pt; width:354.35pt" width="472" %)(((
626 (% style="margin-bottom:.0001pt; padding:0cm 5.4pt 0cm 5.4pt" %)
627 (% style="background-color:#f79646; color:white; height:1pt; line-height:115%" %)**Syntax**
628 )))
629 (% style="height:1.0pt" %)|(% nowrap="nowrap" style="border-style:solid; border-top:none; border-width:1.0pt; width:106.1pt" width="141" %)(((
630 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
631 Single item sales non-adjusted
632 )))|(% nowrap="nowrap" style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:354.35pt" width="472" %)(((
633 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
634 (% style="height:1pt; line-height:115%" %)+Basket_Value=Line_Price +Discount_Amount:"0" +Quantity:"1"
635 )))
636 (% style="height:1.0pt" %)|(% nowrap="nowrap" style="border-style:solid; border-top:none; border-width:1.0pt; width:106.1pt" width="141" %)(((
637 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
638 Single item sales adjusted
639 )))|(% nowrap="nowrap" style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:354.35pt" width="472" %)(((
640 (% style="margin-bottom:10.0pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
641 +Basket_Value<>Line_Price +Discount_Amount:"0" +Quantity:"1"
642 )))
643 (% style="height:1.0pt" %)|(% nowrap="nowrap" style="border-style:solid; border-top:none; border-width:1.0pt; width:106.1pt" width="141" %)(((
644 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
645 Sales with a discount higher than the basket value
646 )))|(% nowrap="nowrap" style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:354.35pt" width="472" %)(((
647 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
648 (% style="height:1pt; line-height:115%" %)+Basket_Value<Discount_Amount
649 )))
650 (% style="height:1.0pt" %)|(% nowrap="nowrap" style="border-style:solid; border-top:none; border-width:1.0pt; width:106.1pt" width="141" %)(((
651 (% style="margin-bottom:.0001pt; padding:0cm 5.4pt 0cm 5.4pt" %)
652 (you will notice the same results either way around)
653 )))|(% nowrap="nowrap" style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:354.35pt" width="472" %)(((
654 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
655 +(% style="height:1pt; line-height:115%" %)Discount_Amount(%%)>(% style="height:1pt; line-height:115%" %)Basket_Value
656 )))
657 (% style="height:19.4pt" %)|(% nowrap="nowrap" style="border-style:solid; border-top:none; border-width:1.0pt; width:106.1pt" width="141" %)(((
658 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
659 Products sold at a profit or at cost
660 )))|(% nowrap="nowrap" style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:354.35pt" width="472" %)(((
661 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
662 +Cost_Price<=Line_Price
663 )))
664 (% style="height:1.0pt" %)|(% nowrap="nowrap" style="border-style:solid; border-top:none; border-width:1.0pt; width:106.1pt" width="141" %)(((
665 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
666 Products sold at a loss or at cost
667 )))|(% nowrap="nowrap" style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:354.35pt" width="472" %)(((
668 (% style="margin-bottom:.0001pt; text-align:left; padding:0cm 5.4pt 0cm 5.4pt" %)
669 +Cost_Price>=Line_Price
670 )))
671
672 === Single item sales ===
673
674 This report displays the number of sales of a single product per transaction for each region. Using our saved field comparison queries, we are able to show the difference between adjusted and non-adjusted basket values:
675
676 [[image:1704994800651-359.png||height="246" width="627"]]
677
678 The report has been created using a single **Row** 'Region Name' and a single **Column**, with the Saved entry selected, in the **Axes** tab:
679
680 [[image:1704995748588-712.png||height="279" width="381"]]
681
682 With the **Total Type** set to the default Count option, the report will simply count the records that are returned from each query.
683
684 === Sales with a discount higher than the basket value ===
685
686 Using our saved field comparison queries, this report displays the number of sales where the discount value is higher than the basket value for each region and by payment method:
687
688 [[image:1704998019853-334.png||height="259" width="556"]]
689
690
691 The report has been created using **Rows** with the Saved entry selected (but hidden) and 'Region Name' & 'MOP', in the **Axes** tab:
692
693 [[image:1704997451649-772.png||height="301" width="372"]]
694
695
696 With **'No of Transactions'** using the **Total Type** set to the default Count option, the report will simply count the records that are returned from each query. 'Basket Value' & 'Discount Amount' have been selected using the **Total Type** set to **Sum** and the corresponding measure selected.
697
698 === Products sold at a profit or Loss ===
699
700 Using our saved field comparison queries, this report displays the number of sales where products have been sold at profit vs. loss with a grouping hierarchy drillable by Department and Group:
701
702 [[image:1704999461853-623.png||height="623" width="628"]]
703
704 The report has been created using **Rows** 'Product Department', 'Product Group' and 'Product Name' and a single **Column**, with the Saved entry selected, in the **Axes** tab:
705
706 [[image:1704999784237-589.png||height="344" width="372"]]
707
708 With **'No of Transactions'** using the **Total Type** set to the default Count option, the report will simply count the records that are returned from each query. 'Total Average Profit' has been selected, same as previously, using the **Total Type** set to **Average** and **Line_Price-Cost_Price** selected as the calculation.