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
| 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,
D. Data selection and basic report
Example using the EMPLOYEES table
Start the report explorer: from top menu: Reports > Report Explorer > from File menu > New > Report
In the Report builder window select the Data tab to add the table
In the Data tab from top menu File > New > select the Query Design
...
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
...
Once the fields are selected the user can move the field up or down by using the arrow up or arrow down buttons.
In the Design tab the right section lists the fields available. Bring the required fields into the report. User can add the fields by
1: using the Data Tree section – drag and drop OR
2: use the icons to add the labels and fields
Click the Preview tab to see the outline of the report
E. Sorting record
Example : sort by a specific field - descending by FIRST_HIRE_DATE
Before sort
After sort
Sorting by multiple fields: add the additional filed(s) and sort. Example: sorting by TERMINATION_DATE and FIRST_HIRE_DATE
After sorting
F. Grouping record
Example: group by employee Status. To add a group from the top menu: Report > Groups
Select the field used for grouping from the drop down list and click “Add”. The Group will be now listed in the Design.
Report group by status:
G. Filter record
Example: display the employee with the pay period into a specific pay period.
Report prior apply filter:
Select the filter criteria: in the Query Designer window go to Search tab
Double click on the field that will be used as filter. The field will be listed in lower section of the window. In the Operator cell from the drop down list select the operator. In the Value cell enter the values that will be used for filter. Click OK to save the change.
Report after filter:
Advance report design with multiple tables
In the report builder window select the Data tab to see the datasets.
Select the dataset where you want to add a new table. In the Tables tab select the additional table(s) that you want to link. Tables are linked using a specific ID fields.
Example: adding Department. Select the LEVEL_TWO table and link it with the existing table (EMPLOYEES).
The additional table will be listed in the lower section in the Tables tab
To add the field from the second table go to Fields tab. Double click on the field. The field will be listed in the lower window. Click OK to save the changes.
In the Design tab the new fields will be listed in the Data Tree section. The user can add the new fields to the report.
Report output:
Report output:
Case statement and sub query
In the Data tab from the top menu: File > New > query design > select the table (LEVEL_TWO)
Sub Report
In the Design tab click on the sub report icon to add the subreport then click on section of the report where you want to add the subreport
The subreport line will be display in the main report
For the subreport user can add a new dataset or use the existing data set
Click on the bottom tab to access the subreport
Enter the fields that you required in the subreport
Before subreport to be added – lists only the employee information
After subreport added : lists the employee information and the earnings
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.