Configure Queries

Add a Query Report

Before you begin setting up query reports, consider if all Eagle Portal users can use the same reports, or if they require different reports. You can define the query report assignment via Sys Item 154 - Portal queries entitlement behavior in the System Parameters window as follows:

1. 0 (default value) - Use highest level behavior.

2. 1 – Use union behavior.

You can add one or more queries for each report type in the Eagle Portal. Once you add the query report, you need to associate it with a menu so that Eagle Portal users can select it.

To add a query report:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click Add Query.
   You see the Query Settings dialog box. Required fields are displayed in bold.

6. Complete the options on the Query Settings dialog box.

7. Click Next.

8. Click Finish.
   You have added a query report.
   By default, the query report you are creating is assigned to you. You must assign it to others who should have access to it on the Assign Queries dialog box. Best practice for assigning query reports is to assign them at business group levels if your users require different query reports.

Manage ACCESS DATAMART Queries

If your organization has implemented Data Mart, you can build portal queries using Data Mart's summary model, grouped models, or detailed models. To do this, select the ACCESS DATAMART FUND REPORT type when configuring the query in the Portal Query Explorer.

The Data Mart Fund Summary model is used for displaying portfolios your users have access to in the Eagle Portal's Portfolios window. You can use System Item #152, to control the behavior of Current date evaluation on the ACCESS DATAMART FUND REPORT type, where the Data Mart instance used is in the same database instance.

You can set System Item #152 in the System Parameters to:

• 1. Default value. Set to 1 to enable the Current functionality. The maximum effective date for each portfolio is fetched.

• 0. Set to 0 to enable the functionality that existed pre-V11.0. The maximum effective date across portfolios is fetched.

Display an ACCESS DATAMART FUND REPORT Query

To display an ACCESS DATAMART FUND REPORT query:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click Add Query.
   You see the Query Settings dialog box.

6. In Type, select ACCESS DATAMART FUND REPORT.
   The ACCESS DATA MART FUND REPORT type is based on the Data Mart Fund Summary model. The available fields are determined by the fields in the model.

7. Select the Data Mart instance you want to use. For this query type, Data Mart Fund Summary model is used.

8. Define a filter for this query, if needed. The access for query is enabled by default.

9. In Report Fields, click the ellipsis to select the fields from the Data Mart Fund Summary model that you want to use for this report.
   You see the Report Field Selection dialog box.

10. Click Add and select any fields. Use the + and - buttons to arrange the order of fields.

11. Click OK.
    You see the Query Settings dialog box.

12. Click Next.
    You see Assign Queries dialog box. This query type is assigned to everyone by default. If this query type cannot be used by everyone, you can create another one.

13. Click Finish.

14. Go to the User Settings Explorer window. By default, Default User is selected as Current user.
    If this query can be used by everyone in your organization, then set My Portfolios Query to use this ACCESS DATAMART FUND REPORT type you have just configured.

15. If the same query cannot be used by everyone in your organization, and you have configured multiple queries for this query type, click the Current User link.
    You see the Select User/Group dialog box. The best practice is to configure this at business group level, so each business group has its own ACCESS DATAMART FUND REPORT type query.

16. Select Portal Login.

17. In My Portfolios Query, from the drop down menu, select the query report you just added using ACCESS DATAMART FUND REPORT type.
    If the Eagle Portal users cannot use the same query report, click the Current User link and select the user/business group. Then, select the query to use from the drop down menu.

18. Click Save.

Add an ACCESS DATAMART FUND VIEWS Query

The ACCESS DATAMART FUND VIEWS query type displays summary level data in a Data Mart Fund Summary model. You can use the following view types for viewing Data Mart summary level data:

• Vertical View. Displays multiple columns of data for one or more entities.

• Multi-line View. Displays multiple columns of data for one entity. However, instead of displaying all of the columns as one row, each row is designated a position on a certain row.

• Time Series View. Displays rows of data for one entity. Each row represents a different effective date. If you set View Type to Time Series View, you can add/remove multiple Report Fields in the Datamart Field Selection dialog box.

Best practice is to use this query type for creating dashboard parts, and then including the dashboard parts into a dashboard view for the Eagle Portal users.

