Issue
There are several obsolete or duplicate report templates that are listed in the Reports tab of the Web Applications that the user would like to remove.
Product Line
Power Monitoring Expert 9.0
Power Monitoring Expert 2020
Environment
Web Application - Reports
Cause
- Upgrading an existing installation of StruxureWare Power Monitoring to Power Monitoring Expert 9.0 will sometimes result in duplicate or old report templates being listed that were brought over with the ApplicationModules database of the old system. The user could have also uploaded custom report templates and no longer has a need for them.
- In case the Report Template is no longer needed and needs to be deleted
Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*
PME 9.0
Procedure to delete report templates:
In this example, the report templates to be deleted are "MyTemplate", "PQ Incident Report", and "PQ Waveform Report".
1. Open SQL Server Management Studio (SSMS), then connect to the PME database instance (default is ION)
2. Click New Query
3. Copy and paste in the following script, replacing MyTemplate with the name of the report template to be removed, as it is shown in the Web Applications:
In this example, the report templates to be deleted are "MyTemplate", "PQ Incident Report", and "PQ Waveform Report".
1. Open SQL Server Management Studio (SSMS), then connect to the PME database instance (default is ION)
2. Click New Query
3. Copy and paste in the following script, replacing MyTemplate with the name of the report template to be removed, as it is shown in the Web Applications:
Use ION_Network
SELECT * FROM dbo.RPT_Report
WHERE DisplayName like 'MyTemplate'Note: Some report templates have a different name under DisplayName compared to what is shown in the Web Applications (e.g. "Dashboard Report" has a DisplayName of "Web Report" in the dbo.RPT_Report table).
For multiple report templates you can format the script similar to the example below:
Use ION_Network
SELECT * FROM dbo.RPT_Report
WHERE DisplayName IN ('PQ Incident Report','PQ Waveform Report','MyTemplate')4. Click Execute to run the script
5. Confirm the correct report templates were selected, then delete them from the table by referring to the example script shown below:
Use ION_Network
DELETE FROM dbo.RPT_Report
WHERE DisplayName IN ('PQ Incident Report','PQ Waveform Report','MyTemplate')6. Click Execute to run the script
7. Refresh the Web Applications
8. The report templates should now appear without the page icon; each one can be deleted by clicking the three dots icon to the right of the report template name, then selecting Delete
Procedure to remove duplicate report templates:
In this example, the report templates to be fixed are the duplicate "100 ms Report", "Dashboard Report", and "Tabular Report".
Note: This procedure does not delete the report template from the system, it removes it from the viewable list of report templates in the Reports tab of the Web Applications.
1. Follow steps 1 - 4 of the previous procedure, except run the query to search for the duplicated report templates:
2. Note the corresponding number for the report templates in the ReportID column
3. Click New Query, then copy and paste in the following script, replacing the number 2 below with the ReportID found in the previous step:
Use ApplicationModules
SELECT * FROM Library.Items
WHERE ContentHref LIKE '%=2'For multiple report templates you can format the script similar to the example below:
Use ApplicationModules
SELECT * FROM Library.Items
WHERE ContentHref LIKE '%=2' OR ContentHref LIKE '%=9' OR ContentHref LIKE '%=98'4. Click Execute to run the script
5. Refer to the DisplayName column to confirm the correct reports were selected
6. Choose one of the two entries for each report template and copy the value under the ItemId column
7. Delete the duplicate entries from the table by referring to the example script shown below:
5. Refer to the DisplayName column to confirm the correct reports were selected
6. Choose one of the two entries for each report template and copy the value under the ItemId column
7. Delete the duplicate entries from the table by referring to the example script shown below:
Use ApplicationModules
DELETE FROM Library.Items
WHERE ItemId LIKE 'DE992CE5-9F1C-49DB-A5E8-2278D9989A4D'
OR ItemId LIKE 'BCA783D5-FDCC-42F8-913B-EACB8936FB02'
OR ItemId LIKE 'B7C769A3-8C70-41B1-8D22-FBECE3BB6AAB'8. Click Execute to run the script
9. Refresh the Web Applications
10. The duplicate report templates should now be deleted from the Reports tab
PME 2020
Procedure to delete report templates:
In this example, the report templates to be deleted are "Whakatu Monthly Biling Report":
In this example, the report templates to be deleted are "Whakatu Monthly Biling Report":
1. Open SQL Server Management Studio (SSMS), then connect to the PME database instance (default is ION)
2. Click New Query
3. Copy and paste in the following script, replacing MyTemplate with the name of the report template to be removed, as it is shown in the Web Applications:
2. Click New Query
3. Copy and paste in the following script, replacing MyTemplate with the name of the report template to be removed, as it is shown in the Web Applications:
Use ION_Network
SELECT * FROM dbo.RPT_Report
WHERE DisplayName like 'Whakatu Monthly Biling Report'Note: Some report templates have a different name under DisplayName compared to what is shown in the Web Applications (e.g. "Dashboard Report" has a DisplayName of "Web Report" in the dbo.RPT_Report table).
For multiple report templates you can format the script similar to the example below:
Use ION_Network
SELECT * FROM dbo.RPT_Report
WHERE DisplayName IN ('PQ Incident Report','PQ Waveform Report','MyTemplate')4. Make a note of the Report ID
5. Run the query to delete the Report:
5. Run the query to delete the Report:
Use ION_Network
DELETE * FROM dbo.RPT_Report
WHERE ReportID = X (ReportID noted from the previous step - if multiples, use WHERE ReportID in (x,y,z) )6. You may refresh Web Application to check if the Report Template no longer exist.
7. If still exist, run the following query:
7. If still exist, run the following query:
USE ION_Network
select RPT_ReportTemplateReportMap.ReportTemplateID, RPT_ReportTemplate.LocalizedName
from RPT_ReportTemplateReportMap
inner join RPT_ReportTemplate ON RPT_ReportTemplateReportMap.ReportTemplateID = RPT_ReportTemplate.ReportTemplateID
where LocalizedName = 'Whakatu Monthly Billing Report'
select RPT_ReportTemplateReportMap.ReportTemplateID, RPT_ReportTemplate.LocalizedName
from RPT_ReportTemplateReportMap
inner join RPT_ReportTemplate ON RPT_ReportTemplateReportMap.ReportTemplateID = RPT_ReportTemplate.ReportTemplateID
where LocalizedName = 'Whakatu Monthly Billing Report'
8. Make a note of the ReportTemplateID (eg. = X)
9. Run the query to confirm that the Report Template exist in both tables:
9. Run the query to confirm that the Report Template exist in both tables:
select * from RPT_ReportTemplateReportMap where ReportTemplateID = X
select * from RPT_ReportTemplate where ReportTemplateID = X
Then after confirming this is the right template to be removed, run:
delete from RPT_ReportTemplateReportMap where ReportTemplateID = X
delete from RPT_ReportTemplate where ReportTemplateID = X
select * from RPT_ReportTemplate where ReportTemplateID = X
Then after confirming this is the right template to be removed, run:
delete from RPT_ReportTemplateReportMap where ReportTemplateID = X
delete from RPT_ReportTemplate where ReportTemplateID = X
10. Refresh Web Application and the Report Template will no longer exist.
Released for:Schneider Electric New Zealand
