Column and Row grouping in SQL Server Reporting Services 2008

Question

this is the desired result in need to populate as a report. where xx is no of people.

i have a table which has fields like :

----------
table1
----------
id
state
year(as Quarter)
gender

so i need to determine the count from id and populate as a report. the year is like 20081,20082..20084(in quarter).

i have created a dataset using this query

SELECT STATE,GENDER,YEAR,COUNT(*)
FROM TABLE 1
GROUP BY STATE,GENDER,YEAR

from this query i could populate the result

ex: ca, m , 20081,3
    ny, f , 20091,4

from the above query i could populate the count and using group by(row) state(in ssrs) .

I am new to ssrs, i need your direction how should i go about.

now i need to group by (column) from the gender i get and by year.

1. how do i take the column gender and make it has Male and Female column

2. do i need to create multiple dataset like passing

   where gender = 'M' or gender = 'F'

so that i could have 2 dataset one for Male and One for Female. else is there anyway i could group from the Gender field just like pivot.

1. should i populate result separately like creating mulitple dataset for Male 2008,Female 2009 or is there any way i could group by with the single dataset using SSRS Matrix table and column grouping.

2. Should i resolve it at my Query level or is there any Features in SSRS which could solve this problem.

any help would be appreciated. i believe everybody understood my question.

Answer 1

Your SQL query looks good, but I would remove the quarter with a left statement:

select state, gender, left(year,4) as [Year], count(ID) as N from table1 group by state, gender, left([year],4)

Then you have a classic case for a Matrix. Create a new report with the Report Wizard, choose "Matrix", then drag the fields across:

Rows: State

Columns: Year, Gender

Details: N

This should give you the required Matrix. Then replace the expression of the textbox with the Gender from

=Fields!gender.Value

to

=IIF(Fields!gender.Value="M", "Male", "Female")

Good luck.