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
