Sunday, March 14, 2010

Hierarchical Pie Charts Anyone??

So last week I started my new job where I'm working with SQL Server 2008 and Reporting Services. I was anxious to get my hands on 2008 and start playing around. I learned that Dundas charts was integrated into SSRS with 2008, which is cool. Since I'd not done charts before and I'll be doing dashboards with the new job I started playing around with that pretty quickly. I liked it a lot. But then I ran into a snag. And this isn't just an SSRS charts rant, but a exclamation regarding the lack of true hierarchical reporting in a pie chart across the entire IT industry's BI/Data Visualization niche.

So I wanted to do a pie chart with multiple circles that represented parents as inner rings and children as outer rings in sub-slices of the pie (don't ask me why, it's just something I thought would be a clear way of presenting some data that I me a data geek, you're right, I am). Via Google's image search, I quickly found examples of multi-level hierarchical pie charts like this one. BUT, I wasn't able to locate an application that took a hierarchical data table and presented it like this. Excel doesn't do it. SSRS 2008 doesn't do it. Okay, let me rephrase that, SSRS 2008 and Dunda Charts does have the "capability" of creating a report that "looks" like this; however, ain't pretty and is certainly isn't hierarchical in nature. I was appalled at what I had to do to get some very basic hierarchical data to display in the manner in which I thought was intuitive for this type of chart.

So first I scoured the web looking for an application that did this. Apparently, I'm not the only one because MOST of what I found was content from others, like myself, looking for an app that does this type of chart! The names uses for this chart was mind-numbing (multi-level pie, stacked pie, multi-level donut, radial treemap, sunburst). Then looking at the Dundas website, I found this image in their gallery which definitely looks like a hierarchical pie (they call it a doughnut) although the parent/child ordering rings are inverse. So I definitely thought this was possible and just something I had named incorrectly. I WAS indeed incorrect, on more levels than I knew.

So after much trial and error, (actually I had to download the Dundas template and look at their data), I was able to get this chart. I am happy with the results but lord almighty was this something beyond stupid in terms of what I had to do to my data to get the desired results. And beside that, the inner (sub) chart cannot be moved into place with simple drag-and-drop functionality but must be modified with x-y coordinates and length and width variables which is so so very kludgey. What should have taken seconds took much longer and was much less enjoyable as a result. Yes, the data geek got irritated by the UI (User Interface).

So here is the original data (the design elements should be repeated in each section):

But to get the table I wanted, I ended up with data below. So, I had to delete the parent values in all but the first row of each group and then basically I had to put together ratios that would force the items to chart out the way I wanted. The inner chart has absolutely no relationship to the outer chart except that they use the same base table data. Also I ended having to add an ADDITIONAL column with an incremental count so they displayed around the pie in the order I wanted as it didn't do that as the chart got more complex.

I really am shocked that there isn't a tool that does this, or at least not one that it readily available. Dundas appeared to have it but that just goes to show that just because a vendor displays an image of soemthing doesn't mean it's done the way it appears to have been done. Anticipated or not, it's misrepresenting what the tool actually does. Lesson learned.


  1. After all that work, years later, I determined that all I needed to do was highlight the donut and hit alt-c and then alt-v and it inserts a smaller donut inside the first (did this by accident initially, lol). I was not able to find a way to control the coloring schema in an aesthetically pleasing way, but now I'm pretty sure there is a way and that I just can't find the necessary documentation.,

  2. HI Tracy, Even a year later its hard to find a tool. Did you find the nessessary documentation yet?
    regards Emily