Print Page | Close Window

Problem with sub report linked parameter

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=820
Printed Date: 03 May 2024 at 2:24am


Topic: Problem with sub report linked parameter
Posted By: andyoneill
Subject: Problem with sub report linked parameter
Date Posted: 08 Jun 2007 at 2:57am
I have a report and within that a sub-report.
Both are based on stored procedures which accept parameters.
In the link for the sub report I set it so the two parameters are linked.
I named them differently to avoid confusion - @Risk_Id_Parm is the main report parameter and @Risk_Id_Sub is the subreport parameter.
 
When I run against the database I developed against my code is fine.
When I change to point at another database I get an error telling me the subreport parameter is required.
Okaydoke... so I try and set the sub report parameter explicitly.
I get an error telling me the index is out of range ( the parameter ain't available for setting ).
Now, I can manually set the report to the live server at delivery so this isn't a huge problem for me right now but I'm doing something wrong.
I have patched the machine with the most recent hot fixes.
 
Any ideas?
================
 

ReportDocument rd, rdsub;

rd = new ReportDocument();

rd.Load(Server.MapPath("Reports\\Risk_Assessment.rpt"), CrystalDecisions.Shared.OpenReportMethod.OpenReportByTempCopy);

// Switch database

Sections crSections;

SubreportObject crSubreportObject;

ReportObjects crReportObjects;

Database crDatabase;

Tables crTables;

TableLogOnInfo crTableLogOnInfo;

crDatabase = rd.Database;

crTables = crDatabase.Tables;

ConnectionInfo crConn = new ConnectionInfo();

crConn.ServerName = DB.ServerString;

crConn.DatabaseName = "RiskMan";

crConn.UserID = DB.ServerUser;

crConn.Password = DB.ServerPassword;

crConn.IntegratedSecurity = false;

foreach (CrystalDecisions.CrystalReports.Engine.Table aTable in crTables)

{

Trace.Warn("Table is: " + aTable.Name);

crTableLogOnInfo = aTable.LogOnInfo;

crTableLogOnInfo.ConnectionInfo = crConn;

aTable.ApplyLogOnInfo(crTableLogOnInfo);

aTable.Location = crConn.DatabaseName + ".dbo." + aTable.Location.Substring(aTable.Location.LastIndexOf(".") + 1);

}

// THIS STUFF HERE IS FOR REPORTS HAVING SUBREPORTS

// set the sections object to the current report's section

crSections = rd.ReportDefinition.Sections;

// loop through all the sections to find all the report objects

foreach (Section crSection in crSections)

{

crReportObjects = crSection.ReportObjects;

//loop through all the report objects in there to find all subreports

foreach (ReportObject crReportObject in crReportObjects)

{

if (crReportObject.Kind == ReportObjectKind.SubreportObject)

{

crSubreportObject = (SubreportObject)crReportObject;

//open the subreport object and logon as for the general report

rdsub = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

crDatabase = rdsub.Database;

crTables = crDatabase.Tables;

foreach (CrystalDecisions.CrystalReports.Engine.Table aTable in crTables)

{

Trace.Warn("sub Table is: " + aTable.Name);

crTableLogOnInfo = aTable.LogOnInfo;

crTableLogOnInfo.ConnectionInfo = crConn;

aTable.ApplyLogOnInfo(crTableLogOnInfo);

aTable.Location = crConn.DatabaseName + ".dbo." + aTable.Location.Substring(aTable.Location.LastIndexOf(".") + 1);

}

Trace.Warn(rdsub.Name);

}

}

}

//=======================

int i = rd.DataDefinition.ParameterFields.Count;

Trace.Warn("Number of report parameters:" + Convert.ToString(i));

Trace.Warn(rd.DataDefinition.ParameterFields[0].Name);

Trace.Warn(rd.DataDefinition.ParameterFields[0].ParameterFieldName);

Trace.Warn(rd.DataDefinition.ParameterFields[0].FormulaName);

 ParameterFieldDefinition paramField;

ParameterValues currentValues;

ParameterValues defaultValues;

paramField = rd.DataDefinition.ParameterFields["@Risk_Id_Parm"];

discreteParam.Value = Session["Default_Risk"];

currentValues = paramField.CurrentValues;

currentValues.Add(discreteParam);

paramField.ApplyCurrentValues(currentValues);

ParameterFieldDefinitions parameterFieldDefinitions = rd.DataDefinition.ParameterFields;

ParameterFieldDefinition parameterFieldDefinition = parameterFieldDefinitions["@Risk_Id_Sub", "Risk_Assessment_Sub.rpt"];

// Above gives out of range exception
 
parameterFieldDefinition.CurrentValues.Clear();

parameterFieldDefinition.CurrentValues.Add(parameterFieldDefinition);

parameterFieldDefinition.ApplyCurrentValues(parameterFieldDefinition.CurrentValues);

crvRisk.ReportSource = rd;

crvRisk.DisplayGroupTree = false;


Replies:
Posted By: andyoneill
Date Posted: 12 Jun 2007 at 2:57am

I have spent quite some effort trying to get round this.  In the end I removed the link between the main report and sub report parameters.  This then allows me to provide the parameter values to the viewer.  And it makes the problem go away. 

I use the viewer instead of the sub report because I had problems setting the parametr to the sub report and with the viewer I can just use the numeric index of parameters.
 
The underlying problem is therefore that pointing at a different database from design time must also need some change to the link.  So my method is far from ideal but delivery date was yesterday...
 
My code now:
 

// Note

// There is an outstanding issue with this code

// I had to break the link between the subreport and main report to get it to work

ReportDocument rd, rdsub;

rd = new ReportDocument();

rd.Load(Server.MapPath("Reports\\Risk_Assessment.rpt"), CrystalDecisions.Shared.OpenReportMethod.OpenReportByTempCopy);

 

// Switch database

Sections crSections;

SubreportObject crSubreportObject;

ReportObjects crReportObjects;

Database crDatabase;

Tables crTables;

TableLogOnInfo crTableLogOnInfo;

crDatabase = rd.Database;

crTables = crDatabase.Tables;

ConnectionInfo crConn = new ConnectionInfo();

crConn.ServerName = DB.ServerString;

crConn.DatabaseName = "RiskMan";

crConn.UserID = DB.ServerUser;

crConn.Password = DB.ServerPassword;

crConn.IntegratedSecurity = false;

foreach (CrystalDecisions.CrystalReports.Engine.Table aTable in crTables)

{

crTableLogOnInfo = aTable.LogOnInfo;

crTableLogOnInfo.ConnectionInfo = crConn;

aTable.ApplyLogOnInfo(crTableLogOnInfo);

//aTable.Location = crConn.DatabaseName + ".dbo." + aTable.Location.Substring(aTable.Location.LastIndexOf(".") + 1);

}

// THIS STUFF HERE IS FOR REPORTS HAVING SUBREPORTS

// set the sections object to the current report's section

crSections = rd.ReportDefinition.Sections;

// loop through all the sections to find all the report objects

foreach (Section crSection in crSections)

{

crReportObjects = crSection.ReportObjects;

//loop through all the report objects in there to find all subreports

foreach (ReportObject crReportObject in crReportObjects)

{

if (crReportObject.Kind == ReportObjectKind.SubreportObject)

{

crSubreportObject = (SubreportObject)crReportObject;

//open the subreport object and logon as for the general report

rdsub = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

crDatabase = rdsub.Database;

crTables = crDatabase.Tables;

foreach (CrystalDecisions.CrystalReports.Engine.Table aTable in crTables)

{

crTableLogOnInfo = aTable.LogOnInfo;

crTableLogOnInfo.ConnectionInfo = crConn;

aTable.ApplyLogOnInfo(crTableLogOnInfo);

//aTable.Location = crConn.DatabaseName + ".dbo." + aTable.Location.Substring(aTable.Location.LastIndexOf(".") + 1);

}

}

}

}

crvRisk.ReportSource = rd;

ParameterFields crParameterFields;

ParameterField crParameterField;

ParameterValues crParameterValues;

ParameterDiscreteValue crParameterDiscreteValue;

//Get the collection of parameters from the report

crParameterFields = crvRisk.ParameterFieldInfo;

crParameterField = crParameterFields[0];

crParameterValues = crParameterField.CurrentValues;

crParameterDiscreteValue = new ParameterDiscreteValue();

crParameterDiscreteValue.Value = Session["Default_Risk"];

crParameterValues.Add(crParameterDiscreteValue);

crParameterDiscreteValue = null;

crParameterField = crParameterFields[1];

crParameterValues = crParameterField.CurrentValues;

crParameterDiscreteValue = new ParameterDiscreteValue();

crParameterDiscreteValue.Value = Session["Default_Risk"];

crParameterValues.Add(crParameterDiscreteValue);

//Set the modified parameters collection back to the viewer so that

//the new parameter information can be used for the report.

crvRisk.ParameterFieldInfo = crParameterFields;

crvRisk.DisplayGroupTree = false;

Posted By: BrianBischof
Date Posted: 12 Jun 2007 at 8:46am
Thanks for the update. I haven't seen this problem before so I'm going to bookmark it and see if I can figure out why its happening when I get a chance.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: andyoneill
Date Posted: 13 Jun 2007 at 5:27am
A more elegant solution would be nice.
Presumably the link between the two reports is "brole" when switching database.
I was intending exploring how that part of the object model worked if my adopted work-round failed.

Posted By: Paranoia
Date Posted: 07 Aug 2007 at 2:34am
Hi
 
Is there still no solution for this problem? The work around is nice, but i need to set one subreport parameter at runtime - it depends on the result records of the main report (the subreport is in the detail section). So, i can't set the parameters the way you described above.
 
I hope, you can help me or give me some hints to solve my problem Wink

Posted By: andyoneill
Date Posted: 18 Sep 2007 at 7:06am

I found another problem moving databases or something. I forget now.

Having spent several days trying to fix the problem in the end I spent half a day changing to the push method.
So I extract to a datatable for the report and another for the subreport and present the data to the report objects.
This works and I understand datatables far better than I want to have to understand the intricacies of crystal.
So that's now my default way to go.
The two fiddly bits are getting tihe subreport object and naming the datatables.
The datatable name must exactly match what you design against - stored procedures in my case.
 
Sorry for not getting back earlier I forgot completely about this forum for some time.
 
=================
 

Sections crSections;

SubreportObject crSubreportObject;

ReportObjects crReportObjects;

ConnectionInfo crConn = new ConnectionInfo();

ReportDocument rd;

ReportDocument rdsub;

rd = new ReportDocument();

rdsub = new ReportDocument();

rd.Load(Server.MapPath("Reports\\Risk_Assessment.rpt"), CrystalDecisions.Shared.OpenReportMethod.OpenReportByTempCopy);

// THIS STUFF HERE IS FOR REPORTS HAVING SUBREPORTS

// set the sections object to the current report's section

crSections = rd.ReportDefinition.Sections;

// loop through all the sections to find all the report objects

foreach (Section crSection in crSections)

{

crReportObjects = crSection.ReportObjects;

//loop through all the report objects in there to find all subreports

foreach (ReportObject crReportObject in crReportObjects)

{

if (crReportObject.Kind == ReportObjectKind.SubreportObject)

{

crSubreportObject = (SubreportObject)crReportObject;

//open the subreport object and logon as for the general report

rdsub = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

}

}

}

SqlConnection conn = new SqlConnection(DB.connString);

conn.Open();

SqlCommand cmd = new SqlCommand("Risk_Assessment", conn);

cmd.Parameters.Add("@Risk_Id", Session["Default_Risk"]);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da1= new SqlDataAdapter(cmd);

DataTable dtRisks = new DataTable();

da1.Fill(dtRisks);

dtRisks.TableName = "Risk_Assessment";

SqlCommand cmd1 = new SqlCommand("Risk_Assessment_Sub", conn);

cmd1.Parameters.Add("@Risk_Id", Session["Default_Risk"]);

cmd1.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da2 = new SqlDataAdapter(cmd1);

DataTable dtMitigations = new DataTable();

da2.Fill(dtMitigations);

dtMitigations.TableName = "Risk_Assessment_Sub";

rd.SetDataSource(dtRisks);

rdsub.SetDataSource(dtMitigations);

rd.Refresh();

crvRisk.ReportSource = rd;

 

crvRisk.DisplayGroupTree = false;

 


Print Page | Close Window