Passing Parameter from Visual Basic to Crystal Report 2

[xterra]

How do I do this? I tried using a parameter field but I can't find a way to pass it a value from VB. I have looked everywhere but nothing helps. I tried report.parameterfield(1).setcurrentvalue but that doesn't give any value. Any ideas this is driving me nuts.

thanks,

Adam

 

[SekharEevani]

In the following, the Output report will be displayed using "Crystal Report Control".

Insert the "Crystal Report Control" component in the VB Project and add it in the main form.

#1. Define the report path as in step#1,

#2. Pass the values to Crystal Report Parameters as in Step#2,the value being passed and the parameter in Report should be of same data type. The values can be passed from controls like Textbox,Combobox etc.

#3. Invoke the Report with parameters as in step#3.

'#1.Define the path of the Report
CrystalReport1.ReportFileName = "D:\Crystal Reports\REPORTS_TO_SITE\Production Run Balance.rpt"
'#2.Parameter Assignment
CrystalReport1.ParameterFields(0) = "SELROLLNAME;" & ROLLCombo.Text & ";True"
CrystalReport1.ParameterFields(1) = "SELECTDATE;Date( " & Calendar.Value & "True"
'#3.Display Report
CrystalReport1.Action = 1
'--------
hope this helps. BTW, this is good for CR8.5, I don't know if it works for versions before that.;-)

 

[ThatRickGuy]

Hey, I just went through this a while ago. here's what Ken Handly<sp?> helped me get to: (If you want to see the whole thread, search for &quot;Changing Subreport RecordSelectionFormula at runtime&quot

