Crystal Reports Conditional Grouping Method

Conditional grouping was a big challenge for me until I figured out this method. If I wanted to group by one thing then by another using a parameter or condition it was nearly impossible. The only solution was the occasional nested group with suppression but this is often not an ideal solution.

It is very simple once you figure it out. What you need to do is make a formula as a placeholder with an IF statement determining which group you want to use. This would work with a parameter you set (I will show my example with a boolean parameter for ease).

Example Formula Field (Called {@GROUP})

IF {?boolean} = TRUE THEN {TABLE.GROUP_FIELD01} ELSE {TABLE.GROUP_FIELD02}

So you could make this more or less complicated or stack various groups really to customize any number of scenarios. If the {?boolean} parameter is set to TRUE then it will display FIELD01 and group by this, if {?boolean} is FALSE then it will display FIELD02 and group by it.

It seems so simple! But when you think about how powerful this formula is when grouped you can do many things. If FIELD01 was say a ‘Sales Employee’ in terms of your data then by setting the parameter to TRUE you would group by Sales Employee. If the parameter was set to FALSE and FIELD02 was say ‘Territory’ then you would output and group by Territory.

Leave comments if you have any questions. This is of course extremely difficult to simulate to give an example. Even done with SAP Business One and a standard set of tables would not make much sense so hopefully my description was clear.

Thanks! Mike

Posted in SAP

6 thoughts on “Crystal Reports Conditional Grouping Method

  1. I’ve just bumped into this problem . I was expecting some built-in function for that. Like grouping by a formula. If formula says false then don’t group by that field at all. I like the formula and solution you have provided but what if for one condition I don’t want to group it at all. then what is going to happen? I don’t think I can omit the grouping. It will give error. I am going to try anyways. I guess I will need to group same field as one level down group’s field and suppress the group level so it will group for same thing in both level but one would be hidden.

    1. Say you had a parameter like GROUPPARAMETER – Group by Parameter with Boolean options for True / False. If you do your formula like: IF {@GROUPPARAMETER} = True THEN ELSE “” then it would take into account the True / False user input indicating whether you want to group or not. This way if you set the parameter to False the entire dataset will be set to “” (a blank) and it will not group because all are equal. If you set it to True then it will read your parameter and group by the specified output. With some formatting you should be able to get this working properly, it’s one of the main reasons I came up with this structure.

  2. Hi Mike,

    Fancy meeting you here 🙂 I have a Crystal Report completed with the exception of the additional parameter. I need to have the report prompt for Sales Employee first, and then Posting Date Range.. can you help? Currently it returns no value for Sales Employee, (report formula does not include) Posting Date Range parameter is working.

    Thanks in advance!

    Talk to you soon,
    Mary

    1. Hi Mary,

      I responded to your SG email with a suggestion.

      My Crystal Reports course will help you, watch the parameters lecture.

      Thanks!

      Mike

  3. I don’t know if anyone is still following this posting. Good information. I’m trying to find a solution to Groups and this might be it. However, I’m not sure Where and how to use the Conditional Parameter? I have a complex set of tables; one for Racks and another for IO Racks. Each has a sub-list coming from a different table. I need to test if an flag says “is IORack” and display one Group, or if not IORack pull data from the other table. Do I create two groups, one for each type, and disable?

    1. Hey Avram! For my method you make a single formula. Then in the formula you have the determination. So your formula would be like IF FIELD = True THEN “IO Rack” ELSE IF FIELD = FALSE THEN “Not IO Rack” ELSE “” Then you use the group expert and group by the formula. So as the fields are being output Crystal will determine what type they are and group them according to the formula. So it’s a single field but it does the data determinations there. Make sense?

Leave a Reply

Your email address will not be published. Required fields are marked *