2 Replies Latest reply on Apr 30, 2015 1:34 PM by Tor Robinson

    Scheduling a BI Report via the Web Services (ScheduleService)

    Tor Robinson

      Hello,

       

      Lately I've been creating an tool for our company to assist with BI Reports and scheduling in bulk. I'm doing this by interacting with the ScheduleService (v2) offered by BI. So far I'm able to traverse the BI catalog for reports and allow bulk scheduling and cancelling of reports/jobs. But so far, the only way I can see a schedule working is if I don't provide a "recurrence expression".

       

      I'm using the scheduleReport() method and passing in a ScheduleRequest (http://docs.oracle.com/cd/E21764_01/bi.1111/e18863/datatypes.htm#BABJECGI).

       

      I am specifying 2 delivery channels (one SFTP, one Email for quicker debugging). The report executes and delivers to these channels just fine if I don't specify a recurrence.

      But if I generate and pass along a cron recurrence expression, such as "0 8 */2 * * ? *" (every second hour on the 8th minute) and a recurrenceExpressionType of "cron" and schedule the exact same report for the same delivery channels, then the service does return a new JobID, but nothing happens. No emails or SFTP file drops happen, and no schedules appear in the "Manage Schedules" section of BI (XYZ.com/analytics/saw.dll?bipublisherEntry&Action=schedules)

       

      Since the service returns a Job ID, I would assume that the schedule is set up, but not executing for some reason.

       

      But since the job apparently is created, I can at least run a getScheduledJobInfo call and see what BI thinks about the job.

       

      Here's the ScheduleRequest before it sends off:

       

      -scheduleRequest{FECU.OracleServices.ScheduleService.ScheduleRequest}FECU.OracleServices.ScheduleService.ScheduleRequest
      bookBindingOutputOptionfalsebool
      bookBindingOutputOptionFieldfalsebool
      dataModelUrlnullstring
      dataModelUrlFieldnullstring
      +deliveryChannels{FECU.OracleServices.ScheduleService.DeliveryChannels} [ignore these, they work fine]FECU.OracleServices.ScheduleService.DeliveryChannels
      +deliveryChannelsField{FECU.OracleServices.ScheduleService.DeliveryChannels}FECU.OracleServices.ScheduleService.DeliveryChannels
      endDate"2015-04-29T150739"string
      endDateField"2015-04-29T150739"string
      ExtensionDatanullSystem.Runtime.Serialization.ExtensionDataObject
      extensionDataFieldnullSystem.Runtime.Serialization.ExtensionDataObject
      jobLocale"en_US"string
      jobLocaleField"en_US"string
      jobTZ"America/Edmonton"string
      jobTZField"America/Edmonton"string
      mergeOutputOptionfalsebool
      mergeOutputOptionFieldfalsebool
      notificationPasswordnullstring
      notificationPasswordFieldnullstring
      notificationServernullstring
      notificationServerFieldnullstring
      notificationTonullstring
      notificationToFieldnullstring
      notificationUserNamenullstring
      notificationUserNameFieldnullstring
      notifyHttpWhenFailedfalsebool
      notifyHttpWhenFailedFieldfalsebool
      notifyHttpWhenSkippedfalsebool
      notifyHttpWhenSkippedFieldfalsebool
      notifyHttpWhenSuccessfalsebool
      notifyHttpWhenSuccessFieldfalsebool
      notifyHttpWhenWarningfalsebool
      notifyHttpWhenWarningFieldfalsebool
      notifyWhenFailedfalsebool
      notifyWhenFailedFieldfalsebool
      notifyWhenSkippedfalsebool
      notifyWhenSkippedFieldfalsebool
      notifyWhenSuccessfalsebool
      notifyWhenSuccessFieldfalsebool
      notifyWhenWarningfalsebool
      notifyWhenWarningFieldfalsebool
      PropertyChangednullSystem.ComponentModel.PropertyChangedEventHandler
      recurrenceExpression"0 8 */2 * * ? *"string
      recurrenceExpressionField"0 8 */2 * * ? *"string
      recurrenceExpressionType"cron"string
      recurrenceExpressionTypeField"cron"string
      repeatCount0int
      repeatCountField0int
      repeatInterval0int
      repeatIntervalField0int
      -reportRequest{FECU.OracleServices.ScheduleService.ReportRequest}FECU.OracleServices.ScheduleService.ReportRequest
      attributeCalendar"GREGORIAN"string
      attributeCalendarField"GREGORIAN"string
      attributeFormat"xml"string
      attributeFormatField"xml"string
      attributeLocale"en_US"string
      attributeLocaleField"en_US"string
      attributeTemplatenullstring
      attributeTemplateFieldnullstring
      attributeTimezone"America/Edmonton"string
      attributeTimezoneField"America/Edmonton"string
      byPassCachetruebool
      byPassCacheFieldtruebool
      dynamicDataSourcenullFECU.OracleServices.ScheduleService.BIPDataSource
      dynamicDataSourceFieldnullFECU.OracleServices.ScheduleService.BIPDataSource
      ExtensionDatanullSystem.Runtime.Serialization.ExtensionDataObject
      extensionDataFieldnullSystem.Runtime.Serialization.ExtensionDataObject
      flattenXMLfalsebool
      flattenXMLFieldfalsebool
      parameterNameValuesnullFECU.OracleServices.ScheduleService.ParamNameValues
      parameterNameValuesFieldnullFECU.OracleServices.ScheduleService.ParamNameValues
      PropertyChangednullSystem.ComponentModel.PropertyChangedEventHandler
      reportAbsolutePath"/Custom/PCL/Data Integrations/Reports/PCL_UEN_Validation_REP.xdo"string
      reportAbsolutePathField"/Custom/PCL/Data Integrations/Reports/PCL_UEN_Validation_REP.xdo"string
      reportDatanullbyte[]
      reportDataFieldnullbyte[]
      reportOutputPathnullstring
      reportOutputPathFieldnullstring
      reportRawDatanullstring
      reportRawDataFieldnullstring
      sizeOfDataChunkDownload-1int
      sizeOfDataChunkDownloadField-1int
      XDOPropertyListnullFECU.OracleServices.ScheduleService.MetaDataList
      XDOPropertyListFieldnullFECU.OracleServices.ScheduleService.MetaDataList
      -reportRequestField{FECU.OracleServices.ScheduleService.ReportRequest}FECU.OracleServices.ScheduleService.ReportRequest
      attributeCalendar"GREGORIAN"string
      attributeCalendarField"GREGORIAN"string
      attributeFormat"xml"string
      attributeFormatField"xml"string
      attributeLocale"en_US"string
      attributeLocaleField"en_US"string
      attributeTemplatenullstring
      attributeTemplateFieldnullstring
      attributeTimezone"America/Edmonton"string
      attributeTimezoneField"America/Edmonton"string
      byPassCachetruebool
      byPassCacheFieldtruebool
      dynamicDataSourcenullFECU.OracleServices.ScheduleService.BIPDataSource
      dynamicDataSourceFieldnullFECU.OracleServices.ScheduleService.BIPDataSource
      ExtensionDatanullSystem.Runtime.Serialization.ExtensionDataObject
      extensionDataFieldnullSystem.Runtime.Serialization.ExtensionDataObject
      flattenXMLfalsebool
      flattenXMLFieldfalsebool
      parameterNameValuesnullFECU.OracleServices.ScheduleService.ParamNameValues
      parameterNameValuesFieldnullFECU.OracleServices.ScheduleService.ParamNameValues
      PropertyChangednullSystem.ComponentModel.PropertyChangedEventHandler
      reportAbsolutePath"/Custom/PCL/Data Integrations/Reports/PCL_UEN_Validation_REP.xdo"string
      reportAbsolutePathField"/Custom/PCL/Data Integrations/Reports/PCL_UEN_Validation_REP.xdo"string
      reportDatanullbyte[]
      reportDataFieldnullbyte[]
      reportOutputPathnullstring
      reportOutputPathFieldnullstring
      reportRawDatanullstring
      reportRawDataFieldnullstring
      sizeOfDataChunkDownload-1int
      sizeOfDataChunkDownloadField-1int
      XDOPropertyListnullFECU.OracleServices.ScheduleService.MetaDataList
      XDOPropertyListFieldnullFECU.OracleServices.ScheduleService.MetaDataList
      saveDataOptionfalsebool
      saveDataOptionFieldfalsebool
      saveOutputOptionfalsebool
      saveOutputOptionFieldfalsebool
      scheduleBurstingOptionfalsebool
      scheduleBurstingOptionFieldfalsebool
      scheduleBurstringOptionfalsebool
      scheduleBurstringOptionFieldfalsebool
      schedulePublicOptionfalsebool
      schedulePublicOptionFieldfalsebool
      startDate"2015-04-29T090838"string
      startDateField"2015-04-29T090838"string
      userJobDesc"PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_090751 (Scheduled via the PCL BI Schedule Tool)"string
      userJobDescField"PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_090751 (Scheduled via the PCL BI Schedule Tool)"string
      userJobName"PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_090751"string
      userJobNameField"PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_090751"string
      useUTF8Optionfalsebool
      useUTF8OptionFieldfalsebool

       

       

       

      And when I get the job info with the resulting ID (100400), here's the result:

       

      -client.getScheduledJobInfo("100400",[REMOVED],[REMOVED]){FECU.OracleServices.ScheduleService.JobDetail}FECU.OracleServices.ScheduleService.JobDetail
      burstingfalsebool
      burstingFieldfalsebool
      burstingParametersnullstring
      burstingParametersFieldnullstring
      +created{4/29/2015 3:07:55 PM}System.DateTime?
      +createdField{4/29/2015 3:07:55 PM}System.DateTime?
      dataLocatornullstring
      dataLocatorFieldnullstring
      deletedfalsebool
      deletedFieldfalsebool
      deliveryDescriptionnullstring
      deliveryDescriptionFieldnullstring
      deliveryParameters"{CONTROL_XML=<ROWSET><ROW><KEY/><LOCALE>en_US</LOCALE><OUTPUT_FORMAT>xml</OUTPUT_FORMAT><TIMEZONE>America/Edmonton</TIMEZONE><CALENDAR>GREGORIAN</CALENDAR><SAVE_OUTPUT>false</SAVE_OUTPUT><OUTPUT_NAME>PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_0907510-Document-1</OUTPUT_NAME><DEL_CHANNEL>EMAIL</DEL_CHANNEL><PARAMETER1>trobinson@pcl.com</PARAMETER1><PARAMETER3>trobinson@pcl.com</PARAMETER3><PARAMETER4>Scheduled execution of PCL_UEN_Validation_REP</PARAMETER4><PARAMETER5></PARAMETER5></ROW><ROW><KEY/><LOCALE>en_US</LOCALE><OUTPUT_FORMAT>xml</OUTPUT_FORMAT><TIMEZONE>America/Edmonton</TIMEZONE><CALENDAR>GREGORIAN</CALENDAR><SAVE_OUTPUT>false</SAVE_OUTPUT><OUTPUT_NAME>PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_0907510-Document-1</OUTPUT_NAME><DEL_CHANNEL>FTP</DEL_CHANNEL><PARAMETER1>PostOfficeInbox</PARAMETER1><PARAMETER2>[REMOVED]</PARAMETER2><PARAMETER3>[REMOVED]</PARAMETER3><PARAMETER4>/E_1/ftp_inbox/PostOffice</PARAMETER4><PARAMETER5>sched_PCL_UEN_Validation_ %y%m%d_%H%M%S.xml</PARAMETER5><PARAMETER6>true</PARAMETER6></ROW></ROWSET>}"string
      deliveryParametersField"{CONTROL_XML=<ROWSET><ROW><KEY/><LOCALE>en_US</LOCALE><OUTPUT_FORMAT>xml</OUTPUT_FORMAT><TIMEZONE>America/Edmonton</TIMEZONE><CALENDAR>GREGORIAN</CALENDAR><SAVE_OUTPUT>false</SAVE_OUTPUT><OUTPUT_NAME>PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_0907510-Document-1</OUTPUT_NAME><DEL_CHANNEL>EMAIL</DEL_CHANNEL><PARAMETER1>trobinson@pcl.com</PARAMETER1><PARAMETER3>trobinson@pcl.com</PARAMETER3><PARAMETER4>Scheduled execution of PCL_UEN_Validation_REP</PARAMETER4><PARAMETER5></PARAMETER5></ROW><ROW><KEY/><LOCALE>en_US</LOCALE><OUTPUT_FORMAT>xml</OUTPUT_FORMAT><TIMEZONE>America/Edmonton</TIMEZONE><CALENDAR>GREGORIAN</CALENDAR><SAVE_OUTPUT>false</SAVE_OUTPUT><OUTPUT_NAME>PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_0907510-Document-1</OUTPUT_NAME><DEL_CHANNEL>FTP</DEL_CHANNEL><PARAMETER1>PostOfficeInbox</PARAMETER1><PARAMETER2>[REMOVED]</PARAMETER2><PARAMETER3>[REMOVED]</PARAMETER3><PARAMETER4>/E_1/ftp_inbox/PostOffice</PARAMETER4><PARAMETER5>sched_PCL_UEN_Validation_ %y%m%d_%H%M%S.xml</PARAMETER5><PARAMETER6>true</PARAMETER6></ROW></ROWSET>}"string
      endDatenullSystem.DateTime?
      endDateFieldnullSystem.DateTime?
      +ExtensionData{System.Runtime.Serialization.ExtensionDataObject}System.Runtime.Serialization.ExtensionDataObject
      +extensionDataField{System.Runtime.Serialization.ExtensionDataObject}System.Runtime.Serialization.ExtensionDataObject
      instanceId0int
      instanceIdField0int
      issuer"[USERNAME HERE, REMOVED]"string
      issuerField"[USERNAME HERE, REMOVED]"string
      jobGroup"[USERNAME HERE, REMOVED]"string
      jobGroupField"[USERNAME HERE, REMOVED]"string
      jobId100400int
      jobIdField100400int
      jobSetId-1int
      jobSetIdField-1int
      jobType"Submit"string
      jobTypeField"Submit"string
      +lastUpdated{4/29/2015 3:07:55 PM}System.DateTime?
      +lastUpdatedField{4/29/2015 3:07:55 PM}System.DateTime?
      localenullstring
      localeFieldnullstring
      notificationParametersnullstring
      notificationParametersFieldnullstring
      owner"[USERNAME HERE, REMOVED]"string
      ownerField"[USERNAME HERE, REMOVED]"string
      parentJobId-1int
      parentJobIdField-1int
      PropertyChangednullSystem.ComponentModel.PropertyChangedEventHandler
      publicfalsebool
      publicFieldfalsebool
      reportParametersnullstring
      reportParametersFieldnullstring
      reportUrl"/Custom/PCL/Data Integrations/Reports/PCL_UEN_Validation_REP.xdo"string
      reportUrlField"/Custom/PCL/Data Integrations/Reports/PCL_UEN_Validation_REP.xdo"string
      runType"Run Recursively"string
      runTypeField"Run Recursively"string
      scheduleContextnullstring
      scheduleContextFieldnullstring
      scheduleDescription"recurrence"string
      scheduleDescriptionField"recurrence"string
      scheduleParameters"{RECURRENCE_EXPRESSION=0 8 */2 * * ? *}"string
      scheduleParametersField"{RECURRENCE_EXPRESSION=0 8 */2 * * ? *}"string
      scheduleSourcenullstring
      scheduleSourceFieldnullstring
      startDatenullSystem.DateTime?
      startDateFieldnullSystem.DateTime?
      status"Scheduled"string
      statusDetailnullstring
      statusDetailFieldnullstring
      statusField"Scheduled"string
      storageTypenullstring
      storageTypeFieldnullstring
      userDescription"PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_090751 (Scheduled via the PCL BI Schedule Tool)"string
      userDescriptionField"PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_090751 (Scheduled via the PCL BI Schedule Tool)"string
      userJobName"PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_090751"string
      userJobNameField"PCL_UEN_Validation_REP_SCHEDULED_2015_04_29_090751"string
      xmlDataAvailablefalsebool
      xmlDataAvailableFieldfalsebool
      xmlDataCompressedfalsebool
      xmlDataCompressedFieldfalsebool
      xmlDataContentType"text/xml"string
      xmlDataContentTypeField"text/xml"string
      xschurlnullstring
      xschurlFieldnullstring

       

      Is there something noticeably wrong with my cron expression or some other reason why this job makes it in but never happens? Off the bat, I notice that startDate and endDate are null in job returned, but as you can see, they aren't null in the request.

       

      To reiterate, the above works fine if I don't specify recurrence. It's when I do that, though I still get a JobID back, nothing seems to schedule.

       

      When I examine a job that was the result of scheduling via the UI, it looks like the "scheduleParameters" field doesn't contain cron/recurrence info, and there are start and end dates, so this leads me to believe that the service is interpreting these two things entirely differently.

       

      Is there's something wrong with my cron expression?

       

      Thanks,

      Tor