To add an ACCESS DATAMART FUND VIEWS query:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click the Add Query link.

6. Complete the options on the Query Settings dialog box and click Next.

7. For this example, select Vertical View from the View Type drop down list.
   You see the Query Result Configuration dialog box. Note the Display Dimensions section in the following figure.

8. In Rows, specify the number of rows you want to display in your query results and click Apply.
   The value for Columns is set to 1 and cannot be modified because the display dimensions for the Vertical View are for a single entity. However, you can run this query report for more than one entity to view display dimensions for multiple entities.
   You see four rows below the Display Dimensions section on the Query Result Configuration dialog box.

9. Click the ellipsis to the right of the first cell in the first blank row.
   You see the Field Setup dialog box.

10. In Cell Type, select Title.

11. In Text Value, enter the text you want to use as the column header in your query results, and click OK.

12. Click the ellipsis button to the right of the second cell in the first row.
    You see the Field Setup dialog box.

13. In Cell Type, select Value.

14. In Field Name, select a field from the drop down list.

15. Accept the remaining default values and click OK.

16. Repeat these steps to complete the remaining rows in the Query Result Configuration dialog box.

Add an ACCESS DATAMART Query

The ACCESS DATAMART type can be used for viewing Data Mart’s fund summary, group level or detailed models. It can also be configured so that users can view data in a transposed time series view. Each column of data represents a different effective date of data. Since this view displays a different date for each column, it requires one date rule per column.

To add an ACCESS DATAMART query:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click Add Query.
   You see the Query Settings dialog box.

6. Complete the options on the Query Settings dialog box and click Next.

7. Click the ellipsis in Report Fields.
   You see the Report Field Selection dialog box.

8. Specify a date rule for the fields by clicking the ellipsis in the As Of column.
   The ability to assign specific date rules to individual Data Mart fields allows you to be more efficient in your storage of data in the Data Mart. Data values that are unchanged from past dates, such as Prior Year Return, do not have to be recalculated daily to be shown in Portal next to data fields that require daily recalculation, such as Year to Date Return.
   After clicking the ellipsis, you see the Portal Date Rule Selector dialog box.

9. Select the date rules and click OK.
   You see report results after running the query in the Eagle Portal.

   Open

   

About the EAGLEMART Packaged Portal Content

EAGLEMART is a Data Mart instance used by Eagle Packaged Reports. This Data Mart acts as a staging area for Eagle provided metadata and Eagle Portal contents.

In Packaged Portal Content, EAGLEMART is used by default as the Data Mart Instance that queries use to access Data Mart tables. While you can build EAGLEMART tables, this is not best practice. You should leave the EAGLEMART schema as read-only so Eagle can add content without overwriting any of your customizations. Best practice is to use another Data Mart schema, such as DATAMARTDBO or another schema your Database Administrator sets up for you.

In pace_masterdbo.portal_reports, you can update the column READ_ONLY with a value of Y. This makes the migrated in queries from EAGLEMART Packaged Portal Contents read only.

To see how a read only query is configured, click View Query in the Portal Query Explorer. You can configure the copied queries to use another Data Mart instance, such as DATAMARTDBO.

Manage the ACCESS SQL QUERY Type

The ACCESS SQL QUERY type allows you to distribute any data stored in the Eagle data model or any other database through the Eagle Portal. The option to specify any SQL query as the data source makes this query unique. Like other queries, you can specify an entity and effective date. The information is passed to the query in the form of parameters that are substituted during run time. You can also use this query type as a part in the dashboard. When entity type List is used in ACCESS SQL QUERY, the Eagle Portal enumerates for the list.

Set Up Column Values

In the Maintain Options/Parameters window, you can select a database table and column so that its value is displayed in the Eagle Portal and also passed to the ACCESS SQL QUERY type queries. You can do this if you do not want to set up or use the codes the Codes window.

Before setting up an ACCESS SQL QUERY type:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Options/Parameters in the Start Search text box.

3. Click the Maintain Options/Parameters (Reporting Center) link.
   You see the Maintain Options/Parameters window.

4. To access the Add/Edit Option dialog box, click Add or Edit.

5. In the Type field, select Column Value.

