> For the complete documentation index, see [llms.txt](https://documentation.connexica.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://documentation.connexica.com/reporting-guides/crosstab-reporting-guide.md).

# 02. Crosstab Reporting Guide

## Crosstab Reporting Guide

This guide outlines the key steps needed to create a number of reports. Although the examples use demonstration retail data, the methodology is easily transferable to other data sets.

When creating a report, the preview at the bottom of the screen reflects the current report layout against a limited number of records. Clicking this preview also reveals styling options to tailor the reporting output.

## Basic Reports

When \[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 simply displays the number of records:

!\[Screenshot 2019-03-12 at 11.15.06.png]\(image:Screenshot 2019-03-12 at 11.15.06.png)

When creating \[Crosstab]\(doc:Technical Documentation.CXAIR.User Guide.02. Reporting.2c. Crosstabs.WebHome) reports, remember that fields requiring aggregations should not be used as rows or columns. Instead, create them as totals. With more than one total and only rows specified as the axis, the totals effectively become the columns.

### Report Containing a Single Row and Single Total

The following report displays the number of transactions by Region Name:

!\[Screenshot 2019-03-12 at 11.20.40.png]\(image:Screenshot 2019-03-12 at 11.20.40.png)

This has been created by selecting a single **Row** in the **Axes** tab:

!\[Screenshot 2019-03-12 at 11.21.29.png]\(image:Screenshot 2019-03-12 at 11.21.29.png)

A single total with the default Count **Total Type** was used:

!\[Screenshot 2019-03-12 at 11.24.11.png]\(image:Screenshot 2019-03-12 at 11.24.11.png)

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.

### Report Containing Multiple Rows in Sorted, Filtered Groups

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:

!\[1705328936083-685.png]\(image:1705328936083-685.png||height="297" width="453")

The report has been created using **Rows** `Product Department`, `Product Group`, and `Product Name` in the **Axes** tab:

!\[1705329031381-317.png]\(image:1705329031381-317.png||height="202" width="372")

Row settings have been changed to **Drillable Hierarchy**, with **Row Axis Labels** and **Show Row Numbers** both enabled.

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** set to **Cell** and **Filter Value** set to **5**:

!\[1705329698888-622.png]\(image:1705329698888-622.png||height="341" width="632")

!\[1705329778029-670.png]\(image:1705329778029-670.png||height="298" width="648")

### Report Containing Single Column and Single Total

The following report displays the number of transactions for each method of payment:

!\[Screenshot 2019-03-12 at 12.01.51.png]\(image:Screenshot 2019-03-12 at 12.01.51.png)

This has been created by selecting a single **Column** in the **Axes** tab:

!\[Screenshot 2019-03-12 at 12.02.34.png]\(image:Screenshot 2019-03-12 at 12.02.34.png)

A single total with the default Count **Total Type** was used, as detailed in the previous example.

### Report Containing Basic Total

Creating reports that contain basic totals, such as Average or Sum, is a simple task that can be created on an ad hoc basis using a number of user-friendly drop-down lists.

The following report displays the average basket value for each region:

!\[Screenshot 2019-03-12 at 12.08.23.png]\(image:Screenshot 2019-03-12 at 12.08.23.png)

This has been created by selecting a single Row in the Axes tab:

!\[Screenshot 2019-03-12 at 11.21.29.png]\(image:Screenshot 2019-03-12 at 11.21.29.png)

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, contact the system administrator.

In this example, the **Value Format** has been set to `#0.00` and a **Value Prefix** of `£` has been added to denote monetary value:

!\[Screenshot 2019-03-12 at 12.10.33.png]\(image:Screenshot 2019-03-12 at 12.10.33.png)

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**:

!\[Screenshot 2019-03-12 at 14.48.36.png]\(image:Screenshot 2019-03-12 at 14.48.36.png)

### Report Containing Standard Calculation

The following report displays the average basket value for cash transactions against each region:

!\[Standard Calc.png]\(image:Standard Calc.png)

This has been created by selecting a single **Row** in the **Axes** tab:

!\[Single Row\.png]\(image:Single Row\.png)

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.

Once Calculation has been selected, click `…` to open the Calculation Builder. Add the following:

```
CASE WHEN MOP = 'Cash' THEN Basket_Value END
```

A standard calculation performs 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.

!\[Average Basket Value (Cash).png]\(image:Average Basket Value (Cash).png)

Finally, enable row axis labels to add headers to the report. This is achieved by clicking the **Cog** icon in the **Axes** tab in the **Rows** section and enabling **Row Axis Labels**:

### Report Containing Cell Calculation

Cell Calculations behave very differently to Standard Calculations.

Rather than applying the calculation on a row-by-row basis, values from existing totals are used, permitting the use of pre-aggregated values in calculations.

A Cell Calculation can only be performed when at least one other total has been created in the report.

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:

This has been created by selecting a single Row in the Axes tab:

!\[Single Row\.png]\(image:Single Row\.png)

Multiple totals have then been created.

The first total has the **Total Type** set to Count and has been labelled `No. of Transactions`:

!\[No of Transactions.png]\(image:No of Transactions.png)

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:

```
CASE WHEN MOP = 'Credit Card' || MOP = 'Debit Card' THEN 1 END
```

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:

!\[No of Transactions CP.png]\(image:No of Transactions CP.png)

The third total, labelled `% of Card Transactions`, has the **Total Type** set to Calculation and the following calculation logic applied:

```
'No. of Transactions (Card Payments)'/'No. of Transactions'*100
```

This logic calculates the percentage value from the two previously created totals. Calculations are handled sequentially when the **Run Report** tab is clicked. This means that the first two totals must run before the Cell Calculation.

This report can be taken a step further by hiding the first two totals and only displaying the percentage value. Enable the **Hide Row Total** option for the first two totals and enable the **Row Axis Labels** option in the Axes screen. This displays the following report, where the underlying logic for all three totals is still present, but only the Cell Calculation total value is displayed:

!\[of Card Transactions.png]\(image:of Card Transactions.png)

### Report Containing DrillThru

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.

To ensure that users navigate to the correct underlying values, the **DrillThru** value is used in calculations.

The following report displays the number of cash transactions per region:

!\[No of Cash Trans.png]\(image:No of Cash Trans.png)

This has been created by selecting a single **Row** in the **Axes** tab and enabling the **Row Axis Labels** setting:

!\[Region Name.png]\(image:Region Name.png)

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:

```
CASE WHEN MOP = 'Cash' THEN 1 END
```

!\[Cash Trans Calc.png]\(image:Cash Trans Calc.png)

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:

Amend the calculation to the following:

```
CASE WHEN MOP = 'Cash' THEN DrillThru(1) END
```

The following query is now generated when clicking the same value in the report:

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.

### Report Referencing Specific Cells

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 that include results of both the London & Midlands and East region's store card sales:

!\[1705079377839-775.png]\(image:1705079377839-775.png||height="280" width="770")

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:

```
Sum(rows('London'),columns('Store Card'),0)
+
Sum(rows('Midlands and East'),columns('Store Card'),0)
```

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:

!\[1705078214884-282.png]\(image:1705078214884-282.png||height="271" width="368")

MOP values selected in the column axis are `'Cash'` and `'Store Card'` to filter down to just these results:

!\[1705079255295-732.png]\(image:1705079255295-732.png||height="521" width="489")

In the **Totals** tab, **`No of Transactions`** is entered using the **Total Type** 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`**:

!\[1705078716311-819.png]\(image:1705078716311-819.png||height="575" width="260")

### Report Referencing Min/Max Column Totals

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` and `Region Maximum`, which demonstrate the **ColumnMin()** and **ColumnMax()** reference functions:

!\[1705081202113-982.png]\(image:1705081202113-982.png||height="276" width="846")

You will notice that due to referencing min and 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:

```
ColumnMin( 'No. of Transactions' )
ColumnMax( 'No. of Transactions' )
```

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:

!\[1705078214884-282.png]\(image:1705078214884-282.png||height="271" width="368")

MOP values selected in the column axis are `'Cash'` and `'Store Card'` to filter down to just these results:

!\[1705079255295-732.png]\(image:1705079255295-732.png||height="521" width="489")

In the **Totals** tab, **`No. of Transactions`** is entered using the **Total Type** 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`** and **`Region Maximum`** respectively:

!\[1705081698817-110.png]\(image:1705081698817-110.png||height="722" width="331")

## Variance Reports

Variance reports are a common reporting requirement and are often used for comparing period-to-period or year-to-year sales.

### Report that Compares Sales

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:

!\[Conditional formatting comparing sales.png]\(image:Conditional formatting comparing sales.png||height="213" width="312")

This has been created by selecting a single **Row** in the **Axes** tab:

!\[Single Row\.png]\(image:Single Row\.png)

Multiple totals were then created.

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:

```
CASE WHEN ToYear(Transaction_Date) =  ToYear(TODAY) -1 THEN Line_Price END
```

For each row, this extracts the year from the Transaction Date field and determines 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.

!\[PYS.1.png]\(image:PYS.1.png||height="481" width="941")

Clicking the **Copy** icon for a total creates an exact copy below. This is especially useful when display options, such as **Value Format**, have already been set.

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:

```
CASE WHEN ToYear(Transaction_Date) =  ToYear(TODAY) THEN Line_Price END
```

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.

!\[CYS.1.png]\(image:CYS.1.png||height="511" width="939")

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:

```
('Current Year Sales'/'Previous Year Sales')*100
```

This divides the second total value from the first and multiplies the output by one hundred to derive the variance percentage value.

!\[Variance Total.1.png]\(image:Variance Total.1.png||height="601" width="941")

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.

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 is set to the current total.

In this example, the `Variance` total has been selected from the **Source Value** drop-down list for two conditions. The first condition changes the text colour to red when the total is less than 100, while the second condition changes the text colour to green when the total is greater than or equal to 100.

Finally, the **Scope** drop-down list controls which portion of the report will be styled when the conditions are met. In this example, **Row Totals Displayed** has been selected.

!\[Conditional Formatting using different source value.png]\(image:Conditional Formatting using different source value.png||height="642" width="942")

## Row Total Reports

For reports that require a separate total for each row, Row Totals are used.

In most cases, reports that use Row Totals use placeholders as axes to structure the output rather than actual fields from the Index. Placeholders act as row labels that do not perform filtering, with filtering instead handled at calculation level.

### Report Containing Row Totals

The following report displays a summary of key metrics across the different regions:

!\[RT1.png]\(image:RT1.png||height="98" width="508")

This has been created by selecting the Saved entry for the **Row** and a single entry for the **Column** in the **Axes** tab:

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.

!\[RT3.png]\(image:RT3.png||height="179" width="369")

Multiple row totals were then created.

With the **Total Type** drop-down list set to Row Total, the **Measure** drop-down list automatically selects Row Total Calculations. The drop-down list below can then be used to select each row that will be used in the report.

!\[RT4.png]\(image:RT4.png||height="198" width="258")

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:

!\[RT Basket ID.png]\(image:RT Basket ID.png)

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.

The second total for `No. of Returns` uses the following logic:

```
CASE WHEN Has_Returns = '1' THEN 1 END
```

For every row, the Has\_Returns field is scanned for the value `1`. When true, the row is added to the row count that represents the number of returned items in the Index.

Finally, the third total for `Average Sale Price` uses the following logic:

```
Average( Line_Price )
```

This averages the `Line_Price` value for every row. More mathematical functions can be found in the Calculation Builder **Functions** drop-down list.

As the first two totals display row counts and the third total displays an average monetary value, the **Value Format** needs to be different. When using row totals, this can be handled separately for each total via the **Style** icon:

!\[Style Icon.png]\(image:Style Icon.png||height="16" width="27")

For the third total, the **Value Format** and **Prefix** can then be set accordingly:

!\[RT5.png]\(image:RT5.png||height="241" width="282")

Finally, when creating row total reports, 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 does not produce meaningful output. In this report, they are hidden.

This is achieved by clicking the **Cog** icon in the **Axes** tab in the **Rows** section and clicking **Row Totals Hidden**:

The same can be applied to the column totals by clicking **Column Totals Hidden**:

If the logic used dictates that row or column totals are required, ensure that **Calculate Row Total From Sum of Displayed Values** is enabled in the **Totals** tab. This sums the on-screen values rather than taking the count from the unfiltered underlying Index.

## Row Calculation Reports

Not to be confused with row totals, row calculations allow blank rows to be repurposed to contain calculations between other rows in a report.

### Report Containing a Row Calculation

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:

!\[9a.png]\(image:9a.png||height="243" width="180")

This has been created by selecting a single **Row** in the **Axes** tab:

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`:

The default Crosstab behaviour is to hide rows that do not have attached data. To override this behaviour and ensure that the empty row is displayed, enable the **Lock Values On Axis** option.

In the **Totals** tab, a new **Row Calculations** tab is now available that allows a calculation to be written against the empty row:

The following logic has been applied:

```
ROW('Credit Card')+ROW('Debit Card')
```

This sums the output from both the `Credit Card` and `Debit Card` rows.

## Formfield Calculation Reports

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 loading time by reducing the number of records used in calculations.

Rather than filtering the data for the entire report, it is also possible to use filters as referenceable parameters.

### Report Containing a Formfield Calculation

The following report displays the month-to-date (MTD), year-to-date (YTD), and last year’s month-to-date sales for each region:

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.

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 **Advanced Options**. This stops the filters having any effect on the report unless specified in a calculation.

The report has been created by selecting a single **Row** in the **Axes** tab:

!\[Single Row\.png]\(image:Single Row\.png)

Multiple totals have then been created.

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:

```
CASE WHEN ToMonth( Transaction_Month ) = ToMonth( FORMFIELD^"Transaction Month" ) && ToYear( Transaction_Year ) = ToYear( FORMFIELD^"Transaction Year" ) THEN Line_Price END
```

In this statement, to define the month-to-date sales, every row is scanned to determine whether the Transaction Month value matches the Transaction Month filter and whether the Transaction Year value matches the Transaction Year filter. These values are then summed and output into the report.

The `FORMFILED^` syntax is used to reference filters in the calculation builder.

{% hint style="warning" %}
When referencing a filter using the `FORMFIELD^` syntax, double quotes must be used.
{% endhint %}

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:

```
CASE WHEN ToMonth( Transaction_Month ) BETWEEN 1 AND ToMonth( FORMFIELD^"Transaction Month" ) && ToYear( Transaction_Year ) = ToYear( FORMFIELD^"Transaction Year" ) THEN Line_Price END
```

In this statement, to define the year-to-date sales, every row is scanned to determine whether the Transaction Month field is between January and the Transaction Month set in the Filter and whether the Transaction Year field matches.

The **ToMonth** function returns a numeric value (January = 1, February = 2, and so on) and the **BETWEEN** statement uses `1` (January) as the starting point of the range. These values are then summed and output into the report.

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:

```
CASE WHEN ToMonth( Transaction_Month ) = ToMonth( FORMFIELD^"Transaction Month" )
&& ToYear( Transaction_Year ) = ToYear( FORMFIELD^"Transaction Year" ) - 1
THEN Line_Price END
```

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.

### Variance Report Containing a Formfield Calculation

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:

To produce this report, a date range filter named Transaction Date is required. Importantly, the **Ignore** option must be enabled for this filter under **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.

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:

Multiple totals have then been created.

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:

```
CASE WHEN Transaction_Date BETWEEN FORMFIELD^"Transaction Date"^LOW  AND FORMFIELD^"Transaction Date"^HIGH THEN Line_Price - Cost_Price END
```

In this statement, 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.

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.

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:

```
Line_Price-Cost_Price
```

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.

The third total has the **Total Type** set to Calculation and has been labelled `Variance`. The following calculation logic has been applied:

```
CASE WHEN 'Filter Range Average Profit' < 'Total Average Profit' THEN 1 ELSE 0 END
```

This cell calculation 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 hides the values:

Save the current report and import the following images:

In the Conditional Formatting tab for the third total, create the following conditions:

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.

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.

When end-users view the report and change the Transaction Date filter, the report dynamically outputs the corresponding image when the filtered value is greater than or less than the total average value.

## Saved Query Reports

Utilising the flexibility of saved queries in Crosstab reports provides ways to output cohorts of interest to optimise rendering time and maintain the integrity of a published report.

### Report Using a Saved Query as a Hidden Filter

The following report displays the number of transactions that have been processed for the current week for each region and method of payment:

Rather than using a filter or the query bar to 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.

The dynamic saved query used in the report returns all rows from the current week and uses the following syntax:

```
+Transaction_Date:["FIRST_DAY_OF_WEEK" TO "TODAY"]
```

The report has been created by nesting a **Row** below the Saved option and a single **Column** in the **Axes** tab:

For the Saved entry, clicking the `…` button allows the saved query to be selected by clicking the **Select Reports** button:

To hide the saved query from the reporting output, click the **Visibility** icon in the **Axes** tab:

!\[Screenshot 2019-04-01 at 12.39.26.png]\(image:Screenshot 2019-04-01 at 12.39.26.png||height="36" width="157")

This does not remove the saved query or stop the data from being filtered; it simply hides the heading when the report is run.

### Report Using a Saved Query as Axes

Using a saved query in a Crosstab Axis filters the data and displays the saved query name. To change this default behaviour and display the axis label as the description field rather than the name field, apply the **Use Description** checkbox.

!\[Screenshot 2022-06-10 at 16.14.05.png]\(image:Screenshot 2022-06-10 at 16.14.05.png||height="406" width="396")

When using saved queries with dynamic dates, you can also specify dynamic syntax to display in the row or column axes.

Use the following structure:

```
{<point in time>, <format of date>}
```

Any text outside the `{ }` is 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
```

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:

```
'{<point in time>, <format of output>}'
```

Note the single quotes around the statement compared to the simple structure shown above.

For example, to have a different output for February, use the following code:

```
CASE WHEN CurrentMonth = 02 THEN '{LAST_DAY_OF_MONTH-1YEAR-1MONTH,MMM YY}' ELSE '{LAST_DAY_OF_MONTH-1MONTH,MMM YY}' END
```

#### Reporting Example

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 update automatically:

First, six saved queries were created. Each month value is driven by a saved query with the following syntax:

```
Transaction_Date:["FIRST_DAY_OF_MONTH-6MONTHS" TO "LAST_DAY_OF_MONTH-6MONTHS"]
```

Change the `-6MONTHS` values to `-5MONTHS`, `-4MONTHS`, and so on to create queries looking at different months:

In the above examples, simple dynamic descriptions have also been added for every query.

The report has been created using a single **Row** and a single **Column**, with the Saved entry selected, in the **Axes** tab:

Using the Saved column entry, a number of saved queries can be selected.

These are loaded into the report using the `…` icon for the Saved entry:

Ensure the **Use Description** option has been enabled. The report then refers to the saved query’s dynamic description rather than the static query name. If there is a blank description, the name is used instead.

With the **Total Type** set to the default Count option, the report counts the records returned from each query. The dynamic query syntax ensures that the report automatically updates to reflect a rolling six-month period, removing the need to update reports every month.

Dynamic descriptions can use **CASE** or **IF** statements to select different dynamic dates depending on a condition. In this example, the months may be held back from shifting forward until working day 2 to allow more time to review the previous month in full, against the one six months prior.

```
CASE
WHEN TODAY <= FIRST_WORKING_DAY_OF_MONTH THEN
FORMAT(LAST_DAY_OF_MONTH-2MONTH,'MMM YY')
ELSE
FORMAT(LAST_DAY_OF_MONTH-1MONTH,'MMM YY')
END
```

Use the above **CASE** statement to replace the description within the **-1 Months** query to enable this behaviour. Do the same, adjusting for the number of months, for the subsequent queries.

{% hint style="info" %}
You will need to add some additional days to `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 `FIRST_WORKING_DAY_OF_MONTH+13DAYS` and `FIRST_WORKING_DAY_OF_MONTH+14DAYS` to test both conditions.
{% endhint %}

{% hint style="warning" %}
When using **CASE** or **IF** statements, ensure that the **Evaluate calculation** option is enabled.
{% endhint %}

!\[1705342472236-141.png]\(image:1705342472236-141.png||height="500" width="579")

### Report Using a Saved Query with Field Comparisons

Saved queries can make use of the same functions available when writing query syntax. The following examples compare values between two columns.

Save the following queries run against the EPOS Index:

| Name                                                 | Syntax                                                         |
| ---------------------------------------------------- | -------------------------------------------------------------- |
| Single item sales non-adjusted                       | `+Basket_Value=Line_Price +Discount_Amount:"0" +Quantity:"1"`  |
| Single item sales adjusted                           | `+Basket_Value<>Line_Price +Discount_Amount:"0" +Quantity:"1"` |
| Sales with a discount higher than the basket value   | `+Basket_Value<Discount_Amount`                                |
| (you will notice the same results either way around) | `+Discount_Amount>Basket_Value`                                |
| Products sold at a profit or at cost                 | `+Cost_Price<=Line_Price`                                      |
| Products sold at a loss or at cost                   | `+Cost_Price>=Line_Price`                                      |

#### Single item sales

This report displays the number of sales of a single product per transaction for each region. Using saved field comparison queries, the difference between adjusted and non-adjusted basket values can be shown:

!\[1704994800651-359.png]\(image:1704994800651-359.png||height="246" width="627")

The report has been created using a single **Row** `Region Name` and a single **Column**, with the Saved entry selected, in the **Axes** tab:

!\[1704995748588-712.png]\(image:1704995748588-712.png||height="279" width="381")

With the **Total Type** set to the default Count option, the report counts the records returned from each query.

#### Sales with a discount higher than the basket value

Using 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:

!\[1704998019853-334.png]\(image:1704998019853-334.png||height="259" width="556")

The report has been created using **Rows** with the Saved entry selected, but hidden, and `Region Name` and `MOP` in the **Axes** tab:

!\[1704997451649-772.png]\(image:1704997451649-772.png||height="301" width="372")

With **`No of Transactions`** using the **Total Type** set to the default Count option, the report counts the records returned from each query. `Basket Value` and `Discount Amount` have been selected using the **Total Type** set to **Sum** and the corresponding measure selected.

#### Products sold at a profit or Loss

Using saved field comparison queries, this report displays the number of sales where products have been sold at profit versus loss with a grouping hierarchy drillable by Department and Group:

!\[1704999461853-623.png]\(image:1704999461853-623.png||height="623" width="628")

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:

!\[1704999784237-589.png]\(image:1704999784237-589.png||height="344" width="372")

With **`No of Transactions`** using the **Total Type** set to the default Count option, the report counts the records returned from each query. `Total Average Profit` has been selected, as previously, using the **Total Type** set to **Average** and `Line_Price-Cost_Price` selected as the calculation.