Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

code to show top five entries in a pie chart Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 2:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 6:23 AM
Points: 15, Visits: 50
Hello

I have a pie chart which displays many legends on a pie chart but i need to write sql code to show the top five entries/legends on the piechart. anybody provide a solution?
Post #1437742
Posted Tuesday, April 2, 2013 8:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:31 AM
Points: 26, Visits: 180
Would this be a matter of adding TOP 5 to the SQL statement?

Something like

SELECT TOP 5 fieldname1, fieldname2 FROM tablename

Post #1437945
Posted Wednesday, April 3, 2013 8:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:43 AM
Points: 317, Visits: 1,524
Couple of thoughts for you to consider which might not give you the answer you desire, but certainly provides options:

1) Pie charts are not the best data visualization tool. Recommend using a horizontal bar graph instead. Here's a link from a data visualization expert, Stephen Few, on the topic: http://www.perceptualedge.com/articles/visual_business_intelligence/save_the_pies_for_dessert.pdf

2) If your data is aggregated in the SQL code, you could stamp row_number() in the SQL as a separate field (called "Rank") to identify your amounts. Then reference the Rank field to trigger the visibility of the category descriptions of the top 5: = iif(Fields!Rank.value<=5,False, True).

3) If you're using SSRS 2008 R2 or above, you could easily go with data bars within a table rather than a pie chart. Again, based on some sort of rank, only show the descriptions for the top 5:
http://msdn.microsoft.com/en-us/library/ee633676(v=sql.105).aspx

4) If you're still on SSRS 2005 (like me) you could embed a chart in a table. One column would display the category, another column would contain the bar chart. (Again, based on some sort of rank, only show the descriptions for the top 5.) See "SSW Better Rules to SQL Reporting Services": http://www.ssw.com.au/ssw/standards/Rules/RulesToBetterSQLReportingServices.aspx#maxminvalues

--Pete



Post #1438373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse