Dynamic Grouping Crystal Reports 9

Question

Good Day,

I have a crystal report that has a report parameter that can be a value between 1 and 100. If the value is less than 50, I need to group by CustomerID. If the value is greater than 50, I need to group by OrderID.

I've been searching the web on how to do this and I've come across several links that says this can be done via a formula. So inside the formula I have:

if {ReportParameter} < 50 then
    {TableName.CustomerID}
else
    {TableName.OrderID}

I created a group manually that correctly sifts through the data, but when I drag the formula into a grouped category, it's not working for me.

Is there an additional step I'm missing?

TIA,

coson

Answer 1

There are several ways for me to tackle this one...

1. If I understand your follow-up comments correctly, all you did was change the display name of the group, you didn't actually change the grouping. I tried this myself just to make sure and that's what it looks like to me.

2. Second, (someone correct me if I'm wrong), you cannot group on a formula. I also tried this in CR2010 and couldn't do it. (Edit scratch that. Ryan is right. I am wrong)

3. I recommend using Reports->Group Expert to see what is grouped where. By default, it should also be on your toolbar.

I can think of 2 ways of setting up your report that would make this issue null and void:

1. Make 2 separate reports, 1 for each view. Set the selection criteria appropriately

OR

1. Make 1 Master report. Set up your group, page, and report headers & footers in here. Before the data gets into Crystal, create a field to act as your 'index' (perhaps with the formula you listed) and group on that.
2. Create 2 details subsections. If you want to change the label of your groups, just drag & drop a formula field onto your group header.
3. Suppress the first subsection if {ReportParameter} < 50. Create a subreport in there and group that subreport by {OrderID}.
4. Suppress the second subsection if {ReportParameter} > 50. Create a subreport in there and group that subreport by {CustomerID}.

Answer 2

OK, here is how I ended up doing this:

I defined the formula, but I didn't link the group to the formula. So I went back to the Group Header > Change Group > Change Group Options > Options Tab

and pointed to the name of my formula and it worked.

I thought that by adding the formula to the Group Header, CR would automatically associate my formula with the Group Header. DOH!!!