cancel
Showing results for 
Search instead for 
Did you mean: 

How to the values for the first and last incidences of a field in the report without minimum/maximum

former_member1110314
Participant
0 Kudos

Hi there

I want to place, anywhere in my report, a simple statement that shows the first and last values from a particular report, based on the order they are included in the report.

I cannot use minimum/maximum for this, as the value in the field is not sorted in a linear fashion.

For example, here are some values, sorted by the report...I have numbered the rows to show the report sort order, let's call the field value "Registration Number"....

Record 1: 2009/43/1

Record 2: 2009/43/9

Record 3: 2009/43/11

I want a statement that reads "Registration Numbers 2009/43/1 to 2009/43/11", based on the first and last records included in the report.

Unfortunately, if you use minimum and maximum, the largest number is 2009/43/9, instead of 2009/43/11, as the 11 is counted before the 9.

Can anybody advise how to just grab the values from this field from the first and last records, according to the sort order in my report?

many thanks

Karen

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Karen,

1. Create a formula called @first with this code and place on the Report Header:

shared stringvar first := {RegistrationNumber_Field};

2. Create a formula called @last with this code and place on the Report Footer:

shared stringvar last := {RegistrationNumber_Field};

3. Insert a second Report Footer b section

4. Create a formula to display the range and place this on the Report Footer b:

shared stringvar first; 
shared stringvar last;
first & " to " & last

If the registration numbers are always xxxx/xx/xx, you can convert it to a number using this formula:

If isnumeric(replace({Registration_Number_Field},'/','')) then 
tonumber(replace({Registration_Number_Field}, '/',''));

You should then be able to create a formula with the Minimum and Maximum functions that references the formula field above.

-Abhilash

former_member1110314
Participant
0 Kudos

Awesome thanks Abhilash! Only saw this now 🙂

-KB

Answers (0)