Querying InfoObjects to get Paramater Details 1

[DallasAggie]

Okay all. This is going to be a tough one. I am banging my head against the wall to figure this out. Hopefully some of you have more experience doing this than I have.

I am developing a C#.Net Web application that takes advantage of Crystal Enterprise's InfoObjects methods -- by calling certain properties, I am wanting to dynamically create a custom paramater prompt to the end user.

Here is what I am trying to do:

--code start--------------------------------------
string sqlQ = "SELECT SI_PROCESSINFO.SI_PROMPTS FROM CI_INFOOBJECTS WHERE SI_ID=" + rp.Id;

reportParams = ceInfoStore.Query(sqlQ);
--code end----------------------------------------

from this query result I want to get the number of parameters the report has, the name of the parameter, the parameter type (e.g., date, string, etc.) and parameter prompt message.

I want to be able to write this information into a database for the report_id being passed into this query.

Ex.
Report ID Param# ParamName ParamType ParamPrompt
--------- ------ --------- --------- -----------
123 1 @auditName dropdown Please select..
123 2 @begindate date Select Date
123 3 @amount currency Enter $ amt.

etc...

When I enter that query (shown above) into "Query Builder" within the Crystal Entperise Launchpad I get a whole slew of information...

Check out this link...it has all the information I need, but I don't know how to get it

http://www.dallasaggie.com/work/query_builder.htm

Thanks in advance.
Dallasaggie

 

[Turkbear]

Hi,
The parameters, etc can be used in this way ( at least we use it this way)

Result = IStore.Query("Select SI_PROCESSINFO.SI_PROMPTS From CI_INFOOBJECTS Where SI_ID = " + ReportID);
ReportObject = Result.Item(1);
ReportInterface = ReportObject.PluginInterface("");
var reportPars = ReportInterface.ReportParameters;
param = reportPars.Item(i);
ParamName = param.ParameterName;

Using the Count (reportPars.Count) you can loop through the parameters to obtain the name and any defauult values..Look at the CE SDK for the prperties and collections you can access.

Hope it gives a start..

 

[mdwyer]

As a shortcut, take a look at rptschedule.csp (in v10; schedule.csp in v9). Do a search for the string "prompti." to find the many parameter attributes that are used in eportfolio. Also look at the variable npar to see where the number of parameters is retrieved from the object model.

 

[DallasAggie]

Turkbear / mdwyer:

Those are some great resources you provided. I have taken a little bit of both and have come up with the following (see code). I still have more to do to test it out, but I think this might do it...I know I probably have too much code, but I'm still an amatuer C# / Enterprise developer, so I'll have to clean it up when I learn more about it.

I'll post again with my final results and give the proper kudos

Thanks again...
DallasAggie

		public static void GetReportParameters(ReportProfile rp)
		{
			//Enterprise variables
			EnterpriseSession ceSession = getEnterpriseSession(rp.ReportServer);
			InfoStore ceInfoStore;
			EnterpriseService ceEnterpriseService;

			//Create the infostore object
			ceEnterpriseService = ceSession.GetService("", "InfoStore");
			ceInfoStore = new InfoStore(ceEnterpriseService);

			//declare InfoObjects collection to retrieve from the InfoStore
			CrystalDecisions.Enterprise.Desktop.Report ceReport;
			InfoObjects newReportParamCollection;
			InfoObject newReportParams;
			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
			//newReportParamCollection = ceInfoStore.NewInfoObjectCollection();
			//newReportParams = newReportParamCollection.Add(ceReportPlugin);

			string sqlQ = "SELECT SI_PROCESSINFO.SI_PROMPTS " +
						  "FROM CI_INFOOBJECTS " +
						  "WHERE SI_ID=" + rp.Id;

			newReportParamCollection = ceInfoStore.Query(sqlQ);
			newReportParams = newReportParamCollection.Add(ceReportPlugin);

			//Set the new report to the report object
			newReportParams.Files.Add(rp.Id);
			ceReport = new CrystalDecisions.Enterprise.Desktop.Report(newReportParams.PluginInterface);
			
			int numParams = ceReport.ReportParameters.Count;
			
			if(numParams > 0)
			{
				string test1 = "There are " + numParams + " Parameters in report __blah";
			}
	
			int i;
			for(i = 1; i <= numParams; ++i)
			{
				string reportParamName = ceReport.ReportParameters[i].ParameterName.ToString();
				string reportParamPrompt = ceReport.ReportParameters[i].Prompt.ToString();
				string reportParamType = ceReport.ReportParameters[i].ValueType.ToString();
			}

			ceSession.Logoff();
			return;
		}

 

[DallasAggie]

Forgot to post final outcome --- Here it is:: Works Like a Charm!!

Note: Enterprise Session is being from a return Session function. Also...this code doesn't have any try-catch blocks --- which is my next step

