on 04-25-2016 10:47 AM
Hi Gurus,
There is a requirement where the Audit reports(Most Accessed Documents) have to run automatically on
Monthly, Quarterly, Halfyearly basis without any prompts.
what is the solution for this requirement...
Thank you for the help !!!!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
can you try with some other queries.might be for this condition there is no audit record.I have tested with simple query eventid,event status and previous month filter and i am getting data.
Might be in your case no user is not created in the last month.Remove these two filters from your query and try.If you will get the data then it mean for your specific conditions there is no data.
This is the SQL.
SELECT
UATBOAUD.ADS_EVENT.EVENT_ID,
UATBOAUD.ADS_STATUS_STR.STATUS_NAME
FROM
UATBOAUD.ADS_EVENT,
(
SELECT
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CurrentDateTime,
TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS CurrentDate,
TO_CHAR(SYSDATE, 'HH24:MI:SS') AS CurrentTime,
'EN' as Language FROM dual
) Table__2,
UATBOAUD.ADS_STATUS_STR
WHERE
( UATBOAUD.ADS_EVENT.EVENT_TYPE_ID=UATBOAUD.ADS_STATUS_STR.EVENT_TYPE_ID and UATBOAUD.ADS_EVENT.STATUS_ID=UATBOAUD.ADS_STATUS_STR.STATUS_ID )
AND ( UATBOAUD.ADS_STATUS_STR.LANGUAGE = @Prompt(Select language) )
AND
( (
cast(to_char(to_date(Table__2.CURRENTDATE, 'YYYY-MM-DD'), 'YYYY') as number) - ( cast(to_char(UATBOAUD.ADS_EVENT.START_TIME, 'YYYY') as number) ) = 0
AND
(cast(to_char(to_date(Table__2.CURRENTDATE, 'YYYY-MM-DD'), 'MM') as number)-1) - ( cast(to_char(UATBOAUD.ADS_EVENT.START_TIME, 'MM') as number) ) = 0
) )
Hi Amit,
When i am using Previous Quarter filter it is giving results for march month
when i am using previous month filter it say "No data to retrieve in Query1"
When using the Prompt start date: and end date: for march month
i am getting results, please see the snapshot,,
Results for using Prompts:
Results for using "Previous quarter" filter
Results for using "Previous month" filter...
Thank you for the help !!!
Hi Divya,
below is the SQL query of predefined filters
SELECT
ADS_EVENT.Object_Name,
ADS_EVENT.Event_ID,
ADS_EVENT_TYPE_STR.Event_Type_Name,
CAST( ADS_EVENT.Start_Time AS CHAR ),
ADS_EVENT_TYPE_STR.Event_Type_ID
FROM (
SELECT
GetDate() AS CurrentDateTime,
CAST(GetDate() AS DATE) AS CurrentDate,
CAST(GetDate() AS TIME) AS CurrentTime,
'EN' as Language
) CurrentDateTime, ADS_EVENT_TYPE_STR INNER JOIN ADS_EVENT_TYPE ON (ADS_EVENT_TYPE.Event_Type_ID=ADS_EVENT_TYPE_STR.Event_Type_ID AND ADS_EVENT_TYPE_STR.Language = @Prompt(Select language))
INNER JOIN ADS_EVENT ON (ADS_EVENT.Event_Type_ID=ADS_EVENT_TYPE.Event_Type_ID)
WHERE
(
ADS_EVENT_TYPE_STR.Event_Type_Name IN ( 'View' )
AND
( (
YEAR(DateAdd(yy,-1,CurrentDateTime.CurrentDate)) - YEAR(ADS_EVENT.Start_Time) = 0
AND
MONTH(DateAdd(mm,-1,CurrentDateTime.CurrentDate)) - MONTH(ADS_EVENT.Start_Time) = 0
) )
)
Hi,
Based on the SQL the previous Month is for getting the data for previous Year and previous Month based on the current date. It is trying to fetch data for March 2015 if your current date is April 2016
Where as your other filter conditions created at report level are fetching data for March 2016.
So Year is different in both the conditions that is why you are not getting data.
You can check this with custom sql by using these definitions in the where clause
YEAR(DateAdd(yy,-1,CurrentDateTime.CurrentDate)) - YEAR(ADS_EVENT.Start_Time) ,
MONTH(DateAdd(mm,-1,CurrentDateTime.CurrentDate)) - MONTH(ADS_EVENT.Start_Time)
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.