Data Connectivity
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Data Connectivity
Message Icon Topic: Problem with sub report linked parameter Post Reply Post New Topic
Author Message
andyoneill
Newbie
Newbie


Joined: 19 May 2007
Online Status: Offline
Posts: 12
Quote andyoneill Replybullet Topic: Problem with sub report linked parameter
    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;

IP IP Logged
andyoneill
Newbie
Newbie


Joined: 19 May 2007
Online Status: Offline
Posts: 12
Quote andyoneill Replybullet 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;

IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
andyoneill
Newbie
Newbie


Joined: 19 May 2007
Online Status: Offline
Posts: 12
Quote andyoneill Replybullet 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.
IP IP Logged
Paranoia
Newbie
Newbie


Joined: 07 Aug 2007
Online Status: Offline
Posts: 1
Quote Paranoia Replybullet 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
IP IP Logged
andyoneill
Newbie
Newbie


Joined: 19 May 2007
Online Status: Offline
Posts: 12
Quote andyoneill Replybullet 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;

 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.