Vista Customization- Add Creation Date to Job Reporting

Scenario: Looking to add Job Creation Date to a Report:

Example Solution:

Create a Custom SQL View with code listed at the bottom.

Link the cts_JOBAUDIT view with the JCJM view or similar view as seen below.

Add the DateTime and UserName fields to your custom report in Crystal Reports.

Test and make sure the report and view are returning valid data.

SQL Query

USE [Viewpoint]
GO

CREATE VIEW [dbo].[cts_JOBAUDIT]

/** User Defined View **/ 

/** Purpose: View that adds Job Add Audit Detail **/

/** Created by Constructive Tech Solutions 8/19/2020 **/

AS

SELECT j.JCCo, j.Job, a.DateTime, a.UserName from JCJM j
LEFT OUTER JOIN HQMA a on j.JCCo = a.Co and ('JC Co#: ' + convert(char(3), j.JCCo) + ' Job: ' + j.Job = a.KeyString) and a.RecType = 'A' and a.TableName = 'bJCJM'

GO

GRANT SELECT ON [dbo].[CTS_JOBAUDIT] TO VCSUsers

GO