Problem in creating a Pie Chart

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Problem in creating a Pie Chart

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-11-26 : 06:51:28

Hi All,

I have a table which stores values like Yes, Never and Close.

I need to display the % of each of them in a Pie chart.

my query is like:

select CONVERT(float,LC)/CONVERT(float,Total) as LC_Perc
,CONVERT(float,LY)/CONVERT(float,Total) as LY_Perc
,CONVERT(float,LN)/CONVERT(float,Total) as LN_Perc
from
(
select LC,LY,LN,LC+LY+LN as Total from
(
select sum(LessonClose) as LC,sum(LessonYes) as LY, sum(LessonNever) as LN
from Lessons
)tbl1

)tbl2

and the result set is :
LC_Perc----------LY_Perc--------LN_Perc
22%--------------18%-----------60%

I tried to plot these on the pie chart and m not getting the desired result. I get only one sector on the Pie. the other two are not mapping correctly.

Can we not display multiple columns on the same pie?

How can i solve this?

Please suggest. its bit urgent.

Thanks,
Malavika.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 07:03:26

try using a single column for the amounts

select type, CONVERT(float,amt)/CONVERT(float,tot) as amt
from
(
select type = 'LC', sum(LessonClose) as amt, tot = sum(LessonClose)+sum(LessonYes)+sum(LessonNever) from Lessons
union all
select type = 'LY', sum(LessonYes) as amt, tot = sum(LessonClose)+sum(LessonYes)+sum(LessonNever) from Lessons
union all
select type = 'LN', sum(LessonNever) as amt, tot = sum(LessonClose)+sum(LessonYes)+sum(LessonNever) from Lessons
)tbl1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 07:07:27

Just tried it.
If you do that and set the category field to type and data field to amt it should work
I also don't think you need to calculate the percentages - it will do that for you.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mrm23
Posting Yak Master

198 Posts

Posted - 2010-11-26 : 07:21:20

Thanks a lot for your reply. I now get the desired output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources