How to run sql based on filter condition in Tableau report

Question

I have this data output in Sql server for a table to which i have extracted in tableau using extract data :-

Requirement is that when user select source_nbr and stock_date which is under filter criteria in tableau dashboard, a sql query should fire which should bring the custom result from the extracted data in tableau and show it on tableau dashboard.

Query which will be fired based on user selection is :-

select top 1 with ties
   source_nbr,
   source_cc,
   stock_date,
   item_nbr,
   fin_rpt_grp_code,
   base_div_code,
   dept_nbr,
   item_desc,
   item_upc
from dbo.inventory_stock_status
WHERE source_nbr = <UserSelectedsource_nbr> and stock_date < <userSelectedDate> 
order by row_number() over (partition by item_nbr order by stock_date desc);

This is how my filtered column looks like in tableau workbook :-

In a nutshell, when user selects source nbr and stock data on right side of tableau dashboard, above SQL should run behind the scene on extracted data and output data is shown to user.

Hope i made my question clear.

You will have a better experience with Tableau if you learn how its core features work and allow Tableau to generate SQL as needed - rather than trying to micromanage the SQL yourself. That's akin to trying to change the assembly code generated by your compiler -- useful in rare circumstances but in general an inefficient way to work — Alex Blakemore

Neeraj Agarwal Neeraj Agarwal · Accepted Answer · 2019-10-19T22:57:11

You can use a cte as follows:

with table1 as
(
select
   source_nbr,
   source_cc,
   stock_date,
   item_nbr,
   fin_rpt_grp_code,
   base_div_code,
   dept_nbr,
   item_desc,
   item_upc,
   row_number() over (partition by item_nbr order by stock_date desc) as rn
from dbo.inventory_stock_status
WHERE source_nbr = <UserSelectedsource_nbr> and stock_date < <userSelectedDate>
)
select
   source_nbr,
   source_cc,
   stock_date,
   item_nbr,
   fin_rpt_grp_code,
   base_div_code,
   dept_nbr,
   item_desc,
   item_upc
from table1
order by rn

How to run sql based on filter condition in Tableau report

1 Answers