I am trying to create a simple impact analysis: which reports are effected by a table.
At first i thought i would to create some SDK but know i have found this value under the field AUDIT_DETAIL .DETAIL_TEXT.
if i use this in the where clause and take also the document name
i am getting the SQL for each report.
is this field can be reliable for such queries ?does it have any drawbacks ?
does it cover also free-hands ?
i will be happy to hear about your experience with it.
I am trying to retrieve information about the number of reports in my system
The SQL is:
SELECT
DERIVED_DOCUMENT_NAME.Detail_Text
FROM
( select
AUDIT_EVENT.Server_CUID, AUDIT_EVENT.Event_ID, cast(AUDIT_DETAIL.Detail_Text as nvarchar(128)) as Detail_Text
from
AUDIT_EVENT, AUDIT_DETAIL
where
(AUDIT_EVENT.Server_CUID = AUDIT_DETAIL.Server_CUID) and
(AUDIT_EVENT.Event_ID = AUDIT_DETAIL.Event_ID) and
(AUDIT_DETAIL.Detail_Type_ID = 8)) DERIVED_DOCUMENT_NAME
Another query retrieves the reports names and their SQL’s
The SQL for this query is:
SELECT
DERIVED_DOCUMENT_NAME.Detail_Text,
AUDIT_DETAIL.Detail_Text
FROM
DETAIL_TYPE INNER JOIN AUDIT_DETAIL ON (DETAIL_TYPE.Detail_Type_ID=AUDIT_DETAIL.Detail_Type_ID)
INNER JOIN AUDIT_EVENT ON (AUDIT_DETAIL.Event_ID=AUDIT_EVENT.Event_ID and AUDIT_DETAIL.Server_CUID=AUDIT_EVENT.Server_CUID)
INNER JOIN ( select AUDIT_EVENT.Server_CUID, AUDIT_EVENT.Event_ID, cast(AUDIT_DETAIL.Detail_Text as nvarchar(128)) as Detail_Text
from
AUDIT_EVENT, AUDIT_DETAIL
where
(AUDIT_EVENT.Server_CUID = AUDIT_DETAIL.Server_CUID) and
(AUDIT_EVENT.Event_ID = AUDIT_DETAIL.Event_ID) and
(AUDIT_DETAIL.Detail_Type_ID = 8)
) DERIVED_DOCUMENT_NAME ON (AUDIT_EVENT.Event_ID=DERIVED_DOCUMENT_NAME.Event_ID and AUDIT_EVENT.Server_CUID=DERIVED_DOCUMENT_NAME.Server_CUID)
WHERE( DETAIL_TYPE.Detail_Type_Description = ‘SQL value’ )
However the second query fetches a much lesser amount of reports .
Shouldn’t the where clause :
DETAIL_TYPE.Detail_Type_Description = ‘SQL value’ just add the SQL value to the report name without any effect on the report count ?
My test indicates that only if the report is edited than the SQL value is being audit.
Does anybody have information about this field behavior?