Editing Report Queries in InfoAssist

How to:

You can edit an existing Quick Data query from an Excel file worksheet. The Edit Query option automatically launches InfoAssist using the same connection attributes and data sources that were selected when the query was first created or last saved.

If you want to edit the connection attributes, before editing the query, right-click any cell in the existing query, and select Edit Connection to open the Web Server Connection dialog box.

Note:


Top of page

x
Procedure: How to Edit an Existing Report Query in InfoAssist
  1. Open the Excel file that contains the existing query you want to update.
  2. Right-click any cell in the existing query data range, and click Edit Query, as shown in the following image.

    Query Data Range shortcut menu, Edit Query command selected

    Note: Any cell that contains data from the query is a part of that query, enabling you to click anywhere within that range of data to edit the query.

  3. If the Excel file contains only one query, you can also use the WebFOCUS menu in the Add-Ins tab of the ribbon to select the Edit Query command.

    Selecting Edit Query launches InfoAssist, where you can edit the existing query.


Top of page

x
Procedure: How to Update the Format of an Existing Report Query

After you have transferred the results of a report query to Excel, you can use InfoAssist to open and revise the format, scope, and arrangement of data in that query.

  1. Open an existing Excel file, such as the file that you created in How to Create a New Report Query in InfoAssist.
  2. Right-click any cell in the existing report query range, and click Edit Query.

    InfoAssist opens, displaying the query, as shown in the following image.

    InfoAssist screen displaying the query and query results selected from the Excel data range.

  3. To change the format of the Gross Profit measure, in the Query pane right-click Gross Profit, and click Edit Format, as shown in the following image.

    InfoAssist Query Pane Shortcut menu, Edit Format command selected

  4. In the Field Format Options for (Gross Profit) dialog box, edit the following options:
    1. Set the Field type to Decimal.
    2. Type 0 in the Decimals field.
    3. In the Display options section, select the Floating Currency (M) and Use Comma (C) check boxes.
  5. Click OK.

    Your changes appear in the Gross Profit column of the report, as shown in the following image.

    Gross Profils column with reformatted values, no decimal places and commas included

  6. In the Query pane right-click Cost of Goods, and click Edit Format.
  7. In the Query pane, under By, right-click Product,Category, and then click Delete, as shown in the following image.

    Query pane shortcut menu, Delete command selected

  8. In the Data pane, expand the Product and Product fields, if they have not already been expanded, and double-click Model.

    The Model data field moves to the By Query bucket in the Query pane automatically, and replaces the Product,Category data field you deleted in the previous step, as shown in the following image.

    InfoAssist window with the reformatted query results

  9. Click Save.

    When you save the updated query in InfoAssist, Excel also refreshes, as shown in the following image.

    InfoAssist and Excel with the reformatted query results


Top of page

x
Procedure: How to Preserve Report Data Formatting in Excel

Both WebFOCUS and Excel enable you to format data. When you choose to override existing data when editing a query, you run the risk of losing not only your original data but any additional formatting you applied to that data in Excel. You may therefore choose to preserve any formatting applied in Excel before editing a query.

  1. Right-click any cell in the query range, and then click Data Range Properties.

    The External Data Range Properties dialog box opens.

  2. Select the Preserve cell formatting check box in the Data formatting and layout area of the dialog box, and then click OK, as shown in the following image.

    In Excel, the External Data Range Properties dialog box with the Preserve cell formatting check box selected


Top of page

x
Procedure: How to Use a Named Range in an Excel Formula

By replacing numerous cell references with a range name, named cell ranges created by the Quick Data add-in help simplify the creation of any formula in Excel that analyzes query results. To demonstrate the value of named cell ranges, you can create a formula that counts the number of values in a named range, by performing the following steps.

  1. Select any cell to the right of the existing query data.
  2. On the Home tab, in the Editing group, click the AutoSum (formula) drop-down button.
  3. Select Count Numbers, and start typing over the range of cells in the function text box with the named range for the Gross Profit column, which is QDATA1__WF_RETAIL_LITE.WF_RETAIL_SALES.GROSS_PROFIT_US. You can finish typing the whole range name or select it from the list box that appears when you start typing.

    When you have typed or selected the range, this cell now contains the expression:

    =COUNT(QDATA1_WF_RETAIL_LITE._WF_RETAIL_SALES.GROSS_PROFIT_US)
  4. Press Enter.

    Because there are 105 values in the Gross Profit column, the cell displays the number 105, as shown in the following image.

    The Count formula based on a range name and the results of that formula in the spreadsheet

    Note: The COUNT formula appears in the function text box above the query data.

  5. To edit the query again, right-click any cell in the query data range, and then click Edit Query.

    InfoAssist opens, displaying the query.


Top of page

x
Procedure: How to Add a Filter to Query Results

Filters enable you to limit very large complicated displays of query results to smaller, easier to understand, displays of query results grouped by categories in your database.

  1. From the Data pane, expand Dimensions, Product, and Product.
  2. Right-click Product,Subcategory, and click Filter, as shown in the following image.

    InfoAssist Data Pane shortcut menu, Filter command selected

  3. In the Create a filtering condition dialog box, double-click <Value> in the statement:
    Product,Subcategory Equal to <Value>

    The Values dialog box opens, as shown in the following image.

    The Create a filtering condition dialog box with the Values dialog box open ready for a selection

  4. In the Values dialog box, click Get Values, and then click All.

    The list of current values assigned to the Product,Subcategory field moves into the list box, as shown in the following image.

    The Create a filtering condition dialog box with the Values dialog box with the Product,Category values in the available list box

  5. Select Receivers, and then click Add values (>>).

    The value Receivers moves into the Selected list box, as shown in the following image.

    The Create a filtering condition dialog box with the Values dialog box open with values from the Receiver field

  6. Click OK.

    The Create a filtering condition dialog box closes, and the assigned value Receivers appears in the filtering statement, as shown in the following image.

    The Create a filtering condition dialog box with the Values dialog box open with the Receiver field value assigned

  7. Click OK.

    A filtered view of the report results appears, as shown in the following image.

    InfoAssist with the new filtered query displaying a more limited set of results

  8. Click Save.

    WebFOCUS returns the filtered data to the Excel file, as shown in the following image.

    Excel spreadsheet with tne new filtered query displaying a more limited set of results

  9. Save the Excel file with its filtered data, or open the query again to make further adjustments.

WebFOCUS