There have been many requirements about getting the full folder location of all the reports(crystal/webi/deski) in the enterprise system. The information can be figured out using the query builder by executing multiple queries but it becomes time consuming as well as difficult when this information has to be retrieved for a large number of reports.
Using Query Builder
Using the query builder(AdminTools Application), and execute the below query
SELECT SI_ID,SI_NAME,SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_KIND IN(‘cryatalReport’,’Webi’,’Deski’) AND SI_INSTANCE=0, you get to list the id, name and parentId of all the crystal/webi/deski reports. The SI_PARENTID property bag contains the id of the object which is parent for a particular report. In basic scenarios, the parent of a report can be a folder, objectpackage, FavoritesFolder or an Inbox.
Again executing the below query in the query builder you can get the name of the parent.
SELECT SI_ID,SI_NAME,SI_PATH FROM CI_INFOOBJECTS WHERE SI_ID=<Parent-Id retrieved from the previous query>
If the above query returns a folder(SI_KIND=Folder), then the SI_PATH property bag contains the hierarchy of folders. i.e it contains the name of the folders this object belongs to. The SI_PATH property bag has a sub property bag “SI_NUM_FOLDERS” which contains a numeric value and it lets you know the number of folders this partcular folder has as its parents in hierarchy.
Consider a report is inside an objectPackage, then the parent of the report becomes an object package who inturn has a parent as a folder.
Thus using the query builder, this becomes a two step query.
First, to get the parentid of the report, then to get the name of the object package and get the parent id of the object package and then again execute a query to get the folder name and path.
Using BusinessObjects Enterprise SDKs
The below code lists folder path of all the reports(crystal/webi) in the enterprise system.
Note:
For other scripts and information on how to run these scripts see here:
Scripts and Samples
| Get Folder Path |
|
<%@ page import=”com.crystaldecisions.sdk.properties.*” %>
<%@ page import=”com.crystaldecisions.sdk.framework.*” %>
<%@ page import=”com.crystaldecisions.sdk.occa.infostore.*” %>
<%@ page import=”com.crystaldecisions.sdk.plugin.desktop.folder.*” %>
<%@ page import=”java.util.*” %>
<html>
<head></head>
<body>
<table border=”2″ cellpadding=”0″ style=”border-collapse: collapse” bordercolor=”#111111″ width=”100%” id=”AutoNumber1″>
<tr>
<th width=”30%”>Report Name</th>
<th width=”10%”>Report Id</th>
<th width=”40%”>Folder Path </th>
</tr>
<%
IEnterpriseSession es=null;
try
{
String CMS = “localhost:6400”; //CMS Name
String UserID = “Administrator”; //Administrator User Account
String Password = “”; //Administrator Password
ISessionMgr sm = CrystalEnterprise.getSessionMgr();
es = sm.logon(UserID, Password, CMS,”secEnterprise”);
IInfoStore iStore = (IInfoStore)es.getService(“”,”InfoStore”);
String sq = “select si_id,si_name,si_parentid from ci_infoobjects where si_kind in (‘CrystalReport’,’Webi’) and si_instance=0”;
IInfoObjects iObjects = iStore.query(sq);
IInfoObject iObject = null;
for(int i=0;i<iObjects.size();i++)
{
iObject = (IInfoObject)iObjects.get(i);
out.print(“<tr><td>” + iObject.getTitle() + “</td><td>” + iObject.getID() + “</td>”);
IProperties prop = iObject.properties();
IProperty getProp = prop.getProperty(“SI_PARENTID”);
String FolderID = getProp.toString();
IInfoObjects folder = iStore .query(“select si_id,si_name,si_parentid,si_path from ci_infoobjects where si_id=” + FolderID);
IInfoObject ifolder=(IInfoObject)folder.get(0);
if(ifolder.getKind().equals(“Folder”))
{
IFolder iifolder=(IFolder)ifolder;
String finalFolderPath=””;
if(iifolder.getPath()!= null)
{
String path[]=iifolder.getPath();
for(int fi=0;fi<path.length;fi++)
{
finalFolderPath = path[fi] + “/” + finalFolderPath;
}
finalFolderPath = finalFolderPath + iifolder.getTitle();
}
else
{
finalFolderPath=finalFolderPath+iifolder.getTitle();
}
out.println(“<td>” + finalFolderPath + “</td></tr>”);
}
else if((ifolder.getKind().equals(“FavoritesFolder”)))
{
out.println(“<td><b>FavoritesFolder</b> :: ” + ifolder.getTitle() + “</td></tr>”);
}
else if((ifolder.getKind().equals(“Inbox”)))
{
out.println(“<td><b>Inbox</b> :: ” + ifolder.getTitle() + “</td></tr>”);
}
else if((ifolder.getKind().equals(“ObjectPackage”)))
{
//out.println(“<td><b>ObjectPackage</b> :: ” + ifolder.getTitle() + “</td></tr>”);
IProperties prop1 = ifolder.properties();
IProperty getProp1 = prop1.getProperty(“SI_PARENTID”);
String FolderID1 = getProp1.toString();
IInfoObjects folder1 = iStore .query(“select * from ci_infoobjects where si_id=” + FolderID1);
IInfoObject ifolder1=(IInfoObject)folder1.get(0);
if(ifolder1.getKind().equals(“Folder”))
{
IFolder iifolder1=(IFolder)ifolder1;
String finalFolderPath1=””;
if(iifolder1.getPath()!= null)
{
String path[]=iifolder1.getPath();
for(int j=0;j<path.length;j++)
{
finalFolderPath1= path[j] + “/” + finalFolderPath1;
}
finalFolderPath1 = finalFolderPath1 + iifolder1.getTitle()+”/”+ifolder.getTitle();
}
else
{
finalFolderPath1=finalFolderPath1+iifolder1.getTitle()+”/”+ifolder.getTitle();
}
out.println(“<td>” + finalFolderPath1 + “</td></tr>”);
}
}
}
}
catch(Exception e)
{
out.println(e);
}
finally
{
if(es !=null)
{
es.logoff();
}
}
%>
|
Nice Prithviraj. I will be refer this.
Regards,
Vijay
Hi. I'm trying to execute this query with QUeryBuilder, in order to obtain the parent's name, but I produces a query error. What's wrong with my query?
SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS,
SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES,
SI_PROCESSINFO.SI_PROMPTS , (SELECT x.SI_NAME FROM CI_INFOOBJECTS x WHERE x.SI_ID= CI_INFOOBJECTS.SI_PARENTID) as t FROM CI_INFOOBJECTS
WHERE SI_KIND = 'CrystalReport'
Excellent! Thanks a lot for sharing Prithvi. I have tried your code however I am getting only 1000 results by default, how can get all the report details?
Hi Sohel,
Thanks for your feedback.
As you are aware by default any query running on the infostore returns 1000 results(provided we have not modified registry entries), be it from query builder or through sdk is the reason for this.
You would need to change the below query above
String sq = "select si_id,si_name,si_parentid from ci_infoobjects where si_kind in ('CrystalReport','Webi') and si_instance=0";
Use sql top fuction in the query.
For example you need to get data about 5000 reports so the query would become
String sq = "select top 5000 si_id,si_name,si_parentid from ci_infoobjects where si_kind in ('CrystalReport','Webi') and si_instance=0";
Thanks,
Prithvi
Thank you Prithvi, I actually tried that but it was throwing java exception but then I realized there was a typo 🙂
Hi experts,
Could someone let me know to use above script.
1. Do I need to copy script any location on BOE server
2. How to use it
Thanks in advance
You can refer to the blog by Shawn, it has mentions the ways how you can execute the script.
For other scripts and information on how to run these scripts see here:
http://scn.sap.com/people/shawn.penner/blog/2013/06/04/scripts-and-samples
Thanks for reply
I would like to add one more column to pull BEX query name in same report.
Can you help me out to edit script.
Thanks in advance
That's not a simple task and how you do it depends on the SI_KIND of the report and whether the BEx query is accessed through a universe.
I highly suggest using QueryBuilder to look at all of the properties of a couple of reports of each kind to determine which property contains the name of the query. There is a possibility that the name is not in the information in the InfoObject! If it is there, you can then update the code to get the information from that property. If it isn't, how you get it will depend on which version of BO you're using (Webi in 3.x is different from Webi in 4.x) may or may not be possible.
-Dell
Hi Dell,
Thanks for reply.
We get query name under SI_WEBI_DOC_PROPERTIES .
I am trying to pull report name, location and query all together.
Now through above sdk we are getting report name and location and by using SELECT SI_NAME, SI_PARENT_FOLDER, SI_WEBI_DOC_PROPERTIES FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi' we are getting report name and query. Its getting double work to me to club both reports into one report .
So only I am trying to get all in one execution.
We are on 4.0 sp4
I'm not sure what you mean by "Its getting double work to me to club both reports into one report ."
If the information is in SI_WEBI_DOC_PROPERTIES, you should be able to get the value by going to the .properties of the IInfoObject. I would just add the code that gets that info to the existing code from above to display the value.
-Dell
Hey,
Have you wrote any code for pulling Bex query name into the report.
Regrads,
Tulika
We have a lot of reports that are configured to save output to a network folder. The file server we have been using is being replaced, so I need to get a list reports and the locations that the reports are set to save report output to. Prefer to use the SDK to find these reports and programmatically update the output location to the new folder. Seems like this is close to providing what I need, but I have not been able to find the right property. Appreciate any help.
Are you referring to scheduling of reports? A bit more insight might help.
- Prithvi
Yes - scheduled reports that save output to a network location. Typically these are reports that are used for audits or maybe even as input to another application.
To get to the file destination, do the following:
1. Get an IInfoObjects based on this query: Select SI_ID, SI_NAME, SI_SCHEDULE_STATUS, SI_SCHEDULEINFO from CI_INFOOBJECTS where SI_INSTANCE = 1 and SI_SCHEDULE_STATUS in (8,9) order by SI_ID.
NOTE: SI_SCHEDULE_STATE 8 = Paused and 9 = Pending/Recurring
2. For each IInfoObject in IInfoObjects, get the ISchedulingInfo. From the ISchedulingInfo, get the IDestinations.
3. For each IDestination in the IDestinations, call getName() to get the destination type. I
4. If the destination type is not "CrystalEnterprise.DiskUnmanaged", skip the destination. Otherwise get the IProperties of the IDestination.
5. Get the "SI_DEST_SCHEDULEOPTIONS" set of properties from the Destination's IProperties. If the resulting IProperties is not null, get the "SI_OUTPUT_FILES" set of properties.
6. If the SI_OUTPUT_FILES properties is not null, use its "SI_TOTAL" property to get the number of files listed. The files in this properties set will be listed under properties named "1" through the number in SI_TOTAL.
7. You can then walk through the set of files looking for the specific path (NOTE: the back-slashes in the path to the file have been replaced by forward slashes when the file name was stored in the database). If you find the path, you can updated it and set the property to the new path.
8. Be sure to call "IInfoObject.save()" after you update the file name(s) for each schedule.
-Dell
This looks like it is exactly what I need. I will give it a try and let you know how it works for me. Thank you for your time in helping with this. 😎
Many thanks to Dell for his *huge* help on this. Using his notes, I was able to put this c# method together and it works like a charm.
query to get scheduled report objects to work on:
"SELECT TOP 100000 * FROM ci_infoobjects WHERE SI_RUNNABLE_OBJECT = 1 AND SI_RECURRING = 1";
// method to update output file folder server name
private void UpdateDestinationFolder()
{
InfoObjects myInfoObjects = _InfoStore.Query(BOQUERY_ScheduledReport.ToString());
// in dev/test environment need the ability to run multiple times to validate with Business Analysts
// the bAllowSwitchback flag allows quickly switching between oldservername and newservername and back
const bool bAllowSwitchback = true; // when true, reports that have been set to newservername will get switched back to oldservername
const bool bSavechanges = true; // when true, changes are saved, when false changes are discarded
var n = 0; // keep track of how many reports have been processed for debugging
int fn = 0; // keep track of how many reports have been changed
// string array to easily see the changes being made
string[,] filenames = new string[200, 3]; // array to help with visualizing changes being made while debugging
foreach (InfoObject myInfoObject in myInfoObjects)
{
String myReportName = myInfoObject.Title; // only need this for debugging
//Loop through all scheduled reports and correct the SI_Destination
//for those reports that are pointing to a folder on the <oldservername>
SchedulingInfo mySchedInfo = myInfoObject.SchedulingInfo;
try
{
if (
mySchedInfo.Properties["SI_DESTINATIONS"].Properties["1"].Properties["SI_PROGID"].ToString()
.ToLower() == "crystalenterprise.diskunmanaged")
{
var outputfilecount =
Convert.ToInt16(mySchedInfo.Properties["SI_DESTINATIONS"].Properties["1"].Properties["SI_DEST_SCHEDULEOPTIONS"]
.Properties["SI_OUTPUT_FILES"].Properties[1].Value);
if (outputfilecount > 0)
{
for (int i = 2; i <= outputfilecount+1; i++)
{
String outputfilename =
mySchedInfo.Properties["SI_DESTINATIONS"].Properties["1"].Properties["SI_DEST_SCHEDULEOPTIONS"]
.Properties["SI_OUTPUT_FILES"].Properties[i].Value.ToString().ToLower();
String newfilename=outputfilename;
if (outputfilename.Contains("//oldservername/"))
{
newfilename = outputfilename.Replace("//oldservername/", "//newservername/");
}
if (outputfilename.Contains("//newservername/") && bAllowSwitchback)
{
newfilename = outputfilename.Replace("//newservername/", "//oldservername/");
}
if (outputfilename!=newfilename)
{
mySchedInfo.Properties["SI_DESTINATIONS"].Properties["1"].Properties["SI_DEST_SCHEDULEOPTIONS"]
.Properties["SI_OUTPUT_FILES"].Properties[i].Value = newfilename;
// the following helped with debugging - I can examine the filenames array to see the cummulative changes
filenames[fn, 0] = myReportName;
filenames[fn, 1] = outputfilename;
filenames[fn, 2] = newfilename;
fn = fn + 1;
}
}
}
}
}
catch(Exception ex)
{
if (ex.Message != "Item SI_PROGID was not found in the collection.")
throw ex;
}
n = n + 1;
}
//Commit the changes back
for (int i = 0; i < 2000; i++)
{
if(filenames[i,0]!=null)
Console.WriteLine("Report {0} was {1} is now {2}", filenames[i, 0], filenames[i, 1], filenames[i, 2]);
}
if (bSavechanges)
_InfoStore.Commit(myInfoObjects);
}
The one change I would make would be to change this:
InfoObjects myInfoObjects = _InfoStore.Query(BOQUERY_ScheduledReport.ToString());
to this:
using (InfoObjects myInfoObjects = _InfoStore.Query(BOQUERY_ScheduledReport.ToString())
{
<rest of code>
}
At a very low level, the .NET components rely on COM. .NET does not memory manage COM objects very well. So, you have to either explicitly call .Dispose() on many of the objects from the SDK or you have to put them in Using clauses or For loop so that they are automatically disposed. If you don't, you will have memory leaks.
-Dell
Very good point - I will add that to my code.
Thanks again!
@dell.stinnett-christy - Hi Dell please can you help me run the above code? I'm very new to SDK and would like to invoke this code to get the folder locations of my Live reports. Where should I be running this script in? Doesn't look like its in Query Builder?
Are you working in Java or in .NET? How you run it is specific to which one you're using.
-Dell
Hi
Please can you give me instructions on where to run this code to get the results? I'm very new to SDK and not sure how to get the results.
Hey guys, Im trying to run this script in my 4.0 SP8 system but getting the following exception. I have already copied all jar files to /web-inf/lib on my tomcat server. Any ideas what i could be doing wrong ?
message
description The server encountered an internal error () that prevented it from fulfilling this request.
exception
org.apache.jasper.JasperException: Unable to compile class for JSP:
An error occurred at line: 33 in the jsp file: /dash.jsp
IProperties cannot be resolved to a type
30: {
31: iObject = (IInfoObject)iObjects.get(i);
32: out.print("<tr><td>" + iObject.getTitle() + "</td><td>" + iObject.getID() + "</td>");
33: IProperties prop = iObject.properties();
34: IProperty getProp = prop.getProperty("SI_PARENTID");
35: String FolderID = getProp.toString();
36: IInfoObjects folder = iStore .query("select si_id,si_name,si_parentid,si_path from ci_infoobjects where si_id=" + FolderID);
An error occurred at line: 34 in the jsp file: /dash.jsp
IProperty cannot be resolved to a type
31: iObject = (IInfoObject)iObjects.get(i);
32: out.print("<tr><td>" + iObject.getTitle() + "</td><td>" + iObject.getID() + "</td>");
33: IProperties prop = iObject.properties();
34: IProperty getProp = prop.getProperty("SI_PARENTID");
35: String FolderID = getProp.toString();
36: IInfoObjects folder = iStore .query("select si_id,si_name,si_parentid,si_path from ci_infoobjects where si_id=" + FolderID);
37: IInfoObject ifolder=(IInfoObject)folder.get(0);
An error occurred at line: 68 in the jsp file: /dash.jsp
IProperties cannot be resolved to a type
65: else if((ifolder.getKind().equals("ObjectPackage")))
66: {
67: //out.println("<td><b>ObjectPackage</b> :: " + ifolder.getTitle() + "</td></tr>");
68: IProperties prop1 = ifolder.properties();
69: IProperty getProp1 = prop1.getProperty("SI_PARENTID");
70: String FolderID1 = getProp1.toString();
71: IInfoObjects folder1 = iStore .query("select * from ci_infoobjects where si_id=" + FolderID1);
An error occurred at line: 69 in the jsp file: /dash.jsp
IProperty cannot be resolved to a type
66: {
67: //out.println("<td><b>ObjectPackage</b> :: " + ifolder.getTitle() + "</td></tr>");
68: IProperties prop1 = ifolder.properties();
69: IProperty getProp1 = prop1.getProperty("SI_PARENTID");
70: String FolderID1 = getProp1.toString();
71: IInfoObjects folder1 = iStore .query("select * from ci_infoobjects where si_id=" + FolderID1);
72: IInfoObject ifolder1=(IInfoObject)folder1.get(0);
Stacktrace:
org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:92)
org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:330)
org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:439)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:356)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:334)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:321)
org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:592)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
Check if you have cecore.jar under WEB-INF/lib of your application.
Thanks,
Prithvi
Yes, its there
Verify you have copied the below import statement in your code
<%@ page import="com.crystaldecisions.sdk.properties.*" %>
If yes, re-build your application and test. And make sure you have your jars in the application's lib rather than tomcat's lib.
Guys,
I am unable to access query builder Url, I tried http://[Myserver]:[Serverport]/AdminTools/ but got webpage unavailable error.
Does it require to perform some activities to enable query builder ?
Regards,
Mayank
AdminTools is a web application which comes with BusinessObjects installation. Check whether you have the application deployed on the server(tomcat by default).
Also check if you are hitting the right server.
Yes, I am able to access CMCAPP and INFOVIEW app. My website is hosted on IIS. . Can you please tell where to find this on IIS.
Regards,
Mayank
Its a java web app and you do not have it installed on IIS.
how do we get this result set converted to an excel format, please help me. reason I have too many reports and currently I need to add each record manually.
I usually export to .csv file instead of Excel - they can be opened in Excel and you don't run into the same types of limits as exporting to Excel.
-Dell
Thanks Prithviraj for your script, it's very useful!!
Can I ask you to improve this script adding some files info (for ex: file size, file path, file name from SI_FILES)
Thank you so much in advance
Thanks for the suggestions. I would definitely try to extend this blog with further details when I have sometime.
Thanks,
Prithvi
Hi Prithviraj,
When I schedule a webi report, and choose "Default Enterprise Location" as destination, does your solution work for this scenario?
Thanks,
Sarah
Hi Sarah,
This works for reports in public folders and not for report instances.
By few modifications this can be used to get the details for instances as well. I already have that code, I would need to check for it and will share.
Thanks,
Prithvi
You can refer to the below document
Java Enterprise BE 14 Get Recurring Instance Information
Its for recurring instance information. Change the first query in the code to as shown below.
IInfoObjects infoobjects = iStore .query("SELECT * from CI_INFOOBJECTS WHERE si_instance=1 ");
Hi Prithvi,
Thank you very much for the share:)
Best regards,
Sarah
Hi Prithvi,
I got this code working.
I want to have one more column SI_UPDATE_TS added in the output, could you please help me to achieve this.
Regards,
Marven
You can use the below line.
String update_TS_Prop= prop.getProperty("SI_UPDATE_TS").getValue().toString();
You can then print the update_TS_Prop with out.println() statement.
Thanks,
Prithvi
Thanks a lot, i got this working.
Great!!
Hi Prithviraj, I am trying to use the java code you provided to pull the required list of reports and folder structure but the link you provided for more information around this process seems to be broken.
Can you provide me some more information on how to run the java code, so I can send on instructions to my IT department to run and get the required information I need.
Many thanks in advance
Joel
It seems there were some changes done to the page in the link earlier. I have updated the blog with the new link. You can refer to it now.
Thanks,
Prithvi
Hi Joel,
If you are on BI 4.x please create a new web application as below so you can execute the code:
- Go to Tomcat Installation Directory. (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6)
- Now go to webapps folder in tomcat directory (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\Webapps).
- Create a new application folder within webapps (ex. Test)
- Create a new folder named ‘WEB-INF’ within ‘Test’ Folder. (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\Test\WEB-INF)
- Copy the ‘lib’ folder from the mentioned location: (C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\lib) and paste it to (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\Test\WEB-INF \lib)
- Place your all jsp files at the following location (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\Test\index.jsp)
- Restart tomcat and run the code in browser (http:\\localhost:8080\Test\index.jsp).
Above steps are for BI on windows if you are n Linux then you will get those jar files in parallel directory structure.
Regards,
Kedar Kale.