Highlight the entire block of text you want to be numbers Alt + A, then E, then Alt + F, you're done. This application works fine! 360Suite empowers SAP BusinessObjects professionals by pulling metadata and offering powerful automations. WebBusinessObjects Query builder 708 Views Follow RSS Feed List all the Public folders (including Subfolders) and which UserGroups has access to those folders/subfolders. With no option to export the results in XLS or CSV format, you can only review the results on your screen, making it hard to leverage and manipulate the output. So far so good on our BI4.0 SP6 installation. Select a cell in the data and then select Query > Edit. This will be handy for those who searching for Query Builder Queries. To export the results to a .csv file, complete the following: Click Query Results. Remove all references to the libraries and reattach them. The document exists in the FRS but the link doesnt exist in the CMS. AdminTools Session should be opened with Administrator account if possible, since this account has faster security rights check than with standard account (even compared to a user part of Administrators group). What is the version of BO? I guess, i will be asking couple more questions about the operational side of this tool and some queries as well in the near future. There is no limit to the scope of the queries, you can query all the content, including content not normally accessible through the CMC or BI LaunchPad. WebIn this video, we will create a new Web Intelligence document based on a universe. In Excel, you may want to load a query into another worksheet or Data Model. For example, rename Sheet1 to DataTable and Table1 to QueryTable. It is a technical tool, and to make queries against the SAP BusinessObjects repository, you need to have the technical knowledge because SAP doesnt provide any documentation or tutorials on how to create them in the tool, other than online blogs there is no help. Is is possible to query and find a particular universe object used in any reports. Lire cet article en Franais window.tgpQueue.add('tgpli-644fa23dd29c1'). Query is fine in Query builder, suspect its a security restrciyion in Excel preventing it being called from your app. Hi, I am trying to run the following query, however, the query only returns SI_DESCRIPTION, SI_NAME, SI_ENT_USERFULLNAME, and SI_LASTLOGONTIME. You can only export one database object in a single export operation. However, you can merge multiple worksheets in Excel after you complete the individual export operations. Before performing an export procedure, it is a good idea to review the data that you want to export to make sure that it does not contain any error indicators or error values. Hello ! The report object defines the underlying data model and specifies which database tables and fields to pull data from. when I am trying to login by opening the exe, for both the versions its giving the error Unable to logon. There isnt an "execution time" field. SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4, To get BO File Repository Server Information, WHERE SI_KIND = SERVER AND SI_NAME LIKE %FILEREPOSITORY%, To get the all the public folders (Non System Folders), WHERE SI_PARENTID=23 AND SI_NAME!=REPORT CONVERSION TOOL AND, SI_NAME!= ADMINISTRATION TOOLS AND SI_NAME!= AUDITOR, To list all the WebI reports with prompts. is it possible to query the comments solution table to get the context which was using for each front end tool to apply the comment ? I am also interested in this tool. Alerting is not available for unauthorized users, Right click and copy the link to share this comment. What is the variable to use to select users whose accounts have been disabled (Disabled option checked) ? It provides advanced features like code highlighting, auto-completion, and syntax checking to help you write error-free code. Just found this and unzipped onto my machine. I want to know the query for the query builder. In For example, you can delete WebI documents or even targeted users per request. In CMC->Folders-> [choose folder] ->User Security-> [choose UserGroup] ->View Security. Thank you. it was not working for me as well, but just corrected some formatting like single codes, semi colon, equalto sign, etc and then it worked. To extract all the report names from specific folder". Probably I had an IE cache problem. How can I get the Instances which took the maximum time to execute? All objects that dont have an SI_FILES property at all. So far Ive only used it to export a list of users with name, email and last logon time, to Excel. For example, manipulating data in an Excel worksheet is fundamentally different than Power Query. Yes. Please note using Query Builder we can only query the information stored in the CMS database not from the File repository files. This command is just like the Data > Get Data command in the Excel ribbon. I need to find ithe list of universes where ,a specific TABLE is being used. Good work. Terms of use | Confirm that the correct table displays. WebIn the Export - Excel Spreadsheet dialog box, review the suggested file name for the Excel workbook (Access uses the name of the source object). In the list of queries, locate the query, right click the query, and then select Load To. BusinessObjects Query builder queries 240 104 344,412 Hello Techies, Some of the Query builder queries to explore the BusinessObjects repository. Could be that the InfoSteward Objects are store in a different table.You can try a query like:select si_id, si_name, si_kind from ci_infoobjects, ci_appobjects, ci_systemobjects where si_instance != 0This will return all instances, may be a good idea to insert a condition on the name: and si_name='sample'. select * from ci_systemobjects where si_kind='Event', select SI_FILES from CI_INFOOBJECTS where SI_FILES.SI_NUM_FILES=0, Plug-ins where that was no icons associated with them, SELECT top 200000 SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='MyGroupNameHere'") AND SI_KIND='USER', SELECT TOP 20000 SI_NAME, SI_LASTLOGONTIME FROMCI_SYSTEMOBJECTS WHERE SI_NAME NOT IN ('Administrator','Guest') AND SI_KIND='USER' AND SI_LASTLOGONTIME IS NULL ORDER BY SI_NAME, SELECT top 200000 SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='MyGroupNameHere'") AND SI_KIND='USER' AND SI_NAME NOT IN ('Administrator','Guest') AND SI_KIND='USER' AND SI_LASTLOGONTIME IS NULL, 1201157 - Sample administrator queries for Query Builder, 1542511 - How to access query builder on Web Application Container Service (WACS) deployment, 1854982 - Using query builder to find number of reports for certain schedule statuses. Contains InfoObjects that the BI Platform uses, User, User Group, Server, Server Group,Folder, Connection, Calendar,Event, Holds InfoObjects that are used by documents, Contains InfoObjects that are consumed by the end user, WebI, Crystal Report, FullClient, PowerPoint, Pdf, Excel, Word, Rtf, Txt,Program, Shortcut, Query Builder can be found at the below URL. WebThe tool allows extracting Unv universe metadata to Excel spreadsheet. {"serverDuration": 297, "requestCorrelationId": "12b290e7937bf855"}, SAP BusinessObjects Business Intelligence Platform (Enterprise), https://launchpad.support.sap.com/#/notes/1895241, Unlock the CMS database with new data access driver for BI 4.2, Number of Webi documents (excluding instances), select top 100000 si_id from ci_infoobjects where si_kind='Webi' and si_instance=0, Number of Webi instances only (not the master doc), select top 100000 si_id from ci_infoobjects where si_kind='Webi' and si_instance=1, Number of Crystal Reports (excluding instances), select top 100000 si_id from ci_infoobjects where si_kind='CrystalReports' and si_instance=0, Number of Deski documents (excluding instances), select top 100000 si_id from ci_infoobjects where si_kind='FullClient' and si_instance=0, Number of Deski instances only (not the master doc), select top 100000 si_id from ci_infoobjects where si_kind='FullClient' and si_instance=1, Number of Publications (excluding instances), select top 100000 si_id from ci_infoobjects where si_kind='Publication' and si_instance=0, Number of Publication instances only (not the master doc), select top 100000 si_id from ci_infoobjects where si_kind='Publication' and si_instance=1, select top 100000 * from ci_infoobjects where si_schedule_status=9, select * from ci_systemobjects where si_kind='Server', select si_name from ci_systemobjects where si_nameduser = 1, select si_name from ci_systemobjects where si_nameduser = 0. If you have some suggestions about functionality let me know: dmytro.bukhantsov at gmail.com !:FDtF)YM,D2?o?!$C EP;sj{B%*!} How can I get a list of all of the fields in these tables? Hi Amir, here: bukhantsov.org/tools/QueryBuilder_src.zip. its a good tool which saved me a lot of time. Steps:- 1) Create a project say Prj_Test at Business Objects Data Services. Given a particular group name (replace MyGroupNameHere accordingly) this query will return ALL the users in the group and any sub groups. Basically, I need to be able to build queries that mirror what I see in the Instance Manager in CMC. Text Format, Hi Dymytri You can also set default query load settings in the Query Options window. I luv this tool, it is running fine on BO 3.1, no issues period. PublicKeyToken-692fbea5521e1304 or one of its dependencies. List universes that reference a database column. Please suggest me. I'm guessing InfoSteward stores them somewhere else in the CMS? We have also been toying with this: https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3. Using SI_PARENT_FOLDER returns documents and instances of these documents, that are in specified folder. there isn't, the tool is not built for exporting, however CMS DB driver now allows you to build universes on top of repository and report of them. If you have multiple accounts, use the Consolidation Tool to merge your content. . Please help.. Tip Sometimes the Load To command is dimmed or disabled. Its always good practice to change the default names of worksheet tabs to names that make more sense to you. you usually have to write several queries to get the required result. WebOver 8 years of Business Intelligency solutions with experience on all stages of software apply development life cycle.Strong experience into Developing BI reporting applications using Business Objects, Crystal Reports.Good Comprehension in Data Warehouses and Transactions database design and research, Data models, Business Intelligence FROM ci_infoobjects, ci_appobjects, ci_systemobjects where si_recurring=1 and si_runnable_object=1 but the results for scheduling and processing do not show up. You can use command line version of the tool to generate the Excel file Queries with si_ancestor conditions can run for a long time. Im not a coder so i cannot modify your source code. On the other hand: Depending on the delimiter char used, the method of opening the file in Excel might be the problem: Restart Excel and choose "File > Open > I use the odbc connector on my pc with ms access to create queries and then publish them on a switchboard. What authentication are you using? I am looking for a query to get information of most accessed reports with report folder excluding shortcuts and reports instances. For example if I created a user such as Account Name: 12345, Full Name John Doe, etc. I tried to use it on a BO BI 4.0 SP02 system, however, the output Excel file contains maximum 1000 rows although there are more files in the system. In the Import Data dialog box, select Add this data to the Data Model. You can change the limit using option TOP: e.g. The problem is, that the logo doesn't show up after exporting it to Excel. This is what the Query Builder application looks like in Business Objects and you can access it via this link: It is clear that Query Builder by itself isnt enough to be able to really take advantage of your SAP BusinessObjects metadata. Is it possible to list users who have refresh and save the report in Favorite Folders?. You can find a free trial available here. I am really curious to know if there is a way to query object SI_DESTINATIONS because it doesnt return values when i mention this in select objects. They are in the folder: [SAP BusinessObjects]\SAP BusinessObjects Enterprise XI 4.0\win64_x64\dotnet\iPoint, The libraries: On daily basis I monitor Web intelligence reports whose status is success in CMC Instance manager and then I have to calculate the time taken by each report from a column giving information about report refresh time and then I have to apply some maths in it manually(using calculator). We provide a library of WebI documents in order to efficiently query the metadata it aggregates. SP3 on a Windows 2003 server. Hi, I want to use the tool for BO XI R2. Hello Dmytro, thank very much for this valuable tool, its a pleasure to manipulate Infostore data in Excel, thanks to you ! To a Data Model. Is there some kind of a limit on the maximum number of returned rows in the tool? The link exists but the document in the FRS no longer exists or. Wasy. PublicKeyToken-692fbea5521e1304 or one of its dependencies. Security profiles,but not able to get the name of the each DATA Security Profile name from the query. Yes. Once you submit your query, results are shown on a web pagebut thats about as far as it goes. In Excel, select Data > Data & Connections > Queries tab, right click the query and select Properties, select the Definition tab in the Properties dialog box, and then select Edit Query. Dont wait, create your SAP Universal ID now! For example these scripts you've provided, don't give an actual name. Trademark, SAP BusinessObjects Business Intelligence platform 4.2 ; SAP Crystal Server 2016, How to export query results from Query Builder to a CSV file, SAP BusinessObjects Business Intelligence Platform 4.0, SAP BusinessObjects Business Intelligence Platform 4.1, SAP BusinessObjects Business Intelligence Platform 4.2, SAP BusinessObjects Business Intelligence Platform 4.3. Query Builder can be found at the below URL http://MyServer:Port/AdminTools. For 4.0 SP4 Patch 6 When I run a query likeselect si_name, si_cuid from ci_infoobjectsagainst my CMS database using the query tool,I don't see any objects that look like scheduled jobs / tasks. I noticed this behavior when you use this in Mac. Note that this information is stored in universe files in BO file repository, not in CMS. Anyone have a query for getting a list of scheduled recurring jobs for INFOSTEWARD? You can use the Query Builder only to retrieve universe and connections, prompts, etc. I would like to have the complie code for bi 4.0.. SELECT TOP 10000 * FROM ci_systemobjects. The second module, 360Eyes, allows you to go further and fill the gaps that Query Builder doesnt. Or you can select Home and then select a command in the New Query group. http://www.reportminer.com/products/rptminersuite.shtml. CrystalDecisions.Enterprise.Framework.dll Could you try to close all excel windows and maybe EXCEL processes? I will send you source code. Let me know if this resolves the issue. CI_INFOOBJECTS information about all kinds of documents (Webi, Crystall Reports, Txt, Doc etc.). you cannot actually build a query, you have to write the query manually. In the Power Query Editor, select Home > Close & Load > Close & LoadTo. Global settings that apply to all your workbooks. I am trying to find the right SQL to return the SI_User field contents from the SI_Prompts of Crystal Reports. WHERE SI_KIND = WEBI AND SI_INSTANCE = 0 AND SI_ANCESTOR = [SI_ID OF THE FOLDER]. Microsoft is aware of this problem and it is under investigation. However, the CMS DB Driver still remains limited in its usage and is very hard to use. system cannot find the file specified. I'm sorry, I have not found how you can find the information that is included when you create a user in the cmc. Ill be exploring further. Share Improve this answer Follow Is it possible to get the folder and all the sub folder under it. This will be handy for those who searching for Query Builder Queries. 1354397 - How to retrieve information of report instances using Query Builder? I tried Describe CI_INFOOJBECTS but Query builder doesn't like that. I realize that this is a lot to ask, but I have been trying to figure this out myself but without any luck! Implementing a third-party tool such as 360Suite will give you complimentary access to not only the same data as Query Builder (the System Database) but to both the Auditor and the FRS (file repository server), with the possibility to leverage this data by carrying out impact analyses or analyzing the usage and non-usage of objects within your environment. After entering the query and clicking on export to excel its giving a message that no application is associated with the specified file for this operation but when I am trying to open it from a diferent server its allowing to login and get the result of the previous server where its giving error. Simple queries to use against the repository, SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=USER, SELECT * FROM CI_APPOBJECTS WHERE SI_KIND=UNIVERSE, SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=WEBI, BusinessObjects Query builder Best practices & Usability, 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, BusinessObjects Environment assessment using Query builder, BusinessObjects Environment Cleanup using Query builder, BusinessObjects Query builder Whats New in BI 4.0.