I'm new to Excel Services.
My requirement is to use parameters in the Excel spreadsheet to refresh the data from External data sources.
In an Excel spread sheet, I have created an ODBC connection to get data from SQL Server. The basic query works fine and when I publish the Excel to Share Point 2013 works great. But the moment I add a parameter to the SQL Query in Excel the Excel does not work in Share Point, which is so annoying. I have been looking for a solution for a long time and tried different solutions suggested by different people, such as defining a named cell and map that cell as input to the query parameter and then adding the parameter while publishing Excel to Share Point. But it doesn't work. Some people suggest to use Webparts or REST api. Can't it be done in a simple way with out using these? Why Microsoft makes this simple requirement so complex. Why are there parameters option available when publishing Excel when they don't work?
The error message i get is
We're sorry. We can't open the workbook in the browser because it uses these >unsupported features:
PivotTable reports that are set to refresh when the item in a report filter >changes, or external data ranges (also called query tables) that use parameters Do you want to open the file in Excel instead?
It is very important for me to find a solution for this, Please suggest a solution or a place where I can look for the solution. Is there any way I can get help from Microsoft?