Solved

SSRS pie chart

Posted on 2011-03-14
5
1,288 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

16 Experts available now in Live!

Get 1:1 Help Now