ExcelBanter - Excel Pivot tables connecting to sql

You can use build an external data query (with parameters) to import the data
into a list on a worksheet, then use the query's result range to be the data
range for the pivottable. Testing this on Excel 2000, the pivottable data
range did properly update to match the new size of the query result range but
I did have to manually refresh the pivottable after the query was rerun. You
could automate (e.g. with a Command button) so that after entering the
parameters your code would first refresh the querytable and then refresh the
pivottable.

Another option is to set up the pivottable using the full data set and then
use VBA code to modify its PivotTable.PivotCache.CommandText property, which
contains the SQL query. You could run VBA code after your users enter the
parameters so that it modifies the CommandText by substituting the new
parameter values; e.g:

Sub PivotUpdate()
Dim OldSQL as String

With Worksheets("SheetName").PivotTables(1).PivotCache
OldSQL = .CommandText ' store the unmodified query
' Next line will add a condition to the SQL query
.CommandText = Replace(.CommandText, "WHERE ", "WHERE
COL1="&Range("A1").Value
' this assumed you wanted cell A1 to be the criteria for COL1
.Refresh
.CommandText = OldSQL ' to leave it unmodified so it can be reused
End With
End Sub

Just the basics but hope you get the idea.
--
- K Dales

"Justin" wrote:

Need some help. I need to get data from sql server and create a pivot table.
Unfor, i need to have user tell what date range the pivot table to show.
I just want to know what is an easy way to make a pivot table with parameter
that is link to a view in sql

I can't bring in the data since it is more than 65k, that is why a view was
created/