The Report Designer/Data Source page allows users to.

• view, search and select data sources to be used in a report.

• set up and validate relationships between data sources in a report.

Open

Figure 1.1

Click the new report (+) icon on the left hand menu and you will be directed to the Data Source page.

Visible tables, views, or stored procedures provided to you by CAMMS will be displayed in the Middle Panel.

The categories are expanded by default so the user can see the data sources inside. The user can click on each category name to collapse it and easily access categories presented below. 

Open

Figure 2.1

In the Report Designer, it is recommended to collapse the left menu to have maximum screen space for the design. 

The list of data sources can grow very large over time. In this case, the Search box will help you to quickly find specific items.

Type a partial name and click the search icon. Only data sources with matching names or matching field names will be displayed. 

Click on the + icon on the right of the data sources to select them. Selected data sources will be listed separately on top.

Existing relationships between selected data sources will be automatically added to the relationship list in Content Panel.

Click on the bin icon on the right of the data source name, under the 'Selected Data Sources' area to remove a data source.

Relationships with deleted data sources will be automatically removed from the relationship list in Content Panel, upon confirmation. 

The Distinct checkbox on top of Middle Panel if ticked will force the report to return unique values only.

Leave it unticked to allow duplicated values in the result.

The amount of data in preview panel under the relationship list can be configured by selecting from Preview Records drop-down at the top.

Click Save button at the top to open the Save pop-up.

If the report has been saved already then there is another option to Save As a new one. 

Enter the name for the report in the Report Name box.

Select to save as Templates.

Select a category for the report in Category drop down. Type a partial name to quickly search for the category.

A new category can be created in-place by typing the desired name and pressing Enter. 

Similarly select a sub-category for the report in Sub-Category drop down.

Click OK to save the report.

To run a report from more than one data source, relationships among the data sources must be available or defined to join the data sources together.

In most cases, default relationships will already be available, so after selecting data sources the relationship list will have been fully populated. However, relationships need to be defined for some cases where the number of possible joins are too great to be set as default.

For example, you need to create a join manually in a case where you want to get the KPIs which are linked to Goals - this means that the Planning_KPILinks_STND must be joined with Planning_PlanningHierarchy_STND. This is usually not setup as a default because a KPI can link to Goal, Outcome or Strategy levels. When you wish to retrieve the KPIs linked to Goals only, you can build the following join relationship manually.

[KPILinks_STND].[GOALID] = [Planning_PlanningHierarchy_STND].[GOALID].

Select the two data sources in the Middle Panel. The relationship list remains empty and the report cannot be saved while in this state.

Click Add Relationship button, a blank new row is inserted into the list.

Select Left in the Join Type drop-down.

Select values in Category, Data Object, Join Field, then Category, Foreign Data Object and Field in left-to-right sequence for data to populate correctly.

Select data so that the row reads: | Planning | KPILinks_STND | GOALID | = | Planning | Planning_PlanningHierarchy_STND | GOALID |

Click Validate Syntax button and see success message. 

To quickly add more relationships, you can copy an existing one by clicking the copy icon and then modify it.

Relationships which are no longer needed can be removed by clicking the remove icon (X).

A single column is needed for the example join above. There are some cases when multiple columns might be needed in a join. In these cases, you would create your first join between the two data sources and then click the add key join icon. Now a blank new row is inserted under that first relationship.

Select values in Operator, Category, Data Object, Join Field, then Category, Foreign Data Object and Field in left-to-right sequence for data to populate correctly.

Click Validate Syntax button and see success message.

The joined tables can be given alias to be referred to in subsequent join clauses.

For example, to get data of Parent Projects and Child Projects, the Project_ProjectDetails_STND dataset needs to be joined with itself:

Since the Project_ProjectDetails_STND datset is used twice it should be given a meaningful alias as shown - this allows you to differentiate between say the Project Owner of a Parent Project vs the Project Owner of a Child Project.