Scenario: Looking to add Job Creation Date to a Report:
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.
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
Leave A Comment