Hi,
I write custom query for advance report on module Accounts.
Part of query is:
IFNULL(accounts.billing_address_country, '')
Field is dropdown list.
In a normal report this field shows full country name
But in custom query it only shows country code with 2 characters.
How can I add full country name of an account to custom query?
/and
Hi Andreas Pfau ,
Sugar's standard reporting natively displays the dropdown display value as you have noted. The custom queries available in the Advanced Reports module directly queries the database and does not model the results, so that is why you see the 2 character code corresponding to your dropdown list entry. In order to display a country name, your query would need to use a CASE statement in the SELECT portion of your query like the following:
SELECT
id,
name,
CASE
WHEN
billing_address_country = 'US'
THEN
'United States'
WHEN
billing_address_country = 'CA'
THEN
'Canada'
WHEN
billing_address_country = 'UK'
THEN
'United Kingdom'
ELSE
billing_address_country
END
FROM
accounts
WHERE
deleted = 0;
The ELSE portion of that query ensures that value of the billing_address_country will be displayed if you did not account for one of the possible values in the WHEN statements. If you do not include the ELSE portion, then any values returned that do not have a matching option in the WHEN statements will display as blank in the query results.
Chris
Hi Andreas Pfau ,
Sugar's standard reporting natively displays the dropdown display value as you have noted. The custom queries available in the Advanced Reports module directly queries the database and does not model the results, so that is why you see the 2 character code corresponding to your dropdown list entry. In order to display a country name, your query would need to use a CASE statement in the SELECT portion of your query like the following:
SELECT
id,
name,
CASE
WHEN
billing_address_country = 'US'
THEN
'United States'
WHEN
billing_address_country = 'CA'
THEN
'Canada'
WHEN
billing_address_country = 'UK'
THEN
'United Kingdom'
ELSE
billing_address_country
END
FROM
accounts
WHERE
deleted = 0;
The ELSE portion of that query ensures that value of the billing_address_country will be displayed if you did not account for one of the possible values in the WHEN statements. If you do not include the ELSE portion, then any values returned that do not have a matching option in the WHEN statements will display as blank in the query results.
Chris