How to Drill-Down from Xcelsius chart to WEB Page

A common requirement in the dashboard is to Drill-Down from a chart to the web-page (WEBI Report/Bex Report/Google) with selected data.

I solved this issue by using "Insertion Option" of Chart component and URL Button component.

In this post I will explain the full solution with examples.

Firstly I used this data," Sales by Country and Month":

I created column chart on Xcelsius:

Chart Parameters:

·         Title: Sales Chart

·         Data: By Series

o   First series:

§  Name: Sheet1!$A$2

§  Values(Y): Sheet1!$B$2:$M$2

o   Second series:

§  Name: Sheet1!$A$3

§  Values(Y): Sheet1!$B$3:$M$3

o   Third series:

§  Name: Sheet1!$A$4

§  Values(Y): Sheet1!$B$4:$M$4

o   Category Labels:  Sheet1!$B$1:$M$1

Under the chart data on worksheet I filled additional cells:

The "Insertion Options of the chart" will be linked to these cells. Insertion Options Parameters:

·         Enable Data Insertion: Checked

·         Series Name Destination: Sheet1!$B$8

·         Insertion Type: Position

·         Series

o   North America:

§  Destination: Sheet1!$B$9

o   Europe:

§  Destination: Sheet1!$C$9

o   Asia:

§  Destination: Sheet1!$D$9

·         Insert On: Mouse Click

·         Default Section:

o   Series: North America

o   Item: 1

Defining of additional cells:

1.       B7 (Trigger Cell): =B8&B9&C9&D9

This cell will update them value on every change of cells: B8,B9,C9,D9, where these cells change on every click on chart

2.       B10 (Current Position): =IF(B8=A2,B9,IF(B8=A3,C9,IF(B8=A4,D9,0)))

This formula will choice a selection position from cells: B9, C9, D9 according to selected description (B8 selected series).

3.       B11 (Selected Month): =CHOOSE(B10,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1)

This formula will choice a month from chart data according to position.

4.       B12 (URL North America): ="http://www.google.co.il/#q=North America Sales "&B11

This formula concatenates URL of North America data (In this example Google search page with constant string) and a selected Month.

5.       B13 (URL Europe): ="http://www.google.co.il/#q=Europe Sales "&B11

This formula concatenates URL of Europe data (In this example Google search page with constant string) and a selected Month.

6.       B14 (URL  Asia): ="http://www.google.co.il/#q=Asia Sales "&B11

This formula concatenates URL of Asia data (In this example Google search page with constant string) and a selected Month.

7.       B15 (URL): =IF(B8=A2,B12,IF(B8=A3,B13,IF(B8=A4,B14,"")))

This formula will choice a URL from B11, B12 and B13 according to a selected description (B8 selected series).

Of course in the same way we can put URL to Webi Report and transfer the month as prompt parameter.

Now create a URL Button, with these parameters:

·         URL: Sheet1!$B$15

·         Encode URL: Unchecked

URL Button Behavior parameters:

·         Trigger Cell: Sheet1!$B$7

·         When Value Changes: Checked

To hide the button just put it on Chart Area -left mouse click -> "Send to Back"

Run the Dashboard – its work!