Ad Hoc reports allow users to slice and analyze data and can also 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.
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
- Sales Details
- Sales Payments
- Transaction Details
Accessing Ad Hoc Reports
Ad Hoc reports are accessed on the Ad Hoc Reports page in the left hand navigation of Accounting (or Operations) – Reports – Ad Hoc Reports. 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 and image numbers will be referenced throughout.
Creating A Report
The following steps describe a process you can use to create a report:
- Find a similar report as a starting point 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.
- 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).
- Select data to use as columns and rows in the report – 2 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 choose the one you desire.
- 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 its easy to see what it will sum by.
- Setup filters to restrict the data – Filters are restrictions on the data so you can specifiy 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 = “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.
- Give a title – Type your desired title for the report by clicking the title at the top (#10 in image)
- Save the report – Click the Save button (#6 in image) and a dropdown will appear with the following values:
- “Save Ad Hoc View” – this option will overwrite the existing Ad Hoc with your changes. Pre-defined Ad Hoc reports built by Restaurant365 cannot be modified so this option will not be available on those
- “Save Ad Hoc View As…” – this option will leave the original report un-modified and will save your changes as a new report where you provide a new name. Once you select this option the Save As window (pictured below) will pop up where you can set the name of the report and choose where to save it. Once you’ve typed the name, in the folder hierarchy at the bottom of the pop up window, follow this folder structure to save your report: root>Organizations>Organization>then click on the folder with your site’s name (my site name in the screenshot below is “demo”). Click your site folder which will expand to Accounting and Manager folders and you will save your report in one of those 2 folders. Saving in Accounting means only users with the Accounting Manager role will see it and saving in Manager means users with either Restaurant Manager or Accounting Manager will see it.
- “Save Ad Hoc View and Create Report” – this option is never used and can be ignored
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 pop up 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 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