6. Select the Required check box in the Properties section if you want the Eagle Portal users to always select a code value before they can run a query.

7. Specify the Database, Table, and Column for the value that is to display in the Eagle Portal and be passed in the query.

   Open

   

8. If you have already created the stored procedure in the database, go to the Portal Query Explorer to create the query for the ACCESS SQL QUERY type. When creating the query, make sure you select the option you have created.

Set Up Codes

If you want to use a code value’s long description, set up the code in the Maintain Options/Parameters window.

Complete this procedure before setting up an ACCESS SQL query:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Options/Parameters in the Start Search text box.

3. Click the Maintain Options/Parameters (Reporting Center) link.
   You see the Maintain Options/Parameters window.

4. To access the Add/Edit Option dialog box, click Add or Edit.

5. Select the code you want to use from the Code drop down and select the Use Long Description check box.
   When you use a long description, the Eagle Portal users see that description as an input for the query. When your users are logged in to the Eagle Portal, they see the long description displayed as an input for the query.

You can set up the stored procedure parameters in the Maintain Options/Parameters window. These values should already be defined before you set up the ACCESS SQL QUERY report.

You can create the stored procedures in the Oracle or SQL database. Refer to Appendix: Stored Procedure Parameters and Examples, for templates you can follow.

Add an ACCESS SQL QUERY Type

To add an ACCESS SQL QUERY type:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. In the Portal Query Explorer, click Add Query.
   You see the Query Settings dialog box.

6. Complete the options on the Query Settings dialog box. The inputs to the ACCESS SQL QUERY are dynamic. Date inputs may or may not exist, and there may be multiple date inputs. Make sure the stored procedure’s name is the name of the stored procedure you have created in the database. If the stored procedure name does not match, when you click Execute, an error message be displayed and you will not be able to complete the procedure.

7. To view the error logs, click View Log File.

8. To configure the ACCESS SQL QUERY type query, execute the stored procedure with test values to determine the columns that it returns.
   Sometimes the stored procedure may require certain values in order to execute properly. As a workaround, you can either add exception handling in your stored procedure, or make it always return the same columns so you will see the results you expect. For example:
   SELECT user_field12
INTO v_entity_id
FROM rulesdbo.entity
WHERE entity_id = iEntityId;
exception
when no_data_found then
v_entity_id:=' ';
end;

9. You can input the test parameters in the Stored Procedure Parameters dialog box when creating this query type. This allows you to change the test values the stored procedure requires for returning data.

10. Click Next.
    You see Stored Procedure Parameters dialog box. In the following example, the values available in the Option column are determined by what you have defined in the Options/Parameters window These values should already be defined before you begin setting up the ACCESS SQL QUERY report.

11. Click Next.
    You see the Stored Procedure Columns dialog box.
    The Analysis and Charting check boxes are enabled by default so you can use this query for analysis and charting.
    The number of columns configured here is the same as the number of columns that display in Portal after the user runs the query. In this example, there are four columns: effective date, primary index YTM, secondary index YTM, and security YTM. After the user runs this query in Portal, the record will always return these four columns.

12. Double click each row in the Stored Procedure Columns dialog box.
    When you double click a row, you see the Column Configuration dialog box for that column.

13. Complete the fields in the Column Configuration dialog box.
    Description: The description you enter is the description that the user sees in Portal.
    XML Tag: Enter a unique XML tag. The value you enter must be unique.
    Type: The type Character is displayed by d

14. Click OK to close the Column Configuration dialog box.

15. Repeat steps 7 and 8 for each row.

16. Click Finish.

Manage the ACCESS MICROSTRATEGY REPORT Query

You can use the ACCESS MICROSTRATEGY REPORT query type to run a report or document created in MicroStrategy Web. The ACCESS MICROSTRATEGY REPORT type is unique because MicroStrategy documents support interactivity. However, you cannot change and save a MicroStrategy query in the Eagle Portal.

The information for the MicroStrategy query is stored as meta data in its own database. When you run the MicroStrategy query, a call is made to the MicroStrategy server to process the data and then it is passed back to the application server. Data criteria are used in the same way as an ACCESS SQL QUERY, where the data inputs are options and parameters.

