Hi All,

This is the continuation of my previous blog related to Business Objects Query Builder.

We are going to see some of the Query builder queries that I used real time in my experience.

1. To get  all the Web Intelligence documents created by user named ’Mani’

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ and SI_AUTHOR=’Mani’

2. To return  list of Web Intelligence documents that have a name starting with  “Annual” and are scheduled

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ AND SI_NAME LIKE ‘Annual%’ AND SI_RUNNABLE_OBJECT=1

3. To extract  list of Web Intelligence documents that are scheduled in a specified period of time

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’  and SI_RUNNABLE_OBJECT=1 and

SI_NEXTRUNTIME between ‘2010.07.08.09’ and ‘2010.07.08.11’

4. To return all report folders containing a string

SELECT * FROM CI_INFOOBJECTS WHERE SI_NAME LIKE ‘%Service%’ AND SI_KIND=’Folder’

5. To returns all Universe folders containing a string

SELECT * FROM CI_APPOBJECTS WHERE SI_NAME LIKE ‘%Sales%’ AND SI_KIND=’Folder’

6. To see what type of rights you have for your BO software

SELECT SI_NAME from CI_SYSTEMOBJECTS where SI_NAMEDUSER=0 AND SI_KIND=’User’

7. To find all crystal and webi reports – not instances

Select si_id, si_name from ci_infoobjects where (si_kind = ‘CrystalReport’ or si_kind = ‘Webi’) and si_instance = 0 and si_children = 0

  • To find all crystal reports – not instances or shortcuts

select si_id, SI_NAME,   si_owner,  SI_PARENT_FOLDER,  si_children, SI_PROCESSINFO.SI_FILES,  SI_PROCESSINFO.SI_LOGON_INFO, SI_PROCESSINFO.SI_RECORD_FORMULA from CI_INFOOBJECTS where (si_kind = ‘CrystalReport’) and si_instance = 0 and not si_name like ‘Shortcut to%’

  • To find all the failed instances

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME>=2 and SI_SCHEDULEINFO.SI_STARTTIME>=’2011.01.01′

  • To find successful instances

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO, SI_SCHEDULEINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME<2 and SI_SCHEDULEINFO.SI_STARTTIME>=’2011.01.01′

  • To find successful instances of a particular report after a specific date

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO, SI_SCHEDULEINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME<2 and SI_SCHEDULEINFO.SI_STARTTIME>=’2011.01.01′ and SI_NAME = ‘Test.rpt’

  • To find scheduled instances for a specific time range

select SI_NAME, SI_SCHEDULEINFO.SI_submitter, SI_SCHEDULEINFO.SI_STARTTIME from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_STARTTIME>=’2011.01.01.16.00.00′ and SI_SCHEDULEINFO.SI_STARTTIME<’2011.01.02.13.00.00′ order by SI_SCHEDULEINFO.SI_STARTTIME

  • To find successfully scheduled reports (not instances) scheduled after a certain date

select si_id, SI_NAME, si_owner, SI_PARENT_FOLDER, si_children, SI_PROCESSINFO.SI_FILES, SI_PROCESSINFO.SI_LOGON_INFO, SI_PROCESSINFO.SI_RECORD_FORMULA,  SI_SCHEDULEINFO.SI_STARTTIME, SI_SCHEDULEINFO.SI_SUBMITTER,  SI_SCHEDULEINFO.SI_DESTINATION, SI_SCHEDULEINFO.SI_UISTATUS from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME = 1 and si_instance = 0 and SI_SCHEDULEINFO.SI_STARTTIME>=’2008.11.01′

  • To find recurring instances

select si_id, SI_NAME, si_owner, SI_PARENT_FOLDER, si_children, si_recurring, SI_PROCESSINFO.SI_FILES, SI_PROCESSINFO.SI_LOGON_INFO, SI_PROCESSINFO.SI_RECORD_FORMULA, SI_SCHEDULEINFO.SI_STARTTIME, SI_SCHEDULEINFO.SI_SUBMITTER, SI_SCHEDULEINFO.SI_DESTINATION, SI_SCHEDULEINFO.SI_UISTATUS from CI_INFOOBJECTS where not si_name like ‘Shortcut to%’ and si_recurring=1 and SI_SCHEDULEINFO.SI_STARTTIME>=’2008.11.01′

  • To find users who have logged in since a specified date or whose userid was created after a specified date, but may not have logged in

select si_name, SI_CREATION_TIME, si_lastlogontime from ci_systemobjects where si_kind = ‘user’ and (si_lastlogontime > ’2008.11.01.04.59.59′ or SI_CREATION_TIME > ’2009.04.01.04.59.59′ )

  • To find reports that have not been scheduled

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER from CI_INFOOBJECTS where (si_kind = ‘CrystalReport’ or si_kind = ‘Webi’) and si_instance = 0 and si_children = 0 and SI_SCHEDULEINFO.SI_SCHED_NOW = 0

  • To find users are all logged in to Business Objects at a given Point of time

SELECT TOP 1000 * FROM CI_SystemObjects WHERE si_kind = 'Connection' AND si_parent_folder = 41 AND si_authen_method != 'server-token' ORDER BY si_name

  • To get list of Crystal reports by data connection from BO Enterprise

SELECT SI_NAME FROM CI_APPOBJECTS WHERE SI_KIND=’MetaData.DataConnection’

  • To find universe used by the report

SELECT SI_ID, SI_NAME, SI_WEBI , SI_OWNER
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
Where PARENTS(“SI_NAME=’Webi-Universe'”,”SI_NAME =’Your Universe Name'”)

  • To get all recurring reports from Specific folder

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ’3711′ and

SI_recurring = 1

  • To get all recurring reports from Specific folder NOT Paused

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ’3711′ and

SI_recurring = 1 and SI_SCHEDULEINFO.SI_SCHEDULE_FLAGS = ’0′

  • To get all recurring reports from Specific Folder, All Recurring PAUSED:

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ’3711′ and

SI_recurring = 1 and SI_SCHEDULEINFO.SI_SCHEDULE_FLAGS = ’1′

  • To get list of users who is logged in to your Business Objects XI at a given Point of time

SELECT TOP 3000 * FROM CI_SystemObjects WHERE si_kind = 'Connection' AND si_parent_folder = 41 AND si_authen_method != 'server-token'

ORDER BY si_name

  • To get Get All Webi reports from the repository

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Webi’ And SI_INSTANCE=0

  • To get Full Client Reports from the repository

SELECT SI_ID, SI_NAME,SI_FILES FROM CI_INFOOBJECTS WHERE SI_KIND in( ‘webi’ ,’FullClient’)

  1. To get all reports from the repository
    Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Report’ And SI_INSTANCE=0
  2. To get all universes from the repository
    Select SI_ID, SI_NAME, SI_WEBI, SI_KIND From CI_APPOBJECTS where SI_KIND =’Universe’
  3. To get all Users from the repository
    SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID=’CrystalEnterprise.USER’
  4. To get all groups from the repository
    Select * from CI_SYSTEMOBJECTS Where SI_KIND=’UserGroup’
  5. To get all folders from the repository
    Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Folder’
  6. To get all categories from the repository
    SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND=’Category’
  7. To get all personal categories from the repository
    Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND=’PersonalCategory’

Hope all these could be useful to you when it comes in to handy. In the forthcoming post, I will discuss on Business Objects file repository servers in detail.

Posted by Manikandan Elumalai
Comments (51)
February 28th, 2011

Comments (51)

Manikandan Elumalai - July 1st, 2011

Hi Vijay, You cant get those information from QB as the internals of the reports (Objects,Tables) used are not stored in CMS.You need .wid(WebI) or .rep(Crystal) to reference them and can be done only by BO SDK.This is the reason why we are importing the reports from the repository first and Opening.

Manikandan Elumalai - July 1st, 2011

Hi Lavanya, This can be achieved only by SDK code. start digging in to that

Vijay - July 1st, 2011

Hi Mani, Is it Possible to identify reports (Webi or Crystal) Which are using specfic Dimension or Detailed object Thanks Vijay

Lavanya - June 24th, 2011

Hi Mani, Thx for the reply. Is there any way that I can extract the instances information which has the SI_destinations as attachments. We actually send out all our reports via email with either a hyper link or attachments to the users. I understand that we have SI_SMTP_ENABLEATTACHMENTS as true for attachments. But I would like to know if there is any way to extract only the said column for the SI_Instance=1 in the repository. I have around 100,000 instances and to retrieve the SI_Destinations for all these instances is taking very long time in Query Builder. I would appreciate if you can provide me some idea. Thanks, Lavanya

