BMC is working with the vendor Yellowfin on implementing housekeeping functionalities like having an option to automatically clean up draft reports. Currently, there’s an idea logged in BMC Communities and in Yellowfin portal requesting to add this kind of functionality (refer to related content links at the bottom of this post).
Meanwhile housekeeping is added to the product, there’s an activity that can be done at database level to clean the draft reports to avoid displaying them in the user interface (UI) and to avoid manual one by one report deletion from the UI.
Notice that we do not recommend deleting any record at database level and before making any change you need to make sure to stop Smart Reporting service and to have a recent database copy.
In Smart Reporting database, the majority of records have a “flag” available to mark records as deleted instead to actually deleting information from the database.
The tables involved or related to reports metadata are:
- ReportHeader. Contains the main information related to a report like its ID, title, description, modification date, owner and among others, the status of the report. This status has the function of indicating if the report is set as a draft, if it’s archived, deleted or “open” (published).
- ReportView. Contains the relation of all available Views. Same as ReportHeader table, it contains the details of all views (ID, title, description, etc.)
The change to be performed does not include modifying ReportView table but it is important to mention it to understand the relation between two important items in Smart Reporting: Reports and Views and how its metadata is stored.
Following query can be used to identify all reports that are currently set as “draft”:
Select * from ReportHeader where ReportStatusCode = 'DRAFT'
Also, a “count” can be used to obtain only the number of reports that are set to “draft”:
Select count (*) from ReportHeader where ReportStatusCode = 'DRAFT'
Once we have an idea of the amount of reports to be marked as deleted, we can execute below query to make the change, again, make sure to have a backup of the database and stop Smart Reporting service prior executing below update:
update ReportHeader set ReportStatusCode = 'DELETED' where ReportStatusCode = 'DRAFT'
Now, just start Smart Reporting service and access to the application. No draft report will be displayed.