Complete the Data Source tab

This topic provide details on how to complete (or update) the Data Source tab of the Data Source : <Data Source Name> window.

  1. Set Use Bind Variables.

    Set the Use Bind Variables option to Yes, except when:

    • Any of the filter fields are multi-select–enabled.

    • The portlet query statement includes tokens.

  2. Click Edit Query. The Query Definition window opens.

  3. Provide the following information before proceeding to the next step.

    Example: The following is an example of a complete SQL query for a portlet data source: 

    SELECT OBJECT_TYPE, OBJECT_NAME, REVISION_NUMBER
FROM KACC_PSFT_VC_V
WHERE LOCKED_FLAG="Y"
ORDER BY OBJECT_TYPE

  4. (Optional) Click View Full Query to see the entire SQL query—the composite of the information from each of the tabs.

  5. Click Apply to commit any interim changes. Click OK to save your changes and close the window.

Back to top

Select/From

You use the Select/From tab of the Query Definition window to:

  • Add the columns for the SELECT portion of the portlet data source query
  • Add the FROM clauses for the portlet data source query

To complete the Select/From tab in the Query Definition window:

  1. Add a new column.

    1. Click Add in the Select section. The Data Source Column: New window opens.

    2. The information that should be provided depends on the Column Type. See Details for data source column types for the details associated with each column type.

    3. Click Save to commit any interim changes. Click OK to save your changes and close the window.

  2. Repeat step 1 until you add all the desired columns.

  3. In the From Clause section of the Select/From tab of the Query Definition window, define the table from which the columns are selected.

    This is a free-form SQL entry area. You can Click Enlarge to create a larger text entry field.

  4. Click Apply to commit your changes. Click OK to save your changes and close the window.

Back to top

Where/Filter

You use the Where/Filter tab of the Query Definition window to:

  • Add the WHERE clause to the SQL query. The WHERE clause is used to specify the selection criteria.
  • Specify the filter fields that appear on the portlet's edit page.

To complete the Where/Filter tab in the Query Definition window:

  1. In the Where Clause section, define the WHERE clause of the portlet query.

    This is a free-form SQL entry area. You can click Enlarge to create a larger text entry field.

  2. Add new filter fields.

    Note: Each filter field is appended to the WHERE clause of the portlet query, but only when the filter field is being used by a PPM Dashboard user.

    Filter field values can be used in the SQL by using the token name P.TOKEN_NAME or VP.TOKEN_NAME.

    To add a new filter field:

    1. Click New in the Filter Fields section.

      2. Note: Adding filter fields here has the same results as adding filter fields using the Filter Fields tab as the Data Source : <Data Source Name> window.

      The difference in the two areas is how the information is visually displayed.

      • In the Filter Fields section of the Query Definition window, the SQL clauses are displayed.

      • On the Filter Fields tab of the Data Source : <Data Source Name> window, the PPM entities are displayed.

    2. Provide the data as detailed in Complete the Filter Fields tab.

    3. Click Add to commit any interim changes. Click OK to save your changes and close the window.

  3. Click Apply to commit your changes. Click OK to save your changes and close the window.

Back to top

Group By/Order By

You use the Group By/Order By tab of the Query Definition window to add GROUP BY, HAVING, or ORDER BY clauses for the portlet data source query.

To complete the Group By/Order By tab in the Query Definition window:

  1. In the Group By Clause section, provide the GROUP BY or ORDER BY terms.

    This is a free-form SQL entry area.

    • GROUP BY: Adds a keyword to aggregate the result.

    • ORDER BY: Adds a keyword to sort the result.

  2. In the Having Clause section, provide the HAVING terms.

    This is a free-form SQL entry area.

  3. In the Default Order By Clause section, provide the default ORDER BY terms.

    This is a free-form SQL entry area.

    Limitation:

    If the data source SQL is in the following format: 

    SELECT A, B
FROM Table_C
WHERE X=Y
ORDER BY D, E

    and if E is nullable or has non-unique values, custom porlters return unpredictable results when sorted by columns containing duplicated values.

    This issue is caused by a known limitation of Oracle row_number() function.

    To work around this issue, you should add additional columns with unique values or rowid of the tables in the "from" clause to the "order by" clause. For example:

    SELECT A, B
FROM Table_C
WHERE X=Y
ORDER BY D, E

    and E should be rowid or a column in Table_C with unique value.

    Note: You can add more than one column to the "order by" clause when no column with unique value or rowid is applicable. For performance sake, it is recommended that you add columns with index.

  4. Click Apply to commit your changes. Click OK to save your changes and close the window.

Back to top