How to write a report with multiple filters for the same field in SSRS

Question

The below report is currently written using linq to entities to get the data and building an html table in code. I'd like to rewrite it using SSRS and need some advice.

All of the data is in the same table. There will also be date range parameters.

Here is the basic template for the report that is needed.

Categories    0-30     31-60      61-90       >90
Category1     0*         0          0          0
Category2     0          0          0          0
Category3     0          0          0          0
Category4     0          0          0          0
Category5     0          0          0          0

As an example, to get the first value (*) the select statement would look something like

SELECT
  SUM(ItemCount) As Sum_ItemCount
FROM
  Table
WHERE
  TotalLength < 30
  AND Date >= @Date
  AND Date <= @Date2
  AND Category == 'Category1'

Answer 1

This link should help:

http://msdn.microsoft.com/en-us/library/ms156280.aspx

I believe the relevant section/sublinks are available in "Adding a Report to a Project or Solution" in that article.

Answer 2

You can do this in SSRS using a Matrix object (similar to an Excel PivotTable) - for further details, see here: http://msdn.microsoft.com/en-us/library/ms157334%28SQL.100%29.aspx

EDIT - suggested sample query:

SELECT
  CASE WHEN TotalLength <= 30 THEN '0-30'
       WHEN TotalLength > 30 AND TotalLength <= 60 THEN '31-60'
       WHEN TotalLength > 60 AND TotalLength <= 90 THEN '61-90'
       ELSE '>90'
  END AS LengthBand,
  Category,
  SUM(ItemCount) As Sum_ItemCount
FROM
  Table
WHERE
  Date >= @Date
  AND Date <= @Date2