Select rpt.ts_Id as [TS_ID] , Cast(rpt.ts_Uuid as VarChar(36)) as [TS_UUID] , rpt.ts_Id as [TS_RPT_TS_ID] , Cast(rpt.ts_Uuid as VarChar(36)) as [TS_RPT_TS_UUID] , Cast(rpt.ts_Name as VarChar(64)) As [TS_NAME] , Cast('RPT-' + Cast(rpt.ts_Id As VarChar) + Case When Len(IsNull(rpt.ts_ReferenceName,'')) < 1 Then '' Else '-' + rpt.ts_ReferenceName End As VarChar(80)) As [TS_ISSUEID] , Cast(Case When Len(IsNull(rpt.ts_ReferenceName,'')) < 1 Then Replace(rpt.ts_Name,' ','_') Else '-' + rpt.ts_ReferenceName End As VarChar(128)) As [TS_CONSTRUCTED_REF_NAME], Cast( (Select ts_StringValue + '/workcenter/tmtrack.dll?shell=swc&ReportPage&Template=reports%2Flist&ReportId=' + Cast(rpt.ts_Id As VarChar) from ts_SystemSettings With (NoLock, ReadUncommitted) Where ts_Name='NSHTTPLinkAddress') As VarChar(255)) As [TS_REPORTLINK] , rpt.ts_TableId As [TS_TABLEID] , Cast(tbl.ts_DbName as VarChar(28)) As [TS_TABLE_DBNAME] , Case Coalesce(rpt.TS_ACCESS,0) WHEN 1 THEN {SELECTION_ACCESS_1} WHEN 2 THEN {SELECTION_ACCESS_2} WHEN 3 THEN {SELECTION_ACCESS_3} WHEN 4 THEN {SELECTION_ACCESS_4} ELSE {SELECTION_ACCESS_0} END As [TS_ACCESS] , Coalesce(rpt.ts_AuthorId,0) As [TS_AUTHORID] , Coalesce(rpt.ts_AuthorId,0) As [TS_TS_AUTHORID] , Coalesce(AuthUsr.ts_Email,'') as [TS_AUTHOREMAIL] , Coalesce(AuthUsr.ts_Status,-1) as [TS_AUTHORSTATUSID] , Case Coalesce(AuthUsr.ts_Status,-1) WHEN 0 THEN {SELECTION_AUTHSTATUS_0} WHEN 1 THEN {SELECTION_AUTHSTATUS_1} WHEN 2 THEN {SELECTION_AUTHSTATUS_2} ELSE {SELECTION_AUTHSTATUS_99} END As [TS_AUTHSTATUS] , Trim(',' From Case When AuthUsr.ts_AccessType & 0x0001>0 Then 'Normal' Else '' End + Case When AuthUsr.ts_AccessType & 0x0002>0 Then ', Occasional' Else '' End + Case When AuthUsr.ts_AccessType & 0x0004>0 Then ', External' Else '' End + Case When AuthUsr.ts_AccessType & 0x0008>0 Then ', 0x08' Else '' End + Case When AuthUsr.ts_AccessType & 0x0010>0 Then ', Managed Admin' Else '' End + Case When AuthUsr.ts_AccessType & 0x0020>0 Then ', API/Script' Else '' End + Case When AuthUsr.ts_AccessType & 0x0040>0 Then ', 0x40' Else '' End + Case When AuthUsr.ts_AccessType & 0x0080>0 Then ', 0x80' Else '' End ) As [TS_AUTHACCESSENUM], Coalesce(rpt.ts_LastModifier,0) As [TS_LASTMODIFIERID] , Coalesce(rpt.ts_LastModifier,0) As [TS_TS_LASTMODIFIERID] , Coalesce(LastModUsr.ts_Email,'') as [TS_LASTMODIFIEREMAIL] , Coalesce(LastModUsr.ts_Status,0) as [TS_LASTMODIFIERSTATUSID] , Case Coalesce(LastModUsr.ts_Status,0) WHEN 0 THEN {SELECTION_LASTMODUSRSTATUS_0} WHEN 1 THEN {SELECTION_LASTMODUSRSTATUS_1} WHEN 2 THEN {SELECTION_LASTMODUSRSTATUS_2} ELSE {SELECTION_LASTMODUSRSTATUS_99} END As [TS_LASTMODUSRSTATUS] , Trim(',' From Case When LastModUsr.ts_AccessType & 0x0001>0 Then 'Normal' Else '' End + Case When LastModUsr.ts_AccessType & 0x0002>0 Then ', Occasional' Else '' End + Case When LastModUsr.ts_AccessType & 0x0004>0 Then ', External' Else '' End + Case When LastModUsr.ts_AccessType & 0x0008>0 Then ', 0x08' Else '' End + Case When LastModUsr.ts_AccessType & 0x0010>0 Then ', Managed Admin' Else '' End + Case When LastModUsr.ts_AccessType & 0x0020>0 Then ', API/Script' Else '' End + Case When LastModUsr.ts_AccessType & 0x0040>0 Then ', 0x40' Else '' End + Case When LastModUsr.ts_AccessType & 0x0080>0 Then ', 0x80' Else '' End ) As [TS_LASTMODACCESSENUM], Case Coalesce(rpt.ts_Type,0) When 1 Then {SELECTION_RPT_TYPE_1} When 2 Then {SELECTION_RPT_TYPE_2} When 3 Then {SELECTION_RPT_TYPE_3} When 4 Then {SELECTION_RPT_TYPE_4} When 5 Then {SELECTION_RPT_TYPE_5} When 6 Then {SELECTION_RPT_TYPE_6} When 7 Then {SELECTION_RPT_TYPE_7} When 8 Then {SELECTION_RPT_TYPE_8} When 9 Then {SELECTION_RPT_TYPE_9} When 10 Then {SELECTION_RPT_TYPE_10} When 11 Then {SELECTION_RPT_TYPE_11} When 12 Then {SELECTION_RPT_TYPE_12} When 13 Then {SELECTION_RPT_TYPE_13} When 14 Then {SELECTION_RPT_TYPE_14} When 15 Then {SELECTION_RPT_TYPE_15} When 16 Then {SELECTION_RPT_TYPE_16} When 17 Then {SELECTION_RPT_TYPE_17} When 18 Then {SELECTION_RPT_TYPE_18} When 19 Then {SELECTION_RPT_TYPE_19} When 20 Then {SELECTION_RPT_TYPE_20} When 21 Then {SELECTION_RPT_TYPE_21} When 22 Then {SELECTION_RPT_TYPE_22} When 23 Then {SELECTION_RPT_TYPE_23} When 24 Then {SELECTION_RPT_TYPE_24} When 25 Then {SELECTION_RPT_TYPE_25} When 26 Then {SELECTION_RPT_TYPE_26} When 27 Then {SELECTION_RPT_TYPE_27} When 28 Then {SELECTION_RPT_TYPE_28} When 29 Then {SELECTION_RPT_TYPE_29} Else {SELECTION_RPT_TYPE_0} End [TS_RPT_TYPE] , rpt.ts_ReportDefId As [TS_REPORTDEFID], Cast( Case When rpt.ts_ReportDefId Is Null Then '-null-' When rpt.ts_ReportDefId = -1 Then 'Composer-defined Listing' When rpt.ts_ReportDefId = 0 Then 'Non-App report' When rpt.ts_ReportDefId > 0 Then (Select r2.ts_Name From ts_Reports r2 With (NoLock, ReadUncommitted) Where r2.ts_Id=rpt.ts_ReportDefId And r2.ts_ReportDefId=-1) Else '-unknown-' End As VarChar(64) ) As [TS_REPORTDEF], Case When Coalesce(rpt.ts_ReportDefId,0)=-1 AND rpt.ts_AuthorId=0 AND rpt.ts_CreateDate=-2 Then {SELECTION_COMPOSER_LISTING_RPT_1} When Coalesce(rpt.ts_ReportDefId,0)=0 Then {SELECTION_COMPOSER_LISTING_RPT_2} When Coalesce(rpt.ts_ReportDefId,0)>0 AND rpt.ts_AuthorId=0 AND rpt.ts_CreateDate>0 And Exists (Select 1 From ts_Reports r2 With (NoLock, ReadUncommitted) Where r2.ts_Id=rpt.ts_ReportDefId AND r2.ts_ReportDefId=-1 AND r2.ts_AuthorId=0 AND r2.ts_CreateDate=-2) Then {SELECTION_COMPOSER_LISTING_RPT_3} Else {SELECTION_COMPOSER_LISTING_RPT_0} End As [TS_COMPOSER_LISTING_RPT] , Case Coalesce(rpt.ts_QueryType,0) When 1 Then {SELECTION_RPT_QUERY_TYPE_1} When 2 Then {SELECTION_RPT_QUERY_TYPE_2} Else {SELECTION_RPT_QUERY_TYPE_0} End As [TS_RPT_QUERY_TYPE] , DateAdd(ss,rpt.ts_StartDate,'1/1/1970') As [TS_START_DATE] , DateAdd(ss,rpt.ts_EndDate,'1/1/1970') As [TS_END_DATE] , rpt.ts_TimePeriod As [TS_TIME_PERIOD] , rpt.ts_CreateDate as [TS_CREATE_DATE_EPOCHSEC], DateAdd(ss,(Case When IsNull(rpt.ts_CreateDate,0) <= 0 Then 0 Else rpt.ts_CreateDate End),'1/1/1970') As [TS_CREATE_DATE] , Cast( Case IsNull(rpt.ts_CreateDate,-4) When -4 Then '(NULL)' When -3 Then '(EMPTY)' When -2 Then '(NEVER)' When -1 Then '(AUTO)' Else Convert(VarChar(24),DateAdd(ss,rpt.ts_CreateDate + IsNull( ( Select tzdnorm.ts_GMTOffsetSeconds From ts_TimezoneGmtOffsetDenorm tzdnorm With (NoLock, ReadUncommitted) Where tzdnorm.ts_TimeZone=IsNull((Select sysset.ts_StringValue From ts_SystemSettings sysset With (NoLock, ReadUncommitted) Where sysset.ts_Name='TimeZone' ),0) AND (Case When IsNull(rpt.ts_CreateDate,0) <= 0 Then 0 Else rpt.ts_CreateDate End) Between tzdnorm.ts_StartGMTSeconds And tzdnorm.ts_EndGMTSeconds ),0),'1/1/1970'),120) End As VarChar(24) ) As [TS_CREATE_DATE_EST_TXT] , rpt.ts_LastModifiedDate as [TS_LASTMODIFIED_DATE_EPOCHSEC], DateAdd(ss,(Case When IsNull(rpt.ts_LastModifiedDate,0) <= 0 Then 0 Else rpt.ts_LastModifiedDate End),'1/1/1970') As [TS_LASTMODIFIED_DATE] , Cast( Case IsNull(rpt.ts_LastModifiedDate,-4) When -4 Then '(NULL)' When -3 Then '(EMPTY)' When -2 Then '(NEVER)' When -1 Then '(AUTO)' Else Convert(VarChar(24),DateAdd(ss,rpt.ts_LastModifiedDate + IsNull( ( Select tzdnorm.ts_GMTOffsetSeconds From ts_TimezoneGmtOffsetDenorm tzdnorm With (NoLock, ReadUncommitted) Where tzdnorm.ts_TimeZone=IsNull((Select sysset.ts_StringValue From ts_SystemSettings sysset With (NoLock, ReadUncommitted) Where sysset.ts_Name='TimeZone' ),0) AND (Case When IsNull(rpt.ts_LastModifiedDate,0) <= 0 Then 0 Else rpt.ts_LastModifiedDate End) Between tzdnorm.ts_StartGMTSeconds And tzdnorm.ts_EndGMTSeconds ),0),'1/1/1970'),120) End As VarChar(24) ) As [TS_LASTMODIFIED_DATE_EST_TXT] , rpt.ts_LastExecDate As [TS_LASTEXEC_DATE_EPOCHSEC] , DateAdd(ss,(Case When IsNull(rpt.ts_LastExecDate,0) <= 0 Then 0 Else rpt.ts_LastExecDate End),'1/1/1970') As [TS_LASTEXEC_DATE] , Cast( Case IsNull(rpt.ts_LastExecDate,-4) When -4 Then '(NULL)' When -3 Then '(EMPTY)' When -2 Then '(NEVER)' When -1 Then '(AUTO)' Else Convert(VarChar(24),DateAdd(ss,rpt.ts_LastExecDate + IsNull( ( Select tzdnorm.ts_GMTOffsetSeconds From ts_TimezoneGmtOffsetDenorm tzdnorm With (NoLock, ReadUncommitted) Where tzdnorm.ts_TimeZone=IsNull((Select sysset.ts_StringValue From ts_SystemSettings sysset With (NoLock, ReadUncommitted) Where sysset.ts_Name='TimeZone' ),0) AND (Case When IsNull(rpt.ts_LastExecDate,0) <= 0 Then 0 Else rpt.ts_LastExecDate End) Between tzdnorm.ts_StartGMTSeconds And tzdnorm.ts_EndGMTSeconds ),0),'1/1/1970'),120) End As VarChar(24) ) As [TS_LASTEXEC_DATE_EST_TXT] , IsNull((Select Max(rusg.ts_ExecTime) From ts_ReportUsage rusg With (NoLock, ReadUncommitted) Where rusg.ts_ExecTime Is Not Null AND rusg.ts_ReportId=rpt.ts_Id),0)/1000 As [TS_RPT_USAGE_EPOCHSEC] , DateAdd(ss,(IsNull((Select Max(rusg.ts_ExecTime) From ts_ReportUsage rusg With (NoLock, ReadUncommitted) Where rusg.ts_ExecTime Is Not Null AND rusg.ts_ReportId=rpt.ts_Id),0)/1000),'1/1/1970') As [TS_RPT_USAGE_DATE] , Cast(Coalesce(rpt.ts_JoinSpecifications,'') as VarChar(8000)) As [TS_JOIN_SPECIFICATIONS] , Cast( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace(Coalesce(rpt.ts_Comment,'') , Char(7) , '\bel'), Char(8),'\bs'), Char(9),'\t'), Char(10),'\n'), Char(11),'\vt'), Char(12),'\ff'), Char(13),'\r'), Char(34),'"'), Char(39),''''), Char(60),'<'), Char(62),'>'), Char(96),'`'), Char(1),'{SOH}'), Char(2),'{STX}'), Char(3),'{ETX}') as VarChar(8000) ) As [TS_COMMENT] , Cast( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace(Coalesce(rpt.ts_Footer,'') , Char(7) , '\bel'), Char(8),'\bs'), Char(9),'\t'), Char(10),'\n'), Char(11),'\vt'), Char(12),'\ff'), Char(13),'\r'), Char(34),'"'), Char(39),''''), Char(60),'<'), Char(62),'>'), Char(96),'`'), Char(1),'{SOH}'), Char(2),'{STX}'), Char(3),'{ETX}') as VarChar(8000) ) As [TS_FOOTER] , Cast( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace( Replace(Coalesce(rpt.ts_Specifications1,'') , Char(7) , '\bel'), Char(8),'\bs'), Char(9),'\t'), Char(10),'\n'), Char(11),'\vt'), Char(12),'\ff'), Char(13),'\r'), Char(34),'"'), Char(39),''''), Char(60),'<'), Char(62),'>'), Char(96),'`'), Char(1),'{SOH}'), Char(2),'{STX}'), Char(3),'{ETX}') as VarChar(8000) ) As [TS_SPECIFICATIONS1] , rpt.ts_ProjectId As [TS_PROJECTID] , Cast(Coalesce(rpt.TS_HtmlTemplate,'') as VarChar(255)) As [TS_HTML_TEMPLATE] , Cast(Coalesce(rpt.ts_Columns,'') as VarChar(255)) As [TS_COLUMNS] , Cast(Coalesce(rpt.ts_SortBy,'') as VarChar(255)) As [TS_SORTBY] , Cast(Coalesce(rpt.ts_ReferenceName,'') as VarChar(128)) As [TS_REFERENCENAME] From ts_Reports rpt With (NoLock, ReadUncommitted) Left Outer Join ts_Tables As tbl With (NoLock, ReadUncommitted) On rpt.ts_TableId=tbl.ts_Id Left Outer Join ts_Users As AuthUsr With (NoLock, ReadUncommitted) On rpt.ts_AuthorId=AuthUsr.ts_Id Left Outer Join ts_Users As LastModUsr With (NoLock, ReadUncommitted) On rpt.ts_LastModifier=LastModUsr.ts_Id {0} NAME