Hi,
I am trying to embed metabase in an application with multi language support. I am using native SQL like data source. I can dynamically alias the column names according to the language selected. There is no problem when showing data as table but when it comes to draw a chart, data fields of chart gets lost as language changes. So, when i switch the language of application it always asks; Which fields do you want to use for the X and Y axes?
Is is possible to assign data fields by column order for charts when using native SQL? or any ideas?
Thanks,
Metin
my demo query as follows:
use test_db
/* getting user information, setting hierarchical rules and language config*/
declare @sql Nvarchar(2000)
declare @username varchar(50)
declare @userid int
declare @managerid int
create table #temp (id int)
declare @language varchar(2)
declare @col1 varchar(50)
declare @col2 varchar(50)
declare @col3 varchar(50)
declare @col4 varchar(50)
declare @col5 varchar(50)
declare @col6 varchar(50)
declare @col7 varchar(50)
declare @col8 varchar(50)
declare @col9 varchar(50)
declare @col10 varchar(50)
declare @col11 varchar(50)
declare @col12 varchar(50)
/*passing parameters from application to Metabase */
set @language = {{languageSelection}}
set @username = {{user_id}}
set @userid = (select employeeid from employees where EmployeeName = @username)
set @managerid = (select managerid from employees where EmployeeName = @username)
BEGIN
if @managerid is null
insert into #temp (id) select employeeid from employees
else if @userid in (select managerid from employees)
insert into #temp (id) select employeeid from employees
where ManagerId = @userid
else
insert into #temp (id) values(@userid)
END
/*setting column_names according to language setting*/
set @col1 = (select column_name from language_lookup where col_order = 1 and language = @language)
set @col2 = (select column_name from language_lookup where col_order = 2 and language = @language)
set @col3 = (select column_name from language_lookup where col_order = 3 and language = @language)
set @col4 = (select column_name from language_lookup where col_order = 4 and language = @language)
set @col5 = (select column_name from language_lookup where col_order = 5 and language = @language)
set @col6 = (select column_name from language_lookup where col_order = 6 and language = @language)
set @col7 = (select column_name from language_lookup where col_order = 7 and language = @language)
set @col8 = (select column_name from language_lookup where col_order = 8 and language = @language)
set @col9 = (select column_name from language_lookup where col_order = 9 and language = @language)
set @col10 = (select column_name from language_lookup where col_order = 10 and language = @language)
set @col11 = (select column_name from language_lookup where col_order = 11 and language = @language)
set @col12 = (select column_name from language_lookup where col_order = 12 and language = @language)
/*query table*/
set @sql = 'select "Retailer country" as ' + @col1 +
', "Order method type" as ' + @col2 +
', "Retailer type" as ' + @col3 +
', "Product line" as ' + @col4 +
', "Product type" as ' + @col5 +
', "Product" as ' + @col6 +
', "Year" as ' + @col7 +
', "Quarter" as ' + @col8 +
', "Revenue" as ' + @col9 +
', "Quantity" as ' + @col10 +
', "Gross margin" as ' + @col11 +
', t2.EmployeeName as ' + @col12 +
' from sales t1 left join employees t2 on t1.EmployeeId = t2.EmployeeId
where t1.EmployeeId in (select id from #temp)'
BEGIN
if @userid is null
select 'Wrong User Id'
else exec (@sql)
END
drop table #temp