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 