Creating Pie Chart

There is no combination when I drag in cost and margin that I ever get two slices. I can only drag 1 item into color and 1 into size which always gives me 1 slice.

Thanks Chad, can you show me a screenshot of your data tab?

It's basic cost and margin. I want to link this on a dashboard so the numbers will change according to time range selected. I've also added date field to this data but it doesn't help to ever get two slices I've only been allowed to have 1 item in each box on the chart creation.

Unfortunately you are going to need to have each of your metrics in one column. Right now you are seeing that cost is 100% of itself, and is the color of margin. To get this to work you will want the data in this format:

Type | Amount

Cost | 50,000

Margin | 25,000

I think that there is a little trick to do this conversion in one report, but its not on the top of my head. If you can give me a few minutes I will try and mess around with this. Hopefully I will have something useful for you soon here.

Regards,

Nathan

So to do this we will need to:

1. Create a view level calculated field called "cost" that is simply "cost", and another called margin:

2. Create a union sub-query, joining on these calculated fields (cost=margin) and your pre-existing fields(cost=margin):

Which will give you the amounts in the proper format. Now the metrics can be size, and the labels(cost/margin) can be your color.

Please let me know if this works for you.

One other note, it will be necessary to include another date filter in the union sub-query and link this filter to the primary date range filter that your report needs.

Well it does work but that's an ugly work around with an undesired effects. The labels are numbers instead of being Cost and Margin. Also is there a feature request to allow a user to change slice color?

In this case it is a difficult work around, but In terms of report construction, the current pie chart design works in far more cases than having the metrics be individual columns. (Eg when you have sales by region, your regions will most likely be rows rather than columns)

The reason that I included the view level calculated fields was specifically so that the labels would be appropriate:

You can adjust what order colors are assigned in all charts through the content settings (the chart builder will assign colors in the order specified in the "Colors"). There are currently enhancements to allow you to change this on a report by report basis which I will add your information to.

Hi Chad,

Any luck getting the labels to work?

Regards,

Nathan

I had left yesterday right after the image of the pie chart post and didn't get to try until this morning. I was able to get it to work. I had misread the create calculated fields as just returning the same number not actually typing in a string even though it's obvious now. So this is a fine workaround just not something I would have figured out on my own. Also thanks for showing me the Color settings. I had not been in the Content Settings before so there wee all sorts of nice defaults to set for our environment.

Glad to hear you got it working. I'm going to switch this to answered, but let me know if you run into any issues or have any other questions.

Nathan

On a Dashboard I was trying to link a drill down field to this report. I can do it successfully on other reports but this report doesn't list any options. I can link a user input filter but not a drill down. Is this not possible in a sub-query report?

Hi Chad,

I am not sure if this is what you are referring to, but it is not possible to link a drill down through a sub-query. In the underlying SQL, the join will go from:

A_parent=A_parent to A_child = A_parent

I'm not sure if that answers my question or not. It explains why I can't drill down on a sub query report but is that the same thing I'm trying to do on the dashboard? On a dashboard I'm trying to have a drill down on one report be a filter on this pie chart report. I have a line chart broken down by dates. So when I click on say November on my line chart I want it to filter my pie chart results to November so I can see our cost and margin in November.