I understood the request a little differently: Choose a month/year in the SQL server data and show the corresponding month/year in the Excel data.
I set up some different varations of this in the attached and found some strange behavior. Here are the worksheets:
- Orig SQL Server - SQL server data using the Custom SQL data source
- Orig Excel - Excel data using the Sheet1 data source. The data sources have custom relationships set up on Year and Month of Dispatch Date in SQL Server to Year and Month of Month in the Excel data.
- Orig Blend Works - Shows that the blend is returning the correct results. This could be a usable worksheet all on it's own, without the Action Filter.
- Orig action on All Fields Fails - This dashboard has an Action Filter from the Orig SQL Server to the Orig Excel worksheets using "All Fields" for the Target Filters and it doesn't work. Clicking on any value in 2nd SQL Server makes no change in the other Orig Excel worksheet.
I don't know why this doesn't work, I'll be asking Tableau tech support about this.
Ok, next attempt at an Action Filter, let's use Selected Fields in the filter.
- 2nd SQL Server - a duplicate of the Orig SQL Server worksheet, so we can have a separate action filter
- 2nd Excel - a duplicate of the Orig Excel worksheet
- 2nd Action w/Selected Works - This dashboard has an Action Filter from the 2nd SQL Server worksheet to the 2nd Excel worksheet with the specific fields in the blend for the Target Filters and it works.
This one works, but is more effort to set up because we have to a) define the data relationships in the blend and then b) re-define those relationships for the Action Filter.
Third try...I first duplicated both of the datasources so the relationship between the data sources would be clean.
- Copy SQL Server - This shows the SQL Server data from the duplicated SQL data source
- Copy Excel - Shows the Excel data from the duplicated Excel data source. I created a calculated field called "Dispatch Date" that just has the value of Month, I set up the data relationship between the two data sources to be automatic.
- Copy Blend Works - a worksheet to show that the data relationship returns results. This could be used instead of the Action Filter to show the Excel data for a given year/month in the SQL Server data.
- Copy Action on All Fields Works - a dashboard with an Action Filter from the Copy SQL Server worksheet to the Copy Excel worksheet, set up on all fields. This Action Filter works.
I think I'd go with this solution, creating the extra field in the secondary data source avoids the need to do extra work to define custom relationships in both the blend and the Action Filters.