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:
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.
Selecting Edit Query launches InfoAssist, where you can edit the existing 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.
InfoAssist opens, displaying the query, as shown in the following image.
Your changes appear in the Gross Profit column of the report, as shown in the following image.
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.
When you save the updated query in InfoAssist, Excel also refreshes, as shown in the following image.
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.
The External Data Range Properties dialog box opens.
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.
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)
Because there are 105 values in the Gross Profit column, the cell displays the number 105, as shown in the following image.
Note: The COUNT formula appears in the function text box above the query data.
InfoAssist opens, displaying the query.
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.
Product,Subcategory Equal to <Value>
The Values dialog box opens, as shown in the following image.
The list of current values assigned to the Product,Subcategory field moves into the list box, as shown in the following image.
The value Receivers moves into the Selected list box, as shown in the following image.
The Create a filtering condition dialog box closes, and the assigned value Receivers appears in the filtering statement, as shown in the following image.
A filtered view of the report results appears, as shown in the following image.
WebFOCUS returns the filtered data to the Excel file, as shown in the following image.
WebFOCUS |