Grouping formula not working?

[Scroller52]

Hi, using Crystal XI release 2 on a SQL database.

My data looks like this:

Investor Inv Short Name Entity Year Deal Date Amount
Inv1 Inv1 Fund 4 2011 ABC 2/4/11 100
Inv1 Inv1 Fund 4 2011 DEF 2/4/11 50
Inv1 trust Inv1 Fund 4 2011 ABC 2/4/11 25
Inv1 trust Inv1 Fund 4 2011 DEF 2/4/11 10

My report is grouped based on a formula called Investor_Alt_Name which is as follows:

if(isnull({Carry_Detail.Investor Short Name})
or{Carry_Detail.Investor Short Name}=""
or{Carry_Detail.Investor Short Name}="'")
then {Carry_Detail.Investor}
else {Carry_Detail.Investor Short Name}

This is used to combine the report for Inv1 and Inv1 trust. My report is grouped by:
Investor_Alt_Name, Entity and Year.

I need to show only two rows to show each deal and date that the entries occurred. If I put them in the details sections, shouldn't it do just that? Instead it is still breaking it out into 4 rows.

Any help is greatly appreciated!

 

[lbass]

No, you would get four rows. The formula will return Inv1 for each row. What results are you hoping to get for the above sample data?

-LB

 

[Scroller52]

hi once again lbass!

i was hoping to get the report to return this:

Investor Inv Short Name Entity Year Deal Date Amount
Inv1 Inv1 Fund 4 2011 ABC 2/4/11 125
Inv1 Inv1 Fund 4 2011 DEF 2/4/11 60

So the total amount for each deal and date by Investor_Alt_Name, entity and year.

thanks.

 

[lbass]

Add a fourth group on Deal, insert a sum on the Amount field at the Deal group level, drag the fields into the group footer next to the subtotal of Amount, and then suppress the other sections (if you wish).

-LB

 

[Scroller52]

That is how I had the report before, and that does give me the two rows I expect. But the problem is when the same deal has activity on different dates. It will group them together when I need them broken out.

Actually if I add another group based on date and insert the summaries in that section, it is showing up how I need it to. the only issue is that I would like to order the entries based on the date first, then deal. Is there a way to do that w/o moving the groupings?

 

[lbass]

Yes. Insert a maximum on the datefield at the date group level. Then go to report->group sort->select maximum of date as your sort field.

-LB