Skip to main content
insightsoftware Documentation insightsoftware Documentation
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

Add Joins

When you add a join, you will first select the detail view and then the master view that contains the standard or rowid column-based master key.

To add a join

  1. On the NoetixViews Workbench home page, click Modify a View. The Modify View page appears.

  2. Select a detail view, and then click Modify. For more information, see Select a View.

  3. On the Modify View » <view label> page, under View, click Add, modify keys and joins, and then click Next. The Modify View » <view label>» Add, modify keys and joins page appears.

  4. Under Joins to other views, click the Add Join for View link. The Add Join for View<detail view label> dialog box appears.

  5. Select a master view to which the detail view needs to be joined. The master views that are displayed contain either standard or rowid column-based master keys. In the case of rowid column-based master keys, only those master views will be displayed for which the tables underlying the master keys are also present in all active queries of the detail view. The list displays the following information:

    • View Label: Displays the label of the master view.

    • Managed: Indicates if the master view is being managed in the current environment.

    • Current revision: Indicates the current revision number of the master view, if the view is a managed one in the environment.

    • Updated date: Indicates the date and time when the master view was added or last modified.

    • Updated by: Indicates the name of the user who added the master view or last modified the view.

    NOTE: If multiple pages are present in the list, use the page navigation controls to navigate through the pages. In the Rows list, you can specify the number of rows that you want to see on a page; by default, the page will display 10 rows. You can use the boxes under each column label to filter the information based on the specific value of the column.

  6. Click Next. The Add Join for View<detail view label> dialog box appears.

  7. Check the name of the selected view in the Master View Name box. If you want to select a different master view, click Change, and go to step 5; otherwise, proceed to the next step.

  8. In the Master Key Name list, select a master key based on a standard column or rowid column depending on your requirement. If you selected a rowid column-based master key, the Table Name box will be displayed; this field will be unavailable.

  9. In the Join Name box, provide a join name that is unique to the detail view. The join name can contain a maximum of 50 characters and can match with that of an existing join with different capitalization. The join name will be displayed by the BI tools and Noetix Search.

  10. In the Join Description box, provide a meaningful join description.

  11. If you need to specify more than one join between the detail view and master view, and if your target BI tool does not permit multiple joins between views, in the Master View Name Modifier (Presentation Layer) list, create a modifier. The list also displays modifiers that have already been used. By default, the master view name modifier will be set to "None". You will need to specify a modifier for each additional join between the views. When the view name modifier is specified, a copy of the view will be created in the presentation layer of the BI tool. In the detail view, the foreign key will use the Z$<view name modifier>$<master view label> format for a rowid column-based join.Preferably, do not use a modifier for the first join.

    NOTE: The master view name modifier that you create for each additional join must be unique and cannot be blank. The modifier must begin with a letter or number, can contain only letters, numbers, and underscores, and can contain a maximum of 20 characters. However, insigihtsoftwarerecommends that you restrict the view name modifier to a maximum of 10 characters to avoid any issues that may be caused by the length of the resulting foreign key label.

  12. In the Join Condition options, select one of the following:

    • Outer on Master: The report based on this join condition will return all rows of the detail view even if there are no associated rows in the master view.

    • Equi-join: The report based on this join condition will return only those rows of the master view that have associated rows in the detail view.

    • Outer on Detail: The report based on this join condition will return all rows of the master view even if there are no associated rows in the detail view.

  13. In the Cardinality options, select the cardinality for the join. For a standard column-based join, the options will be one-to-one and one-to-many if the master key in the master view returns a unique value in every row. Otherwise, the options will be many-to-one and many-to-many. For a rowid column-based join, the options will always be one-to-one and one-to-many.

  14. If you selected a standard column-based master key in step 8, perform the following; otherwise, go to step 15.

    1. In the Available Columns list, select a column of the detail view. The list includes only those columns that are present in all active queries of the view and does not include columns of the descriptive flexfield type and unsupported column types in NoetixViews Workbench.
    2. Under Column Mappings, click the Select button for the master key column to which you want to map the detail view column. The selected column will be listed under Join Column.
    3. Repeat steps a and b to map detail view columns with the remaining master key columns. Then, go to step 16.

    IMPORTANT: If multiple master key columns are present, you must provide mappings for all the master key columns to proceed to the next step; otherwise, an error message will be displayed.

  15. If you selected a rowid column-based master key in step 8, the Table Alias Options area will display the underlying table with aliases or columns of the special column type for every query in the detail view. Such columns will be displayed in the <column label> (Special Column Label) format. For table aliases, the table name will be displayed in parentheses next to the alias; if the table is contained in a base view, the base view name will be displayed in the parentheses. Select a table alias or special column label depending on your requirement. Error messages will be displayed if your selection does not meet any of the following conditions:

    • If you select a special column label, you must use the same column label for each query of the view.
    • You should not mix the selection of the table alias or special column label across the queries in the view.
    • You should not use the table alias or special column label that is already used in other rowid column-based joins to the same master view.

    NOTE: For a query, the table aliases will be listed in the alphabetical order, and by default, the first table alias will be selected. You can use the Query box to filter the information based on a specific query.

  16. Click OK. The Modify View » <view label> » Add, modify keys and joins page appears showing the new join under Joins to other views.

  17. In the Customization comments box, provide a summary of the customizations that you have performed. The customization comments can contain a maximum of 200 characters.

  18. Click Save.The customizations are saved, and the Modify View» <view label> page appears.

Now, you can choose to perform any other customizations on the same view or go to the home page.

