I am working on a report to display data based on Hold Names based on different criteria. Since I have different criteria, I have used a SQL temp table to get the results and the data displays correctly in table format in SQL Studio, but the data shows as a single CSV format line in LiveReports. I am using an Auto LiveReport in Transaction Mode. The SQL statement that I am using is:
CREATE TABLE #LegalHoldNameRecordCount
(HoldName VarChar(MAX) COLLATE DATABASE_DEFAULT,
Total#ofRecords Int,
#60dayDispositionRecords Int,
#30dayDispositionRecords Int,
#EFDDispositionRecords Int);
INSERT INTO #LegalHoldNameRecordCount
SELECT DISTINCT HoldName,'0','0','0','0'
FROM
DispositionHold dh
UPDATE #LegalHoldNameRecordCount
SET Total#ofRecords = a.HoldCount
FROM
(SELECT DISTINCT dh.HoldName,COUNT(d.DataID) AS HoldCount
FROM
Dtree d
left outer join rimsNodeClassification rnc on d.DataID = rnc.nodeID
left outer join RM_CLASSIFICATION rm on rnc.ClassID = rm.NodeID
left outer join RM_ObjectHold h on d.DataID = h.NodeID
left outer join DispositionHold dh on h.HoldID = dh.HoldID
WHERE dh.ActiveHold = 1 and SubType = 749
GROUP BY dh.HoldName) as a
WHERE #LegalHoldNameRecordCount.HoldName = a.HoldName
UPDATE #LegalHoldNameRecordCount
SET #60dayDispositionRecords = a.Disp60Count
FROM
(SELECT dh.HoldName, COUNT(DISTINCT d.DataID) AS Disp60Count
FROM
Dtree d
left outer join rimsNodeClassification rnc on d.DataID = rnc.nodeID
left outer join RM_CLASSIFICATION rm on rnc.ClassID = rm.NodeID
left outer join RM_ObjectHold h on d.DataID = h.NodeID
left outer join DispositionHold dh on h.HoldID = dh.HoldID
WHERE
d.subtype = 749
and dh.ActiveHold = 1
and rimsStatus = '1ST'
GROUP BY dh.HoldName) as a
WHERE #LegalHoldNameRecordCount.HoldName = a.HoldName
UPDATE #LegalHoldNameRecordCount
SET #30dayDispositionRecords = a.Disp30Count
FROM
(SELECT dh.HoldName, COUNT(DISTINCT d.DataID) AS Disp30Count
FROM
Dtree d
left outer join rimsNodeClassification rnc on d.DataID = rnc.nodeID
left outer join RM_CLASSIFICATION rm on rnc.ClassID = rm.NodeID
left outer join RM_ObjectHold h on d.DataID = h.NodeID
left outer join DispositionHold dh on h.HoldID = dh.HoldID
WHERE
d.subtype = 749
and dh.ActiveHold = 1
and rimsStatus = '2ND'
GROUP BY dh.HoldName) as a
WHERE #LegalHoldNameRecordCount.HoldName = a.HoldName
UPDATE #LegalHoldNameRecordCount
SET #EFDDispositionRecords = a.EFDCount
FROM
(SELECT dh.HoldName, COUNT(DISTINCT d.DataID) AS EFDCount
FROM
Dtree d
left outer join rimsNodeClassification rnc on d.DataID = rnc.nodeID
left outer join RM_CLASSIFICATION rm on rnc.ClassID = rm.NodeID
left outer join RM_ObjectHold h on d.DataID = h.NodeID
left outer join DispositionHold dh on h.HoldID = dh.HoldID
WHERE
d.subtype = 749
and dh.ActiveHold = 1
and rimsStatus = 'DST'
GROUP BY dh.HoldName) as a
WHERE #LegalHoldNameRecordCount.HoldName = a.HoldName
SELECT * from #LegalHoldNameRecordCount
The results of the statement return the following in LiveReports:
{23,V{<'HoldName','Total#ofRecords','#60dayDispositionRecords','#30dayDispositionRecords','#EFDDispositionRecords'><'116TEST',15,0,0,0><'ADVANCED SEARCH HOLD',16,0,0,0><'AMBER\'S HOLD',0,0,0,0><'AUDIT HOLD 1',1,0,0,0><'AUDIT HOLD 2',2,0,0,0><'AUDIT HOLD 3',0,0,0,0><'CS 10.5 TESTING',0,0,0,0><'HOLD CREATED BY LEGAL USER',0,0,0,0><'IN WC',0,0,0,0><'INFORMAL TEST',2,0,0,0><'LEGAL HOLD 1',6,0,0,0><'LEGAL HOLD 2',2,0,0,0><'LEGAL HOLD 3',5,0,0,0><'LEM075 LEGAL COLLECTION',1,0,0,0><'LEM075 RECORDS ADMIN',0,0,0,0><'LM HOLD CS 10.5',0,0,0,0><'LONG TERM LEAVE EXEMPTION',0,0,0,0><'SEARCH REGION TESTING',1,0,0,0><'TAX HOLD 1',0,0,0,0><'TAX HOLD 2',0,0,0,0><'TAX HOLD 3',0,0,0,0><'TEST',0,0,0,0><'TEST INTERIM',29018,1,0,1>}}
Any assistance to resolve this issue is greatly appreciated.
