on 06-17-2016 4:12 PM
Hello Everyone,
We have a requirement where in the To address needs to be updated in report schedule (success or failure) delivery notifications being sent to the end users. There are n number of reports and opening each and every report to check if that report has this email address that needs a change would be hectic.
So would like to know if there is anyway to get the report names which are scheduled and having this notification with that particular email address in To list.
This would make the task easy to have the reports scheduled notifications updated. Any pointers would be of great help!!
Environment in use: BI 4.1 SP 5
Thanks,
Satya
Unfortunately, there is no easy way to do this.
You can use QueryBuilder to get the information about schedules using this query:
Select SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_DESTINATIONS from CI_INFOOBJECTS where SI_INSTANCE = 1 and SI_SCHEDULE_STATUS = 9 and SI_SCHEDULEINFO.SI_DESTINATIONS.SI_TOTAL > 0
You would then have to go through the list to find the ones that are going to email and have the email address you're looking for. I suggest either just using "Find" in your browser to find the specific email address or exporting the result set to a file and searching there. Note that the default result size for queries is 1000 objects - if you have more than 1000 schedules, you'll want to do something like "Select Top 5000...." using a number that is greater than the number of schedules that are in your system.
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thank you Christy for promptly replying and Yes am trying to figure out using query builder only.
I know I need to query folder by folder and then result this into some excel and then filter out. But then I would like to know how to get the list of scheduled reports only which has notifications set and not all those reports which are just scheduled.
If I can pull only those which have notifications set I can use excel to do my stuff. I am attaching this screen shot and I would like to know how to get this COLUMN SI_MAIL_ADDRESSES.
Cannot we get a column this depth like below:
select SI_NAME, SI_SCHEDULEINFO.SI_NOTIFICATION from ci_infoobjects where SI_PARENTID=XXX AND SI_KIND='CRYSTALREPORT' AND SI_CHILDREN>0 AND SI_SCHEDULEINFO.SI_NOTIFICATION.SI_DESTINATION_FAILURE.SI_DEST_SCHEDULEOPTIONS.SI_MAIL_ADDRESSES='XX...
It doesn't give me any results...how to query against values this deep...?
Thanks,
Satya
That's what makes this such a challenge - you can't query against objects that deep. Every time I've tried, I get an "invalid query" error.
There is a tool available that will export QueryBuilder queries to Excel. You can download it here: InfoStore Query Builder (with export to Excel)
I haven't used it much, but you could run your query there and then possibly manipulate the Excel to get the info you need.
-Dell
Thank you Christy for helping me on this.
The below query would give the list of all the reports which have notifications set :
select SI_NAME, SI_SCHEDULEINFO.SI_NOTIFICATION FROM CI_INFOOBJECTS WHERE SI_ANCESTOR=23 AND SI_KIND IN ('WEBI', 'CRYSTALREPORT') AND SI_SCHEDULEINFO.SI_NOTIFICATION!=' '
Thanks,
Satya
User | Count |
---|---|
76 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.