Work with Query Results
- Pamela Totas (Deactivated)
This section describes how to use the Query Tool to work with query results.
Change the Layout of Query Results
You can change the layout of the query data in several ways, as described in the following table:
Layout | Description |
|---|---|
Swap columns | You can move columns around by clicking on the middle of the column header (for example, the cell with the label Fund Return in it) and dragging it to a new position right or left. |
Rename columns | The Query Tool returns the data with columns named just like the Performance Analysis fields used in the query. For View 1, the flat data view, the Query Tool returns columns with the name of the entity appended to the name of the field attribute, which can be quite a long label. This convention is used so that you can identify the data that comes back the first time you run the query. You can rename the columns by right-clicking the column heading and selecting Edit Column Attributes from the menu, as long as you are the Owner/Creator of the query or if you have access at the User Security level to Edit Others Column Attributes. |
Change data precision | You can change the precision of numerical columns by right-clicking the column header and selecting Edit Column Attributes, as long as you are the Owner/Creator of the query or if you have access at the User Security level to Edit Others Column Attributes. Columns created in the Query Tool through operations are displayed at a maximum of 12 decimal places. As with all calculations in PACE, the operation are performed with the full precision available for the base columns (The PERFORM database is formatted to support a 24,12 format), but are rounded to 12 places maximum. If a precision greater than 12 is specified in the Edit Column Attributes, the value is rounded to 12 places and zeroes added to fill the specified decimal places. |
When you finish moving columns or editing column attributes, click Save to use them every time you do the query.
Work With the Query Tool Toolbar
The following figure shows the toolbar that becomes available after you submit a query report profile.
The following table describes the options available on the Query Tool toolbar.
Toolbar | Description |
|---|---|
Home | Returns you to the Performance Queries home page while retaining any query results in the window link. |
Resubmit | Allows you to re-query the database right from the results window; you do not need to return to the query menu. Click Resubmit and you can change any query profile parameters that you have access to and resubmit the query. Upon submission, the PACE Server returns the new results in a new spreadsheet tab; preserving the results from the previous query parameters. Your original parameters are not be overwritten or changed with a Resubmit, instead it works like a one-time override. To save the new parameters, click Save from within that result set. |
Operation | Lets you define statistical operations on the data, such as the calculation of cumulative multiperiod returns |
Data | Presents two other options, Group By and Explode. Group By allows you to change the grouping order of the query results. In the Group by window you can also specify the sorting order of each grouping level ascending or descending. Explode allows you to drill down to a specific report level. |
Export | Saves the results in a .CSV data file that opens in Excel. |
Save | Saves the query parameters, any operations, and any formatting that you have defined/changed. If you make changes and try to exit the Query Tool without saving, it asks you if you want to save your changes. |
Brings up the printer dialog where you can select a printer and set printing options. Printing is WYSIWYG in PACE, so if levels of the report are not expanded, the printed results do not include them. | |
Window | Allows you to switch back and forth between the results of different queries that were previously submitted. Provides options to tile all query results listed either horizontally or vertically. Also provides an option to close all query results. |
Close | Closes the query result set you are currently viewing. If any changes have been made to the query parameters or results, you are asked if you want to save them. |
Several other options are available when you right-click in the Query Tool report results, as described in the following table.
Right-Click Option | Description |
|---|---|
Show Report Parameters | Displays the profile parameters to which you have access. |
Copy | Allows you to select an area of the spreadsheet, right-click, and copy the results to Excel. |
Find | Searches the results for a particular value. |
Lock Column | Sets a scroll lock at a specific column. |
Grid Settings | Displays settings for grid lines and column sizing. This option also allows you to choose the size of the cursor (cell vs. row) for each Query Report Profile. |
Export | Saves the results in a .CSV data file that opens in Excel. The exported results are displayed with the full precision of the values returned from the database. In other words, when you open the .CSV file, they may have 6, 8 or 12 decimal places when the Performance Query Tool results had fewer decimal places. This difference is intentional to support further analysis or calculations to the greatest precision available. |
Add Operation | Allows users with privileges to add an operation to their result set. |
You can also click the top-right side of any column heading to sort the data. The Copy and Export options export the column headings along with the data without adding extra rows.
Use Query Tool Features
This section describes how to use some of the Query Tool features in a query.
Calculate the Growth of Dollar
You can calculate the growth of a dollar from any return column and specify the base value. The Growth of a Dollar Operation can be used for all to date periods, such as Inception to Date or Year to Date, but does not support N Observations or Fixed.
Use Multiply Derived Inception to Date Operations (ITD)
The Query Tool supports ITD operations on a derived series. A sample scenario follows.
Say you have two columns that display the returns for two funds from inception to date and each fund has a different inception date. Fund Return and Benchmark Return in the window shown in the following figure. Then you create a Difference column; this is a derived series. (Difference F-B shown in the following figure.)
Now, you want to create a Maximum Difference Inception to Date. In the past, the Query Tool did not support this scenario as it did not know which of the two inception dates to use for the Maximum ITD operation. (Max Diff F-B ITD shown in the following figure.)
The inception date for the Inception to Date Rolling operation is determined by the Series Derivation operation.
In the example in the previous figure, there are two columns, Fund Return and Benchmark Return or C1 and C2. The derived series Difference operation is defined as Fund Return – Benchmark Return or C1-C2 = S1. When the Rolling Inception to Date Operation, R1, is defined on S1, it uses the Entity/Date information from C1 as it is the first operand in the S1 derivation.
In the second example in the previous figure, in the last two columns, the derived series is defined as C2-C1. In turn, the Rolling Inception to Date operation uses the Entity/Date information from C2.
The use of Performance Analysis field options such as: Use Fund’s Inception Date and Use Fund’s Fiscal Year Date overrides the logic previously explained. For example, if the Performance Analysis field Benchmark Return has the Use Fund’s Inception Date option checked, then its inception date, 9/30/1990, is replaced with the fund’s inception date, 8/31/1990, in all instances. The result is that both Max Diff columns have values as of 8/31/1990.
About Daily Frequency Rolling Operations
For daily frequency rolling operations, the begin date is determined the same way for all the rolling periods after the first period.
For example, configure a 1 Month return with daily frequency data. To determine the first period, the Query Tool looks at the end date and subtracts 1 from the month and adds 1 to the day. This always gives 30 calendar days, not business days. It then uses the inclusive values from that date range for the first iteration of the operation.
The Query Tool uses the same logic to determine the dates for the first period for all subsequent iterations of the rolling operation.
Based in the example above, the dates for the second iteration are determined as follows: 1 day is subtracted from the current end date to 2/24/2002. Then 1 month subtracted, 1/24/2002, and then 1 day added, 1/25/2002. The resulting dates for the second iteration are: 1/25/2002 – 2/24/2002.