Excel 2007 with Data in SQL 2008 View.
Trying to create a PivotTable from an external datasource (SQL 2008 - View). I would like to create a filter for a date range, since the view contains a lot of records. The problem is I can't figure out how to specify a filter for my view so the filtering takes place in SQL. Ideally user would be prompted somewhere in the spreadsheet for the parameters or a cell in excel would specify the dates.
Just add parameters on Excel side as you would do with a QueryTable. Those are processed on the server.
Go to MS Query and just put a criterion in Criteria grid. The only problem is, since that is a PivotTable, you can't make those parameters variable, you'll have to put costant dates in the query. In you don't like that, you can make a QueryTable on a separate sheet, add variables to it, make those point to input cells and then build the pivot using result range of the query as a data source.