Before adding a query, make sure you have created options and parameters to match the prompts you have created in your MicroStrategy reports.

MicroStrategy reports typically require one or more inputs that you can set up in the Maintain Options/Parameters window. After these values are set up, you can add these values to the query via the Portal Query Explorer.

Add an ACCESS MICROSTRATEGY REPORT Query

To add an ACCESS MICROSTRATEGY REPORT query:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click Add Query.
   You see the Query Settings dialog box.

6. Complete the options on the Query Settings dialog box.

7. Click Next.
   If the MicroStrategy report contains prompts, you see the Report Parameters dialog box.
   The values in the Option column of the Report Parameters dialog box are determined by what you have defined in the Maintain Options/Parameters dialog box. These values should be defined before you begin setting up the ACCESS MICROSTRATEGY REPORT query.

8. Click Next.
   You see the Assign Queries dialog box.

9. Assign the query to selected users, a group, or everyone.

10. Click Finish.

Set Up Issuer Selector

You can use the Issuer Selector for searching issuers in the Eagle Portal. Before setting up a query, select the Issuer in the Maintain Options/Parameter window.

To set up the Issuer Selector:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Options/Parameters in the Start Search text box.

3. Click the Maintain Options/Parameters (Reporting Center) link.
   You see the Maintain Options/Parameters window.

4. To access the Add/Edit Option dialog box, click Add or Edit.

5. Select Issuer from the dropdown list for Type.

6. Click OK.

Manage the ACCESS ISSUER SELECTOR Query

The ACCESS ISSUER SELECTOR query type is an internal query and does not display as a menu item.

To set up an ACCESS ISSUER SELECTOR query:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click Add Query.
   You see the Query Settings dialog box.

6. Select a field rule to use for this query type
   The fields used in the field rule will be used in the Eagle Portal when searching for issuer.

7. Click Next.
   You see the Assign Queries dialog box. On the Assign Queries dialog box, you can assign the query to Everyone, Users or Groups.

8. If one ACCESS ISSUER SELECTOR type query can be used for all your users, select the Assign this query to everyone checkbox and click Finish. Continue by creating the query using ACCESS SQL QUERY type.

9. If the Eagle Portal users cannot use the same ACCESS ISSUER SELECTOR type query, create multiple ACCESS ISSUER SELECTOR type queries and assign them to various groups. Once completed, in the User Settings Explorer, assign the different ACCESS ISSUER SELECTOR type queries to business groups. Configure Internal Queries for groups in the User Settings Explorer, as shown in the following figure.

10. Click the Current User: Default link in the upper right.
    You see the Select User/Group dialog box.

11. To configure for a group, click Group and then select the group.

12. Click the dropdown arrow to select the query you have configured in the Portal Query Explorer to be used for the internal query.

13. After configuring for ACCESS ISSUER SELECTOR, create a query using ACCESS SQL QUERY type and pass the Issuer Selector as an input.
    Make sure you have first created the stored procedure in the database. When you create an ACCESS SQL QUERY, you are prompted to enter the name of the stored procedure in Query Setting's Procedure field.

14. Click OK.
    You see the Query Settings dialog box.

15. Click Next.

16. In Stored Procedure Parameters, click the ellipsis (….).
    You see the Parameter Option Configuration dialog box.

17. Select the option you created in the Maintain Options/Parameters window and click Next.
    You see the Stored Procedure dialog box.

18. Click the ellipsis (….).
    You see the Parameter Option Configuration dialog box.

19. Select the option you created in the Maintain Options/Parameters window and click Next.
    You see the Stored Procedure Columns dialog box. The fields in this dialog box are the fields that are returned by the stored procedure after the query runs.

20. Double click any cell in the grid.
    You see the Column Configuration dialog box.

21. Enter the Description text, select the Type for this field from the drop down, and the precision if the field is a number type. For each field used, make sure its XML tag is unique.

22. Click OK and then click Next.
    You see the Assign Queries dialog box.

23. Assign the query to Everyone, User or Groups.
    Once the query is assigned, your users can use the Issuer Selector in the Eagle Portal.

Manage Filters

The Query Settings dialog box allows you to filter data returned from a query. For example, you can define a filter that restricts users to seeing only securities with a specific market value in their accounts. Additionally, you can define a filter that queries data for specific entities only.

Define a Query Filter

To define a query filter:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click to select the report you want to set a filter for and click Edit Query.

6. In Filter, click the ellipsis.
   You see the Define Filter dialog box.

7. Define the filter criteria and click OK.

8. Click OK.
   You see the Query Settings dialog box.

9. Click Next and click Finish.
   The filter is applied to the specified query report.

Define an Entity Filter

To define an entity filter:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click to select the report you want to set a filter for and click Edit Query.

6. In Entity Filter, click the ellipsis.
   You see the Define Filter dialog box.

7. Define a filter that queries data for specific entities only and click OK.
   You see the Query Settings dialog box.

8. Click Next and click Finish.
   The filter is applied to the specified report.

Define Data Sources

You can define a data source when you add or edit a query report. The Sources option in the Query Settings dialog box allows you to define the source of query data, such as performance, positions, transactions, entities, and so on, and the order in which the system searches the data sources.

To define one or more data sources:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click to select a report and click Edit Query.
   You see the Query Settings dialog box.

6. In Sources, click the ellipsis.
   You see the Define Report Sources dialog box.

7. Complete the options in the Define Report Sources dialog box:
   Current Source Definition: Displays the current source definition. Sources are displayed in Available Sources. Use the right and left arrows to add and remove sources.
   Source Priority: Allows you to add and delete a source priority. You can add a source priority by clicking Add and selecting the priority. You can delete a priority by selecting the priority and clicking Delete. If you delete a priority, all sources associated with that priority are deleted. If you delete a higher priority, the next lower priority assumes the deleted priority’s level.
   Entity Source: Allows you to query an entity or a combination of data sources and entities by clicking Add. If you query an entity, all entities are queried after the data sources. Also, if you choose to query more than one entity, each entity you add is appended to the end of the source definition. To delete entities from Current Source Definition, select the entity and click Delete in Entity Source.
   Available Sources: Allows you to select one or more data sources using the right arrow.

8. Click OK.
   You have defined the data sources for a query.

Select a Business Calendar

You can configure your report query to use a business calendar. The Business Calendar option in the Query Settings dialog box allows you to specify the dates on which a user can perform a query. Before you can use a business calendar in the Eagle Portal, you have to set up a

business calendar in the System Management Center’s Business Calendar window.

To select a business calendar for a query:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Click to select a query and click Edit Query.
   You see the Query Settings dialog box.

6. In Business Calendar, click the ellipsis.
   You see the Define Query Business Calendar dialog box.

7. Select the business calendar from the drop down list and click OK.
   You see the Query Settings dialog box.

8. Click Next and click Finish.
   The business calendar is applied to the query. Eagle Portal users are restricted to running queries for business calendar dates only.

Block Access to a Query

The Access option in the Query Settings dialog box determines whether the report is accessible to your user. You can block access temporarily, due to data integrity issues, maintenance schedules, or for other reasons.

To block access to a query report:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Select an existing query and click Edit Query.

6. In Access, click the check box to disable access.

7. Click Next and click Finish.
   The user will no longer see this query report in the Eagle Portal.

Set Maximum Number of Rows Returned for Query Result

You can limit the number of rows returned for a query report when you add or edit a query report. The Maximum Rows field on the Query Settings dialog box allows you to limit the number of rows returned. If you do not enter a value for the Maximum Rows field, the number of rows returned defaults to the value set for the Default Query Row Limit in the Global Settings Explorer.

You can also limit the number of rows returned for a query report using the Global Settings Explorer window.

To specify the number of rows returned for a query in the Query Settings dialog box:

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Portal Administration in the Start Search text box.

3. Click the Portal Administration (Reporting Center) link to access Portal Administration.
   You see the Portal Administration window.

4. Click Yes in the Do you see the Eagle logo here? dialog box.
   You see the Portal Query Explorer window as the default.

5. Select an existing query and click Edit Query.

6. In Maximum Rows, enter the number of rows you want returned.

7. Click Next and click Finish.
   If a query report exceeds the specified limit, you see a message stating the maximum size was exceeded. You can define filter criteria to limit the query data and rerun the query report.