Database Reference
In-Depth Information
dbo.fn_JSONCountryTree( [gEO rEGION]) + ' ],
"data": {
"GDP Per Capita": ' + cast( SUM(ROUND(FONR.Value /
131.487000,0)) as varchar(100)) + ',
"$area": ' + cast( SUM(ROUND(FONR.Value / 131.487000,0)) as
varchar(100)) + '
},
"id": "'+[GEO Region]+'",
"name": "'+[GEO Region]+'"
}'
FROM DBO.FactOECDNationalReserve FONR
INNER JOIN
dbo.DimCountry DC
on FONR.DimCountryID = dc.CountryID
INNER JOIN dbo.FactOECDPopulation FOP
on DC.CountryID = FOP.DimCountryID
and fop.DimDateID = 20110101
and FOP.DimOECDStatisticID = 197
and FOP.value is not null
where fonr.DimDateID = 20110901
GROUP BY [GEO Region]
UPDATE @tbl
SET @return = @return + JSON,
FUllJson = @return
SET @return = '{
"children": [' + @return + ' ],
"data": {},
"id": "root",
"name": "Regions by National Reserve and GDP per capita"
}'
select @return
GO
This code calls the previous function for each region, and inserts the result
into a table variable using a JSON template. It then uses the same variable
appending logic to put all the regions together into a single string, and adds
the outside wrappers of the JSON format for the treemaps.
To finish of the work, simply paste the results of this query into
Treemap.
js
where the
var json = {}
is. Double check to be sure the semicolon is
after the last brace!
