General Technical Discussions for Service

Get Involved. Join the Conversation.

Topic

    Guru Perumal
    Custom report extract from Oracle Service Cloud
    Topic posted February 4, 2019 by Guru PerumalRed Ribbon: 250+ Points 
    142 Views, 7 Comments
    Title:
    Custom report extract from Oracle Service Cloud
    Summary:
    Custom report extract from Oracle Service Cloud
    Content:

    Hi There,

    We have a custom report created in Oracle Right Now which is reading the data from multiple tables, up to 6, I guess. 

    If we run the extract manually for all the records, I get the below error

    "The report could not be processed because it exceeds the query size limitation, please reduce the amount of data processed by the report". 

    I modified the filter so that, it would fetch maximum 10000 records only and it is works fine. However, I have to extract approximately , 1 million records and manually performing this task is tedious. 

     

    To avoid this, I have written custom PHP code that would run  the report with the range which would fetch less than 10000 records and it is working fine. ex: My report has filters on Incident Numbers as start and end. so , I always provide the start end numbers to be less than 10000. I have designed the code to provide the start and end range for up to 200000 records and code split them to a batch size which is 10000 and the the code will run the extract and get the records and save them in a file. and I am downloading this file to local machine.

    the challenge here is... this is working only when the range I have provided extracts only up 30-40 k records (20MB) , if the record count increases , it is failing. 

    Any inputs are much appreciated. Thanks.

     

    Abhi

    Version:
    Oracle Service Cloud 18C

    Comment

     

    • Tushar Gupta

      Hi Guru,

      You can try by opening the Options Settings and Check the Box "Set Report to Deferred Execution" as shown in the Screenshot. And save it.

      When you run the report you would get an option saying that Data is huge. And you would get an option to queue the report. Please queue the report. After some time you would get the report data in the queued report. Please try if it works for you.

      Thanks 

      Tushar Gupta

      • Guru Perumal

        Hi Tushar,

        I have tried this option but it did not work when the count of records returned by filter are more than 10k records. I submitted the request and then went to my Queued reports and noticed that a record created with In Progress status. however, after a minute the record disappeared and noticed an system error something saying 'request failed' 

        It appears that, this functionality works if I try to export data from a sinlg table but not when the report is based on multiple tables , in this case I have more than 7 tables involved.

        I folllowed below community article for this.

        http://communities.rightnow.com/posts/6210fc18c8?commentId=44581#44581

         

        Thanks.

         

        regards,

        Abhi

        • Tushar Gupta

          Hi Guru,

          The way I told to run the report is in the normal way we run the report. After running it would show you the popup as in the attachment. Then after clicking on ok, the report would be queued. Please try and tell if it works.

          The way you told is a different way to queue the report as shown in the link mentioned.  

          Thanks

          Tushar Gupta

    • Dev Aditya

      No Solution can be simpler than what Tushar suggested. :) Operational data base shows run time data, and it has limitation of number of rows it can display. However, data displayed by 'Reporting Database' can be up to 24 hours old, but it has huge capacity to process data.

      However, if above solution does not work, you can use your custom code in custom script of report and schedule the report. Using standard scheduling, you can schedule a report in minimum 15 minutes interval. So, if we consider 40K is the limit, it will take 25 runs of report to cover all data.

      However, for this, you will have to add extra piece of code with your code. In this, for every record processed, you will have to switch a flag to 'Yes' and add a filter with this flag. In this way, once a record is processed, it will not appear in the report in next run.

      I hope it helps.

       

      Thanks,

      Dev

    • Justin

      if you are nice enough to the support admin you can also get the hard limit changed