Report Designer/Data Source

The Report Designer/Data Source page allows user to

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

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

../_images/Report_Designer.png

View data sources

  1. ../_images/Report_Designer_Data_Source_Location.png

    Fig. 92 Report Designer - Data Source

    In browser, log in to AgilePoint NX as a user with Report permission.

  2. Click New, then Data Source in the left menu. (Fig. 92)

  3. ../_images/Report_Designer_Data_Source_Middle_Panel.png

    Fig. 93 Report Designer - Data Source List

    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. (Fig. 93)

Note

../_images/Report_Designer_Collapse_Left_Menu.png

Fig. 94 Report Designer - Collapse Left Menu

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

Search for data sources

../_images/Report_Designer_Data_Source_Middle_Panel_Search.png

Fig. 95 Report Designer - Data Source Search

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. (Fig. 95)

Select and unselect data sources

  1. ../_images/Report_Designer_Select_Data_Source.png

    Fig. 96 Report Designer - Select Data Source

    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. (Fig. 96)

Note

Analytics Center supports using stored procedures as data sources. Just make sure that the account in connection string has necessary permissions for this case, see Connector Permissions.

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. ../_images/Report_Designer_Save_As.png

    Fig. 97 Report Designer - Save As

    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. (Fig. 97)

  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.

    • ../_images/Report_Designer_Save_Quick_Search_Category_Name.png

      Fig. 98 Report Designer - Search Category

      Type a partial name to quickly search for the category. (Fig. 98)

    • ../_images/Report_Designer_Save_With_New_Category.png

      Fig. 99 Report Designer - New Category

      A new category can be created in-place by typing the name in and pressing Enter. (Fig. 99)

    • ../_images/Report_Designer_Save_Clear_Category.png

      Fig. 100 Report Designer - Clear Category

      Click the x icon to clear the existing one. (Fig. 100)

  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

    ../_images/Report_Designer_Save_Duplicated_Name.png

    Fig. 101 Report Designer - Duplicated Name

    including Uncategorized.

    ../_images/Report_Designer_Save_Duplicated_Name_Uncategorized.png

    Fig. 102 Report Designer - Duplicated Name 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. (Fig. 103)

    ../_images/Report_Designer_Data_Source_Validate_Syntax_Success.png

    Fig. 103 Report Designer - Validate Syntax Success

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 [Order Details].[ProductID] = [Products].[ProductID] AND [Order Details].[Quantity] = [Products].[UnitsOnOrder].

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

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

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

    ../_images/NW_Order_Details_Product_Add_Key_Join.png

    Fig. 104 Report Designer - Add Key Join

  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 | Order Details | Quantity | = | Products | UnitsOnOrder |

  6. Click Validate Syntax button and see success message.

    ../_images/NW_Order_Details_Product_Key_Join_Quantity_UnitsOnOrder.png

    Fig. 105 [Order Details].[Quantity] = [Products].[UnitsOnOrder]

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:

    ../_images/Table_Alias_Products_Categories_Suppliers.png

    Fig. 106 The alias PRD must be selected instead of the original table name Products

    That is equivalent to the following SQL statement:

    SELECT *
    FROM
       [Products] AS PRD
       INNER JOIN [Categories]
          ON PRD.[CategoryID] = [Categories].[CategoryID]
       INNER JOIN [Suppliers]
          ON PRD.[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.

    ../_images/Table_Alias_Key_Join_OrderDetails_Products.png

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

    That is equivalent to the following SQL statement:

    SELECT *
    FROM
       [Order Details] AS OD
       INNER JOIN [Products]
          ON OD.[ProductID] = [Products].[ProductID]
             AND OD.[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.

    ../_images/Table_Alias_Self_Join_Employees.png

    Fig. 108 The alias Subordinate must be given

    That is equivalent to the following SQL statement:

    SELECT *
    FROM
       [Employees] AS Subordinate
       INNER JOIN [Employees]
          ON Subordinate.[ReportsTo] = [Employees].[EmployeeID]