Can you add parameter field programmatically?

[cfriedberg]

I have a report that is using the RDC component. The data is stored in an SQL database so the parameters coming must be in the order that I want.

When I add them to the report itself they are assigned an index number lower than the original parameter. They have to be in a consistent order.

Because there are several parameters, I would like to create them on the fly.

My intuition says to create them using the following command: Report.Add.ParameterFields(2) "@SelectedRegions", crString.

But, of course that does not work.

The command Report.StoredProcParam() did not work either.

Any suggestions?

 

[LV]

objReport.ParameterFields.Add(parameterName as String, ValueType as CRVFieldValueType) AS ParameterFieldDefinition

CRVFieldValueType:

crBitmapField 17
crBlobField 15
crBooleanField 9
crChartField 21
crCurrencyField 8
crDateField 10
crDateTimeField 16
crIconField 18
crInt16sField 3
crInt16uField 4
crInt32sField 5
crInt32uField 6
crInt8sField 1
crInt8uField 2
crNumberField 7
crOleField 20
crPersistentMemoField 14
crPictureField 19
crStringField 12
crTimeField 11
crTransientMemoField 13
crUnknownField 22

This should give you an idea.

 

[cfriedberg]

This is not working at all.

1. Dim Report As crInvoices
2. Set Report = New crInvoices
3. Report.ParameterFields.Add("SelectedRegions", crStringField) = ParameterFieldDefinition
4. Report.ParameterFields(2).AddCurrentValue "550"

Line 3 bombs completely no matter how I represent it.
Without line 3, the code runs smoothly, but there is no current value listed in the Locals window of VB 6 and no parameter is dispensed to SQL server.

 

[LV]

Are you calling you report from VB?
I thought I said AS ParameterFieldDefinition, not =ParameterFieldDefinition. ParameterFieldDefinition object is what this function returns.

Line 3 syntax is wrong.
Should be something like this:

Dim parm As CRAXDRT.ParameterFieldDefinition
set parm = Report.ParameterFields.Add("SelectedRegions", parm.crStringField).AddCurrentValue "550"

Or just get rid of the second part in line 3, if you know paramter index:

3. Report.ParameterFields.Add("SelectedRegions", crStringField)
4. Report.ParameterFields(2).AddCurrentValue "550"

 

[JoeCrystal]

Code it as follows:

Dim parm As CRAXDRT.ParameterFieldDefinition

Set parm = rpt.ParameterFields.Add("SelectedRegions", crStringField)
parm.AddCurrentValue "55"

 

[cfriedberg]

Thanks for the help.
I know you said "as" but even that does not work.

This line set parm = Report.ParameterFields.Add("SelectedRegions", parm.crStringField).AddCurrentValue "550" is colored red in VB. The syntax is incorrect.


This is the latest attempt that runs smoothly, but the parameter does not get a current value nor is it passed to the SQL stored procedure.

Dim parm As CRAXDRT.ParameterFieldDefinition
Report.ParameterFields(1).SetCurrentValue pdtParam

Set parm = Report.ParameterFields.Add("SelectedRegions", crStringField)
parm.SetCurrentValue "550"
parm.AddCurrentValue "550"

 

[JoeCrystal]

What are you trying to do? Add a parameter to the report or are you trying to set the current value of a stored procedure already in the report? If the stored procedure is already in the report you do not have to add the parameter.

 

[LV]

Does this parameter have to participate in the report record selection formula, in order to filter records?
If so, then you'll have to modify it programmaticaly to include this newly created parameter. If it's used for another purpose, then a good test will be (if you can see the report - in Crystal Viewer, probably) to export this report to Crystal format and open it in the Designer. You should see this newly created parameter in the report, which can help you troubleshoot it.

 

[cfriedberg]

This parameter has to participate in the selection and filtering of records. I put the parameter field on the report and then followed it with the SQL Profiler. It was not passed into the SQL 2000 stored proceedure. Now this is a new wrinkle, or maybe this is the root of the problem.

How can I get the parameter to work correctly? There are going to be several more selection parameters for this report (invoices).

 

[JoeCrystal]

If you have a parameter in your stored procedure you do not have to add a parameter. When you add the store procedure to your report Crystal detect the parameter(s) and add them to your report. Try it by just adding your stored procedure and looking to see if your parameter is in the report. What do you mean by SQL Profiler?

If you do not have a parameter in your stored procedure but want to filter the data return by the store procedure, you will have to add/replace your selection formula using the parameter added in the selection formula.

FYI you do not have to do both parm.SetCurrentValue "550" and parm.AddCurrentValue "550". In this your case either one will suffice.

 

[LV]

Joe is absolutly right - if you originally designed your report off a stored proc, the parameter will be created automaticaly for you by Crystal, you don't have to add anything on the fly in order to filter your records. This parameter doesn't have to be included into the record selection formula. This parameter will have some default value but you'll have to reset it through the code to modify report. The SQL Profiler though (I assume, you are reporting off a SQL Server database), should show this stored proc executed when you run the report.

 

[cfriedberg]

The problem is that I created the report and then added the parameter.

Thanks for all your help.

I have reimported the report after adding parameters in the stored procedure and added the actual parameters to the report itself. It seems to be working after I have added one more parameter. It remains to be seen after I add another parameter or two.

 

[cfriedberg]

The SQL Query Profiler is a tool used with Microsoft® SQL Server.