hello, I have recently started to use SSRS 2008 R2 and Report Builder 3.0.
I need to create a report that shows me ALL current cases AND those that have a risk assessment logged against them. There is one row per case, but each case can have multiple risk assessments (rows) logged against them.
I have 2 datasets that I carred through to Report Builder in the hopes that I could try a LOOKUPSET to join the 2 datasets but this just generated an error. the integer fields that match in each dataset are …
caseheader ].caseno = notedata_person.mainref_per
The SELECT statements for these 2 datasets are shown below, can someone show me how they can be joined together via the sql/query side so that I can produce a report that shows cases AND all of the risk assessments logged against them…thank you for you assistance
SELECT
[category ].decode AS [category decode]
,[caseheader ].caseno
,[caseheader ].status
,[caserecvfrom ].ethnicorig AS [caserecvfrom ethnicorig]
,[caserecvfrom ].callowner
,[caserecvfrom ].propref
,[caserecvfrom ].tensuffix
,[casetype ].decode AS [casetype decode]
,[casehist ].actiondate
,[caseheader ].caseuserid
,[caserecvabout ].sex AS [caserecvabout sex]
,[caserecvabout ].ethnicorig AS [caserecvabout ethnicorig]
,[caserecvfrom ].sex AS [caserecvfrom sex]
FROM
[category ]
INNER JOIN [caseheader ]
ON [category ].code = [caseheader ].categorycode
INNER JOIN [caserecvfrom ]
ON [caseheader ].caseno = [caserecvfrom ].caseno
INNER JOIN [casetype ]
ON [caseheader ].casetypecode = [casetype ].code
INNER JOIN [casehist ]
ON [caseheader ].caseno = [casehist ].caseno
INNER JOIN [caserecvabout ]
ON [caseheader ].caseno = [caserecvabout ].caseno
WHERE
[category ].decode LIKE N’ASB%’
AND [caseheader ].status = N’O’
AND [caserecvfrom ].incfromref = 1
AND [casehist ].[action] = 1
AND [caserecvfrom ].incfromref = 1
AND [caserecvabout ].incaboutref = 1
SELECT
[notecontrol ].notetype
,[notecontrol ].notecode
,[notecontrol ].decode
,notedata_person.mainref_per
,notedata_person.userid
,MAX(notedata_person.notedate)AS Maxdate
FROM
[notecontrol ]
INNER JOIN notedata_person
ON [notecontrol ].notetype = notedata_person.notetype AND [notecontrol ].notecode = notedata_person.notecode
INNER JOIN [caseheader ]
ON notedata_person.mainref_per = [caseheader ].caseno
WHERE
[notecontrol ].notetype = N’CS’
AND [notecontrol ].decode LIKE N’RISK%’
AND [caseheader ].status = N’O’
GROUP BY
[notecontrol ].notetype
,[notecontrol ].notecode
,[notecontrol ].decode
,notedata_person.mainref_per
,notedata_person.userid
,notedata_person.notedate
ORDER BY notedata_person.mainref_per