All reports generated in Nathean Analytics are stored in the Nathean Analytics Audit database.

They can be accessed by developing a Dataset to run against this database using the following SQL as a guide. Developers will need to have the Nathean Analytics Audit website service running in order to support the downloading of historical PDFs and will need to adjust the URL referenced below as “[SERVER_NAME]” to match up with their own implementation of Nathean Analytics.

SELECT 
 convert(varchar,GENERATED_DATE,110) + '-' + DATENAME(WEEKDAY,GENERATED_DATE) AS AUDIT_DAY,
 CASE WHEN A.SCHEDULE_ID = 0 THEN NULL ELSE A.SCHEDULE_ID END AS SCHEDULE_ID,
 CASE
  WHEN A.SCHEDULE_ID = 0
  THEN 'Run by: ' + tb.login_name
  else ISNULL(S.CAPTION,'Deleted Schedule') END AS CAPTION,
 CASE
  WHEN S.RECURRENCEINDEX = 1 THEN 'Never'
  WHEN S.RECURRENCEINDEX = 2 THEN 'Daily'
  WHEN S.RECURRENCEINDEX = 3 THEN 'Weekly'
  WHEN S.RECURRENCEINDEX = 4 THEN 'Monthly'
 END AS SCHEDULE_REPEATS,
 CASE
  WHEN  S.RECURRENCEINDEX = 3 THEN --WEEKLY
     REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(
     'Every ' + ISNULL(CONVERT(VARCHAR, RI_PERIODICITY),'')   +
     CASE RI_WEEK_DAYS_MONDAY WHEN 1 THEN ' Monday ' ELSE ' ' END + 
     CASE RI_WEEK_DAYS_TUESDAY WHEN 1 THEN ' Tuesday '  ELSE ' ' END + 
     CASE RI_WEEK_DAYS_WEDNESDAY WHEN 1 THEN ' Wednesday '  ELSE ' ' END + 
     CASE RI_WEEK_DAYS_THURSDAY WHEN 1 THEN ' Thursday '  ELSE ' ' END + 
     CASE RI_WEEK_DAYS_FRIDAY WHEN 1 THEN ' Friday '  ELSE ' ' END + 
     CASE RI_WEEK_DAYS_SATURDAY WHEN 1 THEN ' Saturday '  ELSE ' ' END + 
     CASE RI_WEEK_DAYS_SUNDAY WHEN 1 THEN ' Sunday '  ELSE ' '  END ,'  ',' '),'   ',' '))),'  ',' ')
  WHEN  S.RECURRENCEINDEX = 2 THEN --Daily
    'Every ' + ISNULL(CONVERT(VARCHAR, RI_PERIODICITY),'')  + CASE ISNULL(RI_PERIODICITY,-1) WHEN -1 THEN 'Day' ELSE ' Days' END
  WHEN  S.RECURRENCEINDEX = 4 AND RI_TYPE IS NULL THEN --Monthly specifying day
   'Every ' + ISNULL(CONVERT(VARCHAR, RI_PERIODICITY),'')  + ' Month on Day ' + CONVERT(VARCHAR,RI_DAY_NUMBER)
  WHEN  S.RECURRENCEINDEX = 4 AND RI_TYPE IS NOT NULL THEN --Monthly specifying day
  'Every ' +  CASE RI_DAY_NUMBER 
   WHEN 1 THEN 'First '
   WHEN 2 THEN 'Second '
   WHEN 3 THEN 'Third '
   WHEN 4 THEN 'Fourth '
   WHEN -1 THEN 'Last ' END  
    + CASE RI_TYPE 
                WHEN 1 THEN 'Day'
                WHEN 2 THEN 'Weekday'
                WHEN 3 THEN 'Sunday'
                WHEN 4 THEN 'Monday'
                WHEN 5 THEN 'Tuesday'
                WHEN 6 THEN 'Wednesday'
                WHEN 7 THEN 'Thursday'
                WHEN 8 THEN 'Friday'
                WHEN 9 THEN 'Saturday'
                WHEN 10 THEN 'Weekend Day' END
  ELSE 'Never'
END AS REPEATING,
 A.REPORT_ID,
 A.REPORT_NAME,
 GENERATED_DATE,
 DATENAME(WEEKDAY,GENERATED_DATE) AS GENERATED_DAY,
 COALESCE(DATALENGTH(CSV_OUTPUT),DATALENGTH(EXCEL_OUTPUT),DATALENGTH(FIXED_FORMAT_OUTPUT),DATALENGTH(XLSX_OUTPUT)) / 1024 AS OUTPUT_SIZE_KB,
  '<a href="http://[SERVER_NAME]/LogixAudit/api/ReportDownload?id=' + CAST(A.SAVED_REPORT_ID AS VARCHAR(110)) + '&fileName=' + convert(varchar,GENERATED_DATE,110) + '-' + A.REPORT_NAME + '" target="_blank" class="btn btn-info btn-xs" role="button"><div class="glyphicon glyphicon-th-list" title="Open View"></div></a>'  as DOWNLOAD_REPORT,
    STUFF((SELECT ',' + t2.LOGIN_NAME 
         from [LOGIX_AUDIT].[dbo].TBL_SAVED_REPORT_BY_USER t2
         where A.SAVED_REPORT_ID= t2.SAVED_REPORT_ID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,LEN(','),'') AS RECIPIENTS,
    STUFF((SELECT ',' + t2.PARAM_NAME + '=' + T2.PARAM_VALUE
         from [LOGIX_AUDIT].[dbo].[TBL_SAVED_REPORT_AUDIT_VIEW_PARAMETERS] t2
         where A.SAVED_REPORT_ID= t2.SAVED_REPORT_ID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,LEN(','),'') AS PARAMS_USED,
 A.MESSAGE AS AUDIT_MSG,
 CASE
  WHEN A.MESSAGE LIKE '%Blank Not Sent -%' THEN 1
  ELSE 0 
 END AS BLANK_NOT_SENT,
    (SELECT COUNT(*)
         from [LOGIX_AUDIT].[dbo].TBL_SAVED_REPORT_BY_USER t2
         where A.SAVED_REPORT_ID= t2.SAVED_REPORT_ID) AS REPORTS_GENERATED
FROM
 [LOGIX_AUDIT].[dbo].[TBL_REPORT_GENERATION_AUDIT] A 
LEFT JOIN TBL_SCHEDULE     S
 ON A.SCHEDULE_ID = S.SCHEDULE_ID
LEFT JOIN [LOGIX_AUDIT].[dbo].[TBL_SAVED_REPORT] T ON
   T.SAVED_REPORT_ID = A.SAVED_REPORT_ID
LEFT JOIN [LOGIX_AUDIT].[dbo].TBL_SAVED_REPORT_BY_USER TB ON
   TB.SAVED_REPORT_ID = A.SAVED_REPORT_ID
   AND A.SCHEDULE_ID = 0 --ONLY JOIN WHEN RUN MANUALLY
WHERE
 DATEADD(dd, DATEDIFF(dd, 0, GENERATED_DATE), 0) BETWEEN dbo.LGX_DATE_RANGE('START_DATE',:DATE_RANGE_MS,:FROM_DATE) AND dbo.LGX_DATE_RANGE('END_DATE',:DATE_RANGE_MS,:TO_DATE)
  AND (a.SCHEDULE_ID = :SCHED_ID OR :SCHED_ID IS NULL OR :SCHED_ID < 0)
Revision: 1
Last modified: May 28, 2020

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment