A new reporting language

A new language has been developed to utilise this structure and is described below. The language will develop with time. You can currently enter the language into the search box and press return.

Syntax

help

Shows all types available on the system. A good place to start.

help country

Shows all the parameters associated with a type. Note that some parameters may appear with a *. This shows that there may be many values returned (ie the currency Euro may have many countries that relate to it).

get area show

Get will get all the items of a given type and then show will display them.

get area

If you leave out the show then the system will try and guess what it needs to display. In this case it will show the name of the original items.

get area country currency

This will join all the above items together. Note there is no need to tell the system how to join the data as it already knows how to do this. Can you see why only one column was returned rather than the 3 you might expect?

get area show country show currency show

This version now shows all the columns. Could you alter this statement to see the fx rate for each currency?

get area show country show currency show as ccy

Here we have renamed the currency column to ccy.

get country show gdp_ppp_pp show top

Here we are showing countries and their GDP per person but, rather than showing all records, we are just showing the top (currently limited to only top 10) items ordered by GDP.

get country currency ?

The system would not understand the '?' and will display a list of all the fields you could have used at this point (fields associated with currency). If you don't want to do anything with currency anymore but instead go back to country then use an and.

get country show population show

Another example showing the population of each country.

get country show population < 5000

Here we are applying a condition so only those countries with a population under 5000 are shown. Note that here we aren't showing the population. Is the first show required.

get country show population < 5000 > 1000 show

All conditions apply to the last parameter so here we're restricting to populations under 5000 and over 1000. Note we're also displaying the population now as the show will still apply to the last parameter. The normal comparison operators >= <= != can all be used with numerics or character types.

get country like land

Here we're restricting to countries whose names are like 'land' (ie Poland). Use double quotes if there are spaces in the string otherwise they are optional.

get country like land not like island

Here we are applying 2 conditions. Note the use of not to reverse a match. You can also use = < > for strings or numbers.

get currency fx show

Note that all the currencies shown have fx rates. This is because any items missing a value will be removed from the report. But what if we want to see currencies where the exchange rate is missing?

get currency show not fx

Here the not is used to show only currencies where there was no fx value stored. What would happen if the show was after the fx?

get country like "s i" show all population show

As well as the NOT there is an ALL which does not delete items if there is no value for this field. Also note the use of quotes because the string has a space.

get country show population pct show

When a parameter is numeric we can change it to a percentage using pct. Here we're seeing what percentage of people live in each country. How would you go about getting the the percentage of people living in each continent.

get continent show country population sum pct show

When the last parameter is numeric we can turn the column into percentages using pct. How would you go about getting the the percentage of people living in each continent.

get country show currency show and population < 5000

Here we're showing the names of countries and their currency where the population is under 5000. Note the use of the 'and'. If we didn't use the and then the system would try and find a population parameter for country and fail. Using 'and' resets the last param to the name of the original type (country).

get area show country population sum show

The interesting thing in this example is the sum which will total the current parameter (population) for the previous items. Here it should show total population for each area. The field being totalled has to be numeric. Other similar commands are avg, min, max and cnt (for count). Could you change the example to just show areas where the total population is under 1 billion? or how how many people speak each language?

get country show population pct show

Here we are using pct to take a column of numbers and show percentages instead. This shows the percentage of people living in each country. Could you alter the example just to show countries with more than 5 percent or could you show percentage of people living in each continent?