Solved

SSRS pie chart

Posted on 2011-03-14
5
1,285 Views
Last Modified: 2012-06-27
Pretty new to the new charts within SSRS, trying to do my first one with percentages.  My logic actually outputs the percentages by default, but the pie chart isn't presenting them properly.  

My data set has two values:  Media Type, Percent
In Chart Data, the Percent is in values section, the Media Type is in the Category Groups.
I have right-clicked my pie chart and selected 'Show Data Labels'
I have right clicked one of those labels and made the Label Data #PERCENT.

I think it is because of the number of slices.  The logic posted below returns 11 records.  
While in design pane, the data is accurately represented, but it only shows six slices.  
When I preview, all eleven or grahically represented, but they're all the same size, and they're all tagged 9.09%.

screen shots of both are attached.

Anybody?

--logic
SELECT
	m.[Description] [Media Type],	    	    
	[Percent %] = convert(VARCHAR,convert(MONEY,100.0 * COUNT(MediumID) / (SELECT COUNT(*) FROM dbo.CampaignDetails)),1) + ' %' 
FROM
	dbo.CampaignDetails c JOIN dbo.MediumTable m
	ON c.MediumId = m.[Id]
GROUP BY
	m.[Description]
ORDER BY
	[Media Type]

Open in new window

design.png
preview.png
0
Comment
Question by:dbaSQL
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:itcouple
ID: 35132750
Hi

I personally avoid pie charts http://www.itcouple.co.uk/when-to-use-pie-charts.aspx (The article is very basic but at the bottom there is a link to proper explanation).

If you have choice I would go with data bar (2008 R2) if you don't have data bar you can still embed chart into tablix.

Regarding your query you seem to return % as string. I wouldn't do that as SSRS does it very well (number to %)

Hope that helps
Emil
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35132789
>>Regarding your query you seem to return % as string. I wouldn't do that as SSRS does it very well (number to %)
Are you suggesting I just change it to do a count, and allow SSRS to translate to % ?

I think the pie chart is usable, in very few areas.  And as I said, this is fairly new to me.  Before I move to a data bar, I would at least like to understand the reason I am having this problem.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35132820
>>I would at least like to understand the reason I am having this problem.
It may have been simply my string calc.  I changed it to COUNT, instead of my percentage calculation, and now the pie previews accurately.
0
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 35133274
Hi

I'm glad to see you fixed the issue. Yes pie chart just needs rows of data with values it works out % by itself because it knows total is 100%.

I think pie charts are very common as people learned proportions on them (kids) but the irony is that proportions is something pie chart does very badly because you cannot compare them very easily. I have tried finding cases where pie charts are better then other options but finally I gave up and I admit pie charts should be only eaten :)

Anyway it takes some time to get used to this and business usually likes pie charts and the audience is one of the key elements of reports developments so if they want it let them have it ;)

Good luck with your new challenges
Emil
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35134654
You got that right... the audience loves the pie charts!

I'll award you the points, Emil.  I appreciate your time and your input.

Thank you.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now