1

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?


Thanks

0

I assume you're trying to run your OBDC-connected query in Excel Viewer Webpart. If so is the case your error message is correct and you can only run these queries in Excel Client, and not the Web part.

The best way to start is to read Difference Between Excel Services, Excel Web Access, Excel Web App, and Excel Online and Comparing Excel Services and Excel Web App (SharePoint 2013), and you get an overview of what is and what isn't in different scenarios. Then decide which step to take to get things working the way you want.

My guess is that you already know Data authentication for Excel Services in SharePoint Server 2013 and have successfully implemented a solution all the way. When I run into trouble with permission on external data sources I usually set up Secure Store service to get things running properly. See Use Excel Services with Secure Store Service in SharePoint Server 2013.

  • 1
    Thank for the reply, I'm not using Web parts yet, just viewing my excel in share point site. I'll check the links you mentioned and update this thread. – MSBI-Geek Jun 14 '16 at 12:21
  • 1
    @MSBI-Geek if you're viewing your excel file in the browser interactively, you are using web parts, whether you realize it or not. – Dan Henderson Jun 14 '16 at 15:08
  • @benny I have gone through the links you sent me, very useful. thanks. I have already finished the Data Authentication bit and can view Excel with data from external sources if there are no parameters. I have tried to create a web part to use parameters as suggested here. This only works for normal parameters but not with the query parameters. This is so important, I'm wondering if you can suggest any other way of achieving this. When the query can work why can't the query with parameters! – MSBI-Geek Jun 14 '16 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.