SQL command object 1

[Harki06]

Hi,

I am new to Command object usage.

I am using Crystal ver 10 and SQL Server database. I am having a report where I display values for a club for range of months selected, year selected and club selected. These are all parameters. I also need to display a summarized total of all clubs and for the club. For the club, I used summations. For 'ALL clubs' I thought of using SQL command object v/s using a subreport.

How do I code to use the parameters selected in the report for the SQL command. For eg.

select sum(estimated_ad_cost) as sum_estimated_cost from clubsummary a where a.club_id = (how do I code it here) and a.month = (???) and a.year = (???). I tried to use {?year} etc but I keep getting syntax error. And do I need linking the table with this command object?

Thanks!

 

[lbass]

If you are using a command, you should be creating the parameter in the command screen (on the right). Then double click to place it in the command. String parameters must be enclosed in single quotes.

Generally you should include all report elements in a command, so that it is your sole datasource. While you can link commands to other tables, the linking occurs locally. In this case, I wouldn't use a command--I would just insert a subreport that is linked on the parameters.

-LB

 

[Harki06]

Thanks a lot! In subreports I face the challenge of aligning the fields when position is changed-

When would you use a command object?

 

[lbass]

It is possible to align exactly, but it takes a little trial and error.

I ordinarily use a command for speed, if I'm designing a complex report, and then it is my only datasource. I also use commands (unlinked) to populate pick lists sometimes.

-LB

 

[Harki06]

Thanks a lot for your help. I have 15 columns on a ladnscape. That is why I was opposed to using the subreport-

 

[lbass]

You can make a subreport display only one section and then resize it so that it is as small as a field.

-LB