How to avoid prompt for parameter

[GwenFranz]

Question?

How to avoid prompt for parameter when parameter is a link to subreport stored procedure?





Main report

Processes all records

Under specific conditions a subreport is called

Main report field link-to subreport

Link-to stored procedure parameter



Run prompts for a parameter value --- which if you provide one the main report will only process the subreport for that ONE parameter value [the subreport is built as expected]



Issue: expect the subreport to process all values passed from main report



Criteria for subsreport x must be in data string



Main report abcd - subreport not called

abcdx - subreport called link parm is abcd, pass to stored proc value abcd

Subreport display “this is OK”

ccdm - subreport not called

cdcc - subreport not called

dcccx - subreport called link parm is dccc, pass to stored proc value dccc

subreport display “this is OK”



stored proc ALTER PROCEDURE [dbo].[sp_SUBREPORT1]



@key varchar (4)



AS



Select t.match

From test_data1 t

Where t.key = @key

 

[synapsevampire]

the way to do this would be to conditionally suppress the section that the subreport is in based on the value abcdx, etc.

So right click the section that the subreport is in, and select format section->X2 next to suppress and place:

instr({table.field},"x") > 0

That checks for x existing anywhere in the field.

However your example data shows in only as the last character, which would have a different suppression:

right(trim({table.field}),1) = "x"

Now this prevents the data from being displayed in the subreport if there's an x within the value.

Anyway, if there's a parameter, crystal is going to prompt for it, you can't turn them of in the Crystal designer, but you can in CE, BOE or via external code such as VB, so in your case just make sure that your main report field is linked via it's field to the parameter of the sub report.

-k

 

[GwenFranz]

Thank you synapsevampire, but getting the data is not the issue. We have the data that we want to see in our reports. The problem is getting the report to run without needing to answer a prompt. Can you elaborate on the later half of your response.

Thanks

-G

 

[synapsevampire]

What specifically do you want me to elaborate on?

The report goes against a subreport with a SP with a parameter, if you don't want a parameter, change the SP to a View and pass the value using the record selection.

As for other code, I'm not going to elaborate on evey possibility of code, CE, etc., if you have a specific question, ask it.

Since you posted in the crystal Enterprise foum, don't select the Prompt for Parameter in CE and the user will never be prompted.

-k

 

[GwenFranz]

If I select to not have the prompt for the parameter, don't I originally need to run the report with a parameter to get data into the report? Once the data is in the report then turn it to use exisiting prompts? This will continually return the data for that one parameter. Not what we want.

The goal is to completely bypass the parameter. We considered using a view but the data is a huge record set so we are not sure that this is the best solution.

Maybe I am misunderstanding something.

 

[synapsevampire]

Yes, you're misunderstanding something.

Link the main table field to the subreport parameter.

And you wouldn't want to preload a report with data.

Also keep in mind that subreports are generally a bad idea.

-k

 

[GwenFranz]

First of all the main report and subreport are linked by this parameter field. It is only returning the subreport for that parameter that we are forced to enter.

Second, how to get the data to load WITHOUT parameters has been my question all along. I think you are misunderstanding something too. Where else other than where I mentioned would you achieve what you are indicating here:

"Since you posted in the crystal Enterprise foum, don't select the Prompt for Parameter in CE and the user will never be prompted.

 

[synapsevampire]

First of all the main report and subreport are linked by this parameter field. It is only returning the subreport for that parameter that we are forced to enter."

does not mean:

Link the main table field to the subreport parameter. The main report shoulod not have a parameter, I can't explain it any more clearly than that. Delete any parameters in the main report. The main report will be linked by the field that is your group, to the parameter in the subreport.

-k

 

[GwenFranz]

You are right, I should have been more clear. There is no parameter field in the main report only in the subreport. The main report and subreport are linked in the way you are indicating. There are no parameters in the main report.

 

[synapsevampire]

Then it should work fine, i does here?!?!?

So it prompts for a parameter within CE??? ow is that possible when it is turned off?

Copy and paste in here what is in the subreports Report->selection formula->Record

-k

 

[synapsevampire]

It may be that you think that you're linking to the parameter and you are not,crystalis a tad weird that way.

You should select the field up above, and then select the Subreport parameter field to use, and turn off the select data in subreport based on field checkbox.

-k