-
Notifications
You must be signed in to change notification settings - Fork 2
5. Report examples
Let’s suppose we have some generic information about all Caribbean Countries: the country name, the governmental form, the population, the names of all cities and also the languages spoken in each Caribbean country (source is MySQL sample database world).
We could show first a list of all countries names and to offer the report user the option to go in more detail for each country. In EspressoViews this could be build up like this:
You may already see that this report uses three SQL entities .
The first query is Caribbean Countries (CountryCode, CountryName, Population, GovernmentForm) transmits the column Country Code to its two children: Languages Spoken in the Caribbean (CountryCode, Language, Percentage) and Caribbean Cities (CountryCode, CityName, District, Population).
Both children can apply the transmitted column name CountryCode:
Below is the full report definition:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ROOT>
<SQLQueryDefinition>
<SQLQueryLabel>Caribbean Countries</SQLQueryLabel>
<SQLQuery>
select distinct Code as CountryCode, a.Name as CountryName,
a.Population,GovernmentForm
from country a
where region='Caribbean'
order by Population desc;
</SQLQuery>
<TransmittedConditionColumns>CountryCode</TransmittedConditionColumns>
<ResultColumns>not: CountryCode,language</ResultColumns>
</SQLQueryDefinition>
<SQLQueryDefinition suppressDisplayIfNoData="true">
<SQLQueryLabel>Languages Spoken in the Caribbean</SQLQueryLabel>
<SuperSQLQuery>Caribbean Countries</SuperSQLQuery>
<SQLQuery>
select
distinct percentage, Code as CountryCode, a.Name as CountryName,
a.Population, c.Language, GovernmentForm
from country a, countrylanguage c
where region='Caribbean'
and percentage>0.00
and c.CountryCode =a.code
order by 1
</SQLQuery>
<ResultColumns>Language, percentage</ResultColumns>
<TransmittedConditionColumns>Language</TransmittedConditionColumns>
</SQLQueryDefinition>
<SQLQueryDefinition suppressDisplayIfNoData="true">
<SQLQueryLabel>Caribbean Cities</SQLQueryLabel>
<SuperSQLQuery>Caribbean Countries</SuperSQLQuery>
<SQLQuery>
select ID, Name as CityName, CountryCode, District, Population
from city a
</SQLQuery>
<ResultColumns>not: CountryCode,id</ResultColumns>
</SQLQueryDefinition>
</ROOT>
You could also say, I want to see first all the Caribbean Languages spoken and then unfold the Countries, where this languages are spoken and then, for each country I want to see a list of its cities. Then you might generate this view:
The first query Languages Spoken in the Caribbean (Language, Speakers) retrieves a the total number of Caribbean speakers per language and transmits the column name Language to the children query Caribbean Country (CountryCode ,Language, Percentage, CountryName, Population, GovernmentForm). This entity transmits the column name CountryCode to its child entity CaribbeanCities (CityID, CountryCode, CityName, District, Population).
Below is the full report definition:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ROOT>
<SQLQueryDefinition>
<SQLQueryLabel>Languages Spoken in the Caribbean</SQLQueryLabel>
<SQLQuery>
select Language, sum(Population) as Speakers
from countrylanguage a,country b
where a.percentage>0.00
and a.CountryCode =b.Code
and b.region='Caribbean'
group by language
order by 2 desc
</SQLQuery>
<TransmittedConditionColumns>Language</TransmittedConditionColumns>
</SQLQueryDefinition>
<SQLQueryDefinition suppressDisplayIfNoData="true">
<SQLQueryLabel>Caribbean Countries</SQLQueryLabel>
<SuperSQLQuery>Languages Spoken in the Caribbean</SuperSQLQuery>
<SQLQuery>
select
distinct percentage, Code as CountryCode, a.Name as CountryName,
a.Population, c.Language, GovernmentForm
from country a, countrylanguage c
where region='Caribbean'
and percentage>0.00
and c.CountryCode =a.code
order by 1 desc;
</SQLQuery>
<TransmittedConditionColumns>CountryCode</TransmittedConditionColumns>
<ResultColumns>not: CountryCode,language</ResultColumns>
</SQLQueryDefinition>
<SQLQueryDefinition suppressDisplayIfNoData="true">
<SQLQueryLabel>Caribbean Cities</SQLQueryLabel>
<SuperSQLQuery>Caribbean Countries</SuperSQLQuery>
<SQLQuery>
select ID as CityID, Name as CityName, CountryCode, District, Population
from city a
</SQLQuery>
<ResultColumns>not: CountryCode,CityID</ResultColumns>
</SQLQueryDefinition>
</ROOT>