Column and row grouping in SQL Server Reporting Services 2008

Question

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

I have a table with fields:

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

I need to determine the counter from id and populate it as a report. The year is similar to 20081, 20082..20084 (per quarter).

I created a dataset using this query:

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

From this query, I could fill out the result

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

From the above query, I could fill the counter and use the group (row) state (in ssrs).

I need to group by (column). From the floor I get and over the years.

• How do I take a half column and make it with male and female columns?

• Do I need to create multiple datasets like transfer

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

  so that I have two data sets: one for men and one for women? Otherwise, is there a way that I could group from the Gender field in the same way as a pivot point?

• Should I fill out the result separately, for example, create several data sets for Male 2008, Female 2009, or is there a way I could combine with one data set using the SSRS Matrix table and column grouping?

• Should I allow it at my query level or are there any features in SSRS that could solve this problem?

Any help would be appreciated.

Answer 1

Your SQL query looks good, but I would remove the quarter with the 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 using the Report Wizard, select the "Matrix", then drag over all fields:

Lines: state

Columns: Year, Gender

Details: N

This will give you the required matrix. Then replace the text field expression with "Gender" from

 =Fields!gender.Value 

to

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

Good luck.