Previous month, Quarter months report.

[Former Member]

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 !!!!!

[amitrathi239 · Accepted solution]

Use predefined filters in the reports to schedule the reports without any prompt.All these conditions are based on the system date.

All these are available in the audit universe.

[Former Member · Accepted solution]

yes basically take a copy of the audit report and then add the let's say "This Month" filter. Save the report and then schedule it to run monthly. No need for any prompt. It will pickup the current month every time the schedule runs.

[Former Member · Accepted solution]

Hi Amith,

Thank you for the response,,,

How to remove Language prompt from the report...

Thank you !!!

[amitrathi239 · Accepted solution]

Not very sure.But if you are going to schedule then you need to select the language once only in the recurring jobs.

[Former Member · Accepted solution]

Hi Amit,

When i am using  prompt on date for  previous month i am getting the results

but when i am using the predefined filter "Previous Month' for report is says " No data to retrieve"

is there any work around for this..

Thank you for the help !!!

[amitrathi239 · Accepted solution]

are you checking the same query for both prompt  & predefined condition?

simply check with eventid and predefined function.

[Former Member · Accepted solution]

Hi Amit,

I am using predefined filter for the New Users and Groups created.

results: No data to retrieve from Query1

Displaying results for below:

[amitrathi239 · Accepted solution]

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

)  )

[Former Member · Accepted solution]

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 !!!

[Former Member · Accepted solution]

Hi,

Can you show us the definition of Previous month filter at the query or report level?

[Former Member · Accepted solution]

Hi Divya,

Thank you for the reply

This gives the output

This gives the errror "No data to display"

Thank you for help !!!

[Former Member · Accepted solution]

Hi,

I meant the SQL definition/query. How it is different and the columns that is referring.

Compare PrviousMonth predefined filter definition with that of other filter definitions

[Former Member · Accepted solution]

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

)  )

  )

[Former Member · Accepted solution]

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)