Data Sources Tab (Defining EAR and Cross-Publication EAR Reports)

Selecting data sources is required.

EAR

Appearance of the Data Sources tab varies, depending on whether user is creating or modifying a report.

Create a new report: One drop-down menu appears. An Add Below icon is displayed to the right of the menu. For the second and any additional menus, three icons are displayed to the right of the menu: Delete, Add Above, and Add Below.
Modify an existing report: Data sources already be selected are displayed. These may be retained, removed (using the Delete icon), or changed (using the drop-down menu to choose a different table or view).

Icons: The list of data sources may be edited using the icons to the right of the list items: Delete (red X), Add Above (arrow indicating above), and Add Below (arrow indicating below).

Using joins

A field that appears in two data sources (common field) may be used to join the data from the two sources based on the way the join is defined. A join may also be used to exclude data to narrow the report output.

Five types of joins are available:

Inner (Direct): Returns data that is common to both source 1 and source 2.
Cross: Creates paired combinations of each row in source 1 with each row in source 2.
Left (First Exists): Combines data from source 1 and intersecting data from source 2.
Right: Combines data from source 2 and intersecting data from source 1.
Full: Combines data from left and right joins when there is a match in either of the sources for the fields defining the join.

Basic steps to define a join:

The statement to define a join on the Data Sources tab looks like this:

[First Data Source].[Field] = [Second Data Source].[Field] [Join type]

Data Sources, Fields, and Join type are selected from drop-down menus. In the steps below, the drop-down menus are identified as A through E (A: First Data Source, B: Field from First Data Source, C: Second Data Source, D: Field from Second Data Source, E: Join type).

Select a data source (table or view) from the drop-down menu.
Click the Add Below icon to enable a second row and drop-down menu.
Select a second data source. Additional drop-down menus will appear next to the second source. These enable selection of the common field on which to join the two sources and the type of join to use.
1. Drop-down A: Data source selected in step 2.
2. Drop-down B: All fields in the data source shown in drop-down A.
3. Drop-down C: Data source selected in step 1.
4. Drop-down D: All fields in the table or view shown in drop-down C.
5. Drop-down E: Join type selector.
Select a common field using drop-down menus B and D. (The system may suggest common fields to use. Change as needed to select fields that meet the requirements of the report being defined.)
Select the type of join using drop-down menu E. (The system defaults to inner join. Change as needed for the aim of the report being defined.)

Tip: Do not join on name fields (e.g., names of Article Types, Authors, Editors, etc.). Use the related ID fields (e.g., Article Type ID, Author ID, Editor ID).

For a demonstration, watch this video on using joins.

Cross-Publication EAR

Use the drop-down menu to select a view to use as the data source for the report.

(Only one data source may be selected for a Cross-Publication EAR report. Cross-publication views are pre-defined with multiple data sources and joins.)

Information on data sources

EAR

Tables or views may be selected as data sources for EAR reports. Information about contents of tables and views is available via the Get more help links on the EAR menu page.

Cross-Publication EAR

Cross-publication views may be selected as data sources for Cross-Publication EAR reports. Information about cross-publication views is available via the Get more help link on the Cross-Publication EAR menu page.

For reference, information on frequently used tables and views is also available in this downloadable list of data sources.

(The full contents of database help may be downloaded as a tab-delimited file via the Database Help link on the Reports page.)

Note: At least one of the four key areas: Fields, Summary, Chart, Gauge must be configured for a report. (Settings on these four tabs are independent of each other with separate field selections for each one. Fields available for selection on these four tabs are from sources selected on the Data Sources tab.)