Manikandan Elumalai - June 22nd, 2011

Hi Yaso, You can't get the Information like Tables, Columns involved in the reports from the CMS database as these are all stored in .rep or .wid files. You need to import them from the repository and need to process them to extract the table and column information. You can use the SDK code to extract these details.

yaso - June 22nd, 2011

This is very informational. In addition to listing the reports, can query builder also provide the list of columns in the report?

Manikandan Elumalai - June 17th, 2011

Lavanya, Try like this. SELECT top 10000 SI_ID,SI_NAME,SI_INSTANCE,SI_CREATION_TIME, SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_CREATION_TIME < '2011-01-01' and SI_INSTANCE= 1 order by SI_INSTANCE SI_INSTANCE= 1 will extracts only the Instances not the reports. SI_CREATION_TIME will give you the instance creation date.

Lavanya - June 17th, 2011

Hi Mani, Thx for the reply. Is there any way that I can get the start time and run time of the instances? I checked the SI_UPDATE_TS, SI_CREATION_TIME, these are wrt the report creation time and modified time. But I am looking at the instance level. Is there any way that I can get this information? We are actually moving from r2 to 3.1 and we do not want to migrate the older instances beyond 3 years. So that's the reason why we are checking thru the query builder as to how many instances does each report have per folder. Thanks, Lavanya

Manikandan Elumalai - June 16th, 2011

Lavanya, Query Builder output will be displayed within a predefined format and you can’t expect it as you require. For your requirement (Folder -> SF -> Rep -> Instances -> Recurrence pattern) the first 4 columns can be queried within a single virtual table (i.e.) CI_INFOOBJECTS and are related with each other by parent/ child relationship like our Employee/Department. The recurrence pattern can be extracted from Scheduling Info of each instance. You have to copy QB output in to a spreadsheet and do the manipulations over there to get desired output. But this is a bit complex process. Obviously BO SDK will be your final destination to get the results as you require. Some useful Info for you: You can match the SI_ID of the folder with the SI_PARENTID of folder/report to get the children. You can identify the Instances of the reports by matching the SI_ID of the reports with SI_PARENTID of instances. SI_INSTANCE= false for the reports where as SI_INSTANCE true for instances.

lavanya - June 15th, 2011

Hi Mani, I was reading your blog and I find it more useful than I thought. I have a a question. 1. I want to find the no of instances of each report under each folder and also the recurrence at which the instances were created. For eg: Under Public Folder, I have Folder 1, Folder 2. Inside the Folder 1, there are 2 more subfolders, namely, SF1 and SF2. Now inside the SF1 I have 10 reports and each report has 100 instances, with rep1 having a daily recurrence and rep 2 having a weekly recurrence and rep3 having a monthly recurrence. Now I need a query such that my output is as shown below Folder 1 SF 1 rep1 Instances(100) Recurrence(daily) Folder 1 SF 1 rep2 100 Weekly Folder 1 SF 1 rep3 100 Monthly Folder 2 SF 2 rep4 100 Recurrence(daily) Folder 2 SF 2 rep5 100 Weekly Folder 2 SF 2 rep6 100 Monthly I want to check this for the past 3 years. Hope I am able to putforth my question properly. Thanks Lavanya

Manikandan Elumalai - June 14th, 2011

Erol, Seems you are so enthusiastic. Thats so good to understand pros and cons of the BO Query Builder. Am sorry in Query Builder you can’t write a range based query as you require. Instead of going for the mouse scroll, Better you can do the select All (Ctrl+A), our traditional way.If this approach has impact in your CR Web Server, you can split the query in to two, One to select the LOGON INFO and the other one to select PROMPT information. Then do the lookup in excel. Even if you haven't easy going with the above, extract all the reports first. keep it in Excel and group them based on first character of their name, then do a pattern based matching where count do not exceed 1000 for each range. but this requires more manual work. Meanwhile let me check for some more options if any. Cheers, Mani

Erol D - June 14th, 2011

