Is the any possibility to add parameters into dataset table (in Data Prep)

I am using SQL Server tables as a source for datasets.
I would like to have parameters in Data Prep page. That allows me to transfer selected values from Top based filters (in analysis) directly into the dataset table.

For example, we have the next structure:
Table1 β†’ (left join ) table2
Table1 β†’ (left join ) table3

As a result, QS will produce the next query

SELECT 
  t1.col1, t1.col2, 
  t2.col3
FROM table1 as t1
LEFT JOIN 
( 
Select col3 , col4
FROM dbo.table2
) as t2 on t2.col2=t1.col2
WHERE t2.col4  IN (value1, value2, ...)

But above query does not cover the situation when I need to take all rows from table1 and only filtered rows from table2.
AS a result I want to have the next sql query that was produced by QS like this:


SELECT 
  t1.col1, t1.col2, 
  t2.col3
FROM table1 as t1
LEFT JOIN 
( 
Select col3 
FROM dbo.table2
WHERE col4  IN (value1, value2, ...) 
) as t2 on t2.col2=t1.col2

Where value1, value2 are values that were selected from top filters in alylysis UI.
Also it would be great when this condition will have the next variance
col4 >= value1 and col4 <=value2

OΠΊ other variance: at the begin of query will declare the next temp table @TempTable with values and this table is used below like this.

Declare @TempTable table (col1 nvarchar(100))
INSERT INTO  @TempTable   values('value1')
INSERT INTO  @TempTable   values('value2')

SELECT 
  t1.col1, t1.col2, 
  t2.col3
FROM table1 as t1
LEFT JOIN 
( 
Select t2.col3 
FROM dbo.table2 as t2
INNER JOIN @TempTable as tt ON t2.col4 = tt.col1
) as t2 on t2.col2=t1.col2

I would be really great to have this possibility.
Also I have a couple other idea about functionality that would be great to have :slight_smile:

Hi @Petro82, we are working on a feature called dataset parameter which is targeted to enable user to create parameter and use it in direct query (through customSQL). We target to launch some capability by the end of this year with some limitations. It will be great to hear your detailed use cases for us to better serve the need. Let me connect with you offline and schedule some discussion. Thanks!

1 Like

Ok. Lets connect in LinkedIn