Column and Row grouping in SQL Server Reporting Services 2008

Question

This is the desired result I need to populate as a report, where xx is number of people.

I have a table which has fields like:

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

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 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 two datasets, one for Male and One for Female? Otherwise, is there any way I could group from the Gender field just like pivot?

3. Should I populate result separately like creating multiple 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?

4. 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.

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.