The first step to designing your report is to select one or more data tables where your desired data fields are located. Therefore, the first menu item, Data Source, is highlighted by default when you begin designing a new report. The list of the tables available is displayed in the center area. The tables listed are from the module selected at the very beginning of the process. (In our examples we selected the Learning Management System module.)

Information about currently available data sources/fields is available here in the downloadable spreadsheet.

Adding a Table

To add a table to the report design, click the green plus sign located next to the desired table(s). Notice each selected table moves up to the Selected Data Sources section.

Deleting a Table

To delete a table and all related fields previously added to a report, click the trash can icon, located next to the table name in the Selected Data Sources section.

Tip – If you remove a table from the report design after fields from that table were added to the report, then those fields will also be removed from the report design.

Remove Duplicate Rows

Distinct - Check this box when a report design would return multiple identical rows, and the desired result is to remove duplicates. For example, you wish to know which certifications employees have achieved, leaving out any for which no employees have achieved. Creating a report from the UserCertification table which includes just the Certification Name provides this information, however, the Certification Name is listed multiple times, once for every user with that certification. Checking the Distinct box eliminates the duplicates.

Available Data Fields

To view the fields that are available in each table, click the table name or the small arrow located to the right of the table name. Seeing the available fields may help you determine which tables to include in the report design. Use the scroll bar to scroll down and view more fields. The scroll bar is available by hovering on the right side of this area. Selecting (rather than viewing) data fields to include in the report happens in the next major step under the Design menu.

Tip – Once the table is added to the Selected Data Sources section, the fields are no longer visible in the Data Source window. They will be visible again in the next step, Design.

Once you have selected one or more data sources, more options become available to the right of the data sources. The bottom right contains a large section for adding relationships. You must add a relationship whenever you add a second, third, fourth, etc. data source. Relationships are further described in a separate section.

• Save – Click this button, located in the upper right corner, to save design changes made in this window. Save As is also available by clicking on this button. The first time the template is saved or when Save As is selected, you have the option to save the report to a Category and Subcategory.

  

  Tip – Typing in a Category or Subcategory name that does not currently exist, creates it on the fly, and saves the template into the newly created category.

• Cancel – Click this button, located in the upper right corner, to undo any changes made since your last save.
• Report Viewer – Click this button, located in the upper right corner, to preview the report.
• Report Name - Click the pencil icon to edit this field and change the report name from the default of Example Report Name.
• Preview Records – This value sets the default number of records presented to the viewer when they first open the report. The viewer has the option to change the number of records displayed at one time.
• Filter – There is no functionality in this area. Adding filters is a feature within the Design menu.

Add Relationship

Whenever more than one data source is selected, each data source must be joined to the other data sources using the Add Relationship. Click the Add Relationship button, located in the Data Sources window, then define the relationship by selecting values for each field in the relationship. Defining a Relationship or Join allows a report to locate the appropriate records from each data source.

The number of Relationships required is generally one less than the number of tables included in the design. Think of it as joining the tables into a chain. For example, if you include two data sources in a report you will need to add and define one relationship. If you add another data source to the report, that data source will also require a relationship to be defined, so now the report includes three data sources and two relationships. In this fashion, reports will have one less relationship defined than there are data sources. You can think of each defined relationship as a link in a chain that joins the tables together based on a field that the tables have in common.

If not completed, the user receives the following message:

Click Add Relationship and notice a new line in this section. Each of the columns may require completion, as described below.

• Join Alias – This is a name for the relationship, it can be left blank.
• Join Type – Select from the dropdown. The most common Join Type is Inner, and the software defaults to this choice. Joins are a common database function, there are many resources that discuss join types.
• Operator – Leave this column blank.
• Data Object – Select a data source from the dropdown. Listed will be the data sources previously selected in the report design.
• Join Field – Select a field from the dropdown. Listed will be the fields included in the previously selected data source.
• Foreign Data Object – Select the data source from the dropdown which you want to join with the data source previously selected on this line. Listed in the dropdown will be the data sources previously included in the report design.
• Field - Select a field from the dropdown. Listed will be the fields included in the previously selected data source.
• Actions – Use these three icons to: add a key join, copy the line, delete the line.
• Validate Syntax – Once all fields are complete on all lines, click Validate Syntax to verify the selections made are valid. This message appears when syntax if correct and validated: