Crystal Reports 1

[jchewsmith]

I am trying to build a report that selects only <=date and the largest number of another number field. I have tried Nth Largest and maximum but I get an error saying that I need a boolean.

Any ideas?

 

[lbass]

You need to show the contents of the formula that is returning the error message and explain where you are trying to use it.

-LB

 

[jchewsmith]

{SSRpt_InvSumbyCustomer.DATE} <= {?Ending Date} and
NthLargest (1,{SSRpt_InvSumbyCustomer.TransID})

Error Message:

A boolean is required here.

 

[jchewsmith]

I am trying to use this formula in the select expert filter.

I have multiple lines for an item and only want the highest record on a before a certain date range:

item qty datarecord# date
1 150 92 11/3/11
1 29 89 10/31/11
1 292 86 10/25/11

I only want to report qty of 29 as I only want to show the last quantity <=10/31/11.

 

[lbass]

Why is quantity 29 the largest? Why not 292? Or do you really mean the most recent less than a certain date?

-LB

 

[jchewsmith]

item qty datarecord# date
1 150 92 11/3/11
1 29 89 10/31/11
1 292 86 10/31/11

Here is what I am trying to do. I want the qty 29 because it is <=10/31/2011 and it is the highest data record for that item.

If I try to use a select filter I can't use the max datarecord# as it tells me I have to evalulate at a later time. I can supress by using: Maximum ({SSRpt_InvSumbyCustomer.TransID}, {SSRpt_InvSumbyCustomer.ItemNum}) <> {SSRpt_InvSumbyCustomer.TransID}
but then I am getting all of the records and just supressing the ones that I don't want.

 

[lbass]

You can use group selection (report->selection formula->Group and enter:

{SSRpt_InvSumbyCustomer.TransID} = maximum({SSRpt_InvSumbyCustomer.TransID}, {SSRpt_InvSumbyCustomer.ItemNum})

The advantage of that is that you can use running totals without any special formulas to calculate across records, while with suppression you would have to explicitly eliminate suppressed records. But, the records are still IN the report even with group selection--just not displayed. To entirely eliminate the records from the report, you could create a SQL expression {%maxID} to return the maximum, like this:

(
select max(TransID)
from SSRpt_InvSumbyCustomer A
where A.ItemNum=SSRpt_InvSumbyCustomer.ItemNum
)

The syntax and punctuation depends upon your CR version and your datasource. Once you adjust the expression based on these, you could use a record selection formula like this:

{SSRpt_InvSumbyCustomer.ItemNum} = {%maxID} and
{SSRpt_InvSumbyCustomer.DATE} <= {?Ending Date}

-LB