Archive Reports Automtically

Tal Mickel shared this question 3 years ago
Answered

Hi,


I am trying to create a process that will automatically archive reports that are not in use for the last 3 months.


Is there a way to export YF reports using an outside command (and also for private reports)?


Also, where can i locate all the reports XML in case i wish to BU those report files before deleting them.


Thanks!

Best Answer
photo

Hi Tal,


Looking through the debug logging for this action, it appears that Yellowfin is doing a few more actions than simply switching the status all of which might have an effect on this error.


This is probably the line that is causing the error (there is a similar delete for the sub-query that you will need to restore as well):


  1. DELETE FROM ReportAssociate
  2. WHERE ParentReportId = 63818
  3. OR ChildReportId = 63818


Looking at a report in this table that contains a sub-query we can see this link (not the same report because the actual row was deleted :) ):


96ebb7de085d93cca8b5ba4b8990801a


I imagine that restoring this will make this accessible, but I have attached a text file containing all of the UPDATE/DELETE/INSERT queries ran in course of the delete action.


In this example 63817 is the primary report, while 63818 is the sub-query


Regards,

Nathan

Comments (8)

photo
1

Hi Tal,

Within the configuration database, all reports are saved as a series of rows between multiple tables.


  • The reportheader contains all of the meta data about the report (Name, creation date, etc)
  • This is linked to another table which contains the data on what report contains what columns (Aggregation, sorting, order, headers, etc)
  • This is linked to another table that contains data on what field in the view this column comes from
  • etc.

When a report is deleted, a flag within the database is set to "DELETED", though the entire definition for this report remains. So technically the reports are always contained within an archive, though they are never stored as XML, unless you are employing the Export/Import tool.

In terms of automating the export of reports, you can use web-service calls to achieve this. This article contains the foundation to get you going on this:

http://wiki.yellowfin.com.au/display/USER73/Administration+Service

This is just a preliminary overview of all of this, so please let me know if you would like more information on anything.

Regards,

Nathan

photo
1

Hi Nathan,


I did an experiment in our dev environment and deleted a report with two appends from YF interface.

After deleting the report i tried "reviving" it by updating the ReportStatusCode in ReportHeader table from DELETED to OPEN for the 3 records (1 - Report, 2- Appends).


After the update the report is showing in the Browse menu, but when i try to run it i get the error message attached.


Is there something else i need to do in order for the report to be operational again?


Thanks!

photo
1

Hi Tal,


Sorry for the delay on this. In my tests, simply switching this to OPEN worked for me. What error does this correspond to in the yellowfin.log? (youryellowfin/appserver/logs)


Regards,

Nathan

photo
1

Hi Nathan,

I tried it again and got the same error message.

The following error message appears in the log:

YF:2017-04-06 08:31:07:ERROR (ReportRunner:runReport) - Error running report: java.lang.NullPointerException

java.lang.NullPointerException

at com.hof.report.AbstractReportContext.getFormatter(AbstractReportContext.java:427)

at com.hof.report.result.modifier.FormatResultSetModifier.modifyResult(FormatResultSetModifier.java:36)

at com.hof.report.result.ReportResultSetUtil.applyModifiers(ReportResultSetUtil.java:136)

at com.hof.mi.process.ReportRunner.J(ReportRunner.java:972)

at com.hof.mi.process.ReportRunner.runReport(ReportRunner.java:248)

at com.hof.mi.util.background.ReportQueueItem.process(ReportQueueItem.java:69)

at com.hof.mi.util.background.ReportQueueItem.process(ReportQueueItem.java:30)

at com.hof.mi.util.background.ExecutionQueue$TaskRunner.call(ExecutionQueue.java:401)

at java.util.concurrent.FutureTask.run(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)


Any ideas?

Thanks!

photo
1

Hi Tal,


After a bit of testing, it appears that reports with sub-queries actually create two rows in the configuration database, one for the primary query and one for the sub-query. These are linked by the "ParentReportId" row:


83f2165451ea4a353caac21fbed8f472


In order to restore this, you will need to update the status of both of these (there may be more but this worked in my test).


Please note that this is all testing, as there are many odd links between rows int eh configuration database, remember to always back up your config DB so you can roll back if something goes wrong!


Regards,

Nathan

photo
1

Hi Nathan,


anything new with this one?


Thanks!

photo
photo
1

Hi Nathan,


I know that i have additional record for each sub-query (append).


if you look at the post above i wrote that i updated the report and the sub-queries records status to open and i still get the error message,


Thanks!

photo
1

Hi Tal,


My apologies, I was out of the office for the last couple of days. I will look into this now and get something to you in the next couple of hours.


Regards,

Nathan

photo
1

Hi Tal,


Looking through the debug logging for this action, it appears that Yellowfin is doing a few more actions than simply switching the status all of which might have an effect on this error.


This is probably the line that is causing the error (there is a similar delete for the sub-query that you will need to restore as well):


  1. DELETE FROM ReportAssociate
  2. WHERE ParentReportId = 63818
  3. OR ChildReportId = 63818


Looking at a report in this table that contains a sub-query we can see this link (not the same report because the actual row was deleted :) ):


96ebb7de085d93cca8b5ba4b8990801a


I imagine that restoring this will make this accessible, but I have attached a text file containing all of the UPDATE/DELETE/INSERT queries ran in course of the delete action.


In this example 63817 is the primary report, while 63818 is the sub-query


Regards,

Nathan

photo
1

Hi Nathan,


inserting the deleted records back into ReportInstance table did the trick.


Thanks a lot!

photo
1

Hi Tal,


No problem! Im going to close this one for now, but if you run into any problems just reply and the case will be re-opened.


Regards,

Nathan

photo