View data sources

1. In browser, log in to Izenda as a user with Report permission.

2. Click New, then Data Source in the left menu.

   

3. Visible tables, views, or stored procedures from all connections 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 collapse each category to provide additional screen real estate to uncollapsed categories.

   

Search for data sources

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

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

Select and unselect data sources

1. Tick the checkbox on the right of data sources to select them.

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

2. Untick the checkbox on the right of data sources to unselect them.

   Relationships with unselected data sources will be automatically removed from the relationship list in Content Panel.

   

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

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

   

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

3. Select to save as Templates.

   The option to save as Reports is only available after any field is defined in Report Designer/Design page.

4. 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 name in and pressing Enter.

     

   • Click the x icon to clear the existing one.

     

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

6. Click OK to save the report.

   The report name will be invalid if it has been given to another report in the same category

   

   including Uncategorized.

   

Add and remove relationships

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

• With most properly designed databases, relationships are already available, so after selecting data sources the relationship list will have been fully populated.
• However, relationships need to be defined for some cases such as ad hoc queries or cross database joins.

For example, from Northwind database, user needs an ad hoc report to find out if any supplier happens to be in one of the territories. The join that user needs to build is [Suppliers].[City]=[Territories].[TerritoryDescription].

1. Select Suppliers and Territories in the Middle Panel.

2. The relationship list remains empty and the report cannot be saved.

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

4. Select Inner in Join Type drop-down.

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

6. Select data so that the row reads: |Category|Suppliers|City|=|Category|Territories|TerritoryDescription|

7. Click Validate Syntax button and see success message.

   

To quickly add relationship, user can copy an existing one then modify it by clicking the Copy icon (that looks a bit like this ❐).

No longer needed relationships can also be removed by clicking the Remove icon (X).

Add Key Join Relationship

A single column is needed for the example join above. There are rare cases when multiple columns are needed in a join. For example, from Northwind database, user needs an ad hoc report to list out the products that are used in a single order. One way to do that is to compare the Quantity in the order with the UnitsOnOrder of the product. The join that user needs to build is [OrderDetails].[ProductID]=[Products].[ProductID]AND[OrderDetails].[Quantity]=[Products].[UnitsOnOrder].

1. Select Order Details and Products in the Middle Panel.

2. The relationship list is populated with the existing relationship [OrderDetails].[ProductID]=[Products].[ProductID].

3. Click the Add Key Join icon in Action, a blank new row is inserted under that existing relationship.

   

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

5. Select data so that the row reads: |And|OrderDetails|Quantity|=|Products|UnitsOnOrder|

6. Click Validate Syntax button and see success message.

   

Add Join Alias

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

• For example, to get data of Products and related Categories and Suppliers, the [Products] table needs to be joined with:

  • [Categories] table [Products].[CategoryID]=[Categories].[CategoryID]
  • [Suppliers] table [Products].[SupplierID]=[Suppliers].[SupplierID]

  
  The [Products] table is used twice and therefore should be given an alias as below:

  

  That is equivalent to the following SQL statement:

  SELECT*FROM[Products]ASPRDINNERJOIN[Categories]ONPRD.[CategoryID]=[Categories].[CategoryID]INNERJOIN[Suppliers]ONPRD.[SupplierID]=[Suppliers].[SupplierID]

• Effects of alias in a key join: if the original table is given an alias, that alias must be selected in the key join.

  

  Fig. 182 The alias OD must be selected instead of the original table name Order Details
  

  That is equivalent to the following SQL statement:

  SELECT*FROM[OrderDetails]ASODINNERJOIN[Products]ONOD.[ProductID]=[Products].[ProductID]ANDOD.[Quantity]=[Products].[UnitsOnOrder]

• An alias is also required in case of a self-join. For example, [Employees].[ReportsTo] is foreign key to [EmployeesID] in the same table, hence, an alias must be given to differentiate the two different [Employees] tables.

  

  That is equivalent to the following SQL statement:

  SELECT*FROM[Employees]ASSubordinateINNERJOIN[Employees]ONSubordinate.[ReportsTo]=[Employees].[EmployeeID]