How to query the Crystal Enterprise db?

[ericb1]

I'm using CE10, and all I want to do is to be able to query the CE db and get back and display the report objects.

Any suggestions or sample code that I can look at? I've already got my token taken care of, so I should be able to just connect and display the ID, description, creator, etc.

All I need is some csp connection string and how to loop or display this info. Any help is greatly appreciated, thanks!

 

[synapsevampire]

One means is to use the Query Builder located on the left side of the Crystal Enterprise User Launchpad.

No CSP required.

-k

 

[ericb1]

I know, but there's too much other csp junk in there. And my query is always going to be the same. I just want to query the object, get the description, and display it.

I was looking for a quick way to just write out the query.

 

[Turkbear]

HI, Is is some code for a JavaScript function you can include is a csp page to create a nice HTML table with all the reports in the provided FolderID with their IDs and Descriptions and Titles. It should give you an idea of how to modify it to show what you want..

function RetrieveReports(ParentID ,IStore) 
{
//This returns an HTML table that is populated with the report name,
//report description, and the last time it was modified.

//Precondition:
//ParentID - The ID of the parent folder containing the reports to be retrieved.
//IStore - The InfoStore object required to interface with server.


//Postcondition:
//Returns an empty string if there are no reports, null if an error occurred, and
//the string if successful.

//Notes:
//The function returns a string that is an HTML table. 
	//The query that will select the reports.
	var Query;  
	//The result of the query.
	var Result;
	//A string to hold the HTML table.
	var HTMLTable; 
	HTMLTable="";

	//Create a query that selects all the reports, but doesn't select the instances.
	Query = "Select SI_NAME, SI_ID, SI_DESCRIPTION, SI_UPDATE_TS From CI_INFOOBJECTS Where " +
	"SI_PROGID='CrystalEnterprise.Report' And SI_INSTANCE=0 AND SI_PARENT_FOLDER=" + ParentID +
	"ORDER BY SI_DESCRIPTION";
		
		
	//Query the server.
	try {
		Result = IStore.Query(Query) ;
	}
	catch(e) {
		return null;
	}
	 
   
	if (Result.Count > 0) {
		//Set up the table header.
		HTMLTable="<TABLE width = \"800\" >" +
		"<TR><TH align='left'><B></B></TH>" +
		"<TH align='left'><B></B></TH></TR>"; 
				
		for (k=1;k<=Result.Count;k++)
		//Add the report name and details to the table.	
		{ 
		         if (Result.Item(k).Description == "") {
			var desc1 = Result.Item(k).Title;
			}
			else {
			var desc1 = Result.Item(k).Description;
			}
			var desc = Result.Item(k).Description
			//var descSize = desc.length - 1;
			//var descDisplay = desc.substr(3,descSize);
			HTMLTable=HTMLTable +
				"<TR  valign=top><TD>" + 
				"<A   class='tableLink' title='Report Name: " + Result.Item(k).Title + "' HRef='#' onClick=\"navReport('" + Result.Item(k).ID +"')\">" +
				Server.HTMLEncode(desc1) + "</A></TD>" ;
		} 
		HTMLTable=HTMLTable+"</TABLE>";
	}
	    
	return( HTMLTable);
}

The query is where changes can be made, most of the rest is used in our system to call reports ( the OnClick event handler does that).


.

 

[jraheja]

Use the link

http://yourserver/Crystal/Enterprise10/websamples/en/query/ie.csp

and then type in your admin user name and password and put in thsi query

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'.

You will get the list of reports. If you have them under folders then add si_parentid to your select parameters which will give you the parent folder id. You can add it to the where clause to retrieve only those id's. Enjoy!

 

[ericb1]

Thanks for the help. I'm new to this csp language and all the "istore" stuff, but all I'm trying to do is get the report description and the last modified date, and display it on a page.

I already know the SQL, the SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'

What I don't know is the rest, like in ASP it's

connection string
object
execute SQL

then you can display each object in the collection.

All I have so far is the iStore info: here's my csp page so far:

<%
Function RetrieveIStore(IStore)
'Precondition:
'None
'
'Postcondition:
'IStore - Returns the InfoStore object that allows us to query the server
'
'The function returns TRUE if successful and FALSE otherwise.
On Error Resume Next

Dim LogonToken
Dim SessionManager
Dim Sess

'Check to see if the cookie is there.
If( Request.Cookies("LogonToken") <> "" ) Then

'Check to see if the InfoStore already exists
If( TypeName(Session("IStore")) <> "ISInfoStore" ) Then

'Retrieve the cookie
LogonToken = Request.Cookies("LogonToken")

'Create a session manager
Set SessionManager = Server.CreateObject("CrystalEnterprise.SessionMgr")

'Logon using the token. This may fail if the token is no longer valid.
Set Sess = SessionManager.LogonWithToken(LogonToken)

'Create the InfoStore object.
Set IStore = Sess.Service("", "InfoStore")

'Save the InfoStore in the session.
Session("IStore") = IStore

RetrieveIStore = TRUE
Else
'The InfoStore already exists so simply retrieve it from
'the session.

Set IStore = Session("IStore")
RetrieveIStore = TRUE
End If
Else
RetrieveIStore = FALSE
End If
End Function
%>

Here's where I want to connect to the db and display the info.

Any help is appreciated, thanks!

 

[mdwyer]

Here's the first piece of code I came across on the subject. It deals with folders instead of reports, but you'll substitute your query and variable names. This code uses MyInfoStore for your Istore.

'Query the APS for folder info, using a collection (it should only contain one folder).
Dim MyQuery
Dim MyFoldersCol
Dim MyFolder
MyQuery = "Select SI_ID From CI_INFOOBJECTS Where SI_ID=" & MyFolderID
[b]Set MyFoldersCol = MyInfoStore.Query(MyQuery)[/b]
If err.number <> 0 then 
	Response.Write "There was an error creating the Folders Collection." 
End if
Set MyFolder = MyFoldersCol.item(1)
If err.number <> 0 then
	Response.Write "There was an error creating the Folder."
End if