on 09-19-2018 7:48 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.