July 31, 2006 at 5:13 pm
I am having trouble with this report. Please help. Here is sample data set:
create table Status
(ID int,
Status varchar (100))
go
insert Status values (1, 'Closed')
insert Status values (2, 'Closed')
insert Status values (3, 'Closed')
insert Status values (4, 'Closed')
insert Status values (5, 'Closed')
insert Status values (6, 'Open')
insert Status values (7, 'Open')
insert Status values (8, 'Pending')
insert Status values (9, 'Pending')
insert Status values (10, 'Pending')
select * from Status
I wanted to create a pie chart including percentage of Status. I create a pie chart, dropped ID column into Data fileds, dropped Status column into category fields. The pie shows exactly the number of status in each slide: 5 for Closed, 2 for Open, 3 for Pending. I am looking to get a percentage of each status by dividing each number of status by the total of rows. However I can't find where to change the scope to look for count(=Fields!ID.Value) for the whole table, not only for each status. Is this possible here? Thanks
August 1, 2006 at 2:03 pm
/* following code is called grouped data (which will produce only one row for each different status)
Select will return STATUS, CNT, PERC as columns */
select S.Status 'STATUS', count(*) 'CNT',
(count(*)/(select count(*) Status(nolock))) 'PERC'
from Status S(nolock)
group by S.Status
order by S.Status
Coach James
August 1, 2006 at 2:14 pm
Thanks for the reply. However it is not what I wanted to find out. I currently use a similar SELECT statement to include the percentage as another column but in SQL RS I would like to know if it is possible to change the scope to unclude all rows.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply