complex report

have the following data from a query
age sex
39 M
48 F
14 M
etc...
need to be displayed as
Age Group Number of Male Number of Female Total in Age Group
0 -2 2 3
5
3 - 9 24 33
57
....
70 and above 12 11
23
Total Males ##
Total Females ##
Total
##
Any suggestions would be helpful.This question is better geared towards a SQL forum rather then reporting
services.
Search online for the SQL syntax of Case. You'll want to create a select
statment where you do something similar to this.
SELECT '0-2' as Age Group, SUM(CASE age>0 AND age < 2 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>0 AND age < 2 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 0 AND age < 2) as Total in Age Group
UNION
SELECT '3-9' as Age Group, SUM(CASE age>3 AND age < 9 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>3 AND age < 9 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 3 AND age < 9) as Total in Age Group
And so on and so forth for each of the age/sex ranges.
-Michael Abair
Programmer Analyst
Chicos FAS Inc
"Chop" <Chop@.discussions.microsoft.com> wrote in message
news:0D4FB1BC-D77D-4E6B-A728-B798B6EAF6E4@.microsoft.com...
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.|||You also have the option of creating fields within RS; for the values
use:
iif(fields!age.value >=0 and fields!age.value < 3, "0 - 2",
iif(fields!age.value < 10, "3-9", ...))
Then use a matrix item and use the calculated field as your row group,
sex as your column group.
Chop wrote:
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.