Mysterious Parameter Fields

[lynchg]

I am working with CR 9 and Access 97 as the datasource. I have a report with 4 subreports on it. Each subreport is linked to the main report by a common ID field. This part has worked fine so far but we recently revised the queries that feed each of the reports, including the main report. When I try to set a new datasource location it seems to work but the datasource remains the original query. I removed the links to each subreport, but there are references to the old query in formulas. This also happens when I try to set the datasource of the subreports to new queries.

Another problem is that I have unknown parameter fields in 3 of the 4 subreports. In the field list for each subreport I have a parameter field known only as ?[]. I must have created them by mistake, I am not sure how or when this happened. They are each marked as being used on their particular report but they are not physically placed on the report itself, so I figure they must be 'named' in a formula or expression somewhere. I haven't been able to locate where they exist and since they are marked as being in the report I can't delete them.

Are these two problems related to each other? Any help would be appreciated.

 

[MattReed]

Crystal builds parameters when creating subreports. These allow the parent report to pass the linking field to the subreport.

Did you try completely deleting the subreports from your parent report and recreate them?

 

[lynchg]

Before doing that I would want to save the subreports as separate entities. I originally created them as separate reports and imported them into the main report, and then made numerous changes to them. How do I save these subreports in their current form as separate reports?

 

[lynchg]

Forget the last question, I've got it.

 

[lynchg]

I figured out the datasource issue. It was changing the datasource of the report but it was holding onto the old query name as an alias. I was able to change that.

I still have the problem of the mystery parameters. I saved each of the subreports separately, deleted them off of the main report, and removed the linking parameter. The mystery parameter remains on each report.

Any ideas?

 

[vidru]

If the reports are based off of parameter queries from Access, check the query def for any bogus "[]" in the query.

-dave

 

[lynchg]

Good suggestion! They are not based on parameter queries, they are each based on union queries. But in the process of reworking the queries that feed the reports there were at one point errors occurring when the report opened because of a field name change. The report wouldn't open properly, I got an error message that it couldn't create a rowset.

Could Crystal have been trying to figure out what the field value was, and since it couldn't, it automatically inserted a parameter field somewhere into the report?

If so, that would explain these mystery fields, but I still have the problem of trying to get at these things to remove them from a formula so that I can delete them.

 

[kenhamady]

First check the selection formula. That is where they would normally be used. If not found there, try changing the data type of the parameter and then run the report. This should force the usage to generate an error. If you can't change the data type, then it may be seeing parameters in the query. Try verifying the database of each subreport.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lynchg]

I can't change the data type of the parameter. When I verify the database I get prompted to enter a value for the parameter.

These subreports are based on pretty complicated queries that have many layers of nested queries. I was using union queries as the datasource for the subreports. I was wondering if there was something about union queries that was causing the problem because they were being categorized as stored procedures in the Set Datasource Location or Database Expert windows. I thought that was odd so I changed the datasource queries to a select of all of the fields from the union queries. It is still seeing these new select queries as stored procedures even after getting out of Crystal, going back in, and creating a new connection.

I think there is something strange about the way Crystal is seeing these queries. When I create a new report and choose one of these queries as the datasource for the new report I get prompted for a parameter value. These queries execute fine in Access, there are no unresolved field names, and I don't get prompted for any parameter values.

I am thinking I need to do some work in Access to adjust these queries but I am not sure of what Cystal is having a problem with.

 

[lynchg]

I figured out the problem. I was connecting to the data thru ODBC, I switched to an Access DAO connection and the mysterious parameters went away.

Thanks to everyone for your help, all the suggestions led me to narrowing down the problem and eventually finding a solution.