Xcelsius drill-down with crosstab data

[Former Member]

I'm new to Xcelsius and can't work this out even though the concept seems simple enough so was hoping for some guidance.

I have a crosstab with countries as rows and cities as columns - the measure is the population of the city. What I wanted was a pie chart of the countries and the share they make up of the total population. That seems straightforward enough with tying the label of the chart to the that of the countries and the values to that of the total for each row. When clicking on the country, I then want to display, in a bar chart below the pie chart, the cities with corresponding population for the country selected in the pie chart.

The problem I have is that when I click a country in the pie chart, my bar chart shows all cities from the cross tab though only with bars for those cities corresponding to the selected country. I suppose the best way of describing it is the drill-down chart has static labels showing all cities with bars changing as I click on countries in the pie chart.

If I try to illustrate that with data:

Header 1	Header 2	Header 3	Header 4
	New York	London	Total
USA	8000000		8000000
England		8000000	8000000

The pie chart will show USA and England both at 50%. If I click USA, my bar chart will show a bar for New York with a value of 8000000 but London also appears on the x-axis. Similarly if I click England in the bar chart, a bar will show for London, no value for New York but it still appears as a label on the x-axis. I want for only those cities of the selected country to appear but I can't seem to do that. I'm hoping it's something really simple but I can't seem to make this happen. Any help will save what's left of my hair and is appreciated.

Cheers

[Former Member · Accepted solution]

Hi Jack,

The solution for your question is (will use the picture attached to make it easier to understand):

1. The Pie Chart should display the values in the Total Column. Enable Drill Down (or Data Insertion if you are using Dashboards aka Xcelsis 2011) and set it up like this: Insertion Type: Row, Data Source: the whole data set, excluding the city names and the totals column, Destination: wherever you want (best practice and common sense says to highlight it with a different colour so that you won't forget). In my example that is A7:G7.

2. The row above your destination:  use the formula in the formula box (in case you can't see it, that is =if(B6>0,B1,"") ) for B6 and then drag it across to every cell above data pushed by the Pie Chart. In my picture there will be data in B7:G7 (A7 will be the name of the selected country). What that formula will do is to display the content of cells B1,C1,D1.... (city names) in B6,C6,D7... only if there is data in B7,C7,D7...

3. The Bar Chart: Tick Data by Series. Display data in B7:G7, use B6:G6 as Labels. ! (what comes next is important) In the Behaviour Section choose both Ignore Data in Series and in Values.

This solves your problem. Good Luck!

Regards,

Matt

PS: If you want to be smarter set the title of the Bar Chart to A7 (which is the selected country)

PS2: Just realised you can't see the row numbers. The first row (with city names) is row 1.

[Former Member · Accepted solution]

Thanks Matt. I'd tried a combobox with filtered row insertion using data of the format you describe but I really wanted to be able to make the selection from the pie chart and not a combobox. I thought I might have been able to write the seletion from the pie chart to the combobox and then have that control the drill-down chart but no luck there either. I'm not able to test your solution at the moment but will do so right away come Monday morning -  I'll definitely report back and hope to be able to confirm your answer :-). For now, can't thank you enough for your help.

Cheers

[Former Member · Accepted solution]

Hi Jack

In your pie chart, enable the drilldown and and set the insertion type as columns and map a column in youe excel part. In the colummn chart set two series and map one series to the first cell and the next cell to the second series.

Add a label separately ad map it to the first cell where the label gets inserted from the pie chart drill down.

If this succeeds you will get your preview pane as given below.

I've done this with a column chart. You can use a bar chart instead and do the same. Hope this helps..

Regards

Varshini.

[Former Member · Accepted solution]

Thanks Matt, tried your solution and that worked brilliantly! I noticed the bars that display in the drill-down chart are positioned as if the rest of the labels are present, just not visible. I thought "ignore data in series and in values" might have completely dropped that and reduced the space, putting visible columns next to each other, but that didn't seem to be the case; I might have done something wrong. Sorting the data in the graph seemed to fix that though.

Thanks for all your help Matt!

[Former Member · Accepted solution]

Hi Jack,

"Ignore blank data in series and values" only works with blanks that are at the end of the series.

For example, the data

{17 , 11 , 8 , (blank) , 3 , 4 , (blank) , 5 , (blank) , (blank) , (blank)}

would become

{17 , 11 , 8 , (blank) , 3 , 4 , (blank) , 5}

[Former Member · Accepted solution]

Thanks Alex and also Varshini - that explains why sorting the selection made the "gaps" disappear. Appreciate all the help.

Cheers

[Former Member · Accepted solution]

Hi Jack,

I know this question was answered but just for your own information I think this might be helpful.

You said that:

I'd tried a combobox with filtered row insertion using data of the format you describe but I really wanted to be able to make the selection from the pie chart and not a combobox. I thought I might have been able to write the seletion from the pie chart to the combobox and then have that control the drill-down chart but no luck there either

You went the right way there by trying to "feed" the selection of the pie chart into the combo box as this is what I would have done myself. Don't know why it didn't work but if you want to use that you need to go to Behaviour Section (of the Combo Box) and bind "Selected" to the value of a cell which is the output (or selection) of the pie chart. You need to make sure that the content of that cell would be one of the Labels of the Combo box.

Good luck with your dashboard!

Regards,

Matt

[Former Member]

Hi,

I will start replicating your data and try to find a solution which does exactly what you want.

Before doing that tho it is worth asking if the data can only be displayed in a crosstab. In Dashboards the best way to have your data would be something like:

Column 1        Column 2           Column 3

USA                 L.A.                 3m

ENGLAND        London             5.5m

GERMANY       Frankfurt           4m

ENGLAND        Birmingham      3m

USA                 New York         4.5m

GERMANY       Hamburg          2m

USA                 Chicago           3m

In this form the data would be very easy to use (using Combo Boxes with Filtered Rows as Insertion Type and the whole Column 1 <country names> as Labels).

As I said I will try finding a way to solve your problem the way you wanted it but if you could put your data in the format I suggested your life will be a lot easier.

Regards,

Matt