How To Delete A Report And Its Instances Through CMS Database In BOE XI R2

Points to be taken into consideration:

- Before proceeding with the following steps, I’d like to inform you to take the back up of your system database (CMS DB) as well as reporting database (FileStore) to be safe if accidentally any other reports or instances get deleted.

- Also, I’d like to prefer you to do these steps on a test machine, on a test report with instances.

- If you get successful in this work around, then you can go ahead and do the same steps on the actual database.

General Overview:

- Find the report’s SI_ID and ParantID in the database.

- Find all the instances associated with the report along with their SI_ID and the actual location in the database.

- Delete the entries through database.

Actual Steps:

Step 1. Find the report’s SI_ID and ParantID in the database.

Step 2. Find all the instances associated with the report along with their SI_ID and the actual location in the database.

Step 3. Delete the entries of instances through system database.

Step 4. Delete the entries of instances through FileStore

Step 5. Delete the empty folders in the database left behind by the instances.

Step 6. Delete the report object.

Step 1. Find the report’s SI_ID and ParantID in the database.

Open Query Builder and run following command,

SELECT

SI_ID, SI_PARENTID, SI_NAME, SI_LAST_SUCCESSFUL_INSTANCE_ID

FROM

CI_INFOOBJECTS

WHERE

SI_NAME = ‘Report Name’ AND SI_INSTANCE = 0 AND SI_KIND = ‘CrystalReport’

The query provides the following information:

SI_ID: This ID is used as the SI_PARENTID value for each report instances it creates beneath it.

SI_PARENTID: The parent ID of the Report. The value is usually the Folder unique SI_ID it resides in.

SI_NAME: The report name.

- Make a note of report’s SI_ID and its ParentID to use further.

Step 2. Find all the instances associated with the report along with their SI_ID and the actual location in the database.

Open Query Builder and run following command,

SELECT

SI_FILES

FROM

CI_INFOOBJECTS

WHERE

SI_PARENTID = ParentID AND SI_INSTANCE = 1

ORDER BY SI_ID DESC

The query provides the following information:

SI_FILES: The physical location and name of Report Instance in FRS.

SI_PARENTID: The main Report Objects SI_ID value.

SI_INSTANCE: Used to confirm that the object is an instance rather than report template. Value should be True or 1.

- If you have a large number of instances and if the Query Builder is being hang out, then modify the query to give minimal outputs and perform the delete operation for these instances first and retrieve next.

Step 3. Delete the entries of instances through system database.

- Now you have to query to the database through DB client tool and delete these instances by querying to the database. Here, I’d like to mention that, we can’t use ‘Delete’ command through Query Builder.

1. Delete the instances by querying to the database. It will delete all the instances associated with the report.

Ex. Query For MySQL database,

DELETE FROM cms_infoobjects5 WHERE ParentID=’ParantID’;

Where,

‘ParentID’ is the main Report Object’s SI_ID value.

Step 4. Delete the entries of instances through FileStore

Copy the output of Step 2 and paste it in a file on the local drive.

- Create and use a batch file to get only “frs://Output/” locations aside in the file and leave all other content.

- Replace “frs://Output/” with “FileStore\Output\”.

- Also, replace all ‘/’ with ‘\’ in the file to reach to actual file location.

Ex. Query Builder output: “frs://Output/a_096/005/000/1376/”

Replace this with the following,

“FileStore\Output\a_096\005\000\1376\”

- Create and use another batch file to delete these entries through the FileStore at a time.

Step 5. Delete the empty folders in the database left behind by the instances.

A simple way to clean the database with empty folders is by removing these folders using following command line switch.

-prune

- Stop the Input and Output FRS through CCM and add this switch to the command line.

- This instructs the FRS to delete the empty folders and then start the server.

- The server shuts down automatically when the empty folders are deleted.

- You can use the –trace command to get a log file for the number of folders visited and number of objects deleted.

- Remove the switch after completion.

- Start the servers again.

Step 6. Delete the report object.

- Now you can simply go ahead and delete the report through CMC.

Or

- Use the following query through the database client.

DELETE FROM cms_infoobjects5 WHERE ObjectID=’ObjectID’;

where,

ObjectID is the main Report Object’s SI_ID value.