Grouping on Different Data Types 1

[groasa]

CR 8.5, SQL db, Pivotal (CRM) front end.
I have a Sales Opportunity report that I'm working on now that is driving me bonkers.

My goal is to give the user the choice to sort either by Account Manager (String) or Date (Date). If AM is chosen group by AM and sort by Date Descending. If Date is chosen, group by Date Descending printed for each month.

I've tried using the AM as Group 1 and Date as Group 2 and conditionally suppressing depending on the paramater. However, when I choose Date, it still groups by AM first.

At this moment, I've changed the report so there is only Group1 based on a formula, but since I have to turn the Date to text, it sorts the dates in Alpha order.

If {?SortOrder} = 1
Then {Employee.Account Manager Code}
Else If {?SortOrder} = 2
Then (MonthName(Month({Opportunity.Expected Decision Date})) + " " + ToText(Year({Opportunity.Expected Decision Date}),0,""))

I'm at a loss on which way to go to get the report grouped and sorted the way they want. Group by formula? Conditionally suppress? Both? Neither?!?!?!

~glenda

 

[pbateman]

Create a sub-report for each and show whichever one in the main report based on a parameter response.

 

[IanWaterman]

Glenda

Change your grouping formula to this

......Then ToText(Year({Opportunity.Expected Decision Date}),0,""))+ "/"+ (totext(Month({Opportunity.Expected Decision Date}),"00",0,"")

This will produce 2004/06 and thus group in correct date order. The additional argument "00" forces it to display a number with two digits using leading zeros as required.

If this is not acceptable in display then create another formula based upon your original, and place this in your group header. Date will then appear as Jun 2004.

Ian Waterman
UK Crystal Consultant

 

[vidru]

Try changing your Grouping formula to this:
[tt]
if {?SortOrder} = 1 then
{Employee.Account Manager Code}
else if {?SortOrder} = 2 then
totext(year({Opportunity.Expected Decision Date})*100 + Month({Opportunity.Expected Decision Date}),0,"")
[/tt]
To display the Group Name in the Group Header, set up the group option to 'Use a Formula as Group Name', and use the formula you're currently trying to use for grouping.

-dave

 

[vidru]

Ah, you got me Ian

-dave

 

[groasa]

Thanks for the quick response!! I'm going to try grouping formula first and see if that's acceptable for everyone.