Grouping a Group in Crystal

Posted on 2011-05-02
Last Modified: 2012-05-11
Hello Experts,

I need help with a Crystal Report. I am tracking the # of events reported by country per month with a chart. So I have a bar chart on change of Date1 (by month), followed by change of Country, which counts Events.

By simply using Running Totals, here is a sample of what I get for April of 2011:

Argentina = 5
Brazil = 8
Canada = 2
USA = 27
Germany =1
Australia = 7
France = 1
Algeria = 2

Now the problem: I would like to then place each country into 1 of 3 groups depending on the number of events reported, and then I would like to chart those three groups by month. So, the result would be three groups: group1 (<3 events), group2 (>3 and <5 events), and group3 (>5 events). For April of 2011, it would look like this. The chart does not need to list each country within each group, just show group1, 2 and 3 side by side for each month reported.

Group1 = 4 (Canada, Germany, France, Algeria)
Group2 = 1 (Argentina)
Group3 = 3 (Brazil, USA, Australia).

How can I do this?

Question by:Marcos27
    LVL 100

    Expert Comment

    How are you calculating the number of events in a country?


    Author Comment

    I'm just grouping by the Country field. In the Chart it summarizes that grouping, and in the listed records I'm just using a Running Total field that counts the total events for each value in Group #1.
    LVL 100

    Expert Comment

    So the data in the table is like

    Country name, Date of Event, Event Description

    You are showing a count of events by country in the chart.

    What do you want the new chart to show.
    Count of countries iin each group?



    Author Comment

    Correct. I am showing count of events per month by country. Now I would like to create 3 new groups, and organized each country into those groups. Group 1 will be less than 3 events. Group 2 will be between 3-5 events. Group 3 will be more than 5 events. So, according to this logic, France (with one event in April) would be in Group 1, and Argentina (with 5 events in April) would be in group 2 etc. I'd like to chart those three sub-groups. Is that possible in Crystal?
    LVL 100

    Expert Comment

    Not sure.  IN general you can't do a summary on a summary field.

    I'll build a small dataset and try a few things.

    LVL 100

    Accepted Solution

    Try this idea

    Create a formula

    If Count ({Events.EventType}, {Events.Country}) < 3 then
        "Less 3"
    Else If Count ({Events.EventType}, {Events.Country}) < 5 then
        "Less 5"

    Use that formula for the grouping of the chart


    Author Closing Comment

    Ok, couldn't quite get this to work with my data, but It's no longer a priority. If I need it, I'll reopen the question with more detail. Thanks!

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now