Quasi CrossTab Report

[KARR]

All,

Using Crysal Reports 11 and pulling information from a table that looks like this:

**************************************
File Code Date

12345 L100 2/2/2004
12345 L102 2/2/2004
45678 X10 3/6/2005
45678 L101 2/2/2004
**************************************

I am trying to create formulas that returns the date for a certain Code and blank if it doesnt match

Such that the report would look like:

File LCode XCode
12345 2/2/2004
45678 2/2/2004 3/6/2005


LCode = Code L101 or L102
XCode = Code X10

I tried writing separate formulas for LCode and XCode, but cant seem to return blanks if they dont match.

Any thoughts?

Thanks
Rob

 

[synapsevampire]

Try something like:

if {table.LCode} in ["L101", "L102"] then
totext({table.date1})
else
""

The other might be:

if {table.XCode} = "X10" then
totext({table.date2})
else
""

-k

 

[lbass]

Try:

//{@Lcode}:
if {table.Lcode} in ["L101","L102"] then {table.date} else date(0,0,0)

//{@Xcode}:
if {table.Xcode} = "X10" then {table.date} else date(0,0,0)

Insert a group on {table.file} and then insert a maximum on each of {@Lcode} and {@Xcode}. Then suppress the details section.

-LB

 

[KARR]

That looks like it did it thanks, but I have two more questions:

1) Does Crystal Reports have a "Select Distinct" like SQL server, whereas it will not return duplicate rows?

2) Say I have something like this:

12345 L100 2/2/2004
12345 L100 2/3/2004

Is there a way to return the later of the two dates?

Once again thanks.
Rob

 

[lbass]

Go to database->select distinct. But this won't work for your example as the entire row is still distinct. If you want the most recent record per file/code, first concatenate the file and code by creating a formula {@filecode}:

{table.file}+{table.code}

Then group on this formula and go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date},{@filecode})

Or you could insert a group on file and then one on code, and use the code group in the group selection formula.

Or you could create a SQL expression {%maxdate}:

(select max(AKA.`date`) from Table AKA where
AKA.`File` = Table.`File` and
AKA.`Code` = Table.`Code`)

Substitute your exact field names for "date","file", "code" and your table name for "Table", leaving "AKA" as is, since it is an alias table name.

Then in the record selection formula use:

{table.date} = {%maxdate}

-LB