Parameters in Command Objects

[gopat]

Database : SQL Server 2000
Crystal : 10
Connection: OBDC

I understand it is possible to have parameters for command objects.

In my report I have multiple command objects with date as a parameter. So instead of feeding seperate dates for each command parameter, I would like to have it all done by specifying the date in the main report & all command objects should read it from there.
Is that possible?

thanks

 

[vidru]

Nope. Commands can't share parameters with the main report, or with each other. If you're using multiple Commands, you may consider using a stored procedure instead.

-dave

 

[gopat]

but the stored procedure can only return one value & I need multiple values, hence the different command objects

 

[vidru]

but the stored procedure can only return one value & I need multiple values

I'd say you've been misinformed. Perhaps you're thinking of a scalar function(?). Stored procedures have no such limitation.

Is there a particular reason you're using multiple Commands instead of accessing the tables/views directly? I can understand why you might, but maybe if you explain your intent, someone may be able to come up with an alternative to multiple Commands.

-dave

 

[Turkbear]

Hi,
Is that a SqlServer limitation?
I have SPs in Oracle that return entire recordsets.

 

[gopat]

This is what my problem is.
I have a database which is not really normalized, more like a flat file. anyway, there is this one report that I want to create which utilises multiple tables.
These tables CANNOT be joined.
I need to count the number of records from each table based on seperate select condition.

Vidru mentioned accessing the tables directly, is that possible if the tables are not related?

I have posted another question which asked how to solve the problem getting data from multiple sources & combining them. Someone suggested using Sub-reports but that makes adhoc reporting a problem

So is there any other solution which does not involve mutilple command objects.

These are two sample command objects which I use

Command Object 1
select count(*) as c1
from orders
where Order_date between '01/01/2001' and '07/01/2001'

Command Object 2
select count(*) as c2
from orders_2
where Order2_date between '01/01/2001' and '07/01/2001'


In the report I add the two counts (c1+c2).
this is an oversimplified example but along the same lines.

 

[gopat]

How can stored procedure help when I need to display c1, c2 & c3 in the report?

stored procedure would help if I am looking only for the total, which is not the case. Or am I missing something?

 

[BobSuruncle]

Have you tried using SQL Expressions?
lbass is the guru on this topic, but each SQL Expression would be something like this:
(Select Count(*) from Orders where Order_date between '01/01/2001' and '07/01/2001' )

The brackets around the SQL Expression are important and the expression can only return 1 row.

HTH


Bob Suruncle