Published:

Add Joins

When you add a join, you will first select the detail view and then the master view that contains the standard or rowid column-based master key.

To add a join

  1. On the NoetixViews Workbench home page, click Modify a View. The Modify View page appears.

  2. Select a detail view, and then click Modify. For more information, see Select a View.

  3. On the Modify View » <view label> page, under View, click Add, modify keys and joins, and then click Next. The Modify View » <view label>» Add, modify keys and joins page appears.

  4. Under Joins to other views, click the Add Join for View link. The Add Join for View<detail view label> dialog box appears.

  5. Select a master view to which the detail view needs to be joined. The master views that are displayed contain either standard or rowid column-based master keys. In the case of rowid column-based master keys, only those master views will be displayed for which the tables underlying the master keys are also present in all active queries of the detail view. The list displays the following information:

    • View Label: Displays the label of the master view.

    • Managed: Indicates if the master view is being managed in the current environment.

    • Current revision: Indicates the current revision number of the master view, if the view is a managed one in the environment.

    • Updated date: Indicates the date and time when the master view was added or last modified.

    • Updated by: Indicates the name of the user who added the master view or last modified the view.

    NOTE: If multiple pages are present in the list, use the page navigation controls to navigate through the pages. In the Rows list, you can specify the number of rows that you want to see on a page; by default, the page will display 10 rows. You can use the boxes under each column label to filter the information based on the specific value of the column.

  6. Click Next. The Add Join for View<detail view label> dialog box appears.

  7. Check the name of the selected view in the Master View Name box. If you want to select a different master view, click Change, and go to step 5; otherwise, proceed to the next step.

  8. In the Master Key Name list, select a master key based on a standard column or rowid column depending on your requirement. If you selected a rowid column-based master key, the Table Name box will be displayed; this field will be unavailable.

  9. In the Join Name box, provide a join name that is unique to the detail view. The join name can contain a maximum of 50 characters and can match with that of an existing join with different capitalization. The join name will be displayed by the BI tools and Noetix Search.

  10. In the Join Description box, provide a meaningful join description.

  11. If you need to specify more than one join between the detail view and master view, and if your target BI tool does not permit multiple joins between views, in the Master View Name Modifier (Presentation Layer) list, create a modifier. The list also displays modifiers that have already been used. By default, the master view name modifier will be set to "None". You will need to specify a modifier for each additional join between the views. When the view name modifier is specified, a copy of the view will be created in the presentation layer of the BI tool. In the detail view, the foreign key will use the Z$<view name modifier>$<master view label> format for a rowid column-based join.Preferably, do not use a modifier for the first join.

    NOTE: The master view name modifier that you create for each additional join must be unique and cannot be blank. The modifier must begin with a letter or number, can contain only letters, numbers, and underscores, and can contain a maximum of 20 characters. However, insigihtsoftwarerecommends that you restrict the view name modifier to a maximum of 10 characters to avoid any issues that may be caused by the length of the resulting foreign key label.

  12. In the Join Condition options, select one of the following:

    • Outer on Master: The report based on this join condition will return all rows of the detail view even if there are no associated rows in the master view.

    • Equi-join: The report based on this join condition will return only those rows of the master view that have associated rows in the detail view.

    • Outer on Detail: The report based on this join condition will return all rows of the master view even if there are no associated rows in the detail view.

  13. In the Cardinality options, select the cardinality for the join. For a standard column-based join, the options will be one-to-one and one-to-many if the master key in the master view returns a unique value in every row. Otherwise, the options will be many-to-one and many-to-many. For a rowid column-based join, the options will always be one-to-one and one-to-many.

  14. If you selected a standard column-based master key in step 8, perform the following; otherwise, go to step 15.

    1. In the Available Columns list, select a column of the detail view. The list includes only those columns that are present in all active queries of the view and does not include columns of the descriptive flexfield type and unsupported column types in NoetixViews Workbench.
    2. Under Column Mappings, click the Select button for the master key column to which you want to map the detail view column. The selected column will be listed under Join Column.
    3. Repeat steps a and b to map detail view columns with the remaining master key columns. Then, go to step 16.

    IMPORTANT: If multiple master key columns are present, you must provide mappings for all the master key columns to proceed to the next step; otherwise, an error message will be displayed.

  15. If you selected a rowid column-based master key in step 8, the Table Alias Options area will display the underlying table with aliases or columns of the special column type for every query in the detail view. Such columns will be displayed in the <column label> (Special Column Label) format. For table aliases, the table name will be displayed in parentheses next to the alias; if the table is contained in a base view, the base view name will be displayed in the parentheses. Select a table alias or special column label depending on your requirement. Error messages will be displayed if your selection does not meet any of the following conditions:

    • If you select a special column label, you must use the same column label for each query of the view.
    • You should not mix the selection of the table alias or special column label across the queries in the view.
    • You should not use the table alias or special column label that is already used in other rowid column-based joins to the same master view.

    NOTE: For a query, the table aliases will be listed in the alphabetical order, and by default, the first table alias will be selected. You can use the Query box to filter the information based on a specific query.

  16. Click OK. The Modify View » <view label> » Add, modify keys and joins page appears showing the new join under Joins to other views.

  17. In the Customization comments box, provide a summary of the customizations that you have performed. The customization comments can contain a maximum of 200 characters.

  18. Click Save.The customizations are saved, and the Modify View» <view label> page appears.

Now, you can choose to perform any other customizations on the same view or go to the home page.

For an optimal Community experience, Please view on Desktop