Example 2: Passing query-based values in drill

In Example 1: Passing column values in a drill, you learned how to create an auto-drill hyperlink that passes a value that the user clicks as a parameter to a report. Sometimes, however, you do not need to transmit the selected value itself, but rather some information that is related to the selected value. To do this, you can add a query-based auto-drill to the logical model.

Walkthrough

In this example, you will add an auto-drill that transmits a product ID when the user clicks the corresponding product name in a report or Viewsheet. This example also illustrates how to target a web page with the hyperlinks (rather than a report, as in Example 1: Passing column values in a drill).

To add a query-based auto-drill, the first step is to build an appropriate query. In the following sequence, you will use the Query Wizard to build a new query that accesses the desired product information. You will design this new query to return the data you wish to pass as parameter in the auto-drilldown, which is the 'PRODUCT_ID' field.

The auto-drill query should accept parameters, and return rows containing the appropriate values to pass to the target report or web page.

1. Open the Asset panel. Expand the 'Data Source' node.

For more information on constructing que­ries, see the Indepen­dent Query section.

2. Right-click the 'Orders' data source, and select 'New Query' from the context menu. This opens the 'Query Wizard'.

3. On the General tab, enter a name for the new query in the 'Name' field (e.g., 'getProdID'). Click 'Next'.

4. Under the Data tab of the Query Wizard, select the 'PRODUCTS' table from the 'TABLE' > 'SA' node.

5. Under the Fields tab of the Query Wizard, select the 'PRODUCT_ID' field.

6. Under the Conditions tab of the Query Wizard, select 'SA.PRODUCTS.PRODUCT_NAME' as the conditioned field, since this will be the input to the query (the attribute that the user will be clicking in the report/viewsheet).

7. Select 'is equal to' from the adjacent popup menus.

8. Select 'Variable' from the right-most popup menu, and enter a variable name in the text field. For this example, enter $(userProductChoice) as the variable name.

 

9. Click the 'Append' button to add this condition.

10. Click the 'Finish' button in the Query Wizard, and then click the 'Save' button in the toolbar to save this query.

In the next sequence of steps, you will use the saved query as the basis of a new auto-drill:

1. Open the Asset panel. Expand the 'Data Source' node and the 'Orders' data source.

2. Double-click on the 'Order Model' to open the model for editing.

3. Click on the cell in the 'Auto Drill' column corresponding to the 'Product' > 'Name' attribute. This opens the 'Auto Drill' window for the 'Name' attribute.

 

4. In the 'Auto Drill' window, click the 'New Drill' button to create a new auto-drill scheme. The new drill is named 'drill0' by default. (If you add more than one drill to a given attribute, the end-user will be presented with a menu of hyperlink choices corresponding to the different drills you have specified.)

 

5. Rename the new drill by entering a name in the 'Name' field (e.g., “SendProdID”).

6. Enter a tooltip in the 'Tool Tip' field (e.g., “View product information”). The tooltip will be displayed when the end-user hovers the mouse over the hyperlink. If there are multiple drills for a given attribute, the tooltip of each drill will be displayed when the end-user hovers the mouse over the name of the drill in the menu of hyperlink options.

This URL is for illus­trative purposes since the 'PRODUCT_ID' attribute is an integer value and will not yield any useful search results.

7. Select the 'Web Link' button. In the 'Link' field, enter the URL of the hyperlink destination with parameters omitted. For example, if the hyperlink is to pass a value as parameter to the Google search engine, the 'Link' field should specify “http://www.google.com/search”.

 

8. (Optional) If you want the target web page to display in a particular browser window, enter the name of the desired window in the 'Target Frame' field. (The specified name becomes the DOM 'name' attribute.) To specify that the target page should open in the User Portal frame, select the 'Self' checkbox.

9. Click the 'Query' button in the 'Auto Drill' window to bring up the 'Query Selection' dialog box. Select the saved query ('getProdID'), choose 'Product.Name' for the 'userProductChoice' dropdown at the bottom, and click 'OK'.

 

Note: If the query takes multiple parameters, you can connect them to various attributes/columns from the current model/query. If those columns are present in the table that is displaying the auto-drill, the values of those columns will also be passed to the subquery.

The 'Value' pane in the 'Auto Drill' window now displays the out­put fields of the 'getProdID' query, which is the attribute called 'PRODUCT_ID'. You can now pass this parameter to the auto-drill. (Note that the 'PRODUCT_ID' attribute is available only as a result of running the auto-drill query; it is not explicitly shown any­where in the user's report.)

10. Select 'PRODUCT_ID' in the 'Value' pane, and click the left arrow button to open the 'Input' dialog box. This indicates that 'PRODUCT_ID' will be passed as a parameter in the auto-drill.

Note: If a query returns multiple rows, the user is shown a pop-up with all of the values. The user can then clarify their selection by choosing one of these to send to the report or URL.

11. Enter the variable name “q” in the 'Parameter Name' field of the 'Input' dialog box. This name will be the parameter name used in the URL that is automatically constructed. (The parameter name “q” is the parameter name expected by the Google search engine.)

 

12. Click the 'OK' button to exit the auto-drill configuration. The final configuration of the 'Auto Drill' window for this example is shown below.

 

The hyperlinks constructed by this auto-drill will be of the form

http://www.google.com/search?q={ProductID}

where {ProductID} is the value of 'PRODUCT_ID' corresponding to the 'Product.Name' clicked by the user. All reports and Viewsheets that use the 'Product.Name' attribute of this logical model will now automatically provide hyperlinks of the above form on that attribute.

For an illustration of how these hyperlinks will appear in a deployed report, see Steps 1418 of Example 1: Passing column values in a drill.

<< Example 1: Passing column values in a drill © 1996-2013 InetSoft Technology Corporation (v11.4) Passing Multiple Parameters in a Query-Based Auto-Drill >>