group by second letter of last name

[abhishekpant]

hello,

i want to add a group with the customer,s second letter of last name?

can anyone help

 

[lbass]

Create a formula to group on:

{table.lastname}[2]

-LB

 

[abhishekpant]

thanks lbass
for your help,it worked.......

 

[jaspaul]

Hi,

Sorry to bother you, but I've been going through all the posted forums as I myself am facing a similar problem of grouping. I'm trying to integrate the following sql in crystal report:
select count(*), name from calls_done group by name;

I don't know how to create a formula to group on calls_done.name
Can you please help?

 

[abhishekpant]

hi jaspaul,
Which version of crystal report yuo sre working on..
IN crystal report 10,
go to insert,group and add group by name

cheers
abhishek

 

[lbass]

If you just want to insert a group, you should be able to go to insert->group->select {calls_done.name} from the dropdown list of fields. I'm guessing your question is more complex than that, but you'll have to spell it out.

-LB

 

[jaspaul]

Well...I'm using crystal 8.5
The problem is that I want to display the result of the sql query as it is, on the report. I can get to group the records as you mentioned but I don't want the actual records showing. Just want the name & the count. But the report is showing me the result of the query as well as it shows the actual records (the records that were counted)
I want :
Name Calls Done
John Hollis 2
Peter Pan 3
Mary 5
....

But the grouping in reports shows
Name Calls Done
John Hollis 2 - result of the query
John Hollis 2 (at 10:30)- The actual 2 records
John Hollis 2 (at 13:25)- that the sql query
is counting
Peter Pan 2
Peter Pan 2
Peter Pan 2

Mary 5
...
I hope I explained the problem..

 

[rana77]

hi jaspaul ,
if u want the result of query
select count(*), name from calls_done group by name
in crystal report, then
best way is make a stored procedure and put ur query in it.
Then design Report using this stored procedure.

ur problem is because of Crystal report dont allow change in "Select" clause of sql query.

If u r using access then make a query. Crystal report will treat it like stored procedure in sql server

Lalit Rana

 

[vidru]

If you create the group as LB stated, I think all you'd need to do to get what you want is to suppress your Details section, create a Subtotal on the Name field (or some other field from the table - choosing 'Count' as the type of summary), then place the Name field and the summary side by side in your Group Header. Suppress the Group Footer section as well if you don't want blank space between each group.

-dave