Report services with multiple grouping

Question

I am new to reporting services. I have 2 tables:

• "cars" with columns id, cartype, capacity
• "values" with column id,carid, year, val1, val2

Records for these tables are:

Cars:
id    cartype  capacity

1     Passat   2200

2     BMW      2800

Values:
id    carid  year val1  val2

1      1     2012 100    1

2      1     2011 200    2

3      1     2010 300    3

4      2     2012 400    4

5      2     2011 500    5

I want to make a report that shows this:

Car Type    Capacity

Passat       2200


       2012   2011    2010

val1   100    200     300

val2    1      2      3


Car Type    Capacity

BMV       2800


       2012   2011 

val1   400    500   

val2   4      5     

I made a data source with this select:

 SELECT m.Id AS carid, m.cartype, m.capacity, v.Id AS idval, v.An, v.val1, v.val2
 FROM  car AS m INNER JOIN  values AS v ON m.Id = v.carid

I have tried to use a matrix but I can't succeed in making this format. Can somebody help me to obtain this report?

Answer 1

Your Dataset is fine for this report.

You need to create a List based on the Cars Group, the within this List add two Textboxes for the Car details and a Matrix for val1, val2, etc.

A List allows you flexibility to place and move items as required, and placing a Matrix with the Cars group means it will only include values in scope for each Car. The List (and hence Car details and the values Matrix) will be repeated for each Car as required.

Added after comment:

It's impossible to say what was causing your error; it's really a specific implementation detail. To give an example of how this might be done I've mocked up a report. First step is to create the Car group:

You can see there is one Group, with one Textbox. In the Textbox there is a Rectangle (Lists in SSRS are just tables with Rectangles inserted). Car and Capacity are just Textboxes. In this example I've used two Matrices, but this could be done any number of ways. Val1:

Val2:

Final result:

So you can see it's very possible, you just need to understand the grouping required and how to construct a matrix. Unfortunately it's impossible to say what caused this error but hopefully this gives you something to aim towards.