Report layout matrix/dynamic columns...

Hello

Could someone please help me with how I can produce a report. I'm new to Reporting Services and I'm thinking this should be so easy, but it doesn't seem to be. A simplified version of my data is:

Table 1
=======
JobNumber Date Staff
123 01/01/06 5
444 01/03/06 6

Table 2
=======
JobNumber FieldName FieldValue
123 Apples $13.23
123 Deleted False
444 Deleted True
444 Oranges 23

I need to create a report with the following output:

Report
======
JobNumber Date Staff Apples Deleted Oranges
123 01/01/06 5 $13.23 False
444 01/03/06 6 True 23

The FieldNames in Table 2 are variable, any number of new field names could be added and I don't know what they are. I have tried using a matrix, a subreport and I cannot get either to produce the desired result.

Also I have control over the layout of table 2 if that helps. It was originally set up as:

JobNUmber Apples Deleted Oranges
123 $13.23 False Null
444 Null True 23

Thanks in advance.

Maybe you can join the tables together to get something like:

JobNumber FieldName FieldValue

123 Date 01/01/06
123 Staff 5
123 Apples $13.23
123 Deleted False
444 Date 01/03/06
444 Staff 6
444 Deleted True
444 Oranges 23

Then you can use a matrix, grouping by JobNumber on the rows and FieldName on the columns.

-Albert

|||

Cheers Albert. I think I can achieve that, but how do I get my Staff and Date columns into the matrix? If I add them to it then I can't get column headers for these fields? I end up with something like this

Report
======
Apples Deleted Oranges
123 01/01/06 5 $13.23 False
444 01/03/06 6 True 23

Cheers

|||

You need to do something like this:

SELECT JobNumber, FieldName, FieldValue FROM [Table 2]
UNION SELECT JobNumber, 'Date', Date FROM [Table 1]
UNION SELECT JobNumber, 'Staff', Staff FROM [Table 1]

Does that work for you?

-Albert