BusinessObjects Board

Using query builder to locate destination email addresses.

scheduling XI Scheduler
#1

I am able to use query builder to view report detail data for a particular report or group of reports.

I’m wondering if it is possible to use query builder to show a list of reports that have a particular email address as the destination. For example, an employee has left the company and I would like to remove this person’s email address from all reports that are scheduled. The problem is that there are many reports and I’m not sure which ones contain the email address. I’m hoping query builder can help.

Any suggestions?

I tried the following code however nothing was returned…even though the email address is indeed listed in the To: field on several scheduled reports.

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_MAIL_ADDRESSES = ‘somebody@somewhere.com’

pete171 (BOB member since 2008-09-22)

#2

You can’t use SI_MAIL_ADDRESSES in the where clause because it is a nested property. The best you can do using strictly Query Builder is to pull back the SI_DESTINATIONS field:


SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_DESTINATIONS FROM CI_INFOOBJECTS WHERE SI_RUNNABLE_OBJECT = 1

The “runnable_object” piece returns all pending/recurring scheduled jobs. Using some Excel magic, you could format the output in such a way as to allow for filtering. The alternative is to use the SDK.

BoB LoblaW :us: (BOB member since 2007-10-23)

#3

Pete, if you are still around, what worked for you? I have the same issue.

I know this is way old, but found it while searching for the same info.

Thanks anyone!

Gary_W (BOB member since 2013-08-16)

Query builder Query required
#4

Hi Pete

The solution provided by BoB will resolve your issue. Once you execute the query, do ctrl+F to search for the employee email id and you should be all set.

I hope you have already figured it out but i just wanted to answer that it would be helpful for the forum members who would be looking for same solution.

Thanks BOB, your query is useful.

Thanks
Arun !!

passport (BOB member since 2010-05-07)

#5

Hi,

I can confirm that Bob’s expression is working for me in SAP BO 3.1 but it would also be fine if I could add 1 filter that gives me only the recurring scheduling sessions.

What I try to achieve is a simple way of removing emailadresses that are no longer valid. 8) :lol:

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

#6

Replace “SI_RUNNABLE_OBJECT = 1” with:

si_recurring = 1 and si_schedule_status = 9

joepeters :us: (BOB member since 2002-08-29)

#7

Thanks, a bit late reaction but it works! I think this the last time I work with BOXi 3.1 :wink:

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

#8

Below is the query I have used using the BI Clever CMS Query Builder
( CMS Query Builder – biclever)

SELECT TOP 100000 SI_ID,SI_KIND, SI_NEXTRUNTIME, SI_SIZE, SI_SPECIFIC_KIND, SI_STARTTIME, SI_SUBMITTER, SI_OWNER, SI_NAME, SI_SCHEDULEINFO.SI_DESTINATIONS
FROM CI_INFOOBJECTS WHERE SI_RUNNABLE_OBJECT = 1