Selecting chart fields by column order when using native SQL


#1

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