sql server reporting services: 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
##

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


[quoted text, click to view]

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.


[quoted text, click to view]