Relating SQL Expression to detail line

[FunkyBunch]

Hello All,

I am writing a report that tells wether a piece of equipment has been transfered, deleted, or added.

I am working with a history table so the only way i can check if the equipment has been transferred is to use an sql expression to count how many times the id shows up in the table. Here is the SQL Expression:

(select count(access_equip_Fw)
from user_Defined20_Fw
where access_equip_fw = "USER_DEFINED20_FW"."ACCESS_EQUIP_FW"
group by access_equip_Fw
having count(access_equip_Fw) > 1)

What i need to do is somehow relate the select statement to each detail line of the report.

Please help

 

[synapsevampire]

Please post the software being used, it's version, and the database.

You can probably do this in the SQL Expression, use the Dtaabase->Show SQL Query to see what is being passed, and then adjust your correlated subquery accordingly.

Or if a later version of Crystal, you can use a Command Object to create a SQL statement as the source for the entire report, including your subquery.

Command objects are listed as Add Command under your data source.

-k

 

[FunkyBunch]

I am using Crystal Reports 9 and SQL SERVER 2005.

The reason i cannot use a command is because the report well be uploaded into an application that is struggling to use reports written from commands.

I have the select statement in the sql expression field. and the field that access_equip_Fw references is "USER_DEFINED20_FW"."ACCESS_EQUIP_FW".

But when i place it into the report it just gives a one number for every detail line. What i am having trouble with is relating it to each piece of equipment specifically.

 

[lbass]

I think that in your main report you should insert a group on the equipment field, and then place the SQL expression there.

-LB

 

[FunkyBunch]

That is what i have tried and it is not returning the correct data. It is returning a static number for the entire report.

 

[synapsevampire]

The reason i cannot use a command is because the report well be uploaded into an application that is struggling to use reports written from commands."

Easiest to fix the app.

Anyway, the correlation isn't as simple as dropping it into the group.

You would need to design the report to be doing a GROUP BY clause, and then add the SQL Expression, which isn't likely.

I suspect you'll end up using a subreport here or figure out how to use a SQL Command appropriately.

-k