KB16866: Reports are listed as being run under "DELETED REPORTS" when a MicroStrategy Enterprise Manager report with the ‘Report’ attribute is executed

SYMPTOM:Users notice that the regular reports are listed as being run under <DELETED REPORTS> when they execute the following report in MicroStrategy Enterprise Manager 9.x. The SQL is listed along with the report in MicroStrategy Enterprise Manager.

select distinct a13.IS_PROJ_ID IS_PROJ_ID,
a16.IS_PROJ_NAME IS_PROJ_NAME,
a13.IS_REP_ID IS_REP_ID,
a14.IS_REP_NAME IS_REP_NAME,
a13.EM_USER_ID EM_USER_ID,
a15.EM_USER_NAME EM_USER_NAME
from DT_DAY a11
join IS_REP_FACT a13
on (a11.DAY_ID = a13.DAY_ID)
join IS_REP a14
on (a13.IS_REP_ID = a14.IS_REP_ID)
join EM_USER a15
on (a13.EM_USER_ID = a15.EM_USER_ID)
join IS_PROJ a16
on (a13.IS_PROJ_ID = a16.IS_PROJ_ID)
where a11.DAY_DATE = '2007-09-21'

1. Create a new report in MicroStrategy Desktop 9.x and execute it.
2. Right click on the report and go to Properties to get the report ID.
3. In the MicroStrategy Enterprise Manager 9.x warehouse, run the following SQL to check if the data from the above execution was moved over to the statistics table:

   SELECT JOBID, SESSIONID, REPORTID FROM IS_REPORT_STATS WHERE REPORTID = 'XXXX'

    

   JobID	SessionID	ReportID
   1	YYYY	XXXX

    
4. In the project metadata, run the following SQL to check if the above report object exists in the metadata:

   SELECT OJECT_NAME, CREATE_DATE, MOD_DATE FROM DSSMDOBJINFO WHERE OBJECT_ID =`XXXX'

   OBJECT_NAME	CREATE_TIME	MOD_TIME
   Test Report	9/21/2007 2:19:04 PM	9/21/2007 2:23:23 PM
5. Now delete the above report from project metadata.
6. Disconnect from the project source.
7. Run a manual data load from MicroStrategy Enterprise Manager 9.x. 
8. In the MicroStrategy Enterprise Manager 9.x warehouse, run the following SQL to check if the data from the statistics table (In Step 3), moved to the fact tables:

   SELECT IS_REP_ID, IS_SESSION_ID FROM IS_REP_FACT WHERE IS_SESSION_ID = 'YYYY' and IS_REP_JOB_ID = 1

    

   IS_REP_ID	IS_SESSION_ID	IS_REP_JOB_ID
   -14	YYYY	1

    
9. Notice that the IS_REP_ID is a negative number (-14) which indicates that it is a truly deleted report. To confirm this, in the MicroStrategy Enterprise Manager 8.x warehouse, run the following SQL:

   SELECT * FROM IS_REP WHERE IS_REP_ID = -14

    
10. The result of the above query can be seen below:

    Column Name	Column Value
    IS_REP_ID	-14
    IS_PROJ_ID	4
    IS_REP_GUID	NA>4
    IS_REP_VER	NA
    IS_REP_NAME	DELETED REPORT
    IS_REP_DESC	REPORT OBJECT ALREADY DELETED IN THE MD REPOSITORY
    IS_REP_LOC	NO LOCATION AVAILABLE
    EM_ACCESS_ID	0
    EM_OWNER_ID	1
    EM_OWNER_NAME	NA
    EM_CREATE_DATE	2000-01-01 00:00:01.000
    EM_MODIF_DATE	2000-01-01 00:00:01.000
    IS_REP_ABBREV	DEL REP
    EM_HIDDEN_ID	0
    EM_HIDDEN_DESC	VISIBLE
    EM_EXISTS_ID	0
    EM_EXITS_DESC	DOES NOT EXIST
    IS_REPTYPE_IND	0

11. Now create a MicroStrategy Enterprise Manager 9.x report with attributes Project, Report, and User and filter on Day. The grid and SQL view of the report will look like the one above in the Symptom section.