Clarity provides you with a variety of standard reports right out of the box. In the event that you need something extra that isn't included in these reports, Clarity includes a report-writer tool to help you define your own! The example below will use the EMPLOYEES table to create the report.
Table of Contents
I. Data Selection for Your Basic Report
A. Selecting One Table For Your Report
1. In Clarity, navigate to Reports > Report Explorer.
2. Navigate to File > New > Report > 'Data' tab.
3. Navigate to File > New.
4. Select 'Query Design' and click "OK".
5. In the 'Tables' tab, locate the tables that you want to use. In this example, we will use the EMPLOYEES table.
6. Double-click on the table you want to use to build your report. The table will now be selected and listed in the lower section of this window.
7. Navigate to the 'Fields' tab. All the fields in the table(s) you selected will be available.
8. Double-click on the fields that you want to use in your report. The selected fields will now be listed in the lower section of this window.
9. Use the arrows on the lower-right side of this window to move the field up or down.
10. Click "OK" to save your selection.
11. Back in the Report Builder, navigate to the 'Design' tab. Here, you will bring in the required fields in to your report. There are two (2) ways you can do this:
a. Drag the fields on the right-side of the window (Data Tree) onto the design area and drop them to include the fields in your report, OR
b. Use the icons to add labels and the fields
12. Once you've created the report how you like it, navigate to the 'Preview' tab to see a print-preview of the report you built.
B. Selecting Multiple Tables For Your Report (Advanced)
You may want to select multiple tables to use multiple fields in your reports.
1. In Clarity, navigate to Reports > Report Explorer.
2. Navigate to File > New > Report > 'Data' tab.
3. Navigate to File > New.
4. Select 'Query Design' and click "OK".
5. In the 'Tables' tab, locate the tables that you want to use. In this example, we will use both the EMPLOYEES table and the LEVEL_TWO table.
6. Double-click on the table you want to use to build your report. The table will now be selected and listed in the lower section of this window.
7. Double-click on the second table you want to use to build your report. The 'Join Tables' window will appear.
8. (optional) Select the fields in both tables and click "Add" to join them together. Joined fields are shown at the bottom section of the window. If this isn't necessary, and you want to add all the fields in the table, ignore this step.
9. Click "OK" to save your changes. Both tables will now be shown at the bottom of the window.
10. Navigate to the 'Fields' tab. All the fields in the table(s) you selected will be available.
11. Double-click on the fields that you want to use in your report. The selected fields will now be listed in the lower section of this window.
12. Use the arrows on the lower-right side of this window to move the field up or down.
13. Click "OK" to save your selection.
14. Back in the Report Builder, navigate to the 'Design' tab. Here, you will bring in the required fields in to your report. There are two (2) ways you can do this:
a. Drag the fields on the right-side of the window (Data Tree) onto the design area and drop them to include the fields in your report, OR
b. Use the icons to add labels and the fields
15. Once you've created the report how you like it, navigate to the 'Preview' tab to see a print-preview of the report you built.
II. Sorting Records In Your Report
You may want to sort the records in your report according to one of your fields. For example, sorting the report by the first hire date (FIRST_HIRE_DATE) in descending order (most recent first). For example, your report may look like this:
But you'd like the report to look like this (notice the first hire date column):
- In the report builder, navigate to the 'Data' tab.
- Click the sort icon. It looks like this:
- In the 'Sort' tab, double-click the field(s) that you want to sort by. For example, in the image below, we selected the first hire date.
- By default, it will be sorted by this field in ascending order. If you'd like for it to be in descending order, checkmark the "Desc (z > a)" field.
- Click "OK" to save your changes.
It is also possible to sort your report by multiple fields. You can simply add all the fields and sort accordingly. For example, the image below will sort by termination date, and then the first hire date. Tip: If you want a certain field to be sorted first, make sure it's at the top of the list by using the arrows!
III. Grouping Records In Your Report
You may want to group the records in your report according to one of your fields. For example, you may want your report to look like this (notice the status rows - the employees are grouped by employee status (EMPLOYEES.STATUS): 'terminated' and 'active' status):
1. In the report builder, navigate to the 'Design' tab.
2. Navigate to Report > Groups...
3. In the Break On section, select "Data Field".
4. Select the field that you want to use for grouping your report in the dropdown list.
5. Click "Add" to add that field.
6. Click "OK" to save your changes. This group will now be listed in the 'Design' tab from earlier.
Your report should now look like this in the 'Design' tab:
IV. Filtering Records in Your Report
You may want to filter the records in your report according to one of your fields. In the example below, we will filter the employees within a date range for the last hire date. For example, your report may look like this now:
But you may want your report to look like this instead (notice the report only includes employees whose last hire date is between 1994-1995):
- In the report builder, navigate to the 'Data' tab.
- Click on the Query Designer button. It looks like this:
- Navigate to the 'Search' tab.
- Double-click on the field that will be used as your filter for the report. This will select the field and it will be shown in the lower section of the window.
- In the "Operator" column, select the appropriate operator. For example, the image below uses the "Between" operator.
- In the "Value" column, enter the values that will be used for the filter. For example, the image below uses a date range from Jan 1, 1994 to Dec 31, 1995. Combining both the operator and value, this means that this report will be filtered based on dates in between Jan 1, 1994 and Dec 31, 1995 (inclusive).
- Click "OK" to save your changes.
V. Sub-Reports
With Clarity's report builder, you can also create a sub-report (a report within a report). For example, your existing report may look something like this (note that it only lists employee information):
But you may want your report to look like this instead (note that it lists the employee information along with their earnings):
1. In the report builder, navigate to the 'Design' tab.
2. Click the SubReport icon.
3. Click on the section of the report where you want to add your sub-report.
4. The sub-report line will be displayed within the main report that you built. You can add a new dataset, or use your existing datasets for the sub-report.
5. To add a new dataset to the sub-report, click the bottom tab to access the sub-report fields.
6. Enter the fields that you require in the sub-report.