Hello Manikandan, Thank you very much for your feedback! It is most appreciated! My last question would be. I'm using this query to get pertinent info from BOXIR2, and can't query sublayers. I'm trying to get logon info and prompting info so i can import into excel and remove what i don't need. Here's my query: SELECT TOP 8000 SI_ID, SI_NAME, SI_PARENTID, SI_PROCESSINFO.SI_LOGON_INFO, SI_PROCESSINFO.SI_PROMPTS FROM CI_INFOOBJECTS WHERE SI_INSTANCE = 'False' AND SI_KIND = 'Crystal Report' The reason I use TOP 8000 is because we have 7800 reports (not instances) that need this query to break down. This output gives me 7800 results and is massively large in internet explorer. WHen i go to copy it, it crashes the Crystal Web Sever and we have to reboot it. Can i query a range say 0-1000, then 1001-2000, 2001-3000 and so on? i have good luck with 1,000, but there's no 'next page' after 1,000 and i don't know how to get inbetween results. I can't simply scroll with the mouse and copy paste because it takes a severly long time. Can you assist?

Manikandan Elumalai - June 10th, 2011

Ram, Follow the below steps. Open the CCM and check the status of all the servers, especially Web Intelligence Processing Server. Make sure that Servers are up and running. Do the same check in CMC as well. If the problem persists still, then you need to work with windows registry key as it might have changed during subsequent BO Installation Go to Start --> Run and type regedit. Go to the below path HKEY_LOCAL_MACHINE\Software\Business Objects\Suite 11.5\Enterprise\InstallRoot the Pathkey value should point the BOE Installation directory. Hopefully this will solve your problem!

Ramchand G - June 10th, 2011

While accessing reports inside BO i get this error in random times . Any idea what could be the issue . Cannot initialize Report Engine server. (Error: RWI 00226)> com.businessobjects.rebean.wi.CommunicationException: Cannot initialize Report Engine server. (Error: RWI 00226)

Manikandan Elumalai - June 9th, 2011

Erol, Thanks for reading my blog. For your Query 1, Select SI_ID,SI_NAME, SI_PROCESSINFO.SI_LOGON_INFO From CI_INFOOBJECTS Where SI_KIND='CrystalReport' and SI_INSTANCE=0 For your Query 2, SELECT SI_ID,SI_NAME, SI_SCHEDULEINFO.SI_DESTINATIONS,SI_SCHEDULEINFO.SI_SUBMITTER, SI_SCHEDULEINFO.SI_STARTTIME, SI_SCHEDULEINFO.SI_ENDTIME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO. SI_SCHEDULE_STATUS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MONTHS, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_HOURS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MINUTES FROM CI_INFOOBJECTS WHERE SI_KIND= 'CrystalReport' and SI_SCHEDULE_STATUS !=8 and SI_RECURRING = 1 For interpreting values for SI_SCHEDULE_TYPE, I_SCHEDULE_STATUS ,etc.., please read my responses for queries raised by other Blog readers. Feel free to get back if you need further clarification.

Erol D - June 9th, 2011

Hello Manikandan, I hope you can answer my question. We have BOXIR2 SP4 11.5. I have two query questions. 1. Our Report Datasources link in AdminLaunch times out: Is it possible to query without SDK to get list of All Crystal Reports showing what DataSource they use? (e.g. in CMC, when you click a report, go to Process, then Database, the Database Logon info is there. We would like all Crystal Reports that show what Server is being used). I saw you had a query up above, (SELECT SI_NAME FROM CI_APPOBJECTS WHERE SI_KIND=’MetaData.DataConnection’) but that only seems to return the unique DataConnections being used. 2. Is it possible to query without SDK to get list of all Crystal Reports and their defined schedules?

Ramchand G - June 9th, 2011

Thanks Mani

Manikandan Elumalai - June 8th, 2011

This is your Query Select * from ci_infoobjects where si_parentid=23 and si_name!='Report Conversion Tool' and si_name!= 'Administration Tools' and si_name!= 'Auditor' even if you dont want anymore folder you can add them in the and list. Cheers!! Mani

Ramchand G - June 8th, 2011

@ Mani , can you please tell the query to get the folders without any Administration Tools,Auditor and Report Conversion Tool folders

Manikandan Elumalai - June 3rd, 2011

