How do I get 1st and last row value of a column in a group

[rkgrp]

Hi All,

Scenario : Report with 2 level groups.
I need to display one column (say Col A) value at 1st row and last row on the header.

Eg:
Col A - value at 1st row : F111
Col A - value at last row : L999

Header
------
Number (F111 thru L999)

Detail
------
F111 - Row 1
S222 - Row 2
T333 - Row 3
-
-
-
L999 - Row 9


Thanks in advance

rk

 

[bdreed35]

Create this formula and put it in your group header:

"Number (" + Minimum({ColA}, {Group}) + " thru " + Maximum({ColA}, {Group}) + ")"

Replace {ColA} with your field in ColA.
Replace {Group} with your Group field.

~Brian

 

[rkgrp]

Brian,

Thanks for your response. When I created the formula, I am getting error: "The summary / running total field could not be created".

Col A is varchar type.
Group column also varchar type.

How do I overcome from this error message.

I have tried VAL() and ToNumber() :
Minimum(VAL(Col A), GroupName (Group 1)) + " thru " + Maximum(VAL(Col A), GroupName (Group 1))

Minimum(ToNumber(Col A), GroupName (Group 1)) + " thru " + Maximum(ToNumber(Col A), GroupName (Group 1))

Still I am getting error.

~RK

 

[bdreed35]

Is "Col A" a database field or a formula?
Is your Group field a database field or a formula?

Please post the exact formula that you are trying to use with the actual fields. You probably have some syntax wrong somewhere. Looking at your formula, you should be using the actual field you are grouping on, not the group name field.

Minimum(ToNumber({ColA}), {table.group_field}) +  " thru " + Maximum(ToNumber({ColA}), {table.group_field})

~Brian

 

[rkgrp]

Hi Brian,

My bad, I had GroupName(Group 1) instead of table.group_field.

Just replaced table.group_field with GroupName, it works fine now.

Thanks again. Keepup your good work.

RK