grouping a database field only by last 2 char's 1

[sameer11]

Hi,
Iam trying to group a field, but I don't really get How I want it. Table.fieldtype( say: grouping by that field)
and that field has data such as OUND0201,OUND0201,OUND302,
OUND0501,OUND0601,OUND0602
this is my @type
if right({Table.fieldtype},2)='01' then
'your business number'
else if right({Table.fieldtype},2)='02' then
'your aff number'
How can I group by only by the last 2 characters of the field. Please help me with any thoughts on this

Thanks,
Sameer

 

[lbass]

It looks like your formula should work just fine, so if when you group on it you are not getting the results you want, please show us the results you are getting, and the results you would like instead.

-LB

 

[sameer11]

I need to show accounts for these Fieldtype since Iam grouping by that field
It show the headings( as in my formula ) as the field value changes for each field value
It should look like this as below
accounts tags total//just other field heading in the report
your business number// @type
1111111 100 99999
2343561 000 99999
your aff number
4352612 200 99999
3456789 100 99999

but what i get is

your business number
1111111 100 99999
your aff number
4352612 200 99999
3456789 100 99999
your business number
2343561 000 99999
I could find where It's going wrong but then found that
it's grouping by the fieldtype(where OUND0201,OUND0201,OUND302,
OUND0501,OUND0601,OUND0602 are changing(if you notice at the 3 char's from last)

Thank you for ur time
Sameer

 

[dgillz]

Create a formula field Right({YourField,2}) and group on the formula field.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[sameer11]

Thank you. It works!
Can you also please look into this...
This formula works only for some details it prints 000 ( when there is ' ') but not for all details which has ' '( when I checked in Toad using decode function it works but in CR no...

@ tag

if {table.tag}= ' ' or {table.tag}= '' then

'N/A' //or either '000'
else
{table.tag}

-Sameer

 

[dgillz]

You need to test for nulls as follows:

If isnull({table.tag}) or if length(trim({table.tag}))= 0 then "N/A" else {table.tag}

Also, it is important to keep the null test as the very first piece of logic in your formula.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[sameer11]

I tried cut & paste your formula but it has same effect as my formula. I going crazy trying to figure it out why it prints in some place and won't in some place.

any more adivces please

Thanks,
Sameer

 

[synapsevampire]

I suspect that there's something else afoot here.

Try the following:

If isnull({table.tag}) or not(isnumeric({table.tag}))then "N/A"
else
{table.tag}

-k

 

[sameer11]

Thank you very much.

It works!

Sameer