x

1. Open an Excel file, and select a cell that will be used as the starting point for your query results.
2. On the Add Ins tab, in the Menu Commands group, click WebFOCUS, and then click Create Query.

   You can also right-click any cell and click Create WebFOCUS Query.

3. In the Web Server URL field, type the URL for a web server in your reporting environment. You can also choose a web server from the drop-down list.

   The following image shows an example of the Web Server Connection page.

   

   Note: If the connection information was not specified during the creation of a previous query, you must supply the Web Server URL.

   For more information, see Manually Defining Web Server Connection Settings.

4. Click Next.
5. If this is your first query during your Excel session, or if your WebFOCUS Quick Data settings require you to sign in for each query, the Sign in to WebFOCUS dialog box opens.
   1. If your WebFOCUS Quick Data add-in is configured to use MR Authentication, type your valid Managed Reporting credentials.

      

   2. If the Reporting Server requires explicit sign on, type valid Reporting Server credentials.

      Note: If the On-Demand Reporting Server Logon option has been activated, you will not be prompted to sign in with valid WebFOCUS credentials after the first report query during an Excel session. If this option has not been activated, you will be prompted to sign in before every query. For more information, see WebFOCUS Quick Data Options.

6. Click Sign In.
7. When the Select a Folder page of the WebFOCUS Quick Data Wizard opens, select a folder or HTML File from the list of available data sources, and then click Next, as shown in the following image.

   

8. When the Data Source Selection page of the WebFOCUS Quick Data Wizard opens, click the Master File you want to use, and click Finish, as shown in the following image.

   

   WebFOCUS InfoAssist opens, as shown in the following image.

   

9. Use InfoAssist to build and run a query that returns output data to Excel.

Top of page

Example: Creating a New Report Query in InfoAssist

This example covers multiple aspects of creating a new report query using the WebFOCUS Quick Data tool from an Excel file.

1. Open an Excel file, and select a cell that will be used as the starting point for your query results.
2. On the Add-Ins tab, in the Menu Commands group, click WebFOCUS, and then click Create Query.

   The Web Server Connection page of the WebFOCUS Quick Data Wizard opens.

3. In the Web Server URL field, type the URL for a web server in your reporting environment, as shown in the following image. You can also choose a web server from the drop-down list.

   The following image shows an example of the Web Server Connection page.

   

   Note: If you type the URL, do not end it with a slash mark (/). The Advanced Connection Option values that will be added to this URL automatically when creating the connection begin with a slash mark (/) already. Any slash mark (/) you enter here will be redundant and will prevent the connection from completing properly.

4. Click Next.

   Note: If your configuration has been limited to the use of SAFs, select an HTML form inside a content folder that you have access to, and then click Next. Continue to create the report from a structured ad hoc form (SAF). For more information, see, How to Create a Report Query from a Structured Ad Hoc Form.

5. When the Select a Folder page of the WebFOCUS Quick Data Wizard opens, click a folder that contains the master file you need. When the list of Master Files accessible by the selected folder appears, click Next.
6. When the Data Source Selection page of the WebFOCUS Quick Data Wizard opens, click WF_RETAIL_LITE from the Data Source list, and then click Finish.

   Note: The WF_RETAIL_LITE data source is part of your Reporting Server setup.

   WebFOCUS InfoAssist opens, as shown in the following image.

   

7. In the Query pane, click By.
8. In the Data pane Dimensions folder, expand Product, and Product, and then double-click Product,Category and Product,Subcategory.

   InfoAssist assigns the Product,Category dimension to the By Query bucket in the Query pane, as shown in the following image.

   

9. In the Data pane, expand Measures and double-click Cost of Goods, Gross Profit, and Quantity, Sold.

   InfoAssist assigns the three measures to the Sum Query bucket in the Query pane, as shown in the following image.

   

10. On the Home tab, in the Report group, click Column Totals.

    The Live Preview refreshes, and displays the column totals, as shown in the following image.

    

11. Click Save.

    If you selected the Show Properties dialog when the query is created option in the WebFOCUS Quick Data Settings dialog box, the Properties dialog box opens, as shown in the following image.

    

    This dialog box contains the following options:

    • Insert cells for new data. Retains results from multiple queries in the same Excel worksheet. Depending on where new data is inserted, data from an earlier request may shift its location in the spreadsheet.
    • Overwrite existing cells with new data. Overwrites existing data to replace results with those from the new request.
12. Click Insert cells for new data, and then click OK.

    The report query data appears in the Excel file, as shown in the following image.

    

Note: When you share an Excel file that was created using Quick Data, with another user, the user can view the saved data. To edit the request or refresh the data in the shared Excel file, the user must install the Quick Data add-in.

x

In Excel, a range of cells is a group of two or more selected cells that share a name. You can use a named range of cells instead of a string of individual cell references when creating or updating formulas.

When the Quick Data add-in transfers the results of a report query to an Excel spreadsheet, it automatically creates a range that includes all of the cells covered by your query results, and separate, subordinate ranges, for each column in the results.

You can see the name of a range of cells in the Name box. On an Excel spreadsheet, the Name box appears below the ribbon, and next to the Formula bar. If an active cell is not part of a range, it displays the name (letter and number) of the active cell. If an active cell is part of a range, it displays the name of the range instead.

Top of page

Example: Working With Query Data Cell Ranges in Excel

1. To see the range created by the report query results, click the drop-down arrow to the right of the Name box in the formula bar of an Excel spreadsheet that contains query results.

   The Name box drop-down list displays the named ranges that were automatically added to the spreadsheet when the Quick Data add-in transferred the query results to the Excel spreadsheet. The named range for the entire first data table is QDATA1, and the named ranges for individual columns appear below it, as shown in the following image.

   

2. Click QDATA1 on the Name box drop-down list. Excel automatically highlights the cells included in that range on the spreadsheet, as shown in the following image.

   

   You can also use a range to protect or update the format assigned to all of the cells within that range in a single operation. The Data Range Properties dialog box contains the tools that enable you to protect or manage the format and other properties of a selected range.

3. To open the Data Range Properties dialog box, right-click any cell within the range and click Data Range Properties, as shown in the following image.

   

4. Modify options in the External Data Range Properties dialog box as needed, and save your changes.

   For more information, see Setting Query Properties.

5. When your review and configuration are complete, save the Excel file.