Creating Report Queries From Structured Ad Hoc Forms

How to:

You can create a new report query directly from Excel by accessing structured ad hoc Forms (SAFs). An SAF is an HTML form containing a report procedure that is already connected to a data source. It enables you to select from a series of parameters to create output that is added to the active Excel worksheet. You can analyze the output data in Excel and rerun the query to refresh the data as needed.

To take advantage of this feature, you must first create one or more SAFs in a WebFOCUS desktop product, such as App Studio or Developer Studio.

Note: You must create these forms in the Content node of the Resource tree.


Top of page

x
Procedure: How to Create a Report Query from a Structured Ad Hoc Form
  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.

    Note: You can also right-click any cell, and then 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.

    WebFOCUS Quick Data Wizard Web Server Connection Screen 1

    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. After you have supplied the connection settings, 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.

      Sign in to WebFOCUS dialog box

    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, open the folders to display the list of structured ad hoc forms, as shown in the following image.

    WebFOCUS Quick Data Wizard Screen 2 Select a Folder displaying ad hoc reports and files

    Note: The list of folders and HTML forms in the display is based on user permissions.

  8. Click the entry for the structured ad hoc form you want to use, and click Next, as shown in the following image.

    WebFOCUS Quick Data Wizard Screen 2 Select a Folder displaying ad hoc reports and files with ad hoc report entry selected

    The WebFOCUS HTML Form and the Properties dialog boxes open, as shown in the following image.

    The WebFOCUS HTML Form and the Properties dialog box

  9. Keep the default option, Insert cells for new data, to retain any previously-entered query results, or click the alternative, Overwrite existing cells with new data to replace previously-entered query results.
  10. Click OK.

    Results from the first selection in the list of parameters load automatically into the spreadsheet, as shown in the following image.

    The ad hoc report selection window next to ad hoc report results imported into an Excel spreadsheet

  11. To change the default selection, select a different parameter in the WebFOCUS HTML Form dialog box, as shown in the following image.

    The ad hoc report select a product category drop-down list

  12. Click Run, as shown in the following image.

    The report populates the Excel file with data assigned to your selected parameter, as shown in the following image.

    The ad hoc report select a prorduct category drop-down list box next to values imported to Excel from the new selection

    Note: You can edit the query to select different parameter values, or rerun the same parameter values to refresh the data. To do so, close the WebFOCUS HTML Form, right-click any cell in the query, and then click Edit Query.


WebFOCUS