Created On:  03 November 2011

Problem:

Assuming a SilkCentral Test Manager installation has multiple projects which all contain a particular report, if in any circumstance, the report data query (SQL statement) of the report is required to be updated, the user would have to make the same update on the numerous other projects which contain the same report.

Is there any functionality that allows a user to update the SQL query used for a SilkCentral report that exists across multiple projects?

Resolution:

It is possible to use the below SQL query to update the report data query of all the other reports in the SCTM database using the report data query from a report that has already undergone the update.


UPDATE
f1 SET f1.AdvancedQuery = f2.AdvancedQuery
FROM SCC_Filters f1 INNER JOIN SCC_ReportFilter rf ON rf.FilterNodeID_fk = f1.FilterID_pk
,
     (SELECT
AdvancedQuery
     FROM SCC_Filters f INNER JOIN SCC_ReportFilter rf ON rf.FilterNodeID_fk = f.
FilterID_pk
     WHERE rf.ReportFilterNodeID_pk = 53)
f2
WHERE rf.ReportFilterNodeID_pk <> 53 AND rf.ReportFilterName=
'Baseline Tests Comparison'


Please be aware that this query performs the update based on the name of the report (therefore, the report name on other projects must also be called for example, "Baseline Tests Comparison", and there must not be any other reports having the same name which you do not wish to update).

Furthermore, in the provided SQL statement you have to use the id of the report where you have already updated the query (as illustrated in the diagram below), so you only need to update one of the reports in one of the projects.  This process is simply copying the report data query of the report that has already been updated to the remaining reports in other projects within the same SCTM database.

Please use the following steps to complete the udpate process:

1. Make sure you have a backup of your database to be able to revert all changes that might have been wrong in the end

2. Make sure, that the report that you want to change has the same name in all other projects (the SQL statement will replace the query on all reports with this name)

3. Goto the “Properties” tab of the report where you have your updated query

4. There note the “Report Name” and the “Report ID

5. Update all other reports by using the SQL statement below using your “ReportID” (≡ rf.ReportFilterNodeID_pk) and your “Report Name” (≡ rf.ReportFilterName) as illustrated in the diagram below