Skip to Content
Author's profile photo Manikandan Elumalai

BusinessObjects Query builder queries – Part II

Dear Folks,

This is the continuation of my previous blog on Query builder queries

http://scn.sap.com/community/bi-platform/blog/2012/10/11/businessobjects-query-builder-queries 

Here we go

To list Universe that doesn’t associated with any WebI reports

SELECT si_id,si_name,si_webi,si_cuid FROM CI_AppObjects

WHERE si_kind = ‘Universe’ and SI_WEBI.SI_TOTAL=0

To list Universes with more than one connections (multi source universe)

SELECT si_id,si_name,si_webi,si_cuid FROM CI_AppObjects

WHERE si_kind = ‘Universe’ and SI_DATACONNECTION.SI_TOTAL>1

To list WebI reports that doesn’t associated with any universe

SELECT TOP 50000 si_id,SI_NAME FROM CI_Infoobjects

WHERE si_kind = ‘WebI’ AND SI_INSTANCE=0 and SI_UNIVERSE.SI_TOTAL=0

To list reports and documents those are in public folders including Sub folders. (Excluding instances, personal documents and inbox documents)

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND IN (‘FullClient’, ‘Txt’, ‘Excel’, ‘Webi’, ‘Analysis’, ‘Pdf’, ‘Word’, ‘Rtf’, ‘CrystalReport’, ‘Agnostic’) AND SI_RUNNABLE_OBJECT = 0 AND SI_INSTANCE_OBJECT = 0 AND SI_ANCESTOR = 23

Find all the WebI reports that use a specific universe

SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS

Where PARENTS (“SI_NAME = ‘Webi-Universe'”, “SI_NAME = ‘Universe Name‘”)

To List of all Groups with Subgroups

Select SI_ID, SI_ALIASES, SI_DESCRIPTION, SI_NAME, SI_USERGROUPS, SI_GROUP_MEMBERS from CI_SYSTEMOBJECTS  where si_kind = ‘UserGroup’

To get a list of Full Client reports

SELECT SI_ID, SI_NAME, SI_KIND FROM CI_INFOOBJECTS WHERE SI_KIND = ‘FullClient’

To get a list of available Calendars 

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_PARENTID=22

To get a list of Users along with their personal folder

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=18

To get a list of Users along with their inbox

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=48

To get a list of available categories

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=45


To count total number of connection on a particular day (Today)

SELECT count (SI_CREATION_TIME) FROM CI_SYSTEMOBJECTS

WHERE SI_LASTLOGONTIME> ‘2013.02.14.00.00.01’ AND SI_KIND = ‘Connection’

To get total number of unique users logged in to the system on a particular day (Today)

SELECT count (SI_NAME)  FROM CI_SYSTEMOBJECTS

WHERE SI_LASTLOGONTIME> ‘2013.02.14.00.00.01’ AND SI_KIND = ‘Connection’

Points to consider while querying  

  • the default limit for returning objects would be 1000 objects normally. In order to get more than 1000 objects we need to use ‘Top N’ function before the column listing in the query. For ex.  SELECT Top 2000 * FROM CI_INFOOBJECTS where Si_KIND=’WebI’
  • You are not allowed to use Sub queries.
  • The order of columns in the SELECT clause has no impact as the results will be rendered in its own order

Hope you find this interesting. Just give a try in your environment and share your findings. Keep reading!

Query Builder Blog series

Basics

             BusinessObjects Query builder – Basics

               BusinessObjects Query builder – Best practices & Usability

Sample Queries

             BusinessObjects Query builder queries

               BusinessObjects Query builder queries – Part II

               BusinessObjects Query builder queries – Part III

               BusinessObjects Query builder queries – Part IV

               BusinessObjects Query builder – Exploring Visualization Objects

              BusinessObjects Query builder – Exploring Monitoring Objects

              BusinessObjects Query builder – Exploring Lumira & Design studio Objects


Use cases

               BusinessObjects Environment assessment using Query builder

               BusinessObjects Environment Cleanup using Query builder

               BusinessObjects Query builder – What’s New in BI 4.0   

Assigned tags

      95 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi, thanks for article!

      How can i get all Instances for publication?

      i try run query:

      select * from CI_INFOOBJECTS WHERE SI_INSTANCE = 1 AND SI_PARENTCUID='CUID'

      no result

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      The required query is

      SELECT SI_ID, SI_NAME, SI_PUBLICATION_DOCUMENTS FROM CI_INFOOBJECTS WHERE SI_KIND='PUBLICATION'

      Author's profile photo Milind Baviskar
      Milind Baviskar

      Thanks for such wonderful information ,

      Is there way to capture time required to refresh report

       

      Thanks,

      Milind

      Author's profile photo Former Member
      Former Member

      cool

      Author's profile photo Henry Banks
      Henry Banks

      Superman!

      Author's profile photo Former Member
      Former Member

      Hi Manikandan,

      Thanks for posting the article.

      Query bulider still using in Business Objects 4.0 .

      I can't find out the query bulider in Business objetcs 4.0..

      please let me know

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      This would be your URL

      http://YourServer:8080/AdminTools

      Author's profile photo Former Member
      Former Member

      I would like to get only webi Reports and associated Instances of WEBI (PDF and Excel), how do i achieve this?

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Jawahar,

      This will give you all your WebI reports and Instances

      SELECT SI_ID, SI_NAME, SI_PARENTID, SI_INSTANCE FROM CI_INFOOBJECTS

      WHERE SI_KIND='WEBI'

      SI_INSTANCE = true gives only schedule instances

      SI_INSTANCE = false gives only report templates (actual reports)

      Report and its associated instances will be related based on SI_PARENTID field. All the report instances will be having SI_PARENTID as their Parent's SI_ID.

      Regards,

      Mani

      Author's profile photo Former Member
      Former Member

      Thanks Mani for the info. But my objective is to backup the WEBI and Instances (PDF or Excel) to biar file. We have crystals also with instances. I am using command line import wizard (3.1) to backup the reports. Since we have huge amount of reports, i am considering to split the properties file one for crystal and other one for WEBI. I would like to capture only WEBI and instances .

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author
      Use this in your .properties file query.   SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND in ('WEBI','Excel','PDF')   you can reuse same query for crystal as well.  Regards Mani
      Author's profile photo Former Member
      Former Member

      That is what i am using currently. But it returns the instances of Crystal as well. I need to get only webi's and associated instances only in one biar file and crystal and the crystal instances in another biar.

      Author's profile photo Neil Louis Dabre
      Neil Louis Dabre

      Hi Mani,

      Thanks for the great post !

      Is it possible to retrieve the objects from a universe i.e. classes, dimensions, details etc using the query language ?

      Thanks,

      Neil

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Neil,

      I would suggest you to have a look at the utility below for the extraction of required information.

      http://bukhantsov.org/2011/09/universe-documenter/

      Regards,

      Mani

      Author's profile photo Neil Louis Dabre
      Neil Louis Dabre

      Thanks Mani, nice tool ! guess I have to download the data then which I wanted to avoid. But looks like there is no other way to use the services of the BO server to search through the universe metadata i.e. within the universe. Anyways, thanks !

      Author's profile photo Former Member
      Former Member

      I think in BO 4.0, Information Stewart can do some what you are looking for.

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Hi Neil,

      The problem here is that the required information (universe objects) are stored in the form of .unv or .unx files in the File repository store rather than at the CMS database tables.

      By using Query builder we can only extract information from CMS database tables. If you want to access information from the file store (Metadata of universe,reports and connection,etc) then we need to develop a utility which is based on Designer SDK or Report Engine SDK.

      This is the reason why I have referenced the external utility rathar than the Query builder query. Hope this helps you to understand the internals.

      Regards,

      Mani

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      How do I find the location of a Universe or a Webi Report?

      I used the queries from your blog to find the universe with no webi's attached and find the webi's with no universes attached. and i'm getting the list of reports and universes. But how do I find their location?

      thank you for your help

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Hi Izak,

      Which path you are referencing? Is it physical or Logical with in BOE?

      For physical location you can just get it from its properties page. This refers to FRS path some thing like frs://Input/a_243/118/021/1406707/aul9qe1bhtxkrpkd2dzklns-guid[ayuviawckzvpjapsv41tgrc].wid

      To get the logical path you need to write an SDK code so that it loops through all its parent objects and gives you the final result path. Logical path would be something like Public Folders->Sales reports->APAC-> Last month sales.wid

      Regards,

      Mani

      Author's profile photo Former Member
      Former Member

      I'm looking for Logical Path. Do you have any queries that I can use to get the logical path ? ๐Ÿ˜€

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Hi Izak,

      You cant achieve this using Query builder query as you need to recursively go through each parent object.I would suggest you to use the sample code provided here

      http://bukhantsov.org/2011/08/getting-started-with-businessobjects-java-sdk/

      Regards,

      Mani

      Author's profile photo Former Member
      Former Member

      Thank Mani. Thats helpful.

      Right now I just went to the Universe list though Infoview and got the location of each universe from there ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      Can I query the Repositry without using the query browser, to extract information from the repository and use this output in 3th party tools?

      Author's profile photo Former Member
      Former Member

      There are some free tools available. One of them will be  :

      http://bukhantsov.org/2012/03/query-builder-with-export-to-excel/

      Try this. It works.

      Author's profile photo Former Member
      Former Member

      Hello All,

      In Production, there are approx 400 users having access to 4 dashboards & some detail reports in WebI for one application ABC, how to collect information regarding how many users have used these dashboards & reports since April 15th, 2013.

      We are currently in BOXI 3.1 SP4.

      can anyone help me with query how to check using Query builder.

      Thanks

      Sandeep

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Hi Sandeep,

      I would suggest to create a separate thread for this.

      However you cannot get the historical information using Query builder as it only capture only snapshot of the repository information. You need to use the Audit universe to get the required information for you.

      Regards,

      Mani

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      Can you please help me how to use the Audit universe to get the information.

      Thanks

      Sandeep

      Author's profile photo Former Member
      Former Member

      Hi  Mani,

      We are using BO4.0 SP5, In our platform one of the power user group requesting CMC access to get the below information.

      1. Ability to view Security that is applied to Finance Folder and Mobile Reports folder and Catagory
      2. Security that is applied to the specific groups.
      3. List of user in the specific group ( I know this can be done in QB).

      However we are not interested to provide CMC access by providing custom access levels.

      SO IF YOU HELP US TO PROVIDE THE OPTION/QUERY WITHIN QUERY BUILDER  FOR #1 , #2 - THAT WILL BE GREAT

      Waiting for your reply with more expectations ๐Ÿ™‚

      Thanks in Advance

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      I have been reading your blogs related to "Query builder series",I have a requirement where I need to get the contents of BIAR file(generate a  detailed list of reports moved thru biar file).I have written the below query and was able to get the BIAR file details but not able to get the names of the reports moved through BIAR file.Reports that are moved through BIAR file  i could find in file store folder->input folder->properties file that has the names of report but these are not stored physically in cmc or audit database.I am using the below mentioned query to get the BIAR job details.

      ***************** my query ********************

      Select SI_ID, SI_NAME, SI_PARENTID, SI_KIND From CI_INFOOBJECTS Where SI_KIND In ('CrystalReport','Webi','Excel','PDF','Rtf','Txt','Universe','Word','LCMJob') And SI_INSTANCE_OBJECT < 1 Order By SI_NAME, SI_ID

      I need the list of reports moved by using BIAR file meaning BIAR file contents.

      I have tried various forms of queries after reading your blogs and by googling but of no use.Can you help me with a custom query or any other suggestion.

      Regards,

      Kumar

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Hi Kumar,

      Try this

      Select SI_ID, SI_NAME, SI_PARENTID,SI_KIND, SI_JOB_EXPORT_FILE_NAME,SI_REFERRING_DOCS

      From CI_INFOOBJECTS Where SI_KIND = 'LCMjob'

      SI_REFERRING_DOCS is the one you are looking for. Please note you can only query the LCM Job not the BIAR file as the BIAR file is no where available in BusinessObjects repository.

      Hope this makes you clear.

      Regards,

      Mani

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      SI_REFERRING_DOCS is not being returned in the query results,I guess SI_REFERRING_DOCS is not present in CI_INFOOBJECTS table.

      I am using bo 4.0 version,Can you please check if the query is right.

      Regards,

      Kumar

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      Appreciable contribution. Good work. Please assist us with below information:

      Is there any document that describes all the fields that we see from QB for CI_APP, CI_SYS, CI_INFO?

      Is there a way we could query "Last run time"(not schedule time) of each WEBI/DESKI  report from QB?

      What does field 'SI_UPDATE_TS' state?

      What is the difference between SI_AUTHOR and SI_OWNER?

      Kind regards,

      Sandeep Rana

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Hi Rana,

      To answer your Queries

      1. There is no specific document that speaks about the internals of the repository. I came across all these queries when tried to get the required information for my metadata reporting. I would suggest you to read all my Query builder blogs to make you understand further starting from this http://scn.sap.com/docs/DOC-42952. I have covered as much queries as possible. If not just get back to me I will try to get them for you.

      2. Have you checked SI_LAST_RUN_TIME column.

          

           Sample query is

           select top 10 * from ci_infoobjects where si_kind='WebI' and si_instance=0

           Please note I have queried only report templates (SI_INSTANCE=0) here

      3. 'SI_UPDATE_TS' keeps track on report level modifications such as Objects Add, Delete, Tab change etc. These changes will be tracked only if the report is exported back to repository (Not refresh)

      4. SI_AUTHOR and SI_OWNER are one and the same in most of the cases. But the SI_AUTHOR field may change time to time based on who is modifying the report.

      Hope this helps.

      Regards,

      Mani

      Author's profile photo Former Member
      Former Member

      Thanks Mani.

      SI_LAST_RUN_TIME actually gives last scheduled time not last refresh time.

      I was looking to retrieve Last refresh time.

      With regards to 4th answer did you mean that SI_OWNER would remain the user who created the report and SI_AUTHOR could be the user who later modify the same report?

      Regards,

      Sandeep_R

      Author's profile photo Former Member
      Former Member

      Interesting Mani, Keep Rocking ๐Ÿ™‚ ๐Ÿ™‚

      Regards,

      Giri

      Author's profile photo Former Member
      Former Member

      Excellent Summary,  One thing to note on the Check Relationship type queries on Universes, the PARENTS query above, only works for .UNV Universes, not .UNX.  The query for .UNX would be similar to this:

      SELECT TOP 2000 SI_NAME, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS

      Where PARENTS ("SI_NAME = 'Document-DSL.Universe'", "SI_NAME = 'UNX Universe Name'")

      .UNX Universes do not qualify for SI_NAME = 'Webi-Universe' b/c SAP does not consider them Universes in the InfoStore but rather DSL Objects.

      Enjoy!

      -Mark

      Author's profile photo Chris Waters
      Chris Waters

      Thanks for posting this. My universes are in .UNX format. I was wondering why, within this excellent article, the syntax for "Find all the WebI reports that use a specific universe" returns no results for universes that are definitely used by my Webi reports. That being said, however, your syntax returns no results, also! Any suggestions?

      For instance:

      SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS WHERE PARENTS ("SI_NAME = 'Webi-Universe'", "SI_NAME = 'eFashion'") should return all Webi reports that use the eFashion universe, right?

      Author's profile photo Former Member
      Former Member

      Dear Mark,

      Thank you for posting your query.

      I am trying to retrieve list of .UNX files from my BO repository using your mentioned Query but NO RESULTS. Could you retrieve the list at your end? If so, I will appreciate if you could kindly post your query to retrieve UNX files

      SELECT TOP 2000 SI_NAME, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS

      Where PARENTS ("SI_NAME = 'Document-DSL.Universe'", "SI_NAME = 'UNX Universe Name'")

      I don't want to put the UNX name rather I need all of them those are UN-USED thus trying to use combination of above mentioned queries...

      SELECT si_id,si_name,si_webi,si_cuid FROM CI_AppObjects

      WHERE si_kind = 'Universe' and SI_WEBI.SI_TOTAL=0

      Thanks in advance

      Best,

      Chaitanya

      Author's profile photo Former Member
      Former Member

      Dear Experts,

      Thank you for the useful info you guys have shared - really helpful.

      I just had posted my problem as I wanted to retrieve all the UN-USED Universes. While digging the system I found one PARAMETER for SI_KIND "DSL.MetaDataFile" which will throws .UNX (IDT Universe) files.

      So to retrieve UN-USED .UNX files, I was trying to write query as below:

      SELECT si_id,si_name,si_webi,si_cuid FROM CI_AppObjects

      WHERE si_kind = 'DSL.MetaDataFile' and SI_WEBI.SI_TOTAL=0


      Which returns nothing.

      I want to find UNUSED .UNX Files from repository.


      I will appreciate any help on this.


      Best,

      Ch

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy

      The DSL.MetaDataFile kind (UNX) does not have an SI_WEBI property like the Unverse (UNV) kind does.  Instead, try using SI_SL_DOCUMENTS.SI_TOTAL = 0

      -Dell

      Author's profile photo Former Member
      Former Member

      Dear Dell,

      Thank you very much for your assistance & quick response. Your response helped me resolve my issue - now below query gets me all I want i.e. Unused Universes (.UNX)

      SELECT SI_KIND, SI_NAME, SI_SL_DOCUMENTS FROM CI_APPOBJECTS WHERE SI_KIND ='DSL.MetaDataFile' AND SI_SL_DOCUMENTS.SI_TOTAL=0

      Much appreciated.

      CH

      Author's profile photo Former Member
      Former Member

      Hello Mani,

      Is there a way from query builder to retreive  number of reports for all users in their favoirites.

      I want to get list of UserID's who has more than 20 reports in their favorites?

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Try this Archana

      SELECT SI_NAME,SI_CHILDREN,SI_KIND FROM CI_INFOOBJECTS WHERE SI_KIND='FAVORITESFOLDER'

      SI_NAME wiil give you the user and SI_CHILDREN will give you number of objects in Favorites folder without any subfolder. If you have any sub folder you may requierd to drill again using Subfolder id

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      Thanks for your quick response. But the query that you posted is not fetching correct results. I have 6 reports in 3 folders(no subfolder under my Favorites). The result using this query is giving only 4 under SI_CHILDREN.

      I was trying to use the below query but I guess Group By  is not allowed in SQL Builder. Not sure how to achive this. Is there any way to get the SI_Owner  and Number of reports( reports which are in subfolders too) from query builder?

      select  si_owner, Count(SI_NAME)  from ci_infoobjects where

      si_kind in ('webi', 'crystalreport')

      and si_instance = 0 and si_ancestor = 18

      and Count(SI_NAME)>10

      Group by SI_OWNER

      Author's profile photo Former Member
      Former Member

      Mani and Archana,

      I think this is a use case for using Path Queries.  This requires a small coding change to the AdminTool to implement, but it provides a whole new perspective to reporting against the CMS can can do the recursive subfolder processing you are looking for.

      There are some posts out there on how to modify the AdminTool to accept Path Queries and some documentation on how to run them.

      -Mark

      Author's profile photo Former Member
      Former Member

      Hello Mark,

      I am a developer and my knowldge on BO Admin tools is limited. Could you tell me exactly or point me to material on what exactly needs to be modified for Admin Tool? When you say Admin tool is it CMC? Is there a risk associated for making such change ? 

      What I was trying to achieve was simple but looks like query builder does not have the capability. All I wanted is to get number of reports for each user from their favorites folder.

      Author's profile photo Former Member
      Former Member

      Archana,

      Here is a link to the article from Ted U. on how to make these changes.  We are running BI 4.0/4.1 and this is implemented the same way. 

      The reason I think you are not getting the desired resluts is like Mani said, you are not able to recusively go through all the folders users may have created.  Path queries can do this rather simplisticly.  Again there is not a lot of documentation out there on them, so dig around on SCN and Google and you can piece together what you need once you understand how they work.

      http://scn.sap.com/people/ted.ueda/blog/2009/02/20/businessobjects-enterprise-sdk--xi-3x-path-queries-and-query-builder

      Just be certain that when you make these chanegs that you save off the .jsp page b/c subsequent deploys will overlay the .jsp.  We just park the code somewhere and copy it back when needed.  Enjoy!

      -Mark

      Author's profile photo Former Member
      Former Member

      Great article!

      Is it possible to query a list of reports that are based on Excel as a data source?

      Author's profile photo Lina Chhina
      Lina Chhina

      Hi thanks for this - its very useful.  I am trying to use:

      To list WebI reports that doesn’t associated with any universe

      SELECT TOP 50000 si_id,SI_NAME FROM CI_Infoobjects

      WHERE si_kind = 'WebI' AND SI_INSTANCE=0 and SI_UNIVERSE.SI_TOTAL=0

      it produces a list of nearly 2500 reports, but if I search for a report they are either there in infoview and run successfully so there is a universe, or I cannot find the report when I search for it.  I have checked some reports to see if they are inbox documents by search for the owner but am still unable to locate them.. please can you explain what it is actually displaying, and also how to use the results?

      Thanks,

      Lina.

      Author's profile photo Ajay Gupta
      Ajay Gupta

      Lina,

           You may want to run the REPOSCAN in scan mode to see how many INCONSISTENT objects are in the CMS.   Also run it in REPAIR mode to fix the issue.

      Then re-run the query again.

      Regards,

      Ajay

      Author's profile photo Lina Chhina
      Lina Chhina

      hi, how do I do this?

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Agree with Ajay Gupta here.


      The reason is that those reports are  linked with the universes using their short names. I noticed this behaviour in earlier BusinessObjects releases. You may need to run reposcan in order to remove the discrepancies between CMS DB and File repository servers.


      Have a look here http://blogs.hexaware.com/business-objects-boogle/businessobjects-administration-%E2%80%93-repository-diagnostic-tool/

      Author's profile photo Former Member
      Former Member

      Dear Mani,

      Your article is very interesting, great job keep it up and thanks a lot for contributing your knowledge with the community.

      Could you help me with this please,

      We have a number of folders, users and groups in our BI 4 FP3 env.  We would like to be able to see which folders exist but the list is too long to be displayed in the CMC, could you help me with a  query to the CMC to tell us the folders that exist and the groups and users that have access to those folders?

      Thanks

      Avi

      Author's profile photo Former Member
      Former Member

      Mani,

      I have a question. I would like to get all the reports (Crystal) scheduled as a crystal instances.

      I am using this query which is getting all the instances names. The report might have many instances. With the below query i am getting all of them. But i just need report name which is schedule as crystal instances.

      select si_name from ci_infoobjects where si_kind='crystalreport' and si_instance=1

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Hello Jawahar, You have almost done that.

      Your query will fetch all the crystal instances.

      I assume you need only reports that scheduled as crystal instaces.. If  it is so you need to go with two queries

      1. select si_id,si_name, si_parentid from ci_infoobjects where si_kind='crystalreport' and si_instance=1

      2. Select * from ci_infoobjects where si_id in { si_parentid list from query one}.

      . It is obvious that you need to go to SDK utility.

      Author's profile photo Former Member
      Former Member

      Thanks Mani

      Author's profile photo Former Member
      Former Member

      Mani,

      How will you find out how many reports and name of the reports ran in a given period? And also is there way to find reports never ran or accessed using query builder?

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Jawahar,

      To iterate again, Audit reports will give you the historical information where as CMS database tables captures only Snapshot (transactional in nature).

      How will you find out how many reports and name of the reports ran in a given period?


      You can get this from auditor

      is there way to find reports never ran or accessed using query builder?


      Refer these blogs which gives all the related informations required for migration & cleanup


           BusinessObjects Environment assessment using Query builder

          BusinessObjects Environment Cleanup using Query builder         

      Author's profile photo Former Member
      Former Member

      Dear Mani,

      Sorry i am posting this question again,

      We have a number of folders, users and groups in our BI 4 env.  We would like to be able to see which folders exist but the list is too long to be displayed in the CMC, could you help me with a  query to tell us the public folders that exist and the groups and users that have access to those folders?

      Thanks

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Hi Avinash,

      Sorry for noticing your query only now.

      You can get the list of public folders using this.

      To get the all the public folders (Non System Folders) 

      SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=23 AND SI_NAME!='REPORT CONVERSION TOOL'  AND SI_NAME!= 'ADMINISTRATION TOOLS' AND SI_NAME!= 'AUDITOR'

      in the query above you may required to include default folders. Add as per your requirement.

      For your security requirement I would suggest you to do a Folder by Folder analysis using security query. You can refer here for Security query usage. here

      BusinessObjects Administration - The less known Security Query

      Author's profile photo Andrew Dale
      Andrew Dale

      Hi Manikandan

      thanks for a great set of blogs.

      i have one question. Is it possible to determine users' preferences settings for Web Intelligence to see if they have set View or modify to WEB/Rich Internet Application, Desktop or PDF?

      query builder - WEB or RIA.PNG

      I am using SAP BO 4.0 SP4 Patch 16.

      Many thanks

      Andrew

      Author's profile photo Andrew Dale
      Andrew Dale

      I have worked it out

      this gives those users with their view preference set to JAVA

      SELECT si_name FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER' and SI_DATA.DOCUMENT_WIViewTechno = 'J'

      this gives those users with their modify preference set to JAVA

      SELECT si_name FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER' and SI_DATA.DOCUMENT_WICreateTechno = 'J'

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Thanks for sharing your R&D Andrew.

      Author's profile photo Former Member
      Former Member

      Hi,

      Is there a way to alter the default setting so that we can retrieve more than 1000 records while fetching data using Query builder?

      I have come across an environment where in a simple select * from CI_SYSTEMOBJECTS has fetched 10,000 records.

      Thanks

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Please try with Top N clause something like select Top 15000 * from CI_SYSTEMOBJECTS

      Author's profile photo Kelly Stone
      Kelly Stone

      Hi - lots of great stuff here!  I have not found or figured out something I have been trying to do.  I would like a list of reports along with their folder structure.  But I only want if for two public folders and their subfolders.  Is this do-able?

      Kind regards,

      Kelly

      Author's profile photo Former Member
      Former Member

      Hi,

      Please use the below query.

      SELECT SI_ID,SI_NAME,SI_PARENT_FOLDER,SI_FILES FROM CI_INFOOBJECTS WHERE SI_INSTANCE = 0 AND SI_ANCESTOR = [SI_ID OF Public Folder]

      Regards,
      Nachiket

      Author's profile photo Kelly Stone
      Kelly Stone

      Thanks Nachiket - I want the folder structure for each report.  This gives me all the reports and all of the folders individually.  I was able to get that already...

      Kind regards,

      Kelly

      Author's profile photo Former Member
      Former Member

      Hi kelly,

      In my view the folder structure that you see from CMC/BI launchpad is pseudo-reference to the physical path('Filestore' folder path on the OS Server on which BO Server is deployed). From query builder you could get only the physical path of the BO report object not the pseudo-reference.

      As per my knowledge there is no way to retrieve this pseudo-reference by simply executing query from querybuilder. You would need some assistance from SDK to write JSP which you can place in the following directory:

      "<SAP BO installation path>\Tomcat7\webapps\AdminTools"

      After placing JSP in above directory you could open below URL in browser:

      http://<BO Server IP:port>/AdminTools/<JSP file name>

      Below are the JAVA packages that you would need:

      com.crystaldecisions.sdk.framework.*

      com.crystaldecisions.sdk.occa.infostore.*

      com.crystaldecisions.sdk.occa.security.*

      com.crystaldecisions.sdk.exception.*

      com.crystaldecisions.sdk.properties.*

      com.crystaldecisions.sdk.plugin.desktop.folder.*

      Note: I have tested this only in 3.1 SP6 Platform for DESKI and WEBI reports

      Hope this might assist.

      Regards,

      Sandeep

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      How do I find the list of all the webi reports? I'm using the below query and it is pulling webi instances also. Is there a way to eliminate the number of instances?

      SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND='WEBI'

      Thanks again,

      Author's profile photo Andrew Dale
      Andrew Dale

      try this

      SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND='WEBI' and SI_INSTANCE_OBJECT = 0

      Author's profile photo Andrew Dale
      Andrew Dale

      although this also works but I am not sure of the difference

      SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND='WEBI' and

      AND SI_INSTANCE=0

      Author's profile photo Masood Aftab
      Masood Aftab

      Hi Mani,

      Do we have any query by which we can get the list of reports using a particular class object in a universe?

      Regards

      Masood

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      I am trying to extract the report list which is assigned to mobile category however i am not getting the results. Can you help me here with exact query?

      Author's profile photo Former Member
      Former Member

      Hi Mani, Thanks for the detailed post, I am looking for ways to extract the user groups that have access to a specific report? Do you have any thoughts or suggestions on how to retrieve?

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Easiest way is to making use of Security Query option. Have youu got any chance to check my blog on Security query?

      Author's profile photo Former Member
      Former Member

      Thanks Mani

      Author's profile photo Erdem Uren
      Erdem Uren

      how about find all the webi reports that use a specific bex connection.. i didnt find about bex connections.

      Author's profile photo S man
      S man

      Hi,

      Please use  below query to get list of webi list per bex query

      SELECT SI_NAME, SI_WEBI_DOC_PROPERTIES,  SI_PARENT_FOLDER FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi' AND SI_WEBI_DOC_PROPERTIES LIKE '%Q00%'

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      1. Could you please help me in SQL for listing all tables corresponding to a universe.

      2. Also is there a way to know Not Used Tables in an universe so that they can be removed.

      Thanks,

      Venkat

      Author's profile photo Jawahar Konduru
      Jawahar Konduru

      You cannot get the table names using query builder. There are some macros which can extract the universe tables.

      Check below link

      BOB's Downloads

      Author's profile photo Former Member
      Former Member

      Hi Jawahar,

      Currently i am using BO 4.1 SP3 and using universes UNV by UDT.

      I have downloaded GetSetUniverse_3_5_25.xls, but i am getting errors as Complie error: Can't find project or library.

      Can you provide some other links or some idea so that i can get tables in an universe.

      Thanks,

      Venkat

      Author's profile photo Former Member
      Former Member

      Is there a way to find out the relational connection parameters. I can find the OLAP connection parameters details. I can use this query to find the oracle connections.

      Select SI_DESCRIPTION, SI_NAME, SI_CONNECTION_PARAMETERS, SI_CONNECTION_PARAMSTRING, SI_CONNECTION_CONNECTSTRING from CI_Appobjects where SI_CONNECTION_NETWORKLAYER='Oracle OCI'

      But unable to decode what SI_CONNECTION_PARAMSTRING and SI_CONNECTION_CONNECTSTRING return. I am looking for the database info used in connections thru query builder instead of opening each and every relational connection. Appreciate any quick help.

      Author's profile photo Marcos Morais
      Marcos Morais

      Very helpful document!

      Thanks for sharing your knowledge and experience!

      Author's profile photo vijaykumar ijeri
      vijaykumar ijeri

      Very useful.

      Thanks for sharing!

      Thanks & Regards,
      Vijay

      Author's profile photo Erin Kimak
      Erin Kimak

      Hi,

      how can i get list of all services created in Query as a Web service along with the metadata?

      Thank you,

      Hitesh

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      Need your urgent help. Below is report path :

      E.g :ย "Public Folders\Folder1\Folder2\Folder3\Folder4\MyReport"

      How Can I get full report path as single string?

       

      Thanks in Advance!!

      Author's profile photo Former Member
      Former Member

      Hi Mani,

      I was wondering if we can get the users list along the usergroup they belong to from QB? Thanks in advance!

       

      Author's profile photo Ravikumar Periasamy
      Ravikumar Periasamy

      Dear All,

      I need to find out, In which reports the users are assessing more? how do we find out? please help on this.

      Thanks in advance...

       

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Try this in your Audit reports. Try deploy this in your environment if you are in BI 4.x.

       

      https://blogs.sap.com/2015/07/15/unlock-the-auditing-database-with-a-new-universe-and-web-intelligence-documents-for-bi41/

      Author's profile photo Ravikumar Periasamy
      Ravikumar Periasamy

      Thanks Mani,

      But not working in 4.2 SP4, its saying table or view does not exit. Please let us know..

       

      Thanks,

      Ravi

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      You may need to elaborate bit more to understand your Undit DB and what report you are trying to run, etc..

       

      Thanks

      Mani

       

      Author's profile photo Waleed Nooristani
      Waleed Nooristani

      Hi Mani,

      Thank you! your post is very helpful. ย I have a question. ย Is there any way to query audit database and get webi reports and number of times it was refreshed? ย The ultimate goal is to find the popular reports that is used the most by our users.

      I can get list of all the webi reports, but to get report with the most view need your help.

      Thanks in Adv.

      Waleed

      Author's profile photo sreenivas thoutam
      sreenivas thoutam

      SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where PARENTS (โ€œSI_NAME = โ€˜Webi-Universe'โ€, โ€œSI_NAME = โ€˜Universe Nameโ€˜โ€)

      I used this query for list out number of reports particular universe (IDT unx), I am not getting any list.

      Please help me on this

      Author's profile photo Basis LER
      Basis LER

      Hi Mani,

      How can we get list of Active users only from BOBI Query builder?