Solved

SSRS pie chart

Posted on 2011-03-14
5
1,295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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