Modifying the Query Used in a Simplified Analytic Report

When you want to change the delivered base template of the pivot grid on a transaction page, you can:

  • Modify axis, rows or columns or the chart type and so on to change the configuration of the pivot grid definition. These changes will not affect existing simplified analytic reports on the same base template.

  • Modify the query definition used in the base template pivot grid. Any change to the query is disruptive and will effect existing reports too.

The following section lists methods to modify query in non-invasive ways.

To modify the simplified analytic query by adding a new field to the query:

  1. Search and open the required query from the Query Manager. Under the Query tab, the records associated with the query are listed.

  2. Expand the records to see fields associated with each record. The list of fields for a record also includes fields not selected to be used in the query.

  3. Select a new field to be added to the query from this list. You can verify the selected field appears under the Fields tab and the View SQL tab of the Query Manager.

  4. Verify the new field is also shown on Selecting a Data Source- Step 2 page of the Pivot Grid Wizard as a Data Source Column. Step through the wizard and save the model.

  5. Open an existing simplified analytics report that uses the same query and pivot grid base template. The report has no change.

  6. Click Edit button on the report. Note that the new field is listed. You can make further modifications to the report selecting the new field on the report.

    The new field added to the query will be available when you create a new analytics using the same query.

To modify the simplified analytic query by adding a criteria:

  1. Search and open the required query from the Query Manager.

  2. Add a criteria under the Criteria tab. Verify the Expression Type of Expression 1 is set to a field and the Expression Type of Expression 2 to a constant.

  3. Save the query.

  4. Open an existing simplified analytic report which uses the query. The report reflects the modification in criteria.

To modify the simplified analytic query by adding a new expression to a criteria or a field:

  1. Search and open the required query from the Query Manager.

  2. Add an expression to the query in the Expression tab.

  3. Add a criteria involving the new expression in the Criteria tab.

  4. Open an existing report that uses the base template with the query you have modified. The report reflects the change carried out in the query.

To modify the simplified analytic query by adding an optional prompt:

  1. Search and open the required query from the Query Manager.

  2. Add a new prompt in the Prompt tab:

    • Add a new Field as a new prompt.

    • Select the Optional check box.

    • Search and select a Prompt table.

  3. Use the new prompt to update the criteria in the Criteria tab.

  4. Verify that the new prompt is added to the query using the Run tab.

  5. Open the base template in the pivot grid wizard from Reporting Tools, Pivot Grid, Pivot Grid Wizard. Navigate to Specify Data Model Values-Step 3 page. Enter appropriate value for the prompt in the Select Query Prompt Values section and save the base template.

  6. Create a new analytics report where you will find the new prompt listed. But existing analytic reports remain unchanged.

Note: You can make the new prompt as a required prompt when you do not select the Optional check box and provide a default value to the prompt. In this case the existing reports will remain unchanged but when you create an analytic report using the same query, the new prompt is reflected.

To modify the simplified analytic query by joining a new record to the query:

  1. Search and open the required query from the Query Manager.

  2. Search and select a record from the Record tab.

  3. Click Join Record link to the join with the any of the existing records as a Standard Join or Left Outer Join.

  4. Click Add Criteria button on the Auto Join criteria dialog box after selecting all the required criteria for the join.

After the above step, you can modify the queries following any of the methods described below:

  • Add a new field from the newly joined record.

  • Add a criteria involving Fields or Expressions.

  • Add an optional prompt.

  • Add a required prompt.

Add a new field from the newly joined record

After you select the criteria you can select a new field from the Records tab in Fields section. See Adding a New Field From an Existing Record in the Query.

Add a criteria involving fields or expressions

After you select the criteria from the Auto Join criteria dialog box, you can add a new criteria under the Criteria tab and save the query. Open existing reports that reflect the change and show the newly added criteria. See Adding a Criteria Involving the Fields and Expressions.

Adding an optional prompt or a required prompt

After you select the criteria from Auto Join criteria dialog box, add a new prompt from the Prompt tab. See Adding an Optional Prompt.

To modify the simplified analytic query by changing the join criteria of the query:

  1. Search and open the required query from the Query Manager.

  2. Search and select a record from the Record tab.

  3. Click Join Record link to the join with any of the existing records as a Left Outer Join or a Standard Join.

  4. Make a change in join criteria for example delete an entity.

  5. Save and open an existing simplified analytic report. The report shows the change in join criteria in the query.

To modify the simplified analytic query by changing the field properties of existing field used in the base query:

  1. Open the Application Designer.

  2. Search and open the field properties of the record.

  3. Modify the field properties for example changing the field type or field length.

    This change in field type is reflected in Query Manager too. Changes are also seen in Pivot Grid Wizard, when we open the base template or when we open any existing simplified analytic reports that uses the modified field.

To modify the simplified analytic query by adding or modifying the security joins:

  1. Open the record properties of the required record and enter or modify the record in the Query Security Record field.

  2. Click OK and save.

  3. Reopen the existing report. You will find no change in the existing simplified analytic report.

See, Using Row-Level Security and Query Security Record Definitions for enforcing Row-Level security.

To modify the simplified analytic query by removing a join if the record is not referenced in select list or under prompts:

  1. Join two records such that none of the fields of newly added record are listed in the select list of the query and is not part of the prompt.

    The fields from the second record appears only in the criteria.

  2. Add an additional criteria. The existing reports change to reflect the join and the change in criteria.

  3. Delete the record from the query. The criteria will automatically be updated to show the changes. The existing report also changes to how they were prior to the join.

To modify the simplified analytic query by changing the text and format of an expression used in the base query:

  1. Open the expression to be edited from the Query Manager

    Click Edit button for the expression on the Fields page.

  2. Modify any of the required field like Heading Text, Heading and so on.

    Save the query. Simplified Analytic Report reflects the change on editing the existing report or creating a new report using the query. Existing reports remain unchanged.