Using the Superstore English database (an Excel file) as an example, there are a few requirements:
1. In order to have Custom SQL available, you will need to open it as a "legacy connection." This is similar to opening the file normally, except that after you browse to and select the file, instead of clicking Open, click the down arrow and select "Open with Legacy Connection."
2. When writing the Custom SQL query for a legacy Excel connection, you need to append a "$" to the end of the table name, e.g. SELECT * FROM [Orders$]
3. I'm not clear how you can use a "single parameter" and use "BETWEEN," since "between" needs two parameters: a beginning and an ending value (date in your requested example). You can create a parameter from within the Custom SQL dialog box:
So, I created two parameters, for beginning and ending date values:
where [Order Date] between <Parameters.Begin Date>
and <Parameters.End Date>
Is this getting you close to what you are looking for?
where is this legacy option, i dont see in tableau desktop 9.2 or please suggest if i am missing this option
The screen shots and instructions I wrote previously were in Tableau Desktop 9.2, so I know it works. As shown in the screen shot, you must click the down-arrow on the Open button. This is only available when creating a new data source connection, not from the link in the UI, and not editing an existing connection.
- Create a new blank workbook.
- Click "Connect to Data"
- Click Excel
- Browse to your Repository\Datasources\9.2\en_US-US\Sample - Superstore.xls but do NOT click Open
- Click the drop-down arrow, and select Open with Legacy Connection.
Thanks a lot for your Information Brill Lyons.