Creting Drill Through Reports in WebI

Introduction

Drill down and drill through are two very powerful features in WebI Reports. They both give the user the ability to see data and information in more detail; but they do so in different ways.

Drill down is a capability that takes the user from a summary view of the data to more detail views at the click of a mouse. Eg., a report that shows sales revenue by state can allow the user to select a state, click on it and see sales revenue by city within that state. In drilldown reports you put all the data in the report, but set it to be hidden until a user clicks to reveal details. Typically, the template of each detail level of the report is similar–what changes is the granularity of the data

Instead of taking the user to a more granular level of the data, drill through capability takes users to another report that is relevant to the data being analyzed, also at the click of a mouse. Using drill-through access, users can move from one report to another within a session while maintaining their focus on the same piece of data. For example, you select a product in a sales report and move to an inventory report about that product.

Drill down reports retrieve data for all levels at the same time. The data in the drill through report is not retrieved until the user clicks the link in the main report that opens the drillthrough report. This gives the Drillthrough reports have better performance.

Drill through reports are separate reports. Thus, they can be used in a number of reports, or displayed as standalone reports.

A drill through report opens via hyperlinks and such reports typically contains parameters that are passed to it by the summary report.

In Web Intelligence reports, we can define cells as hyperlinks. When you click a cell that contains a hyperlink, the target document specified in the link opens. The target document can be another Web Intelligence document, a site on the Web, or a PDF, Excel or Word document.

You can link two Web Intelligence reports together using the InfoView. You can navigate from one Web Intelligence report to another and pass report filter values, attributes of filter values, or on-click value from one report to another.

Scenario 1: Passing Filter Values

The following steps show how to pass report filter values while linking drill through Web Intelligence reports. To pass values from a parent to a child report you need to have prompts in the child report that will hold the values that are passed form parent report. My parent report has a report filter on an object [Sales Org]. My requirement is to pass the selected value of this filter in my linked report, which has a prompt on Sales Org.

Step 1. You can link two Web Intelligence reports using the InfoView only when a report is edited in interactive mode.

To do so login to Infoview by clicking on its web URL.  On the home page click on Preferences as shown in Figure 1.

Figure 1

Under Web Intelligence, select the default view format. Then select the default creation/editing tool as Interactive as shown in Figure 2.

Figure 2

Step 2. Open the report and right-click the cell where you want to create the link. Select Hyperlink > New from the context menu (Figure 3).

Figure 3

Step 3. Select the Link to document tab in the Create Hyperlink dialog box. (Figure 4)

Figure 4

Step 4. Click Browse and select the target document in the Choose a document dialog box . Click OK.(Figure 5).

Figure 5

Step 5. Click Use complete URL path to create a hyperlink to choose whether the link uses the full URL path or a relative path from the current document (Figure 6). Hyperlinks with full URL path include the full path to the target document. Alternatively, the value may contain a relative path that just contains the name of the target document. As reports (hyperlinks) are migrated from one environment to another, the hyperlinks with relative path will not break as the URL path is relative to the server it is running on at the time, and the server name is never explicitly defined.

Figure 6

Step 6. Check the Refresh on open check box if you want to refresh the data of the target document when the hyperlink is selected (Figure 6).

Step 7. Click Link to document instance. (Figure 6) Select an option from the drop down-list to link to a scheduled instance of the selected document. In this article I will not be working with this option in my scenarios, so we will not be checking this option.

Table 1 describes the options in the drop-down list.

Table 1 Description of selection options for linking

Step 8. Mark the Report name button shown in Figure 7 and select the name of the report to link to a specific tab/report of a Web Intelligence document.

Figure 7

Step 9. Check the Report part shown in Figure 7 and then click the Select button that appears.

Right-click the report part (for example a table) to link to a specific part of a report.

Step 10. Select the format of the target document from the Document Format list in the Customize the look and behavior of the hyperlink section shown in Figure 7. The drop-down choices displayed are Default, HTML, PDF, Excel, and Word. When we link two Webi reports we have option of Default, HTML, PDF only enabled to us. But we can even link one webi report to another Microsoft office Excels or word doc available in Infoview. Then these options are enabled to us.

