SSRS 2008R2
Hello,
I need to have the results of a certain ID it one row rather than multiple rows.
SQL (Simplified):
SELECT ID, CAST(CONTACTDATE AS DATE) AS 'DT', DESCRIPTION, CAST(ASSOC_ROW AS INT) AS 'ROW'
FROM SERVER.dbo.TABLE
ORDER BY 'DT', 'ROW'
Expression in SSRS - used in the group header - detail is hidden:
=Fields!DT.Value &": " & JOIN(LOOKUPSET(Fields!DT.Value,Fields!DT.Value,
Fields!DESCRIPTION.Value,"DataSet1"),"")
TABLE
ID Row Date Description
01 1 9/1/18 Received report.
01 2 9/1/18 Saved the information.
01 3 9/1/18 Emailed the client.
01 4 9/15/18 Saw client.
01 5 9/22/18 Emailed client.
02 1 9/15/18 Saw the client.
02 2 9/28/18 When to lunch.
03 1 and so on for multiple IDs.....
Output for ID 01: (3 rows)
9/1/18: Received report. Saved the information. Emailed the client.
9/15/18: Saw client.
9/22/18: Emailed client.
Desired Output for ID 01: (1 row)
9/1/18: Received report. Saved the information. Emailed the client. 9/15/18: Saw client. 9/22/18: Emailed client.
If I need to do this in SQL it would be OK too. I just feel I am so close in SSRS.
Any help would be greatly appreciated!
Jackie
Hello,
I need to have the results of a certain ID it one row rather than multiple rows.
SQL (Simplified):
SELECT ID, CAST(CONTACTDATE AS DATE) AS 'DT', DESCRIPTION, CAST(ASSOC_ROW AS INT) AS 'ROW'
FROM SERVER.dbo.TABLE
ORDER BY 'DT', 'ROW'
Expression in SSRS - used in the group header - detail is hidden:
=Fields!DT.Value &": " & JOIN(LOOKUPSET(Fields!DT.Value,Fields!DT.Value,
Fields!DESCRIPTION.Value,"DataSet1"),"")
TABLE
ID Row Date Description
01 1 9/1/18 Received report.
01 2 9/1/18 Saved the information.
01 3 9/1/18 Emailed the client.
01 4 9/15/18 Saw client.
01 5 9/22/18 Emailed client.
02 1 9/15/18 Saw the client.
02 2 9/28/18 When to lunch.
03 1 and so on for multiple IDs.....
Output for ID 01: (3 rows)
9/1/18: Received report. Saved the information. Emailed the client.
9/15/18: Saw client.
9/22/18: Emailed client.
Desired Output for ID 01: (1 row)
9/1/18: Received report. Saved the information. Emailed the client. 9/15/18: Saw client. 9/22/18: Emailed client.
If I need to do this in SQL it would be OK too. I just feel I am so close in SSRS.
Any help would be greatly appreciated!
Jackie