Module Navigation

Setting Up Your Data Source for a Custom Designed Report

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 available LMS data sources and a brief description are available here.

image63.png

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.

image64.png

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.

image65.png

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.

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.

image66.png

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.

image67.png

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.

image68.png

  • 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.

    image69.png

    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.
  • 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 - When multiple data sources are selected, the report designer must utilize Add Relationship to join the tables by defining a common field between the tables. 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. If not completed, the user receives the following message:

    image70.png

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

      image71.png

    • 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 which 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:

      image72.png

 Next Article

The next article is Designing Your Custom Report

Was this article helpful?
0 out of 0 found this helpful