Linking of Report in Web Intelligence While Drilling.

Statements

• When we use Open document url in Webi Intelligence Report with Drill Down enabled we get #Multivalue error.
• When we have multiple values for an object(defined in hierarchy) in a Drilled Report we get  #Multivalues error.
• How do we achieve Hyperlinking using opendoc url in a Drilled report so that the data relevant to the Drill level will be passed through the URL.

Issue

We have Created a Sample report on Island Resort Marketing Universe.

We have the following Hierarchy defined.

1. Country
2. Resort
3. Service Line
4. Service

We have used the following Url to create the link

=”<a href=http://server:8080/OpenDocument/opendoc/openDocument.jsp?iDocID=AcEQG1My055BgqpaRiJ36dk&sIDType=CUID&sWindow=New

&lsMEnter+Country:=”+Replace([Country];” “;”+”)+”

&lsMEnter+Resort:=”+Replace([Resort];” “;”+”)+”

&lsMEnter+Service+Line:=”+Replace([Service Line];” “;”+”)+”

&lsMEnter+Service:=”+Replace([Service];” “;”+”)+”>”+([Revenue])+”</a>”

When we use this link in the report it gives # Multivalue error.

When we Drill it to the lowest level we do not get that error

Resolution:

Creation of Variables.

1. Create a <Drill levels> variable to record the levels of Drill. This will be used to track the level of the Drill

Dril Levels

=If DrillFilters(“-“)= “” Then 1

ElseIf DrillFilters(“-“)= [Country] Then 2

ElseIf DrillFilters(“-“)= [Country] + “-” + [Resort] Then 3

ElseIf DrillFilters(“-“)= [Country] + “-” + [Resort] + “-” + [Service Line]

Then 4

ElseIf DrillFilters(“-“)= [Country] + “-” + [Resort] + “-” + [Service Line] + “-” + [Service] Then 5

Else 99

1. Create different Links variables depending upon the number of levels available for drilling; in this case we have 4 levels to drills, (excluding the first initial state).

For Each Level use the Objects based on the Hierarchy

For Level 1 use only Country

Link Level1

=”<a href=http://server:8080/OpenDocument/opendoc/openDocument.jsp?iDocID=AcEQG1My055BgqpaRiJ36dk&sIDType=CUID&sWindow=New

&lsMEnter+Country:=”+Replace([Country];” “;”+”)+”>”+([Revenue])+”</a>”

      For Level 2 use Country and Resort.

Link Level2

=”<a href=http://server:8080/OpenDocument/opendoc/openDocument.jsp?iDocID=AcEQG1My055BgqpaRiJ36dk&sIDType=CUID&sWindow=New

&lsMEnter+Country:=”+Replace([Country];” “;”+”)+”

&lsMEnter+Resort:=”+Replace([Resort];” “;”+”)+”>”+([Revenue])+”</a>”

For Level 3 use Country, Resort and Service Line

Link Level3

=”<a href=http://server:8080/OpenDocument/opendoc/openDocument.jsp?iDocID=AcEQG1My055BgqpaRiJ36dk&sIDType=CUID&sWindow=New

&lsMEnter+Country:=”+Replace([Country];” “;”+”)+”

&lsMEnter+Resort:=”+Replace([Resort];” “;”+”)+”

&lsMEnter+Service+Line:=”+Replace([Service Line];” “;”+”)+”>”+([Revenue])+”</a>”

For Level 4 use Country, Resort,Service Line and Service

Link Level4

=”<a href=http://server:8080/OpenDocument/opendoc/openDocument.jsp?iDocID=AcEQG1My055BgqpaRiJ36dk&sIDType=CUID

&sWindow=New&lsMEnter+Country:=”+Replace([Country];” “;”+”)+”

&lsMEnter+Resort:=”+Replace([Resort];” “;”+”)+”

&lsMEnter+Service+Line:=”+Replace([Service Line];” “;”+”)+”

&lsMEnter+Service:=”+Replace([Service];” “;”+”)+”>”+([Revenue])+”</a>”

         Create a variable to display these link based on the Drill level

         Selection.

         Link Selection

= If [Drill Levels] = 1 Then [Link Level1]

ElseIf [Drill Levels] = 2 Then [Link Level2]

ElseIf [Drill Levels] = 3 Then [Link Level3]

ElseIf [Drill Levels] = 4 Then [Link Level4]

Else 999   

Report Navigation.

We have added the Link Selection variable under the revenue column as shown below

Level 1 Display.

Below is the Level 1 Drill Display

Click on the Revenue for US

Following Sub Report will open for US

Level 2 Display.

Below is the Level 2 Drill Display

Click on the Revenue for Bahamas Beach

Following sub report will open

Level 3 Display.

Below is the Level 3 Drill Display

Click on the Revenue for Accommodation.

Following Sub report will open for Accommodation.

Level 4 Display.

Below is the Level 4 Drill Display

Click on the Revenue for Bungalow

We will get the following sub report for Bungalow.

Conclusion.

• The Opendoc url does not display the data correctly if we have multiple values for an object in the Hierarchy.
• The Web Intelligence tool is not aggregating the measure while used in the Hyperlink, so we have to use different links for different levels.