public static void GetReportParameters(ReportProfile rp)
{
	//Enterprise variables
	EnterpriseSession ceSession = getEnterpriseSession(rp.ReportServer);
	InfoStore ceInfoStore;
	EnterpriseService ceEnterpriseService;

	//Create the infostore object
	ceEnterpriseService = ceSession.GetService("", "InfoStore");
	ceInfoStore = new InfoStore(ceEnterpriseService);

	//declare InfoObjects collection to retrieve from the InfoStore
	CrystalDecisions.Enterprise.Desktop.Report ceReport;
	//InfoObjects newReportParamCollection;
	//InfoObject newReportParams;
	PluginManager cePluginMgr;
	PluginInfo ceReportPlugin;

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

	//Query CMS for the report object.
	InfoObjects ceInfoObjects = ceInfoStore.Query("SELECT * FROM CI_INFOOBJECTS WHERE SI_ID=" + rp.ExternalId);

	//Retrieve the first object in collection (only one exists) and convert the 
	//InfoObject into a Report object which allows access to set custom database credentials
	InfoObject ceInfoObject = ceInfoObjects[1];
	ceReport = (CrystalDecisions.Enterprise.Desktop.Report)ceInfoObject.GetPluginInterface("Report");


	
	//Checks to see the number of parameters the reports is expecting
	int numParams = ceReport.ReportParameters.Count;
	
	int i,j;

	//Runs through the parameters in the order expected and
	//pulls the parameter name, prompting message, and type
	ReportParameter[] reportParams = new ReportParameter[numParams];

	for(i = 1; i <= numParams; ++i)
	{
		reportParams[i-1] = new ReportParameter();
		reportParams[i-1].Name = ceReport.ReportParameters[i].ParameterName.ToString();
		reportParams[i-1].Prompt = ceReport.ReportParameters[i].Prompt.ToString();
		reportParams[i-1].Type = _getParameterType( ceReport.ReportParameters[i].ValueType);
		reportParams[i-1].MustSelectDefault = ceReport.ReportParameters[i].DisallowEditing;

		int numDefaultReportVals = ceReport.ReportParameters[i].DefaultValues.Count;				
		
		string[] defaultValues = new string[numDefaultReportVals];

		//for each param type, pull to see what the default values are set to in file
		for(j = 1; j <= numDefaultReportVals; ++j)
		{					
			defaultValues[j-1] = ceReport.ReportParameters[i].DefaultValues[j].MakeDisplayString();			
		}
		reportParams[i-1].DefaultValues = defaultValues;
	}
	ceSession.Logoff();

	rp.ReportParameters = reportParams;

	return;
}


private static ReportParameter.ParameterType _getParameterType(CeReportVariableValueType ceType)
{
	TypeCode tc = ceType.GetTypeCode();
	ReportParameter.ParameterType returnValue = ReportParameter.ParameterType.Unknown;
	string test = ceType.ToString();
	switch (ceType.ToString())
	{
		case "ceRVBoolean":
		{
			returnValue = ReportParameter.ParameterType.Boolean;
			break;
		}
		case "ceRVCurrency":
		{
			returnValue = ReportParameter.ParameterType.Currency;
			break;
		}
		case "ceRVDate":
		{
			returnValue = ReportParameter.ParameterType.Date;
			break;
		}
		case "ceRVTime":
		{
			returnValue = ReportParameter.ParameterType.Time;
			break;
		}
		case "ceRVDateTime":
		{
			returnValue = ReportParameter.ParameterType.DateTime;
			break;
		}				
		case "ceRVNumber":
		{
			returnValue = ReportParameter.ParameterType.Number;
			break;
		}
		case "ceRVString":
		{
			returnValue = ReportParameter.ParameterType.String;
			break;
		}
		
	}
	return( returnValue );
}

 

[cmmrfrds]

Can you post the code from the Session Function, that is the part I am missing in my understanding. Thank you.

 

[DallasAggie]

Here it is:

private static EnterpriseSession getEnterpriseSession(ReportServerProfile rs)
{
	//Enterprise variables
	SessionMgr ceSessionmgr = new SessionMgr();
	EnterpriseSession ceSession;

		ceSession = ceSessionmgr.Logon(rs.User, rs.Password, rs.Name, rs.AuthType);
		return ceSession;
}

Note: We are using a profile that stores the Crystal Enterprise logon information (ReportServerProfile)...you can replace it to:

getEnterpriseSession(string UserID, string Password, string APSName, sting AuthType)
.
.
.
ceSession = ceSessionmgr.Logon(UserID, Password, APSName, AuthType);

Hope that helps you out...

DallasAggie

 

[cmmrfrds]

Thank you, yes it does help.

Can you tell me what goes in rs.AuthType for Active Directory. In the example I have seen there is a constant, such as, secEnterprise which probably translates to a number. Do you happen to know what that number may be for AD.

 

[DallasAggie]

Its actually a string value that needs to get passed...in my case it was secEnterprise .... for Active Directory it's:

secWinAD

....Good luck

 

[cmmrfrds]

Perfect! Thank you.