Alternatives to changing PowerPivot SQL string

[TerryHogarth21]

Anybody have any insights on programmatically changing the SQL string in a PowerPivot?

Do you need to use Visual Studio to get this done at the moment or C++?

I've been searching for a while and I think there's no way interacting with PowerPivot API through VBA with the current version (I'm on Excel 2013 32 Bit).

Some people I work with don't know SQL so it's hard for them to change stuff they want in the query to get different views. We don't want to give people everything by selecting all data and having them filter it out etc. (Well technically we could if everybody switched to Excel 64 bit - but that's another story).

I made some forms in Excel to query the server and include fields and all field values, then had some VBA code to create the string based on what users selected in the form, then users could copy this string manually and paste into the table properties in PowerPivot and have it refresh. That seems like a lot of work and probably not the best way to do it.

So any other ideas without having to learn other programming languages?

 

[mole999]

whats your query look like

I use a VB string within excel, and some bits i use additional VB strings to save rewriting many constant bits

combine that with a CASE statement and you could be there, sounds really easy to get so complex

 

[TerryHogarth21]

whats your query look like

I use a VB string within excel, and some bits i use additional VB strings to save rewriting many constant bits

combine that with a CASE statement and you could be there, sounds really easy to get so complex

Let's say there are 20 fields(field1,field2, etc) and each of the fields could have 20,000 unique values in them(The query can change depending upon what the user wants).
Someone could want

 SELECT field1, field2, field3 FROM TABLE1 WHERE Field1 IN('A','B','C','D');

then someone could want

 SELECT field1, field2 FROM TABLE1 WHERE Field1 IN('A','F','Z','ZZ') AND Field2 ='Test1';

When user's need to select specific values in these fields, that in essence becomes part of the WHERE CLAUSE IN ('blah','blah','bah') Are you suggesting to write a CASE STATEMENT for that many combinations in the PowerPivot SQL input?

I never liked hard-coding the values in case statements in direct SQL strings (maybe in VBA is ok with a changing variable) since I have no idea if these values would change in the future due to other decisions.

 

[mole999]

I think with too many options a CASE would be unmanagable

 

[scottsen]

My gut is to steer you towards Power Query. It can pull data from an excel table... and should allow the user to update values in said table... and hopefully maybe possibly pass them into the sql query.

I will have to pull in the actual pros to be sure though.

You have no realistic options for programmatic updates to the query directly in power pivot.