Hi Ram, Try this, Select * from ci_infoobjects where si_parentid=23 Please note the above selection also includes Administration Tools,Auditor and Report Conversion Tool folders and excludes Inbox and Favorites.

Ramchand G - June 3rd, 2011

Hi Mani, Can you please tell how to get the all the public folders(Non System Folders) from Business Objects ?

Burton - May 9th, 2011

Hi Manikandan, I am have a frequent error in SAP BO XI 3.1, prompt issue. When I run prompt report, the prompt is not displaying in Infoview. Its not loading it from InfoApp folder... Do you have any idea about the issue. It would be much helpful. Thanks, Burton

Manikandan Elumalai - April 28th, 2011

Hi BOUSER, Your best option would be use the below query in QB to get all the reports and paste it in XL/WORD document do the comparison in XL after data manipulation. SELECT SI_FILES, SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND = 'CrystalReport' and SI_INSTANCE = 0 Your required info. is stored in the SI_VALUE1 field of the output and you can't use the SI_FILEx and SI_PATH in Where clause as they are in the nested infoobject.

Manikandan Elumalai - April 28th, 2011

Alex, The internals of a universe such as its Parameters,objects are not stored in CMS database ,so you cant get those from QB. to do this you need to reference .unv file and this can be done only by Designer SDK.Please note you are accessing .unv file of the universe in Designer.

BOUSEr - April 27th, 2011

How can we can only SI_FILE(file name) and SI_VALUE1(size of the file). our requirement is to get largest file stored in crystal server.. i tried "Select SI_FILES.SI_VALUE1 FROM CI_INFOOBJECTS" but no result.

Alex - April 25th, 2011

Unfortunately SI_OWNER shows the owner, most of the time Administrator, but not the info about who created the universe. Created by info available in Designer when we check universe properties and it shows who developed the universe, even after you export to enterprise, but i was wonder how to query this info via Query Builder. Thank you, Alex

Manikandan Elumalai - April 25th, 2011

SI_OWNER Property will give you the Creator of the Universe. By Default user who created the Universe will be the Owner of the Universe as well. select * from ci_appobjects where si_kind='Universe'

Alex - April 22nd, 2011

Is it a way to retrive the creater of the Universe via Query Builder? Thanks, Alex

Manikandan Elumalai - April 22nd, 2011

Dulce, Below query will give you the required result. SELECT * FROM CI_INFOOBJECTS WHERE SI_RUNNABLE_OBJECT = 1 and si_kind in ('WebI','CrystalReport') AND SI_NEXTRUNTIME BETWEEN '2011.04.26.00.00' AND '2011.04.27.00.00' Gives the schedules run between 26/04/2011 to 27/04/2011. Try to remove si_kind in where condition as you are not going to schedule reports as it is(i.e.) as a Crystal or WebI reports. SELECT top 10000 SI_ID, SI_NAME,SI_KIND,SI_SCHEDULEINFO.SI_SCHEDULE_TYPE, SI_SCHEDULEINFO.SI_SUBMITTER,SI_SCHEDULEINFO.SI_STARTTIME,SI_STARTTIME, SI_ENDTIME,SI_NEXTRUNTIME,SI_SCHEDULE_STATUS,SI_STATUSINFO FROM CI_INFOOBJECTS WHERE SI_SCHEDULE_STATUS = 9 and SI_NEXTRUNTIME BETWEEN '2011.04..26.00.00' AND '2011.04.26.23.00' In the resultset values for SI_SUBMITTER gives the User Id. values for SI_SCHEDULE_TYPE gives the Schedule frequency. SI_SCHEDULE_TYPE=0 - Once: the report is run once only SI_SCHEDULE_TYPE=1 - Hourly: the report is run hourly. SI_SCHEDULE_TYPE=2 - Daily: the report is run daily. SI_SCHEDULE_TYPE=3 - Weekly: the report is run weekly. SI_SCHEDULE_TYPE=4 - Monthly: the report is run monthly. SI_SCHEDULE_TYPE=5 - NthDay: the report is run on the nth day of the month. SI_SCHEDULE_TYPE=6 - 1stMonday: the report will run on the first monday of every month. SI_SCHEDULE_TYPE=7 - LastDay: the report will run on the last day of the month. SI_SCHEDULE_TYPE=8 - Calendar: the report will run based on calendar templates. SI_SCHEDULE_TYPE=9 - Run schedule based on calendar plugin object templates. values for SI_SCHEDULE_STATUS gives the current status of the schedule SI_SCHEDULE_STATUS=0 - Running SI_SCHEDULE_STATUS=1 - Success SI_SCHEDULE_STATUS=3 - Failure SI_SCHEDULE_STATUS=8 - Paused SI_SCHEDULE_STATUS=9 - Pending Dont consider SI_NEXTRUNTIME as your required schedule date as this specifies the subsequent Schedule time. Hope this helps!!

