Using a formula field in selection criteria of a subreport

[jimmarkham]

Crystal Reports 8.0
Oracle 8.0 Database

Apologies if this is obvious but I'm a newcomer to Crystal and have acces only to simple documentation.

I have a report that contains a subreport.
The records returned to the subreport contain a field called SORCE indicating where they came from. SORCE is used to sort the records so we get all the type A's first followed by the B's etc., but I don't know what the values will be.

So records are like this

SORCE F2 F3 etc.
AAAAA 99 abcde
AAAAA 98 xxxxx
BBBBB 95 xxxxx
etc.

I require to display only the first set of records i.e. all the records that have SORCE equal to that on the first record. So I have created a formula field that stores the value from the first record and checks the other records against the stored value and returns eitehr a 'Y' or an 'N'. This displays correctly on the subreport.

Formula (called codtype)is as follows (Basic syntax)
.....................................
global typer1 as string
if recordnumber=1 then
typer1={OPSHEETCODING.SORCE_CODE}
formula = "Y"
else
if typer1 = {OPSHEETCODING.SORCE_CODE}then
formula = "Y"
else
formula="N"
end if
end if
...................................

BUT, I cannot use this Formula field to select records via the selection expert because Crystal will not let me use formula fields for selection criteria in a subreport. If I type @codtype into the formula editor it gets treated as a string literal.

Any suggestions welcome

Jim Markham

 

[synapsevampire]

Use a View or a Stored Procedure in Oracle.

I don't think that CR 8 is well suited to your needs, perhaps someone else has a clever idea, but I think that you'd have to use another subreport at best.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jimmarkham]

Thanks for your reply synapsevampire.

I'm already using a view for the subreport. The Oracle procedure would be a last resort as its a poor way to do it (in my opinion anyway)

I have (almost) found a solution in Crystal.

If I create a formula like this

.....................................................
global typer1 as string
global res as string
if recordnumber = 1 then
typer1 = {OPSHEETCODING.SORCE_CODE}
res = {OPSHEETCODING.XLEVEL} & ": " & {OPSHEETCODING.CODE}
end if
if recordnumber = 2 or recordnumber=3 then
if typer1 = {OPSHEETCODING.SORCE_CODE} then
res = res & chr(13) & {OPSHEETCODING.XLEVEL} & ": " & {OPSHEETCODING.CODE}
formula = res
else
formula=res
end if
else
formula=res
end if
.............................................

then it builds a string covering up to the first three records providing they all have the same SORCE_CODE.

Then I display the formula field in the page footer and there we are.

MY only remaining problem is that if there are only three records anyway then the third record gets appended to the variable twice - I suspect that the last record in the dataset is re-read at the start of the page footer or report footer - any advice on that would be welcome.

Thanks again for your help

Jim