June 25, 2015 at 6:56 pm
I'm using SQL Server 2012 and Excel 2013...
I know I can filter everything in Excel -- Can I pass parameters from Excel to a stored procedure and filter in SQL Server? If so, how?
Thanks!
July 2, 2015 at 12:33 pm
Not as far as I know, with Power Pivot only. You may be able to do something with Power Query, and then use the results as input for your Power Pivot model.
July 2, 2015 at 12:55 pm
I think you can, it's just that the interface is extremely unfriendly. (Another reason it works much better against a DW).
After posting this, I got off my lazy butt and found this in BI Tools for Excel Analysts
In VBA, you modify the connection's CommandText property.
Sub RefreshQuery()
With ActiveWorkbook.Connections("Facility Services")
.OLEDBConnection.CommandText = "SELECT * FROM [SalesByEmployee] WHERE [Market] = '" & Range("C2").Value & "'"
.Refresh
End With
(Just seems silly to bring back all the records from the table and then discard most of them...)
July 2, 2015 at 1:02 pm
pietlinden (7/2/2015)
I think you can, it's just that the interface is extremely unfriendly. (Another reason it works much better against a DW).After posting this, I got off my lazy butt and found this in BI Tools for Excel Analysts
In VBA, you modify the connection's CommandText property.
Sub RefreshQuery()With ActiveWorkbook.Connections("Facility Services")
.OLEDBConnection.CommandText = "SELECT * FROM [SalesByEmployee] WHERE [Market] = '" & Range("C2").Value & "'"
.Refresh
End With
(Just seems silly to bring back all the records from the table and then discard most of them...)
Yeah...at that point though, your changing connection properties and not necessarily dealing with Power Pivot. It would have been nice if one could have done it Power Pivot natively, but I guess Power Query would fulfill that role in future anyways.
July 2, 2015 at 1:10 pm
That's what I was thinking... Given that not everyone has a DW handy, it would make sense to me to be able to simplify the connection to the database... so use a stored procedure. I guess MS disagrees, though... :rolleyes:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply