FlexQuery provides a query to report on tenders in detail. It includes invoice header information as well as every tender on each invoice. This does not make the query useful for sales totals, as an invoice with three split tenders will have three lines, with the header information for the invoice repeated 3 times. These fields are only present so they can be used for filtering. It is, as named, intended for auditing your tenders. Some information is best presented in a pivot table, where eg an invoice total can be averaged (rather than summed) if the invoice number is included in the pivot.

The default data shown is:

Once onscreen, though, we can do a number of things with the data, and provide grouped totals. If we group by store and date, it will look like:

Or grouped by date and store:

 Expanded:

This represents total tenders taken by store at this level. We can also add the Tender field to the grouping and analyse by store the breakdown:

Doing this using Tender Name gives a more detailed result:

And drilling down by clicking the plus alongside the Tender name produces:

This is good for a quick summary while still retaining the ability to go deeper to see more detail. However, a pivot can produce a simpler layout as a Tender Reconciliation. If you click the pivot button, you can select the ‘Simple Tender by Day-store’ Layout to see this:

Any format you choose to see the information in can be added to My Flexquery as a Custom View, ready to be launched at the click of a button. Any you can also add it to an appropriate group.

HINT: a tender reconciliation by day Custom View could be present in daily, weekly and monthly groups without having to create the same Custom View three times. For Head Office, this can be used to track the cash bankings for a store, where daily banking is required. This is also a great way to provide a standardized end of day report for a store in a format Head Office has not only approved, but also uses. One way to ensure that the store banking matches the Retail Pro information is to account for unders and overs by recording them as a fee type to increase or decrease the cash total. This then becomes the responsibility of the person doing the cash up to ensure that Head Office gets correct information to reconcile against their bank statements.

What you’ve seen above is the basics, but it is possible to represent the data differently and get a lot more detail from the data provided.

First, with the pivot above, you can right-click the INVOICE_DATE field and select a date interval:

Selecting ‘Date_Month’ will show you Monthly Totals:

If you select Day of Week, you may need to add the Week or even the Year fields to the display:

Extending this idea to the initial query data (prior to pivoting), the query grouped summary layout also has date intervals which can be changed. This can be done by right-clicking the INVOICE_DATE (or any Date field used for grouping and selecting ‘Date/Time Grouping Interval’ then selecting the required date interval:

Example using ‘Relative to Today’ Interval:

HINT: Using the ‘By Hour’ Interval and the CREATED_DATE field allows a look at Hourly tender totals.

Sorting

To set up for this,  right-click the column header of a field with a group subtotal and select ‘Allow Sort by Group Subtotal’. You may need to click the ‘Collapse All’ Button to provide just summary data.

By default, the groups will be sorted in ascending order:

Note the change in the sort indicator on the grouped fields. The extra line is an indicator that you have sorted by the group subtotal. Click the Store Name field to reverse the order:

Right-clicking the group header allows a selection of any of the group-subtotalled fields:

HINT: This group level sorting is available in any query, not just the Tender Audit Report.