Dulce Bautista - April 21st, 2011

Oh, and include the user id, report title, and the frequency selected (daily, weekly, monthly, etc).

Dulce - April 21st, 2011

Can you please provide me a query of all recurring Crystal Reports that will be run on a particular future date? And for WebI?

Manikandan Elumalai - April 19th, 2011

Both Angela and Nathan, You cannot extract UserGroup and Users one at a shot using Query Builder. You have to go for SDK to achieve this. If you are ready to bit of Manual work you can do this. Execute the Below Queries in QB. select SI_ID, SI_NAME from ci_systemobjects where si_kind= 'User' This will list all the Users with their si_id select SI_ID, SI_NAME from ci_systemobjects where si_kind= 'Usergroup' This will list all the usergroups with their associated member users. Paste the above two results into separate spreadsheets and do the vlookup. This will give you the desired result.

Nathan - April 19th, 2011

Hi, Could you please advise how we can get the list of users and their user group? Is it also possible to find the list of users and the universes to which they have access to.

Angela - April 19th, 2011

Any idea how I would get a list of users who are in two groups? Not one group or the other but in both?!

Ravi - April 14th, 2011

how do we get the list of reports which can be accessed by specific user?

Manikandan Elumalai - April 4th, 2011

Ramesh, Objects are no where related to users. Instead they are related to the reports as the report columns.you need to look for the reports which includes the required objects. You need to get confirmation from all these report consumers, then only you can delete the required object from the universe. Hope this helps!

ramesh - April 4th, 2011

how do we get the list of user using a particular object in the universe? Eg: Universe name island Object name : Location i have 1000 users using in the Repository only 500 users are using this Universe in that i want to delete this Location Object from Universe so i want to send a notification to all of them so how can i find which all users are using this particular object.

Manikandan Elumalai - March 28th, 2011

Joe, You must have to go for SDK to achieve your requirement as it needs multiple levels of query. Hope you are planning to utilize the Custom Access levels in XI3.1 to reduce the maintenance overhead. The task you assigned with needs more analysis on existing security structure then transforms it to map the new advance rights. All the advanced rights need to be mapped to its corresponding General/application/content/System level rights. Following Steps will help you to achieve your requirement. Use a spreadsheet to capture all your findings. • Identify and list Potential User Groups/Users • Identify Folders • Identify Rights on Folders for each user Group • Compare the rights • Transform the Advanced rights in to custom Access Levels • Assign the Custom Access Levels at folder level instead of advanced rights Feel free to get back to me if you need further clarification.

Joe Van Norman - March 28th, 2011

We are in the process of migrating from XIr2 to XI 3.1 and I have been tasked with reviewing the 140+ user groups to determine where we have overlapping security settings. What I'm looking for, ideally, is a result that lists the rights a usergroup has on a particular folder.

Manikandan Elumalai - March 28th, 2011

Joe, Can you come with Your BO version and the exact requirement? Because in Classical BO the Security revolves around the user where as in case of XI Series it is Folder based. (i.e.) the Security is applied on the folder specific to the user group contrary to rights assignment on Individual User/Group in Legacy BO.

Manikandan Elumalai - March 28th, 2011

Benny, Are you maintaining CMS and FRS Backup regularly? If So you can restore CMS and FRS Backup in a different test server. Once this is done you restore specific users of interest from the test server. You cant proceed with Parent child relation because once user is deleted all his inbox,personal documents also be deleted. user based hierarchy will also broken. even if you update the same user with Active directory updation in Windows AD plug-in the created user's CUID will not match his old CUID.

Joe Van Norman - March 25th, 2011

Is it possible to report on the security rights of a group via the query builder? Thanks, Joe Van Norman