Step 11. Select New window or Current window from the Target window list in the Customize the look and behavior of the hyperlink section shown in Figure 7 to determine how the target document opens. The drop-down choices are Current window or New Window. We make all entries in rest of screen and then only click Ok

Step 12. Enter the tooltip text in the Tooltip box in the Customize the look and behavior of the hyperlink section in Figure 7. The tooltip text appears when you hover your mouse pointer over the cell containing the hyperlink and is used to provide information to user like ‘Click here to see details’ etc

You could also build a dynamic tooltip by using the Build formula (You build a formula in the Formula Editor to supply the formula output as the tooltip.) or Select object option (You choose the variable from list of objecst available in WebI report to supply its value as the tooltip.)

Step 13. If the linked child document contains prompts, for each prompt in the Document prompts section, select one of the options shown in Table 2. These are options to assign parameter values to be passed in drill through report

Table 2 options to assign parameter values to be passed in drill through report prompts

All the prompts in the child reports are listed under Document Prompts section shown in Figure 8.

Figure 8

If you want to pass the report top filters of the parent report to any of the prompts of the child report, then use the Build formula option against that prompt in the Document prompts section.

Step 14. When you click the Build formula option a Formula Editor pops up where you write the logic/code for passing the values (Figure 9).

Figure 9

Let’s see the syntax of couple of Web Intelligence functions that capture and pass the report filters values:

·        Pos(): Returns the starting position of a text pattern in a string

            Syntax: Pos(test_string;pattern)

·        Substr(): Returns part of a string

            Syntax: SubStr(string;start;length)

·        ReportFilter():          Returns the report filters applied to an object or report

            Syntax: ReportFilter(obj)

If you select one value in the report filter, then the ReportFilter() function give you that single selected value. If no value is selected, then the ReportFilter() function gives you the complete LOV (List of values) of that filter with each value separated by a semicolon.

Now let’s discuss how you can use the combination of above three functions to pass report filter values while linking Web Intelligence reports. Since I want to pass the report filter value in a hyperlink only when the user has made a selection, I first use the Pos() to check the position of  “;” in the result set of ReportFilter([Sales Org]) function. If the user has made some selection then the position of  “;” in that output string should be zero. Then only if position of  “;” is 0, I use the Substr function to avoid any extra blank spaces and capture only the selected value from ReportFilter([Sales Org]) output.

Following the above logic write the following formula in the formula editor against the “Sales Org(s):” prompt shown in Figure 9:

=If Pos(ReportFilter([Sales Org]);”;”) = 0 Then Substr(ReportFilter([Sales Org]);0;Pos(ReportFilter([Sales Org]);” “))

Click the Validate button and then click the OK button.

Now whenever the user clicks the cell with the hyperlink, the user will see the linked child report in a new window  with selected value of [Sales Org] filter passed to the linked report. If the user does not select any value in the top filter then the linked report opens with its default value of [Sales Org] prompt.

Scenario 2: Passing Attributes

Let’s assume that instead of the [Sales Org] value, you want to pass its attribute, which is [Sales Org Code], in the linked report prompt. In such a scenario you have to make sure that the child report has a prompt on the attribute ([Sales Org Code]). Follow steps 1 to 12 as in Scenario 1.

Step 13. In the formula editor use the Pos() to check the position of  “;” in the result set of ReportFilter([Sales Org]) function. If the user has made some selection, then the position of  “;” in that output string should be zero. If position of “;” is 0 then you capture the [Sales Org Code] for the selected [Sales Org] in the report filter and pass that value to the hyperlink.

Following the above logic write the following formula in the formula editor against the “Sales Org(s):” prompt:

=If Pos(ReportFilter([Sales Org ]);”;”) = 0 Then [Sales Org Code] Where ([Sales Org ] = ReportFilter([Sales Org ])).

Click the Validate button and then click the OK button.

Scenario 3: Passing a Truncated Value

Let’s assume the cell on which you are creating the hyperlink is code + text , but you want to truncate the text and pass only the code in the hyperlink. 

Use the Pos() to check the position of the blank space that separates the code and text in Category value. Then use Substr() function to capture the Category cell string from position 0 to the position of first blank space, which gives you only the code.

Following the above logic write this formula in the formula editor against “Category:” prompt shown in Figure 9:

=Substr([Category];0; Pos([Category ];” “) )