Ad Hoc reports allow Users to slice and analyze data, which can then be added to dashboards. An Ad Hoc report can be a chart, table, or pivot table. Restaurant365 comes with a number of pre-defined Ad Hoc reports. With the proper security level, Users can also create their own. Ad Hoc reports become available when you purchase the Business Analytics module.
This article focuses on the following areas:
Reports in this module must be based on pre-defined sets of data called Domains that come with the system. The following is the list of available domains:
- Daily Sales Summary
- Franchisee Sales
- Labor Details
- Purchase Details
- Sales Details
- Sales Payments
- Transaction Details
Accessing Ad Hoc Reports
Ad Hoc reports are accessed by clicking on 'Ad Hoc Reports' under the 'Reports' list in the 'Accounting' or Operations module in the left hand navigation. If you have security access (Business Analytics Admin role), you will see this page. Selecting an Ad Hoc report puts it in edit mode by default. There are 4 dropdowns at the top of this page for editing / creating reports as described below:
- Reports- This lists the Ad Hoc reports created by your company
- Delete button - Once a custom report is selected in the Reports dropdown, a trash can icon will appear (not shown above) and pressing this will delete the selected report
- Create Chart Of - This allows you to create a new chart based on the available data sets as listed above. Hovering over an option in the list will pop up a description of that data set
- Create Crosstab Of - This allows you to create a new crosstab (pivot table) based on the available data sets as listed above. Hovering over an option in the list will pop up a description of that data set
- Samples - This lists a number of sample Ad Hoc reports
Ad Hoc Report Elements
Below is an image of the Ad Hoc Report Designer numbering the different elements in orange. Below the image is a description of how reports are created and modified. Image numbers will be referenced throughout.
Creating A Report
The following steps describe a process you can use to create a report:
1) Find a similar report as a starting poitn to base yours on - Ad Hoc reports are based on a set of data called a Domain. Domains are pre-defined connections to the database setup by Restaurant365 that give you access to data sets such as Sales Details (menu mix), Labor Details, Daily Sales Summary data, etc. There are out of the box reports based on each Domain already, so choose a report that uses the Domain you want for your report, alter it, and Save as a new name.
2) Select what type of report you want - Using the Report Type Selector (#9 in image), select whether the report will be a chart, table (spreadsheet type list of data), or Crosstab (pivot table). If 'Chart' is selected, then you can further define what type of chart, such as line, bar, pie, scatter plot, etc by clicking the Chart Type selector (#5 in image).
3) Select data to use as columns and rows in the report - Two types of data can be used in a report: Fields and Measures as described below. To add these to a report, right click the value and choose "Add to Columns" or "Add to Rows". You can also drag and drop fields in the Columns and Rows sections as well (#7 and #8 in image). The order a field or measure shows up in the Column and Row section is important because that is the hierarchy used to sum the data. You can drag fields in this section ahead of other fields or right click a field and choose "Move Left" or "Move Right" to change the order.
- Fields - these are selected from the Field List (#3 in image), which are pre-defined fields set by Restaurant365 that come with the Domain the report is based on. Location or Fiscal Period are good examples of fields you might have in a column or row of your report.
- Measures - these are selected from the Measures List (#1 in image), which are generally numbers, such as amounts, percentages, and counts, that are used as values in a report. Many measures are pre-defined by Restaurant365 but custom calculated measures can be created as well. To create a custom measure, click the Calculated Measure button (#2 in image) and a window will open where you can give the measure a name and select the math to calculate it. For example, if you had measures of Qty and Each Price but not a Total Price, you could create a calculated measure of Qty * Each Price and name it 'Total Price', which could then be used in the report the same as the pre-defined measures. Once a measure is on the report, you can set the summary calculation on this report to Sum, Avg, Count, Min, Max, etc by right-clicking the measure in the column or row section (#7 and #8 in image), clicking 'Change Summary Calculation', and then choosing the one you want.
4) If a chart, choose the Data Level - Using the Data Level slider (#12 in image), select if the chart will be based on the lowest level of detail (furthest right field in Rows section) or any sub-total or grand total level above that. Slide it all the way to the right for the lowest level of detail and all the way to left for highest summary level. As you drag the selector, it will show the name of the selected level so it's easy to see what it will sum by.
5) Setup filters to restrict the data - Filters are restrictions on the data, so you can specify a certain date range, specific list of locations, etc for this report. Multiple filters can be added to a report. They can be added to the filter section (#15 in image) by right-clicking a field or measure and selecting 'Create Filter' or by dragging and dropping a field or measure in the Filter section. When multiple filters have been created, the first filter is named A, the next B, and so on. By default, all filters are assumed to be necessary so filter A and filter B must both be true in order for that data to show up on the report. However, you can set a Custom Filter Expression (#16 in image) to say A or B, which will return data to the report if either filter is true. Once the filter is set, press the 'Apply' button (#17 in image) to apply the filter and refresh the data. Different data types function differently as filters and a few elements of these filters are listed below:
- Filter Type (#13 in image) - this allows a user to specify 'equals', 'is between', 'is greater than', 'is less than', etc. This list changes slightly based on which data type the filter is. For example, for a date, you can select 'is on or after' or 'is on or before' whereas for a list of data such as a list of locations, you can choose 'is one of', which allows you to select 1 or many locations.
- Remove Filter (#14 in image) - to remove a filter, click this button and choose 'Remove Filter'
- Dates - a filter based on dates will have a Calendar selector to choose a date. You can also use relative date expressions such as 'Year-1', 'Week-1', 'Day-28'. As an example, if you selected a Filter Type of 'is on or after', and set the filter as 'Day-28', then it would return all data that happened within the last 28 days.
- Lists - a filter based on a list can have 1 or many values selected. For example, if you want the report based on a certain list of locations, then you would right click the 'Location' field, choose 'Create Filter', and then in the filter, you set Filter Type to 'is one of', and then click each Location you want in the report. For list filters, you have options at the bottom to 'Select All' and 'Deselect All'.
6) Give a title - Type your desired title for the report by clicking the title at the top (#10 in image)
7) Save the report - Click the 'Save' button (#6 in image) and a drop-down will appear with the following values:
Ad Hoc Analysis Features
The following features help when slicing and dicing an Ad Hoc report:
- Left and Right pane collapse buttons (#4 and #11 in image) - these collapse the left and right pane to give more screen space for report viewing
- Hovering over a point in a chart - this will display a popup with the exact values that make up that point in the chart
- Click values in the legend to hide or show them in a chart (#18 in image) - by default all selected measures will show in the chart, but you can quickly hide and show any measure by clicking it in the Chart Legend
- Zoom in by clicking and dragging - to zoom in on a specific portion of a chart, left click with the mouse, hold it down and drag to highlight a specific portion of the chart and then release the mouse button. Click the 'Reset Zoom' button to go back to original view
- In a Crosstab, to remove the totals or subtotals from rows or columns, right click the header you want to remove totals from in the upper left corner of the crosstab and choose 'Delete Column Summary' or 'Delete Row Summary'. In the below image, I wanted to remove the total column for the Location group so I right clicked Location and chose Delete Column Summary