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
View data sources
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.
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.
Select and unselect data sources
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.
Set the report as Distinct
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.
Set the number of preview records
The amount of data in preview panel under the relationship list can be configured by selecting from Preview Records drop-down at the top.
Save the report
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.
NOTE: The option to save as Report is only available after at least field is added to the report from the Report Designer/Design page.
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.
NOTE: The report name will be invalid if it has been given to another report in the same category
Add and remove relationships
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.
NOTE: For more information about different Join Types click here.
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).
Add Key Join Relationship
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.
NOTE: An example scenario that would require a Key Join is where you want to get a list of KPIs which are linked at either Goal or Outcome Levels. In this case you would use an OR for the Operator.
Add Join Alias
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:
Foreign Data Object
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.