Is there a way to get the stored procedure used in a crystal report...

[SteveMe]

and to store it in a table or text file. The reason for this is that we'd like to run a script that will go through 100+ reports and give the report name with the associated stored procedure being used. Thanks

Steve

PS - VB.net or vb script is fine or just a sample.

 

[DallasAggie]

I've done something similar where I'm pulling the reports name, id, user name, password, description, etc. I created a C#.NET application that takes advantage of Crystal Entperises SDK --- by making called to the InfoObjects object, you can grab vitually any value you want from Crystal Enterprise.

sample of how to query (NOTE: You cannot query using SQL)

C#.NET
//Retrieve the report to be deleted
ceReportObjects = ceInfoStore.Query("SELECT SI_ID FROM " +
				"CI_INFOOBJECTS WHERE " +
				"SI_PROGID = 'CrystalEnterprise.Report' " +
				"AND SI_ID=" + rp.ExternalId);

This is a function I have to upload a report to the Crystal Enterprise Server -- it then returns the title, description and id to the end user:

C#.NET
public static string CreateReport(string ParentFolderID, string reportFile,ReportProfile rp)
{
	string confirmedUpload = "";

	try
	{
		//Enterprise variables
		EnterpriseSession ceSession = getEnterpriseSession(rp.ReportServer);
		InfoStore ceInfoStore;
		EnterpriseService ceEnterpriseService;

		ceEnterpriseService = ceSession.GetService("", "InfoStore");
		ceInfoStore = new InfoStore(ceEnterpriseService);

		CrystalDecisions.Enterprise.Desktop.Report ceReport;

		InfoObjects newCollection;
		InfoObject newReport;
		PluginManager cePluginMgr;
		PluginInfo ceReportPlugin;

		//retrieve the plugin manager for reports
		cePluginMgr = ceInfoStore.PluginManager;
		ceReportPlugin = cePluginMgr.GetPlugins("Desktop")["CrystalEnterprise.Report"];

		//add a new report object to the reports collection
		newCollection = ceInfoStore.NewInfoObjectCollection();
		newReport = newCollection.Add(ceReportPlugin);

		//Set the new report to the report object
		newReport.Files.Add(reportFile,1);

		//Set the properties of the ID of the folder to add the report in
		newReport.Properties.Add("SI_PARENTID", (string)ParentFolderID);
		ceReport = new CrystalDecisions.Enterprise.Desktop.Report(newReport.PluginInterface);
		ceReport.EnableThumbnail = true;

		//commit the report to the InfoStore
		ceInfoStore.Commit(newCollection);
		confirmedUpload = "Report successfully added";
		rp.ExternalId = newReport.ID.ToString();
		rp.Name = newReport.Title.ToString();

		if (System.Convert.IsDBNull(newReport.Description.ToString()))
		{
			rp.Description = newReport.Description.ToString();
		}
		else
		{
			if (newReport.Description.Length > 0)
			{
				if (newReport.Description.Length >= 300) //300 Char max field in database
				{
					//truncate description to 296 characters and append '...' to the end.
					rp.Description = newReport.Description.ToString().Substring(0,296) + "...";
				}
				else
				{
					rp.Description = newReport.Description.ToString();
				}
			}
			else
			{
				//If no description, use Report Title with 
				//'Crystal Report' appended at end
				rp.Description = rp.Name + " Crystal Report";
			}
		}

		rp.TreeViewEnabled = false;
		rp.Enabled = false;
		ceSession.Logoff();
		return confirmedUpload;
	}

	catch (Exception err )
	{
		confirmedUpload = err.Message.ToString();
		return confirmedUpload;
	}
}

Hope this helps.