QUESTION:
In the Strategy Enterprise Manager project, there is a report called "Reports Run by Date," which list down all the reports run by date. If the user wants to know how many of them were actually executed as datasets of documents, and needs to know how many documents were executed on that day, how should the report be defined?
ANSWER:
Enterprise Manager 9.x and 10.x does not keep track of the object dependency between Report Services documents and reports used as datasets for the documents. Therefore, a report needs to be defined to count the number of document jobs per user per day. Such report can be defined as shown below:
Attributes:
Project, Day, Document, User
Metric:
DP Number of Jobs
This report generates following SQL:
Sql
Select a11.DAY_ID CustCol,
a12.DAY_DATE DAY_DATE,
a11.IS_DOC_ID IS_DOC_ID,
a13.IS_DOC_NAME IS_DOC_NAME,
a11.EM_USER_ID EM_USER_ID,
a14.EM_USER_NAME EM_USER_NAME,
a11.IS_PROJ_ID IS_PROJ_ID,
a15.IS_PROJ_NAME IS_PROJ_NAME,
sum(((a11.IS_DOC_JOB_ID * 0) + 1)) WJXBFS1
from IS_DOC_FACT a11
join DT_DAY a12
on (a11.DAY_ID = a12.DAY_ID)
join IS_DOC a13
on (a11.IS_DOC_ID = a13.IS_DOC_ID)
join EM_USER a14
on (a11.EM_USER_ID = a14.EM_USER_ID)
join IS_PROJ a15
on (a11.IS_PROJ_ID = a15.IS_PROJ_ID and
a14.IS_REPOSITORY_ID = a15.IS_REPOSITORY_ID)
group by a11.DAY_ID,
a12.DAY_DATE,
a11.IS_DOC_ID,
a13.IS_DOC_NAME,
a11.EM_USER_ID,
a14.EM_USER_NAME,
a11.IS_PROJ_ID,
a15.IS_PROJ_NAME
