We have a list of reports that need to be deleted. Instead of doing this manually, we would like to use SQL to delete the reports. This list was generated via an XML Query View Advanced Report (QVAR). I've attached the XML. We created a report on the view that shows reports:
- That are private (accessible only to the Report Author)
- Where the Report Author's user account has been deleted
- Where the Report hasn't been executed for over 365 days
This seems like a list of Reports that can be safely deleted.
Just deleting the record of the report from TS_REPORTS seems like a bad idea. There are multiple contexts in which a report can be used (I'm thinking of Dashboards and Scheduled Reports), and undocumented relationships between ts_Reports and other tables. In addition there's the possibility that a report is used in a Report Widget on a form.
The reason we want to use SQL is because of all the steps involved in deleting a Private Report owned by a Deleted User:
- Re-activate the User in App Admin, with all the peril of the User's Contact record being surreptitiously updated.
- "Logon as" that user
- Find and delete the Report
- "Logon as" self
- Re-Delete the User, again with all peril of the Contact record being updated.
- ... lather, rinse, repeat...
Not bad for 10 reports. We've got 200. And this is just the first phase of cleaning up unused / inaccessible reports.
Hopefully the XML for the QVAR is below (as a TXT file to keep the forum from freaking out about illegal file types)
<views>
<!-- An SBM Query View Advanced Report (QVAR) looks and acts like an SBM aux table,
however, it's really just the results of a subquery that is run every time the
aux table is listed or viewed. This has implications for performace - - if the
QVAR performs complex joins or calculates, or returns a large number of rows,
then this can impact system DB and network performance. This also means that
the output of the query must behave consistently. The identity columns of
rows queried cannot change between runs.
-->
<!-- *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* -->
<!-- Author: $PauThompson@caci.com$ -->
<!-- DateTime: $3:07 PM 1/2/2023$ -->
<!-- File: $TSV_Reports.xml$ -->
<!-- Revision: $353$ -->
<!--
"Deploying" this:
Overview:
1. Need to copy the XML from DeveloperShare on the FileShare to the AE server.
2. Setup a temporary directory on the E: drive of the server (use C: for SANDBOX)
3. \\FileShare\DeveloperShare is not accessible to Administrator, so use normal cmd shell to copy the XML from \\FileShare\DeveloperShare to a temp dir,
then an Administrator cmd shell to copy from temp dir to SBM folder.
4. Last step is run TTAdmin as Administrator with the cmd line param to generate the view.
Specific steps:
(one time only) On the AE server, open regular cmd shell and create a temp dir on drive where SBM is installed
mkdir E:\Temp
mkdir E:\Temp\ViewDefinitions
(For each "deployment" of the XML to an AE), open regular cmd shell:
pushd E:\Temp\ViewDefinitions
xcopy /y "\\FileShare\DeveloperShare\iSTAR\Serena Files\src\Query View Advanced Reports\TSV_Reports.xml" .
open cmd shell as Administrator, copy file from temp to SBM, run the "ttadmin" program.
pushd "e:\Program Files\Serena\SBM\Application Engine"
xcopy /y "e:\Temp\ViewDefinitions\First_Time_Quality_Report.xml" ViewDefinitions
bin\ttadmin.exe /GenerateViewsAllowDelete
[optional: view log from ttadmin] type log\ViewGenerator.log
[optional: view System App Event Log] %windir%\system32\eventvwr.msc /s
-->
<!-- Unique UUID Generator : https://www.uuidgenerator.net/ -->
<!-- View fails if using the UUID of the Global Process App :: app="global-a1e2c419-c79d-45e3-beab-4ee540c437e1" -->
<view dbname="TSV_Reports" name="TSV_Reports" singleItemName="Enhanced Reports List" notes="DateTime: $3:18 PM 9/12/2022$; Revision: $350$" description="Enhanced Reports list QVAR" uuid="02eb1302-609b-41b6-94f5-7df295be68ab">
<!--
The results of the SQL should be as similar to an Application Engine table query as possible.
The resultant table must include TS_ID, TS_NAME, and TS_UUID columns that are consistent each
time the SQL is invoked. Columns *MUST* start with "TS_". The name specified in the "fields"
section below is the Column name without the "TS_".
The "TS_ID" column should return a unique, non-NULL value > 0 for each row. If this is not possible,
use additional identity columns with syscode type 26. See below:
NULL values in User, relational, project, and selection columns are not permitted.
SQL Comments prefixed with DASH-DASH are not permitted (XML restriction).
Comparisons must use HTML entities for LESS-THAN and GREATER-THAN instead of the actual symbols.
You can't "fake" a single-selection field as a type=relational on TS_SELECTIONS because TS_SELECTIONS table
doesn't have a Value Display Format.
The SQL is saved in the Table definition as TS_SQLQUERY.
The entire query is executed each time a report is run on the View aux table.
For fields that use enumerated integer values as types, e.g. the "TS_TYPE" or "TS_QUERYTYPE"
fields in the TS_REPORTS table, use the special "{SELECTION_flddbname_number}" XML variables
-->
<!--
For Dates, -2 means "not done"
-->
<!--
Characters that need to be specified as XML / HTML entities:
Ampersand
Slash
Less-than
Greater-than
Quote (double quote)
Apostrophe (single quote)
Grave (backtick / reverse single quote)
-->
<sql>
Select
<!-- we MUST include a TS_ID and TS_UUID column. SBM will automatically create the fields for these columns -->
rpt.ts_Id as [TS_ID] ,
Cast(rpt.ts_Uuid as VarChar(36)) as [TS_UUID] ,
<!-- Versions of the TS_ID and UUID that we can use in the view -->
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] ,
<!-- IssueId: RPT-ts_id[-RefName] -->
<!-- Cast('RPT-' + Cast(rpt.ts_Id As VarChar) + '-' + rpt.ts_UUID + Case When Len(IsNull(rpt.ts_ReferenceName,'')) < 1 Then '' Else '-' + rpt.ts_ReferenceName End As VarChar(112)) As [TS_ISSUEID] , -->
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] ,
<!-- Constructed Reference Name where one doesn't exist -->
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] ,
<!-- First is formatted as User field; second is numeric user ID. Double "TS_" is intentional. -->
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] ,
<!-- Build a CSV string of user's group memberships -->
<!-- On second thought, generating group memberships increases execution time and doesn't add anything of value -->
<!--
(
Select Coalesce(String_Agg(g.ts_Name,','),'')
From
ts_Groups g With (NoLock, ReadUncommitted)
Join ts_Members m With (NoLock, ReadUncommitted) On m.ts_GroupId=g.ts_Id
Where
m.ts_UserId=AuthUsr.ts_Id
) As [TS_AUTHGRPMEMBERSHIPS],
-->
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],
<!-- First is formatted as User field; second is numeric user ID. Double "TS_" is intentional. -->
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] ,
<!-- On second thought, generating group memberships increases execution time and doesn't add anything of value -->
<!--
(
Select Coalesce(String_Agg(g.ts_Name,','),'')
From
ts_Groups g With (NoLock, ReadUncommitted)
Join ts_Members m With (NoLock, ReadUncommitted) On m.ts_GroupId=g.ts_Id
Where
m.ts_UserId=LastModUsr.ts_Id
) As [TS_LASTMODGRPMEMBERSHIPS],
-->
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] ,
<!-- Time period can be actual epoch secs, an offset from epoch secs or a "Special DateTime Value" from "TS_Def.h" -->
rpt.ts_TimePeriod As [TS_TIME_PERIOD] ,
<!-- We will display this is a numeric integer Epoch Seconds -->
rpt.ts_CreateDate as [TS_CREATE_DATE_EPOCHSEC],
<!-- This gives a column to use in report filters. SBM will calc and display this in user's local time -->
DateAdd(ss,(Case When IsNull(rpt.ts_CreateDate,0) <= 0 Then 0 Else rpt.ts_CreateDate End),'1/1/1970') As [TS_CREATE_DATE] ,
<!-- CreateDate corrected for Server UTC offset + DST, formatted as ODBC Canonical w/4 digit year -->
<!-- HEADS UP : Create Date, Last Mod Date and Last Exec Date values can be positive Epoch seconds or -1 (Auto), -2 (Never) or -3 (Empty) -->
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],
<!-- SBM will calc and display this in user's local time -->
DateAdd(ss,(Case When IsNull(rpt.ts_LastModifiedDate,0) <= 0 Then 0 Else rpt.ts_LastModifiedDate End),'1/1/1970') As [TS_LASTMODIFIED_DATE] ,
<!-- Last Mod Date corrected for Server UTC offset + DST, formatted as ODBC Canonical w/4 digit year -->
<!-- HEADS UP : Create Date, Last Mod Date and Last Exec Date values can be positive Epoch seconds or -1 (Auto), -2 (Never) or -3 (Empty) -->
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] ,
<!-- SBM will calc and display this in user's local time -->
DateAdd(ss,(Case When IsNull(rpt.ts_LastExecDate,0) <= 0 Then 0 Else rpt.ts_LastExecDate End),'1/1/1970') As [TS_LASTEXEC_DATE] ,
<!-- Last Exec Date corrected for Server UTC offset + DST, formatted as ODBC Canonical w/4 digit year -->
<!-- HEADS UP : Create Date, Last Mod Date and Last Exec Date values can be positive Epoch seconds or -1 (Auto), -2 (Never) or -3 (Empty) -->
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] ,
<!-- From ts_ReportUsage table. The Last Usage date/time in this table can be as much as 1/2 day off from the Last Exec Date in TS_REPORTS -->
<!-- !!!!! HEADS UP !!!!! Time values in ts_ReportUsage are Msec !!!!! not Sec !!!!! -->
<!-- We have to convert to Sec because XML reports don't have a "number-bigint" field type -->
<!-- !!!!! HEADS UP !!!!! ts_ReportUsage contains records per Report and per User. We want the max Exec Time for any/all users for the specified Report. -->
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] ,
<!-- SBM will calc and display this in user's local time -->
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] ,
<!-- Because of the necessity of using a subquery to get the ReportUsage.ts_ExecTime, I'm not going to do the following. -->
<!--
Cast(
Case IsNull(rptusg.ts_ExecTime,-4)
When -4 Then '(NULL)'
When -3 Then '(EMPTY)'
When -2 Then '(NEVER)'
When -1 Then '(AUTO)'
Else Convert(VarChar(24),DateAdd(ss,IsNull(rptusg.ts_ExecTime,0) + 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(rptusg.ts_ExecTime,0) <= 0 Then 0 Else rptusg.ts_ExecTime End)/1000) Between tzdnorm.ts_StartGMTSeconds And tzdnorm.ts_EndGMTSeconds
),0),'1/1/1970'),120)
End As VarChar(24)+
) As [TS_RPT_USAGE_EST_TXT] ,
-->
Cast(Coalesce(rpt.ts_JoinSpecifications,'') as VarChar(8000)) As [TS_JOIN_SPECIFICATIONS] ,
<!-- Can't use TRANSLATE() because we're replacing single char with multiple chars -->
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
</sql>
<!-- *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* -->
<!-- Dont put in the trailing semi-colon after the SQL !! -->
<!--
static selection list demo
case coalesce(mr.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} "SELECTION_" + DBNAME + "value=" from below
ELSE 0
END TS_ACCESS
<field dbname="ACCESS" name="Report Access" type="selection">
<selections>
<selection value="1" name="Private"/>
<selection value="2" name="Guest"/>
<selection value="3" name="User"/>
<selection value="4" name="Manager"/>
</selections>
</field>
-->
<valuedisplayformat>
<!--
VDF for "view.dbname". Each "{N}" reference in the "valuedisplayformat.format" section is replaced by
the "Nth" field named in the "valuedisplayformat.fields" line. The "valuedisplayformat.fields" line
is a CSV of field DBNames. Each field is defined in the "fields" section. Don't confuse the "valuedisplayformat.fields"
line with the "fields" section.
-->
<format>{0}</format>
<!--
Fields used by the "{x}" placeholders in the "valuedisplayformat.format" section
are listed in the "valuedisplayformat.fields" entity as a simple CSV list.
-->
<!-- Don't confuse the "FIELDS" in the "FORMAT" section with the "FIELDS" section below -->
<fields>NAME</fields>
</valuedisplayformat>
<fields>
<!--
Definitions of each of the "fields" to be created in the pseudo-auxtable. Except for the "ts_ID" (ID),
"ts_UUID" (UUID), and "ts_Title" (TITLE) fields, every column from the query in the "sql" section that
is to be displayed to the user needs an entry here
-->
<!--
Field types
!!! ALERT !!! ALERT !!! These names are case-sensitive !!! ALERT !!! !!! ALERT !!!
text Fixed length text field. Required attributes: "size" (integer)length of the field.
text-memo Memo text field.
user User field.
number-integer Numeric field with data type integer
number-float Numeric field with data type float (allows floating point values)
number-fixed Numeric field with data type float with fixed precision. Required attributes: "precision" (integer)
date Dateonly field
datetime Datetime field
project Project field (not system project field, aux tables do not have system project fields)
selection Singleselection field. Can have a child <selections> tag with a list of <selection> values after it,
which must have attributes "value" (integer) and "name" (text).
Note: Think of this more like a lookup for static value.
<selections>
<selection value="1" name="Analysis" />
<selection value="2" name="Analysis Complete" />
</selections
relational Singlerelational field. Required attributes: "target" (text)UUID of related table.
This field will display the data defined in the <valuedisplayformat> tag from the related item.
The field will display much like a standard SBM relational field.
It will show an icon next to the field when viewing an item.
Clicking the icon allows you to see the related record in a popup window.
<field dbname="DEMANDPLAN" name="Demand Plan" type="relational" target="3c534aa0cb134b27b8a3e3b9c9fc4ca6" />
many-relational Anyrelational field. The many relational field displays the Value Display Format from the related item,
just like a relational field. Also, you can click an icon next to the many relational field value when viewing
an item to see the related item in a popup (just like relational fields). The column data for a many relational
field must be a text column with format tableID:itemID (for example: 1000:17). In order to generate SQL that
works in both MSSQL and Oracle, a special XML Variable can be inserted into the SQL.
<field dbname="AEITEM" name="Item" type="manyrelational" />
The upshot is that a many-relational column can only reference User tables with a defined Value Display Format,
or System tables that have a value display format defined in TS_TABLES.TS_FORMAT. That's a short list. See below
Could you hack TS_FORMAT for a system table like "TS_REPORTS"? No. Because the "{n}" tags in TS_FORMAT refer to Field TS_IDs.
System table fields aren't listed in TS_FIELDS; only user-defined fields. Phooie.
ts_id ts_Name ts_DbName ts_UUID ts_format
===== ================================ ============================ ============================ =================================
37 Companies TS_COMPANIES TS_COMPANIES "{0}",30
38 Contacts TS_CONTACTS TS_CONTACTS "{0} - {1} {2} {3}",705,15,16,17
41 Problems TS_PROBLEMS TS_PROBLEMS "{0}",42
42 Resolutions TS_RESOLUTIONS TS_RESOLUTIONS "{0}",47
97 Languages TS_LANGUAGES TS_LANGUAGES "{0}",1
98 String IDs TS_STRINGIDENTIFIERS TS_STRINGIDENTIFIERS "{0}",4
99 Strings TS_STRINGS TS_STRINGS "{0}",8
146 SharePoint Servers TS_SHAREPOINTSERVERS TS_SHAREPOINTSERVERS "{0}",52
147 SharePoint Project Servers TS_SHAREPOINTPROJECTSERVERS TS_SHAREPOINTPROJECTSERVERS "{0}",59
hidden="true" Add this attribute to move the field to the HIDDEN section. It will be queryable by SQL by won't appear in the list of fields
-->
<!--
*possible* QVAR keywords:
singleItemName view views sql
app binary datetime field
fields fields label1 label2
label3 many-relational multi-relational multi-user
number-fixed number-float number-integer precision
relational selectid selection selection
selections size source sql
target text-memo user valuedisplayformat
versionID currentuser inherit
-->
<!--
ALERT!!! special chars other than UNDERSCORE and MINUS in the NAME field cause trouble!!!
-->
<!--
For the TS_REPORTS table, there are a couple columns that use the
ASCII SOH and STX characters as delimiters. Also, some text
columns can contain Carriage Return, Line Feed, Tab and Form
Feed characters. All of these cause printing weirdness so we
need to replace them.
Here's some minimal SQL Server SQL that will do that for the
TS_SPECIFICATIONS1 column in the TS_REPORTS table.
Cast(
Replace(
Replace(
Replace(
Replace(
Replace(
IsNull(ts_reports.ts_Specifications1,''),char(10),' '
),Char(13),' '
),' ',' '
),Char(1),'{SOH}'
),Char(2),'{STX}'
) as VarChar(128)
) As [TS_SPECIFICATIONS1] ,
*** Here's some over-the-top SQL that replaces a lot of
*** unprintable chars that cause trouble. This is for the
*** TS_COMMENT column in TS_REPORTS.
Cast(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(Coalesce(ts_reports.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] ,
-->
<!-- Syscodes:
4 Title (text)
5 Description (text:memo)
7 SubmitDate (datetime)
13 Submitter (user)
16 Issue ID (text)
26 Key column (number:integer)
-->
<!-- *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* -->
<!-- By default SBM creates the TS_ID and TS_UUID columns as hidden -->
<!-- <field dbname="ID" name="ID" type="number-integer" hidden="true" /> -->
<!-- <field dbname="UUID" name="UUID" type="text" size="36" /> -->
<field dbname="RPT_TS_ID" name="Report TS_ID" type="number-integer" hidden="false" />
<field dbname="RPT_TS_UUID" name="Report TS_UUID" type="text" size="36" />
<field dbname="NAME" name="Name" type="text" size="64" hidden="false" syscode="4" description="I think the description attribute is ignored."/>
<field dbname="ISSUEID" name="Issue Id" type="text" hidden="false" syscode="16" size="80" description="Composite Issue ID."/>
<field dbname="CONSTRUCTED_REF_NAME" name="Constructed Reference Name" type="text" hidden="false" size="128" description="Constructed Reference Name."/>
<field dbname="REPORTLINK" name="Report URL" type="text" size="255" hidden="false" />
<!--
types: selection, text-memo, selectid, number-fixed, number-float, number-integer, user,
multi-relational, relational, selection, datetime, binary, multi-user, many-relational
?? human_short ?? human ?? human_medium ?? human_long
number-integer64
datetime-epoch TTADMIN recognizes this type but does not put records into TS_FIELDS. That makes it useless.
-->
<!--
<field dbname="REPORTID" name="Report TS_ID" type="number-integer" hidden="false" />
<field dbname="REPORTUUID" name="Report TS_UUID" type="text" size="36" hidden="false" />
-->
<field dbname="TABLEID" name="Table ID" type="number-integer" />
<field dbname="TABLE_DBNAME" name="Table DB Name" type="text" size="28" />
<field dbname="ACCESS" name="Report Access Type" type="selection" >
<selections>
<selection value="0" name="-unknown-"/>
<selection value="1" name="Private"/>
<selection value="2" name="Guest"/>
<selection value="3" name="User"/>
<selection value="4" name="Manager"/>
</selections>
</field>
<field dbname="AUTHORID" name="Author" type="user" syscode="13" />
<!-- need to "act as" the report author ID to delete the report -->
<field dbname="TS_AUTHORID" name="Author TS_ID" type="number-integer" hidden="false" />
<field dbname="AUTHOREMAIL" name="Author Email" type="text" size="80" />
<field dbname="AUTHORSTATUSID" name="Author User Status ID" type="number-integer" />
<field dbname="AUTHSTATUS" name="Author User Status" type="selection">
<selections>
<selection value='0' name='Active' />
<selection value='1' name='Inactive' />
<selection value='2' name='Disabled' />
<selection value='99' name='-unknown-' />
</selections>
</field>
<field dbname="AUTHGRPMEMBERSHIPS" name="Author Group Memberships" type="text" size="8000" />
<field dbname="AUTHACCESSENUM" name="Author Access" type="text" size="255" />
<field dbname="LASTMODIFIERID" name="Last Modifier" type="user" />
<field dbname="TS_LASTMODIFIERID" name="Last Modifier TS_ID" type="number-integer" />
<field dbname="LASTMODIFIEREMAIL" name="Last Modifier email" type="text" size="80" />
<field dbname="LASTMODIFIERSTATUSID" name="Last Modifier User Status ID" type="number-integer" />
<field dbname="LASTMODUSRSTATUS" name="Last Modifier User Status" type="selection">
<selections>
<selection value='0' name='Active' />
<selection value='1' name='Inactive' />
<selection value='2' name='Disabled' />
<selection value='99' name='-unknown-' />
</selections>
</field>
<field dbname="LASTMODGRPMEMBERSHIPS" name="Last Modifier Group Memberships" type="text" size="8000" />
<field dbname="LASTMODACCESSENUM" name="Last Modifier Access" type="text" size="255" />
<field dbname="RPT_TYPE" name="Report Type" type="selection">
<selections>
<selection value='0' name='-unknown-' />
<selection value='1' name='Listing' />
<selection value='2' name='Distribution' />
<selection value='3' name='Trend' />
<selection value='4' name='Details' />
<selection value='5' name='Summary' />
<selection value='6' name='Multi-view' />
<selection value='7' name='Change history' />
<selection value='8' name='Deleted items' />
<selection value='9' name='Mass transition' />
<selection value='10' name='State change history' />
<selection value='11' name='System' />
<selection value='12' name='Version control actions' />
<selection value='13' name='Sysfields' />
<selection value='14' name='Analytics' />
<selection value='15' name='External' />
<selection value='16' name='Open and Completed Trend' />
<selection value='17' name='State Activity Trend' />
<selection value='18' name='Backlog Trend' />
<selection value='19' name='Entering a State Trend' />
<selection value='20' name='Advanced Distribution' />
<selection value='21' name='Time in State Duration' />
<selection value='22' name='Avg Time to State Duration' />
<selection value='23' name='Drill Through' />
<selection value='24' name='Elapsed Time Duration' />
<selection value='25' name='Listing Join' />
<selection value='26' name='Calendar' />
<selection value='27' name='Multi-Calendar' />
<selection value='28' name='Backlog Burn-Up' />
<selection value='29' name='Backlog Burn-Down' />
</selections>
</field>
<field dbname="REPORTDEFID" name="Report Def ID" type="number-integer" />
<field dbname="REPORTDEF" name="Report Definition" type="text" size="128" />
<field dbname="COMPOSER_LISTING_RPT" name="Composer App Listing Report Type" type="selection">
<selections>
<selection value='0' name='-unknown-' />
<selection value='1' name='Composer Listing -- not accessible' />
<selection value='2' name='UI Non-App Report' />
<selection value='3' name='UI version of Composer Listing' />
</selections>
</field>
<field dbname="RPT_QUERY_TYPE" name="Query Type" type="selection">
<selections>
<selection value='0' name='-unknown-' />
<selection value='1' name='QBE' />
<selection value='2' name='Adv SQL' />
</selections>
</field>
<field dbname="START_DATE" name="Start Date" type="datetime" />
<field dbname="END_DATE" name="End Date" type="datetime" />
<!-- Time period can be actual epoch secs, an offset from epoch secs or a "Special DateTime Value" from "TS_Def.h" -->
<field dbname="TIME_PERIOD" name="Start/End Date Time Period" type="number-integer" />
<field dbname="CREATE_DATE_EPOCHSEC" name="Create Date/Time Epoch Seconds" type="number-integer" />
<field dbname="CREATE_DATE" name="Report Create Date" type="datetime" syscode="7" />
<field dbname="CREATE_DATE_EST_TXT" name="Create Date-Server Time$" type="text" size="24" />
<field dbname="LASTMODIFIED_DATE_EPOCHSEC" name="Last Modified Date/Time Epoch Seconds" type="number-integer" />
<field dbname="LASTMODIFIED_DATE" name="Report Last Modified Date" type="datetime" />
<field dbname="LASTMODIFIED_DATE_EST_TXT" name="Last Modified Date-Server Time$" type="text" size="24" />
<field dbname="LASTEXEC_DATE_EPOCHSEC" name="Last Execution Date/Time Epoch Seconds" type="number-integer" />
<field dbname="LASTEXEC_DATE" name="Report Last Execution Date" type="datetime" />
<field dbname="LASTEXEC_DATE_EST_TXT" name="Last Execution Date-Server Time$" type="text" size="24" />
<field dbname="RPT_USAGE_EPOCHSEC" name="Report Usage Date/Time Epoch Seconds" type="number-integer" />
<field dbname="RPT_USAGE_DATE" name="Report Usage Last Execution Date" type="datetime" />
<!-- <field dbname="RPT_USAGE_EST_TXT" name="Report Usage Date-Server Time$" type="text" size="24" /> -->
<field dbname="JOIN_SPECIFICATIONS" name="Join Specifications" type="text" size="255" />
<field dbname="COMMENT" name="Comment" type="text-memo" syscode="5" />
<field dbname="FOOTER" name="Report Footer" type="text" size="255" />
<field dbname="SPECIFICATIONS1" name="Specifications 1" type="text" size="255" />
<field dbname="PROJECTID" name="Project" type="project" />
<field dbname="HTML_TEMPLATE" name="HTML Template Filename" type="text" size="255" />
<field dbname="COLUMNS" name="Columns" type="text" size="255" />
<field dbname="SORTBY" name="Sort By" type="text" size="255" />
<field dbname="REFERENCENAME" name="Reference Name" type="text" size="128" />
</fields>
</view>
<!-- A View definition XML file may contain multiple pseudo-auxtable definitions. Each needs it's own "view" section. -->
</views>
Support
Documentation
Learning Services
Partner Programs
