While developing an Xcelsius dashboard for a Clariba customer, I came across two interesting challenges related to the location of a click on drillable charts.
Finding the location of a click on a drillable chart – Part 1
I was working with a drillable chart with two series and I wanted to know where the user had clicked (i.e. which series and which position).
With only one series there wouldn't be any problem. Xcelsius can send the selected series to a cell, and the selected position to another cell. But with more than one series, each series can still send a position, but Xcelsius does not allow all the series to send the information to the same place. So the information has to be sent to different places. As a result, it's hard for the developer to know which point was clicked last, as the information sent before is not deleted.
Solution The idea is to use the series name in a VLOOKUP function to find out which position was sent last.
For the details, I have used the attached Xcelsius file:
As Xcelsius forces us to send the information from the different series to different places, let's do it.
In the attached file, I sent the positions to two different places (Drill!B2 for section 1, Drill!B3 for section 2). But how did I know which value was sent last? I simply used the series name. I have configured the chart to send the series name to Drill!B5, and I use a VLOOKUP function to find the relevant position (see the formula in cell Results!B4). I can then use this position to find the label (or any other relevant information) with the OFFSET function (cell Results!B1).
Finding the location of a click on two drillable charts – Part 2
In the second scenario, I had two drillable charts with the same horizontal axes and the same series. I wanted to know where the user had clicked (i.e. which graph and which point - series and position)
With Xcelsius it is not possible to send the name of the clicked graph somewhere. Each series can send a position (or value, row or column), but Xcelsius does not allow all the series to send the information to the same place. This is the same challenge as Part 1 in that the information has to be sent to different places. As a result, the developer doesn’t know which point was clicked last, as the information sent before is not deleted.
Solution The idea is to send rows with position and graph name (instead of sending only the position), and to use the series name in a VLOOKUP function to find out which row was sent last.
For the details of the solution I have used the attached Xcelsius file:
When the user cliced on a dot in a chart, I wanted the graph to send 2 pieces of data: the graph name and the position (in the horizontal axis). Instead of sending only the value or the position, I configured the charts to send a row (columns also work). The sent row had the two pieces of data in it. In the example file I configured the quantity chart to send the data from Drill!E2:F3, and the value chart to send the data from Drill!E5:F6.
When the first series is clicked, the data is sent to Drill!B2:C2; the second series is sent to Drill!B3:C3.
So now we have the chart names in Drill!B2:B3 and the positions in Drill!C2:C3. But how do we know which row was clicked last? For this we use the series name. I configured each chart to send the series name to Drill!B5. Now, if I click in the chart Quantity, on the dot Section 2 / Month 2, then "Section 2" will be sent to Drill!B5, and Drill!E3:F3 will be sent to Drill!B3:C3.
Then I simply put the information together. I found the chart name with a VLOOKUP function on the series name (formula in cell Results!B1), the position with another VLOOKUP (cell Results!B5), and the month with an OFFSET on the position (cell Results!B2).
This has been a quick look at the ways to find the location of a click on drillable charts… I would be interested to hear if anyone else has found a solution for similar challenges. If you have any feedback or ideas, please feel free to leave a comment below.