How to extract new/ copied reports from YF repository?

Tal Mickel shared this question 2 years ago
Answered

Hi,


I am trying to build a query yo extract the new and copied YF reports.

My query filters event codes RPTCOPY and RPTCREATE.

For some reason RPTCREATE doesn't retrieve newly created reports. Also, RPTCOPY only give me the name of the origin report and not the new report copied.


Any suggestions?


Thanks!

Best Answer
photo

Hi Tal,

Thanks for the clarification. Can you see if this query meets your needs:


  1. SELECT * FROM 735yellowfin20170307.event
  2. WHERE ((EventCode = 'RPTPUBLISH' AND ReferenceId NOT IN
  3. (SELECT ReferenceId FROM 735yellowfin20170307.event WHERE EventCode = 'RPTEDIT')) OR EventCode = 'RPTCOPY')
  4. AND EventDate > DATE_SUB(curdate(), INTERVAL 7 day)

Basically, it selects all copy events, as well as all publish events where there is no corresponding edit event.

Regards,

Nathan

Comments (5)

photo
1

Hi Tal,

In order to query new reports from the configuration database, it would probably be best to compare the report creation time to the current time. Something like:


  1. SELECT * FROM 71yellowfin20160418.reportheader
  2. WHERE PublishDate > DATE_SUB(curdate(), INTERVAL 7 day)
  3. AND ReportStatusCode = 'OPEN';

If you can elaborate a bit on what your final goals are here I should be able to provide you with more case specific suggestions.

Regards,

Nathan

photo
1

Hi Nathan,


Correct me if i'm wrong but your query will also retrieve reports that were edited and activated.


i only want to see new reports meaning:


1. Report Created by Create --> Report

2. Report Created by Report --> Copy


That's why i tried to use the RPTCOPY and RPTCREATE events.


Thanks!

photo
1

Hi Tal,

Thanks for the clarification. Can you see if this query meets your needs:


  1. SELECT * FROM 735yellowfin20170307.event
  2. WHERE ((EventCode = 'RPTPUBLISH' AND ReferenceId NOT IN
  3. (SELECT ReferenceId FROM 735yellowfin20170307.event WHERE EventCode = 'RPTEDIT')) OR EventCode = 'RPTCOPY')
  4. AND EventDate > DATE_SUB(curdate(), INTERVAL 7 day)

Basically, it selects all copy events, as well as all publish events where there is no corresponding edit event.

Regards,

Nathan

photo
1

Thanks for your help!

photo
1

Hi Tal,


No problem! I am going to close this, but if you run into any additional problems, just message me and the case will be re-opened.


Regards,

Nathan


Closed Request Survey.