Benny Gene De Vera - March 25th, 2011

Mani, I've already explored IW route first, the problem is we are using WinAD Auth and with that you can not pick and choose users to restore. They need to have Ent account. This is my next test if backed up WinAD objects will be available upon restore after creating an Ent alias. I was hoping I can get help with the relation function PARENTS or CHILDREN on how tie up Folder/Webi/FavoritesFolder/User Folder, if I can get this I think I will be set. Thanks! Benny

Manikandan Elumalai - March 25th, 2011

Benny, You can able to restore the user along with his personal documents using Import wizard if you have a proper BIAR Backup. In Import wizard if you want to restore only users who are all missing then you need to select appropriate option like merge and update. If the above solution doesn't suits please come up with a complete problem description with screens so that I can check it in my end. Regards, Mani

Benny Gene De Vera - March 24th, 2011

Hi! Thank you very much for this solution. Another thing spwan out from this one. First, please bear with me as I breakdown why I want to use this feature. Recently some of our users, for some reason, are being deleted in our system and we still don't know the reason why. My goal is to backup all objects from favorites folder or personal folder of all users. And should be able to restore any particular user that was missing. I did try the above code, on the WID level it worked fine but when it comes to the folder level the WIDs wont restore most likely because when the folder (~webIntelligence) was recreated it had a new CUID which could not be recognized by the WIDs as parent based on "maybe" SI_PARENT_ID. Any suggestion would be muchly appreciated. Thanks in advanced! Benny

Manikandan Elumalai - March 24th, 2011

Hi Benny, You can use the below query. select top 50 si_id, si_name, si_owner, si_kind from ci_infoobjects where si_kind = ‘webi’ and si_instance = 0 and si_ancestor = 18 – > si_ancestor = 18 refers to the Top Root folder of the User Folders. If your BO environment is very large and each user is having more favourites then the above query will perform poor as si_ancestor is not an Indexed column. In such case you need use the query with column si_owner in where clause to get the Favourites of the Individual User like the below. select top 100 si_id, si_name, si_owner, si_kind from ci_infoobjects where si_owner=’Mani and ’si_kind = ‘webi’ and si_instance = 0 and si_ancestor = 18 If you still need favourites of all users, all the roads leads to a single door. that is BO SDK. Hope this helps!!

Benny Gene De Vera - March 22nd, 2011

How do you get all WebI reports in Favorites of all users? Thanks! Benny

Manikandan Elumalai - March 10th, 2011

Lisa, Can you try this. SELECT SI_NAME, SI_ID, SI_CREATION_TIME, SI_NEXTRUNTIME, SI_KIND, SI_SCHEDULE_STATUS, SI_CONTENT_LOCALE, SI_WEBI_DOC_PROPERTIES, SI_OWNER, SI_SCHEDULEINFO. SI_DESTINATIONS, SI_SCHEDULEINFO. SI_RUN_ON_TEMPLATE, SI_SCHEDULEINFO. SI_SUBMITTER, SI_SCHEDULEINFO. SI_APS_NAME, SI_SCHEDULEINFO. SI_STARTTIME, SI_SCHEDULEINFO.SI_ENDTIME, SI_SCHEDULEINFO.SI_HAS_PROMPTS, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_MONTHS, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NTHDAY FROM CI_INFOOBJECTS WHERE SI_NAME='Sales Report' SI_RECURRING = 1 and SI_SCHEDULE_STATUS = 9 SI_SCHEDULE_STATUS represents the current status of the scheduled job. 0 - Running 1 - Success 3 - Failure 8 - Paused 9 - Pending Along with this to List all the reports scheduled daily you use the below. SELECT si_name,SI_OWNER, SI_NEXTRuntime FROM CI_INFOOBJECTS WHERE SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS = 1 and SI_schedule_status != 8 and SI_RECURRING = 1

Lisa - March 2nd, 2011

You wouldn't happen to have a scheduling report that specifically includes the report parameters would you????

Manikandan Elumalai - March 1st, 2011

Here is the link. http://blogs.hexaware.com/business-objects-boogle/business-objects-query-builder

kaushik - March 1st, 2011

Great got a concrete idea on the subject. A genuine request can i get the link for the 1st part. Thank you, k.kaushik

Comments are closed.