How to Pass Parameter to CommandText on Subreport?

[Former Member]

Hello.  I'm thinking this should be possible, and I don't understand what I've done wrong.  I'm creating a Crystal report, in the Crystal designer, and I'm stuck on how to get it to accept and save my query.  I'm using Crystal 11.0

In the main report, I define two formula fields.  The first is named DateAndTimeOfRun.  The formula contains the Crystal command CurrentDateTime.  The second is DateToInclude.  It has the formula of DateAdd("d",-1,{@DateAndTimeOfRun}).  Basically, I want to include database records that are less than 24 hours old.

My subreport has links to these two parameters.  They do appear in the designer in the field Explorer.  I have data from 4 different tables with no relationships to each other that I want to extract, so I was trying to do it as a commandtext.  When I open the commandtext, the parameters I see in the field explorer do not appear in the parameter list for the commandtext.

At the start of the commandtext, I declared a temporary variable and tried setting it to {?pm-@dateandtimeofrun}.  I'm getting "no value given for one or more required parameters" when I try to save my commandtext query in the Crystal Report designer.

A Snippet of the commandtext follows:

DECLARE @STARTDATE DATETIME,

                @ENDDATE DATETIME

SET @STARTDATE = {?pm-@DateToInclude}

SET @ENDDATE = {?pm-@DateAndTimeofRun} 

DECLARE DMOPENCOUNT CURSOR FOR    

SELECT COUNT(*) FROM DMACTIVE

WHERE (DATEACTIVE >= @STARTDATE) AND (DATEACTIVE <= @ENDDATE)

Can someone please tell me how to do this simple task?

[abhilash_kumar · Accepted solution]

Hi Debbie,

Open the Subreport's Command Object and you should see a 'Create' button to the right. Use that option to create two date prompts and use that prompt in the SQL Query.

Next, go back to the Main report > right-click the Subreport > Change subreport links > Move the DateAndTimeOfRun formula to the pane on the right > From the drop-down at the bottom left that says 'Subreport parameter field to use', choose the right prompt.

Repeat this step for the other prompt as well.

-Abhilash

[Former Member]

Thanks everyone!  That really helped.

[DellSC]

To follow up on this - commands can't "see" parameters that have been created in the report that contains them.  Once you create the parameter in the command it will be available in the report as well, but it has to be created in the command.  See my blog for more information about using commands:  .

-Dell