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   

To report this post you need to login first.

84 Comments

You must be Logged on to comment or reply to a post.

  1. Oleg Luchinskiy

    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

    (0) 
  2. sapkrisboedge mac

    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

    (0) 
    1. Manikandan Elumalai 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

      (0) 
      1. Jawahar Konduru

        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 .

        (0) 
        1. Manikandan Elumalai 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
          (0) 
          1. Jawahar Konduru

            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.

            (0) 
          2. 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

            (0) 
              1. 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 !

                (0) 
                1. Manikandan Elumalai 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

                  (0) 
  3. Mr. Izak

    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

    (0) 
    1. Manikandan Elumalai 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

      (0) 
          1. Mr. Izak

            Thank Mani. Thats helpful.

            Right now I just went to the Universe list though Infoview and got the location of each universe from there 🙂

            (0) 
  4. J. Wieringa

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

    (0) 
  5. Sandy R

    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

    (0) 
    1. Manikandan Elumalai 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

      (0) 
  6. satheesh kumar vadivel

    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

    (0) 
  7. Anjankumar Potturi

    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

    (0) 
    1. Manikandan Elumalai 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

      (0) 
      1. Anjankumar Potturi

        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

        (0) 
  8. Sandeep Rana

    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

    (0) 
    1. Manikandan Elumalai 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

      (0) 
      1. Sandeep Rana

        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

        (0) 
  9. Mark Hornak

    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

    (0) 
    1. 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?

      (0) 
    2. Chaitanya Bhure

      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

      (0) 
      1. Chaitanya Bhure

        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

        (0) 
          1. Chaitanya Bhure

            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

            (0) 
  10. Archana Reddy

    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?

    (0) 
    1. Manikandan Elumalai 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

      (0) 
      1. Archana Reddy

        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

        (0) 
  11. Mark Hornak

    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

    (0) 
    1. Archana Reddy

      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.

      (0) 
      1. Mark Hornak

        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

        (0) 
  12. 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.

    (0) 
    1. 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

      (0) 
  13. Avinash Kumar

    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

    (0) 
  14. Jawahar Konduru

    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

    (0) 
    1. Manikandan Elumalai 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.

      (0) 
        1. Jawahar Konduru

          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?

          (0) 
          1. Manikandan Elumalai 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         

            (0) 
  15. Avinash Kumar

    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

    (0) 
    1. Manikandan Elumalai 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

      (0) 
  16. 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

    (0) 
    1. 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’

      (0) 
  17. Chakri N

    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

    (0) 
  18. 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

    (0) 
    1. Nachiket Sakore

      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

      (0) 
      1. 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

        (0) 
        1. Sandeep Rana

          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

          (0) 
  19. Ilyas Mohammed

    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,

    (0) 
      1. 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

        (0) 
  20. 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

    (0) 
  21. Antony Martial

    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?

    (0) 
    1. 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%’

      (0) 
  22. Venkat Konga

    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

    (0) 
      1. Venkat Konga

        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

        (0) 
  23. Gouri Kumar

    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.

    (0) 

Leave a Reply