Count calculation variable

[henryedsl]

Hello guys.

I'm gonna try to explain my issue with a little example.

Let's suposse i have a report that shows countries from all over the world, like this:

Country - Continent
USA - America
Canada - America
England - Europe
Spain - Europe
France - Europe
Japan - Asia
China - Asia

In the Summary section of the report, i want to show the number of countries per continent. According to the example above, the report should show something like this:

America total countries: 2
Europe total countries: 3
Asia total countries: 2

I have 3 variables: TOTAL_AMERICA_COUNTRIES, TOTAL_EUROPE_COUNTRIES, TOTAL_ASIA_COUNTRIES
Type: Integer
Calculation: Count
Reset type: Report

But i dont know how to exactly make this variables to show the number of countries per continent.

I hope you can give me a hand.


Thanks a lot for your help.

Best regards.

[henryedsl]

Hello!

I solved my problem, i dont know whether it's been the best solution or not, but it's working for me :)
 
This is what i did:

In the SQL sentence a created 3 extra columns, like this:

SELECT *,
                 case when continent_id = 1 then 'America' else null end as america_country,       
                 case when continent_id = 2 then 'Europe' else null end as europe_country,
                 case when continent_id = 3 then 'Asia' else null end as asia_country,
FROM countries;

Now, in the report this is what i set to every variable i created:

TOTAL_AMERICA_COUNTRIES
Type: Integer
Calculation: Count
Reset type: Report
Variable Expression: $F{america_country}

TOTAL_EUROPE_COUNTRIES
Type: Integer
Calculation: Count
Reset type: Report
Variable Expression: $F{europe_country}

TOTAL_ASIA_COUNTRIES
Type: Integer
Calculation: Count
Reset type: Report
Variable Expression: $F{asia_country}


Best regards.