This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

How to mass-delete reports, OR delete a Report with SQL?

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:

  1. Re-activate the User in App Admin, with all the peril of the User's Contact record being surreptitiously updated.
  2. "Logon as" that user
  3. Find and delete the Report
  4. "Logon as" self
  5. Re-Delete the User, again with all peril of the Contact record being updated.
  6. ... 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,'')) &lt; 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,'')) &lt; 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,'')) &lt; 1 Then Replace(rpt.ts_Name,' ','_') Else '-' + rpt.ts_ReferenceName End As VarChar(128)) As [TS_CONSTRUCTED_REF_NAME],


            Cast(
               (Select
                     ts_StringValue +
                     '&#47;workcenter&#47;tmtrack.dll?shell=swc&amp;ReportPage&amp;Template=reports%2Flist&amp;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 &amp; 0x0001>0 Then 'Normal' Else '' End +
               Case When AuthUsr.ts_AccessType &amp; 0x0002>0 Then ', Occasional' Else '' End +
               Case When AuthUsr.ts_AccessType &amp; 0x0004>0 Then ', External' Else '' End +
               Case When AuthUsr.ts_AccessType &amp; 0x0008>0 Then ', 0x08' Else '' End +
               Case When AuthUsr.ts_AccessType &amp; 0x0010>0 Then ', Managed Admin' Else '' End +
               Case When AuthUsr.ts_AccessType &amp; 0x0020>0 Then ', API/Script' Else '' End +
               Case When AuthUsr.ts_AccessType &amp; 0x0040>0 Then ', 0x40' Else '' End +
               Case When AuthUsr.ts_AccessType &amp; 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 &amp; 0x0001>0 Then 'Normal' Else '' End +
               Case When LastModUsr.ts_AccessType &amp; 0x0002>0 Then ', Occasional' Else '' End +
               Case When LastModUsr.ts_AccessType &amp; 0x0004>0 Then ', External' Else '' End +
               Case When LastModUsr.ts_AccessType &amp; 0x0008>0 Then ', 0x08' Else '' End +
               Case When LastModUsr.ts_AccessType &amp; 0x0010>0 Then ', Managed Admin' Else '' End +
               Case When LastModUsr.ts_AccessType &amp; 0x0020>0 Then ', API/Script' Else '' End +
               Case When LastModUsr.ts_AccessType &amp; 0x0040>0 Then ', 0x40' Else '' End +
               Case When LastModUsr.ts_AccessType &amp; 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) &lt;= 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) &lt;= 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) &lt;= 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) &lt;= 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) &lt;= 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) &lt;= 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),'&quot;'),
                                 Char(39),'&apos;&apos;'),
                              Char(60),'&lt;'),
                           Char(62),'&gt;'),
                        Char(96),'&#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),'&quot;'),
                                 Char(39),'&apos;&apos;'),
                              Char(60),'&lt;'),
                           Char(62),'&gt;'),
                        Char(96),'&#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),'&quot;'),
                                 Char(39),'&apos;&apos;'),
                              Char(60),'&lt;'),
                           Char(62),'&gt;'),
                        Char(96),'&#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              Date­only field
                     datetime          Date­time field
                     project           Project field (not system project field, aux tables do not have system project fields)
                     selection         Single­selection 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        Single­relational 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="3c534aa0­cb13­4b27­b8a3­e3b9c9fc4ca6" />
                     many-relational   Any­relational 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 pop­up (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),'&quot;'),
                                 Char(39),'&apos;&apos;'),
                              Char(60),'&lt;'),
                           Char(62),'&gt;'),
                        Char(96),'&#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>


Parents
  • Hi Paul. In the end I am not sure I understand your question. You probably know how to delete from TS_REPORTS and identify the unwanted reports. Are you asking how to avoid deleting reports that are referenced by Scheduled Reports, Dashboards, Home Page Report Settings, etc? Or perhaps to report on where used?

    There will be other areas not easily queried like embedded report widgets on custom forms and SBM Web Service calls using the reports (possibly in or outside of orchestrations). Hopefully since you are including the last executed date in your logic, this will help you avoid having to worry about these later scenarios.

    I can appreciate wanting to clean up a system and make it easier to maintain, but since private reports are only visible from the db and not other users, is their presence causing any other issues that are leading you down this road?

  • Suggested Answer

    Garry:

    Thanks again for the "Why bother?" question.  We've had a team rethink on this and have decided to not pursue it.  The work required doesn't have enough payoff.  It's just cleanup to make the admins happy.  As my wife reminds me "A clean house is a happy house" but some parts of SBM don't support that.  There's just a lot of dust & fuzzballs in the corners.  Don't look there.  ;-)

  • Thanks for the updates. I agree there is value in managing the public reports. If you have specific questions along those lines, let me know and I will see what I can offer.

    As a tip: if you don't have the luxury of a test env to do tricky stuff, at least create snapshots of the deployed solutions to backup reports so they can be repromoted in the event you decide you want the reports back later. OR even maybe make them private for a while as a way to phase them out of use.

  • 1257 Private reports where the owner/author has been deleted.

    381 Reports where Report Last Exec date and Report Usage Last Exec Date are "(Never)"

    952 Reports where Report Last Exec date is more than 365 days ago

    Like I said ... a lot of flotsam and jetsam. 

Reply Children
No Data