filtering a grouped report to show top 3 by each state in each group

[neronikita]

I have a report that shows fuel prices grouped by the route. It contains the diesel pump price, the state taxes, and the actual price (pump-tax). I have it grouped to pull the first 5 characters of each location (for example, I-80, or I-90, or I-95, etc). Then it is sorted from lowest to highest actual price. It currently shows all the locations. I just want to show the lowest 3 per state for each route. Is this possible in a crystal 2008 report? If you need to see the fields, I can provide those, but I'm not sure this can even be done.

Thanks in advance for any advice,

Di

 

[IdoMillet]

Go to the menu option of Report, Group Sort Expert, and select the Bottom N option.

Cheers,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[neronikita]

That option is grayed out for me.... i tried clicking on the different fields, but nothing makes it available to me. Actual Price is a formula field, so I thought maybe that is the issue?

Thanks,

Di

 

[neronikita]

ok, I found that you need to have a summary field to show that. So now it is showing by Route (the group field) but it's showing all within the bottom 5 groups. I want to sort within each group.

Group (by route)
then detail shows state, pump price, actual price (formula field)

I want to show every group and just the bottom actual prices within each group by state.

I can't seem to get it to show that.

Thanks,

Di

 

[neronikita]

I think I found what I need but it's not working. It keeps saying the result must be boolean, but I can't figure out why it is saying that.

Here's what my formula is that is saying it must be boolean:

NthSmallest (1,{@actualprice}, {fuelprices_.State/Prov} )

(Grouping is by route then by state)


I found this link online (

http://pic.dhe.ibm.com/infocenter/r...tion.eclipse.designer.doc/html/topic547.html)

and it says this:


Description
Determines the Nth smallest value in a given field, either for the entire report or for each instance of the (condFld) group.
Overloads
NthSmallest (N, fld)
NthSmallest (N, fld, condFld)
NthSmallest (N, fld, condFld, cond)
Arguments
N is any integer from 1 to 100 (inclusive).
fld is any valid database or formula field that can be evaluated by the function.
condFld is a field used to group the values in fld by.
cond is a String indicating the type of grouping for condFld. You only specify this argument when condFld is a Date, Time, DateTime or Boolean field. For more information on the valid strings for this argument, see Conditions for summary functions.
Returns
Fractional Number
Action
Determines the Nth smallest value in a given field, either for the entire report or for each instance of the (condFld) group.
Examples
NthSmallest (1, {Customer.CUSTOMER ID}
Returns 12, where the Customer ID field contains numerical values ranging from 12 to 50.
NthSmallest(1, {Customer.CUSTOMER NAME}, {Customer.REGION})
Returns the least value in the Customer Name field, per region.
NthSmallest({orders.ORDER AMOUNT}, {Customer.REGION}, "monthly")
Groups values in the Amount field by region, then returns the least value in the Amount field for each region, per month.

Note: Using this function in a formula forces the formula to be evaluated at print time.
For more information on evaluation time considerations, see Evaluation Time.


So where exactly am I going wrong?

Thanks,

Di

 

[pmax9999]

Where are you entering that formula?

Cheers
Pete