Hey, I finally got it. looks like you can't pass a date to a parameter in 8.5. so I passed as standard date to the ?startdate parameter (after changing it's type to text with
report.parameterfields(1).addCurrentValue (&quot;01/01/1900&quot
. the created a formula object that was @startdate with the formula: date({?startdate}) then linked the @startdate to the ?startdate that is on the subreport. works perfect now.

 

[xterra]

Ok Thanks for your help thusfar but I have some problems getting both of these methods to work.

EmsnSekhar: When I try this it gives me the Object does not support this method error. Here is the syntax I am using.

crStatements.ParameterFields(1) = &quot;DateFrom;Date(&quot; & DateFrom & &quot;True&quot;

ThatRickGuy: I don't get any errors but I don't get any values either. Here is the syntax I am using.

crStatements.ParameterFields(1).AddCurrentValue CStr(DateFrom)

In the formula I am doing this

formula = {?DateFrom}

but the formula just ends up blank.

Thanks for you help hopefully you know why I can't get any values. Oh by the way I am using CR 8 if there has been any changes in version 8.5 that could be the problem.

Adam

 

[balves]

First thing is that you are using the RDC technology. Therefore, the first response doesn't work for you because it's for the older OCX technology (that still works) that you are not using. Thus, the bad method error message.

What database are you using and what is the data type of the database field that you are comparing? Is the ?DateFrom in the Record Selection formula?

If you're using SQL Server and the field is a datetime field, and there are times being stored (e.g., 10/26/2001 09:30:01), then you cannot just pass the date 10/26/2001 because it won't find any matches. Note that even if you use a datetime field in SQL Server some people *never* put in a time and therefore equating to just a date works for them.

 

[xterra]

I am using SQL Server and the field is datetime. I am only storing the Date though not the time. It is in my recordselection formula but the problem is not that it isn't finding the records, it is that the value is not getting passed from VB to Crystal Reports at all. I know this because I try printing the value of the parameter field on my Crystal Report and it is blank.

Thanks,

Adam

 

[balves]

What is crStatements object type?

crStatements.ParameterFields(1).AddCurrentValue CStr(DateFrom)


Usually it's Report.ParameterFields(1).AddCurrentValue ...

where Dim Report as CRAXDRT.Report
or Dim Report as CrystalReport1 (if you're building the report inside of VB as a .DSR)

 

[xterra]

crStatements is my Crystal Report I just renamed it. It is dimmed as a craxdrt.report

 

[balves]

I don't know...

Perhaps take the Cstr() off? What datatype is the {?DateFrom} parameter inside of the Crystal report? Datetime or string?

 

[xterra]

The datetime is Date right now but ThatRickGuys said the parameters won't take a Date so I used a String as the parameter (hence the CSTR because Datefrom is a Date Variable) and then converted it in CR using the Date function. So either way string or date I don't get a value. Sigh this is annoying I have absolutly no idea what to do. Thanks for all your help though.

Adam

 

[balves]

Perhaps you ought to just drop back to Crystal Report Designer for a few minutes to try a couple of things.

If I go into Crystal Report Designer and create a parameter of type Date (not datetime) and then use it in the selection formula WHERE table.field = {?P1} (and my table.field data has no times), I get the results that I want.

I can save that report as Test.rpt and then go into VB and do this, it works:

Dim dtP1 as Date
dtP1 = &quot;10/13/1999&quot;
<open the .rpt file>
Report.ParameterFields(1).AddCurrentValue dtP1
CRViewer1.ReportSource = Report
CRViewer1.ViewReport

 

[xterra]

sigh no luck I did it exactly like you have it with no results. I know there is one way around this that would work for me but I can't get it to work either and I have asked before but nobody could help me. How can I pass a value from the Main Report to the SubReport and use that value in the SubReport's selection formula. When I try to use a shared variable it just says that I have to do it whileprintingrecords but then when I run it it says cannot evaluate formula at this time. I am beginning to dislike CR. It should be so easy to pass a variable from one thing to another but they make it so hard. Maybe I have a buggy version or something. Thanks again for doing all you have done.

Adam

 

[balves]

I tend to avoid ShareVariables and WhilePrintingRecords stuff because I'm always worried that I won't get what I want.

If you have a field in the main report (even if you hide it) and you link that field to a field in the sub-report, then that becomes part of sub-report record selection formula automatically.

If you want to insert a parameter in the sub-report that has doesn't appear on the main report, then I wouldn't pass it from the main to the sub-report. I would setcurrentvalue on the sub-report parameter from VB.

 

[xterra]

Ok that would work but when I try to set the value from VB it doesn't find the SetCurrentValue Method and there is no other method that can set the value for some reason. Here is what I tried to do.

Report.SubReport_DateFromParameter.SetCurrentValue DateValue

but it doesn't work for some reason.


Adam

 

[balves]

Sorry, it's AddCurrentValue, just like in the main report. Logically, they should have called it &quot;SetCurrentValue.&quot;

There's a document on how to loop through a sub-report on the Crystal web site: rdc_subreport.pdf

 

[balves]

Okay, this code works. With the following caveat: There is only one subreport. And I'm using a .RPT file.

Also, in my example, the parameter being prompted for in the subreport is parameter #2. Parameter #1 is the linked field between the main report and the subreport. For example, you could have a main report linked to a subreport by state code, then you could add a parameter in the subreport for zip code and pass that through VB. Not that it's an example that is very practical, but...

Here goes...

Dim Report As CRAXDRT.Report
Dim SubReport As CRAXDRT.Report
Dim App As CRAXDRT.Application
Dim Sections As CRAXDRT.Sections
Dim Section As CRAXDRT.Section
Dim RepObjs As CRAXDRT.ReportObjects
Dim SubReportObj As CRAXDRT.SubreportObject

Dim n As Integer
Dim i As Integer

Set App = New CRAXDRT.Application
Set Report = App.OpenReport(...)
Report.Database.LogOnServer ...

Set Sections = Report.Sections
For n = 1 To Sections.Count
Set Section = Sections.Item
Set RepObjs = Section.ReportObjects
For i = 1 To RepObjs.Count
If RepObjs.Item(i).Kind = crSubreportObject Then
Set SubReportObj = RepObjs.Item(i)
Set SubReport = SubReportObj.OpenSubreport
SubReport.ParameterFields(2).AddCurrentValue &quot;60601&quot;
End If
Next i
Next n
CRViewer1.ReportSource = Report
CRViewer1.ViewReport

 

[xterra]

Ok I did some searching and this is a quote from the website &quot;All parameter fields and stored procedures are accessed through the Parameter Field Definiions collection of the report object. This includes all parameter fields and stored procedures in a SUBREPORT&quot; Just to make sure I'm not going crazy I should be able to do this.

Report.ParameterFields(Index of Subreport Parameter).AddCurrentValue DateValue

Well here is another thing that is driving me nuts The parameter collection is empty! I try and use the first parameter because the subreports is the only parameter in the report and it says out of bounds. I msgbox the count property of the parameter fields and it is 0. Maybe I should call Seagate because I am doing everything right it just doesn't work. Maybe you know why it is doing this.

Thanks again,

Adam

 

[balves]

It would nice to just say...

Report.SubReport(i).ParameterFields(1).SetCurrentValue

But, unfortunately, it doesn't appear to work that way. You have to &quot;get&quot; to the subreport before you can talk to the parameter fields.

 

[xterra]

Ok I think I see what your saying, the Subreport has to be running in the report before I can pass the value to the parameter? So is there any way I can pass this stupid subreport a parameter from VB, I guess I will just have to build a file just for the date range and use it as a database field. Segate should know how stupid this is, The way they make it sound is that a Subreport is a Report Object just like the Main Report therefore it should have its own parameter fields. Oh well not worth crying over. Thank you for all your help balves I would vote for you 50 times if it would let me

Adam

 

[ThatRickGuy]

check and see if you can actually see the parameter fields at run time. put a break point form load (after you set the current value of the parameter fields) pop open the immediate window, enter in
?report.parameterfields(1).name
and see if you can actually see the parameter. to actually get the value of it, you have to use:
?report.parameterfields(1).getNthCurrentValue(1)
and it should return the value you just set.

if you want to put the parameters directly into the subreport, it can be done, but you'll have to do the whole opening of subreports PITA. or you can put the parameter field and formula fields (to convert the string to a date) on your main report, then pass them to parameters on the sub report using the subreport linking.