The table chart provides a basic table representation of the data returned from your query. Table charts are effective in providing a quick view of data for your viewers and additional interactivity by allowing that data to be exported to CSV.
In this example, we’ll use the following script:
DECLARE @FieldName VarChar(1000) = 'Confidential Designation' --SET FIELD NAME HERE
--You must replace the value "Responsive Designation" with the name of the
--field that you want to group by
--the field type MUST be a Yes/No Field, Multiple Choice Field or a Single Choice Field
DECLARE @IncludeTotal int = 0 --SET AS 1 TO INCLUDE THE TOTAL NUMBER OF DOCUMENTS IN ANOTHER ROW. SET AS 0 TO NOT INCLUDE THE TOTAL.
DECLARE @CodeTypeID int
SELECT @CodeTypeID = CodeTypeId FROM eddsdbo.Field WHERE FieldArtifactTypeID = 10 AND [FieldTypeID] IN (5,8,3) AND [DisplayName] = @FieldName
IF @IncludeTotal = 0 EXECUTE ('SELECT (CASE WHEN c.Name IS NULL THEN ''Not Set'' ELSE c.Name END) AS ['+@FieldName+'],
COUNT(*) as [Number of Documents]
FROM
eddsdbo.Document d
LEFT OUTER JOIN
eddsdbo.ZCodeArtifact_'+@CodeTypeId+' z ON d.ArtifactID = z.AssociatedArtifactID
LEFT OUTER JOIN
eddsdbo.Code c ON z.CodeArtifactID = c.ArtifactID
GROUP BY (CASE WHEN c.Name IS NULL THEN ''Not Set'' ELSE c.Name END)')
ELSE EXECUTE ('SELECT (CASE WHEN c.Name IS NULL THEN ''Not Set'' ELSE c.Name END) AS ['+@FieldName+'],
COUNT(*) as [Number of Documents]
FROM
eddsdbo.Document d
LEFT OUTER JOIN
eddsdbo.ZCodeArtifact_'+@CodeTypeId+' z ON d.ArtifactID = z.AssociatedArtifactID
LEFT OUTER JOIN
eddsdbo.Code c ON z.CodeArtifactID = c.ArtifactID
GROUP BY (CASE WHEN c.Name IS NULL THEN ''Not Set'' ELSE c.Name END)
UNION
SELECT ''Total Documents'' AS ['+@FieldName+'],
COUNT(*) as [Number of Documents]
FROM
eddsdbo.Document d
')
Doing so will result in a table chart that looks like this:
If there are a lot of results the table can be searched and paged through. A small document icon is visible in the top right corner of table charts that enable the results of the chart to be downloaded to CSV and opened up in Excel.