Search code examples
crystal-reportsbusiness-objects

Remove employee who left from scheduled reports

I need to find all reports that have a schedule with a specific recipient on it, then remove that person. How can I do this programmatically, rather than manually doing it 300+ times.

How can we search schedules by email recipient? Apart from asking the person to forward us the reports so we can try and figure out which ones they are.

Solution

  • Alas, there's no easy way to do this.

    To find the list of schedules, you can use the QueryBuilder (available through http[s]://[your BOBJ server]/AdminTools). This web application allows you to query the CMS repository database with limited, SQL-like queries (e.g. no joins, …).

    For example, the following query will give you all scheduled (si_schedule_status = 9) publications (si_kind = 'publication'):

    select *
  from ci_infoobjects
 where si_schedule_status = 9
   and si_kind = 'publication'

    Here's what the result could look like (the output is a formatted HTML with nested structures represented as nested tables):

    Example output

    Alternatively, you could use a free tool such as CMS Query Builder by biclever which is a little easier to use and offers export to Excel functionality.

    CMS Query Builder

    I would recommend that you start with one object (schedule) which contains the data you need, then look at the fields that contain said data and try to construct a query that way. It's important to know that you cannot query nested data, so you won't be able to extract just the schedules where the given e-mail address appears (as it'll be a few levels down).

    If you have sufficient programming experience, and depending on the version of BusinessObjects you're using, you could resort to the Java or REST SDK, although I'm not sure if all the necessary functionality is available in the latter.

    With both SDKs, your starting point will again be a CMS query to retrieve the desired objects before modifying them. The documentation for the SDKs is available through the SAP Help Portal; look for the Development section. You'll need to look at the Business Intelligence Platform Java SDK Developer Guide and SAP Business Intelligence Platform Java API Reference.