Change/ignore group per parameters 2

[vb6novice]

My CR XI report data Group 1 is by a client name and Group 2 by a date field. The report parameters give the options to select/not select a client as well as the option to select/not select a date range for the date field.

If users choose not to select by Client, then I would like the Group 1 to be ignored, and Group 2 to be the only grouping.

Is it possible to either:
a) have a formula that tells the report NOT to group by Client OR
b) To change Group 1 from grouping by the Client field to grouping by the date.

All help is appreciated.

 

[Turkbear]

Hi,
A formula like

@GroupByClient
if {?Param} = 'Client' then
{table.clientfield}
else
""
when used as the Group that is inserted should only create the group when the parameter indicates it should.








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vb6novice]

Turkbear,

Thanks for the fast reply.

You stated

"... when used as the Group that is inserted should only create the group when the parameter indicates it should."

The group already exists and its header has a text box which displays the Client name if the Client parameter check box is selected.

Does your formula account for that?

 

[synapsevampire]

Change your current group 1 to be the formula turk suggested.

It's easy and quick, if something doesn't work as expected, post back.

-k

 

[vb6novice]

Turk and k,

The formula looks easy, but I'm not sure if I'm doing it right.

I opened Select Expert, clicked Show Formulas, clicked the Group Formula radio button, and clicked Formula Editor.

When I put Turk's formula in I changed it to:

@GroupByClient
if {?cb_CLIENT_NAME_REF} then
{rfc_comp_stats_sql.CLIENT_REF}
else
""

Bear in mind that client field is a number field and that the parameter {?cb_CLIENT_NAME_REF} is Boolean.

When I try to save it, I get the error "The remaining text does not appear to be part of the formula.
So I comment out the @GroupByClient. Then I get the error "A number is required here" at the "" after the else.
So I change that to a field that will have a value of 1 for all records, and I get the error "The result of selection formula must be Boolean".

Turk's formula appears to change the source of the grouping, but CR doesn't think so.

What do you suggest?

 

[Turkbear]

Hi,
Try this ( and this way)

Create this formula ( name it anything you want):

If {?cb_CLIENT_NAME_REF}
 then
{rfc_comp_stats_sql.CLIENT_REF}

Notice No Else statement.

Choose Insert..Group and pick the formula you just created as the Item to Group on.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vb6novice]

Turk,

Thanks for sticking with me.

I as you suggested but it did not have the desired effect. Instead of a 30 page report grouped by the client with all records for a client grouped together, I got a 378 page report because everytime the client name changes in the original record set it starts a new group (even when I told it to suppress the group 1 header when the {?cb_CLIENT_NAME_REF} was unchecked.

What's your next suggestion?

 

[Turkbear]

Hi,
If the Group is based on that formula, and {?cb_CLIENT_NAME_REF} is True then each {rfc_comp_stats_sql.CLIENT_REF} value
will have its own group...If False, no grouping for this field will happen.
If yours is behaving differenty,or you want it to do something else, please detail your report layout and needs..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Davest11]

The problem that you're running into with the formula


If {?cb_CLIENT_NAME_REF} then
{rfc_comp_stats_sql.CLIENT_REF}
else ""


is that you can't have a formula that conditionally chages its datatype. All possible outcomes of a formula must be the same datatype. Where {rfc_comp_stats_sql.CLIENT_REF} is a number and "" is a string, Crystal will return an error. I've also found that simply leaving the else clause off can cause odd results sometimes. I would use


If {?cb_CLIENT_NAME_REF} then
{rfc_comp_stats_sql.CLIENT_REF}
else <a number that will never appear in the database>


This way, all instances where {?cb_CLIENT_NAME_REF} = false will be grouped together. You can also use this method to affect the order in which this group is displayed - use a very small number to put it at the beginning, or a large number to put it at the end. Of course, this will only work if the data in this field is limited in a way that will allow you to make an assumption that a value will never appear.

I hope this helps.

-Dave

 

[vb6novice]

Dave,

Your'e pretty close. I worked using Turk's suggestion all morning and determined that I had to have a field of the same data type to give the group something else to group on.

What I ended up with is a formula called GroupSource which is:

if {?cb_CLIENT_NAME_REF} then
{rfc_comp_stats_sql.CLIENT_NAME}
else
{rfc_comp_stats_sql.BOGUS_GROUP}


My query now includes the line

'AllRecs' as 'BOGUS_GROUP'

to ensure that every record has the same value in that field.

In order to ensure the Group Header displays when desired (visble when the Client check box is checked, suppressed when it isn't), I used the formula

If {?cb_CLIENT_NAME_REF} then FALSE

in the Group Header suppression formula.

It's working well.

Thanks, Turk and Dave. Stars for you both.