Skip to Content

A common requirement in the dashboard is to Drill-Down from a chart to the web-page (Web Intelligence Report/Bex Report/Google page) 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”:

 Sales Data

I created column chart on Xcelsius:

 Column Chart

Chart Parameters:

  • Title: Sales Chart
  • Data: By Series
    • First series:
      • Name: Sheet1!$A$2
      • Values(Y): Sheet1!$B$2:$M$2
    • Second series:
      • Name: Sheet1!$A$3
      • Values(Y): Sheet1!$B$3:$M$3
    • Third series:
      • Name: Sheet1!$A$4
      • Values(Y): Sheet1!$B$4:$M$4
    • Category Labels:  Sheet1!$B$1:$M$1

Chart General Properties 

Under the chart data on worksheet I filled additional cells:

 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
    • North America:
      • Destination: Sheet1!$B$9
    • Europe:
      • Destination: Sheet1!$C$9
    • Asia:
      • Destination: Sheet1!$D$9
  • Insert On: Mouse Click
  • Default Section:
    • Series: North America
    • Item: 1

 Insertion Option Properties

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

  1. 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).

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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  Web Intelligence 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 general properties

URL Button Behavior parameters:

  • Trigger Cell: Sheet1!$B$7
  • When Value Changes: Checked

  URL Button Behavior Properties

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

Run the Dashboard – its work!

http://digilov.blogspot.com/2011/09/how-to-drill-down-from-xcelsius-chart.html

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply