What We’ll Cover
- Bridging Lists with Non-Matching Links in the Visual Report Builder
- Creating a Custom Report with the SQL Report Builder
- Suggested Reading
The Custom Reports page is where you create your own custom reports. Like the pre-made reports built for you that pull data from the replicated database, you can use the Report Builders to show only the specific data you want on your own custom reports. The reports are saved in the Custom Reports page, where they can be edited and deleted.
Important: Roles must be enabled to use this feature:
1. ManageCustomReports - User can create, update, and delete there own custom report, as well as view, export, edit, and delete all custom reports.
2. ViewCustomReports - User can create, update, and delete their own custom reports, as well as view and export all custom reports.
For more information on Employee Roles, see: 5.2. Assigning Employee Permissions.
In Corporate Office, navigate to: Reports > Custom Reports.
+ New Report - Clicking the + New Report button opens a selection menu with two options:
Custom Reports List - Lists all of your custom created reports sorted by Report Name, Owner, Last Modified, and Type.
Report Name - Clicking the name of the report will open a slide-out report page.
Edit and Delete - The Edit button opens an Edit Custom Report slide-out panel of the Report Builder used to create the report.
In the report, when Save is clicked, a pop-up will open with the Report Name field populated with the report’s current name. Leave the name unchanged to save changes to the report.
If you update the name and confirm the save, then the report will save under the new name; it doe NOT create a new report.
If you make changes and try to close the Report Builder without saving, an Unsaved Changes confirmation pop-up will show. Click the Discard Changes button to close the report without saving your edits.
Click the Delete button to remove the report. Opens the Delete Custom Report pop-up window. Deleted reports cannot be recovered.
Custom Report Panel
When you click on a report’s name, a slide-out panel will display the custom report.
Count - Displays the number of returned data.
Export - Export the custom report as a .CSV file.
Search - Filter the report by any of the data points.
Columns - Features column search bars that filter the data show to your search query. Search queries need to match the exact format used when they were entered into the database in order to be included in the results. Use the existing data in the report to help you determine how the data in that column is structured.
Click the column header to sort report columns in ascending/descending alphabetical order.
Pagination - If your report returns more than 10 rows, then the rest of the data can be viewed by clicking the pagination numbers or the Previous/Next button.
SQL Report Builder
Table Schema - View a list of all available tables you can click and use in queries. The query will return up to 300 results. Use the search bar to find a table name, or scroll through the Table Name list.
Query Box - Type your SQL query here. The Query Box supports full SQL. For examples of SQL queries, see: SQL Query Library.
Click in the Query Box and press CTRL+SPACE on your keyboard to enable SQL Type-Ahead. This is a Code Mirror implementation that suggests SQL syntax and tables as you type to aid in writing queries.
Save button - Save the report. Opens the Save Report pop-up window. Once saved, the report is sorted in the Custom Reports List.
Preview button - Opens a slide-out panel for the report without saving.
Data Table - Selecting a table from the table schema will display the data in the Data Table area.
Visual Report Builder
Lists - A selection of tables in the replicated database that you can drag into the Drag and Drop Area to add them to your custom report.
These tables have useful data that pertains to associate and order details. Adding multiple tables to the Drag and Drop Area joins their data on the report allowing you to craft reports that fulfill specific needs.
Links - Color codes that indicate the shared columns between table Lists.
Key - Shows the database columns that appear in multiple tables. Each column is assigned a color.
Only Lists with matching Links can be added to a report (i.e., a brown Link can’t be joined with a yellow Link). You’ll notice that Lists often have multiple Links. The Lists only need to have one color in common. If you want to join non-matching Links, you need to bridge the two Lists with a List that shares a Link with both.
Save Report - Click to save the report. A Save pop-up window will open where you can give the report a name, click Save Report to confirm. Reports are sorted in the Custom Reports List.
Preview Report - Opens a preview of the report in a slide-out panel to see before saving.
Drag and Drop Area - To create a report click and drag a List and drop it in the Drag and Drop Area.
Expanded List - When the List is dropped into the Drag and Drop Area, it expands. In the expanded List, you can filter the table to only show a certain column, toggle whether the data is visible with the Show Data checkbox (helpful when using many Lists in a report. type or date range), and remove the List from the report with the Remove button.
Data Table - Each List you add to the Drag and Drop area will display its data in this table. Click the column heading to sort the column in ascending/descending alphabetical order, click the X icon next to the column heading to delete the column from the report, and click and drag the column heading to change its position on the report.
Bridging Lists with Non-Matching Links in the Visual Report Builder
The Associates List has yellow and blue links.
The Inventory Item List has a brown link.
If you drag both of these Lists to the Drag and Drop area, no data will show.
You can join non-matching Links by bridging the Lists with a List/Lists that share Links with both Lists.
Drag the Associates List the Drag and Drop area (blue and yellow).
Drag the Orders with PV List (purple and blue).
Since it contains a blue link in common the data shows.
Drag the OrderDetail List (purple and brown).
Since it shares purple with Orders with PV List, it can be joined.
Now, you can drag the Inventory Item List (brown).
Inventory Item and OrderDetail share brown in common, so a join can be made.
Uncheck the Show Data checkboxes on the Orders With PV and OrderDetail Lists to only show the Associates and Inventory Item List data on the report.
Creating a Custom Report with the SQL Report Builder
The SQL Report Builder functions similarly to the SQL Manager. Use the Table Schema to find and view different tables; the table displays under the Query Box.
Note: the selected table in the Table Schema pop-up window is view only; only SQL statements entered in the Query Box can be saved as reports.
Write your valid SQL query in the Query Box. You can view the report before saving by clicking the Preview button. Save the report by clicking the Save button. Give the report a name and confirm the save in the Save Query pop-up window. The report can be viewed, edited, and deleted in the Custom Reports page.