none
PowerPivot and query parameters

    Question

  • Is there any plans to support parameterized sql queries?If I try to execute query it requests declared parameters by means of form,but I can't check resulting query.Why?It says "Must declare the scalar variable ...". 
    WTF?

    Regards.
    P.S.
    Without parameterizing PowerPivot is useless  IMHO.
    Thursday, November 19, 2009 6:52 AM

Answers

  • 1) While Importing you restrict the SQL query with the parameters. Declare the parameters and provide the values. Something like below

    Declare @datestart as datetime
    Declare @dateend as datetime
    Set @datestart='01/01/2009'
    Set @dateend='01/10/2009'

    Select * from foobar where date between @datestart and @dateend

    2) Finishing importing your data

    3) Once the date is imported, if you decide to change the date range, click on Table Ribbon on the PowerPivot Window
    4) click on Edit Table Properties
    5) This will present you the same query you created. You can now change the date range and press save.
    6) This will now refresh the entire data set based on your new query.

    Hope this is clear.

    Thanks,
    Deva [MSFT]
    Deva
    • Marked as answer by XASD Thursday, November 19, 2009 5:02 PM
    Thursday, November 19, 2009 4:58 PM

All replies

  • Could you please clarify whether you are using the regular Pivot table in Excel to connect to SQL Server, or you are running the November CTP PowerPivot inside Excel? Can you please provide detailed steps you took that led to error "Must declare the scalar variable"? And what build for each of the components involved - Excel, PowerPivot, SQL server?

    Thanks,
    Lisa
    Thursday, November 19, 2009 3:39 PM
  • I'm talking about November CTP PowerPivot inside Excel(14.04536.1000).From "PowerPivot window/From Database/From SQL Server" I select my server and choose to create query as SQL text.That sql contains some parameters in form of "@my_param...".After that,If I select "Project/Execute" I see parameter window is shown.But if I return to "create sql query" window and press "check" button",I see "Must declare the scalar variable ..." and nothing happens.
    As I can understand PowerPivot does not support parametrized queries at all-you must implement some means to input query parameters otherwise( I can't see any trails of customization for such important feature in PowerPivot).

    Thanks.

    Thursday, November 19, 2009 3:56 PM
  • Hi XASD,

    Are the parameter values are static? Is this just a raw SQL with parameters or stored procedure with parameters?
    How do you plan to supply the parameter values?
    Did you try declaring a varaible, with the name of your parameter and immediately setting the parameter value and then executing the query?


    Thanks,
    Deva [MSFT]
    Deva
    Thursday, November 19, 2009 4:16 PM
  • Keep in mind that PowerPivot is designed to support self-service BI, so the majority of users are likely to be power Excel users and rather than seasoned SQL experts.

    When I tried creating a query that uses parameters in the query designer, it would allow me to specify that the query would use parameters. That resulted in the following SQL Query against the AdventureWorksDW2008 relational database:
    SELECT
      DimCustomer.CustomerKey
      ,DimCustomer.GeographyKey AS [DimCustomer GeographyKey]
      ,DimCustomer.FirstName
      ,DimCustomer.MiddleName
      ,DimCustomer.LastName
      ,DimGeography.GeographyKey AS [DimGeography GeographyKey]
      ,DimGeography.City
      ,DimGeography.StateProvinceCode
      ,DimGeography.StateProvinceName
      ,DimGeography.CountryRegionCode
      ,DimGeography.EnglishCountryRegionName
      ,DimGeography.PostalCode
    FROM
      DimGeography
      INNER JOIN DimCustomer
        ON DimGeography.GeographyKey = DimCustomer.GeographyKey
    WHERE
      DimGeography.EnglishCountryRegionName LIKE @EnglishCountryRegionName

    However, when I clicked the "OK" button on the Table Import Wizard, I got the following message:

    "Parameters are not supported by the wizard. Make sure that none of the "Parameter" checkboxes are checked."

    When I pasted the query above into the SQL Statement on the first dialog box of the Table Import Wizard and clicked the Validate button, I got the same message "Must declare the scalar variable...."

    I was able to work around that by using the following SQL syntax:

    DECLARE @EnglishCountryRegionName as nvarchar(50)
    SET @EnglishCountryRegionName = 'United States'
    SELECT
      DimCustomer.CustomerKey
      ,DimCustomer.GeographyKey AS [DimCustomer GeographyKey]
      ,DimCustomer.FirstName
      ,DimCustomer.MiddleName
      ,DimCustomer.LastName
      ,DimGeography.GeographyKey AS [DimGeography GeographyKey]
      ,DimGeography.City
      ,DimGeography.StateProvinceCode
      ,DimGeography.StateProvinceName
      ,DimGeography.CountryRegionCode
      ,DimGeography.EnglishCountryRegionName
      ,DimGeography.PostalCode
    FROM
      DimGeography
      INNER JOIN DimCustomer
        ON DimGeography.GeographyKey = DimCustomer.GeographyKey
    WHERE
      DimGeography.EnglishCountryRegionName LIKE @EnglishCountryRegionName

    Thursday, November 19, 2009 4:31 PM
  • Are the parameter values are static? Is this just a raw SQL with parameters or stored procedure with parameters?
    Parameters can't be static, if you saw parameter's enter form,you know what I mean.Some kind of user interface must exists to supply query parameters.
    Simplistic version of it you can observe at "execute" stage in "project" mode. Ability to filter query based on USER supplied params is essential for analitic tools like PowerPivot,else you risk to wait query data forever...no matter how powerful/optimized internal engine is.
    Parametrized queries is "must have",if you get serious about PowerPivot IMHO.I'm not declaring variables.I want parametrized quries,so I declare parameters.Naming scheme is same for both indeed:)
    For example,I want to provide @dat1 and @dat2 to limit the range of selected rows.

    Thanks.
    Thursday, November 19, 2009 4:39 PM
  • So,you confirm that parameterized queries is NOT supported by PowerPivot?Sorry, I can't see how it could be useful then...
    Maybe I can't understand something,but our internal analitic tools all parametrized by default.

    Thanks.
    Thursday, November 19, 2009 4:46 PM
  • Thanks for the explanation.

    It doesn't have to be static but while importing the data you need to pass parameters value for us to pull the data.
    Let's take the same case you have mentioned @dat1 and @dat2.
    Let's say you while importing, you provide us the value @dat1=something and @dat2=something.
    You import your data, you do the analysis for that date range.

    Now you decided, hmm, I need to change the data range.
    You can then go back and edit table properties and can change it to new values. We will then refresh automatically for that date range.
    Also the @dat1 doesn't have to be static, you have provide something like today-10 to today+10 (just example not the right syntax though).

    Note: While importing, the only place you can provide us the information is the query page otherwise it will be too annoying if we keep popping up provide us the parameter value while the importing in in progress.


    Thanks,
    Deva [MSFT}
    Deva
    Thursday, November 19, 2009 4:48 PM
  • Now you decided, hmm, I need to change the data range.
    You can then go back and edit table properties and can change it to new values. We will then refresh automatically for that date range.

    Deva
    What "table properties" and where? Can you explain details? I have sql query and nothing more and I want to restrict returned "result set".
    <quote>Let's say you while importing, you provide us the value @dat1=something and @dat2=something.</quote>
    That's what I'm talking about-how I can "provide" values for parameterized sql query if I don't have UI for that purpose?

    Thanks.
    Thursday, November 19, 2009 4:54 PM
  • 1) While Importing you restrict the SQL query with the parameters. Declare the parameters and provide the values. Something like below

    Declare @datestart as datetime
    Declare @dateend as datetime
    Set @datestart='01/01/2009'
    Set @dateend='01/10/2009'

    Select * from foobar where date between @datestart and @dateend

    2) Finishing importing your data

    3) Once the date is imported, if you decide to change the date range, click on Table Ribbon on the PowerPivot Window
    4) click on Edit Table Properties
    5) This will present you the same query you created. You can now change the date range and press save.
    6) This will now refresh the entire data set based on your new query.

    Hope this is clear.

    Thanks,
    Deva [MSFT]
    Deva
    • Marked as answer by XASD Thursday, November 19, 2009 5:02 PM
    Thursday, November 19, 2009 4:58 PM
  • That's exactly I was afraid of,thanks for clarification.

    Thanks.
    Thursday, November 19, 2009 5:02 PM
  • Hi Deva,

    Is this the proposed solution for all source platforms:  Oracle, Informix, etc...? Or an interim fix for sql server only?  As and end user BI tool, are we really expecting end users to start writting batch sql code?


    Kyle Mossman
    Monday, November 23, 2009 5:25 PM
  • Hi, Kyle,

    As stated in John Desch's earlier rely in this post, the GUI currently does not support parametered query. The workaround is to write explicit SQL query with parameter as Deva has suggested. There is a bug filed to track the GUI behavior and the bug is currently being considered in next major release of PowerPivot.

    Thanks,

    Lisa

    Tuesday, April 06, 2010 5:33 PM
  • Will the GUI ever support this? Even Microsoft Query from the 1990's supports parameters and there is no way to do this in the newest technology? I hate using Microsoft Query because there are numerous things that don't work right when querying SQL 2005 or 2008 servers but even as old as it is, it's still worth ten times more than this new technology.

    The purpose of being able to supply parameters on the fly is that you can record or write macros to setup the same experience and view for every user that uses the spreadsheet. For example: We woud use 1 spreadsheet with queries to filter the data based on a company id that the user specifies. We do this by linking a parameter to a cell in the spreadsheet. We couldn't possibly bring in every company's data and then filter it since we are talking about several million rows of data. We can't expect a user who knows little about Excel queries to physically go into the query and change parameter values. Typically, we would run a macro when the spreadsheet is open or when a button is pushed to refresh the query and format the spreadsheet in a way that matches the intended purpose and audience.

    I can't imagine that this hasn't been requested by many people/customers. It seems like a big thing to be missing. Could you please let us know if this is even a thought or feature that might make it in a service pack?

    Thanks,

    Bryan

    • Proposed as answer by jrome99 Tuesday, February 22, 2011 10:57 PM
    • Unproposed as answer by jrome99 Tuesday, February 22, 2011 10:58 PM
    Tuesday, May 25, 2010 9:22 PM
  • Hi Bryan,

    "Even Microsoft Query from the 1990's supports parameters and there is no way to do this in the newest technology?"

    Bear in mind that parameter support in the user interface is an Excel thing, and has nothing to do with Query. You can paste a parameter query in the Connection Properties dialog box (Definition tab), without any involvement from Query. Bear in mind also that Excel has never supported parameter queries for PivotTables natively, whether or not Query is used to import the data.

    "For example: We woud use 1 spreadsheet with queries to filter the data based on a company id that the user specifies. We do this by linking a parameter to a cell in the spreadsheet. We couldn't possibly bring in every company's data and then filter it since we are talking about several million rows of data. We can't expect a user who knows little about Excel queries to physically go into the query and change parameter values."

    Note few of things: 1) Unlike Excel, PowerPivot is designed to handle millions of rows of data. If it helps, you could create a query that imports only the last x years of data. 2) Like an Analysis Services cube, the principal design goal is to run the PowerPivot model on a server (SharePoint in this case). In this scenario, the load is on the server, and not on the client. On the server, there is only one instance of the in-memory cube running for multiple clients. 3) Excel can only query the data source that it is connected to. It does so by sending an SQL query (relational source) or MDX Query (Analysis Services or other similar source). There is no mechanism available to relay the query to another source that it's not connected to. Even if parameter queries were possible, you would be querying only the PowerPivot data. Therefore, if the PowerPivot data is on the client, all of the data you need to filter from must be available in PowrPivot. 4) Lastly, the scenario you describe applies only to the QueryTable object, which is manifested in Excel as either a Query Table (Excel 2003 -) or a Table (Excel 2007 +).

    Wednesday, May 26, 2010 3:01 PM
  • We use parameters for our query tables all the time in the old version of excel.  Imagine a scenario where someone wants an accounting report for only a specific period, then they want to check another, then another, and so on.  End users cannot be expected to reliably modify a query themselves without an IT sitting right over their shoulders.  At least not in the business I am in.  This is a deal breaker for me and I'll advise against upgrading to 2010 in my corporation for just this reason. 

    What is worse is that after installing PowerPivot (my only reason for wanting to upgrade) I can't seem to get back to the old method of creating a querytable without uninstalling it.

    I have attempted to use a wildcard for a parameter as a workaround to get all the data and I can't even get that to work.  If anyone knows the correct string for a sql wildcard I'd love to have some tips.  I don't work with SQL a lot but I thought it was "*".  PowerPivot returns "Error converting data type nvarchar to smallint."

    Friday, August 13, 2010 10:09 PM
  • What about creating a table in your database to store the parameters, then incorporate the parameter table into your SQL select statement and provide an excel UI to update the parameter table values.  Bit of a work around but should get the job done.
    Tuesday, February 22, 2011 11:00 PM
  • I see that this thread has been happening for over 21 months. Is there a way as of yet to use the contents of a PowerPivot pivot table filter set to pass into the table refresh sql code as parameters?

    Thanks!

    Tuesday, July 05, 2011 3:59 PM
  • Hi Lisa,

    I bumped into this thread whiles looking for an answer for exactly the same problem. Currently I use VBA to send query to sql together with the parameters that I let the user type in a parameter area on a spreadsheet.

    Powerpivot with the latest Nov release is beginning to be amazing but is not quite there. It will be fab if we could have an end user interface, perhaps similar to Report Builder mechanism allowing the developer to define date parameters or any other parameter so that each time the end user refreshes the data he/she can define different parameters for the data set.

    whilst on the subject of powerpivot it would be great if we could group them in a similar way to how it works in Excel pivot by week,month, quarter etc. I know you can use DAX for that but will be quicker to do on the pivot sub menu.

    Another feature strikingly missing is drill through.

    Are you able to let me know if the above is planned for a near future release?

     

    Many thanks,

     

    Matti

     

    Monday, November 07, 2011 5:37 PM
  • Hello,

    I have a 30GB database full of data my business users need to analyze. It is a historical ever growing store. They usually analyze it month by month so they need to import data worth of a specific month. Sometimes they analyze even older data. Using MS Query and Excel they have been able to import data to excel based on typing 2 dates into 2 excel cells and import. Then refresh a predefined pivot table and tada it worked. They do not know sql, nor are they IT guys.

    We now have new technology able to work on hundreds of millions of rows which is not able to ask for parameters. So we are supposed to import gigabytes of data so that we can later filter 99% of it out. Welcome to the jungle.

    Thank you MSFT

    I feel refreshed.

     

    Mirronelli
    • Edited by Mirronelli Friday, November 11, 2011 4:52 PM
    Friday, November 11, 2011 4:49 PM
  • Hi Deva,

    The above query is working fine. But is it possible to get the value from the user?

    Instead of changing the parameters in Table Properties i want to get that details from the user.It may be a popup window or Excel cell

    Could you please help me?

    Thanks,

    James

    Thursday, January 05, 2012 12:13 PM
  • What you describe can easily be done with slicers operating on Pivot Tables.
    Friday, January 06, 2012 9:37 PM
  • I have been super impressed with PowerPivot for the last couple days but unfortunately am pondering over the same issue desribed by two users above ("bmeier" & "Mironnelli").

    Can I conclude from this thread that - PowerPivot (on SharePoint) will NOT have the ability to pass a web based parameter that could be used as either a Filter (or slicer/dicer) before loading/executing the worksheet. I want to be able to load the worksheet dynamically based on the user's credentials when they click on it from the browser based sharepoint user interface.

    Can one of the moderators please confirm that this is not possible in the current release? If so, than is it even considered as a possible feature for the future and if so when? Thanks in advance.

    Thursday, March 01, 2012 7:01 PM
  • I would also like to know if its possible to use parameters in a query.

    Its simply impossible to download all 30GB database to Excel every time a user wants to analyze last moth data.

    Tuesday, April 24, 2012 7:23 AM
  • Hello ! Last question was written on April 24... Still no answer / not possible ?

    It would be great to be able to pass parameters coming from our users to our DB queries!

    Thursday, July 19, 2012 8:13 PM
  • Hi Lisa,

    I bumped into this thread whiles looking for an answer for exactly the same problem. Currently I use VBA to send query to sql together with the parameters that I let the user type in a parameter area on a spreadsheet.

    Powerpivot with the latest Nov release is beginning to be amazing but is not quite there. It will be fab if we could have an end user interface, perhaps similar to Report Builder mechanism allowing the developer to define date parameters or any other parameter so that each time the end user refreshes the data he/she can define different parameters for the data set.

    whilst on the subject of powerpivot it would be great if we could group them in a similar way to how it works in Excel pivot by week,month, quarter etc. I know you can use DAX for that but will be quicker to do on the pivot sub menu.

    Another feature strikingly missing is drill through.

    Are you able to let me know if the above is planned for a near future release?

    Many thanks,

    Matti

    Matti- Follow up on your comment about using VBA to pass in a parameter. Would you know how to use VBA to pass the value in an excel cell into a DAX function?  I am trying to pass a couple dates into a DAX formula. Want to be able to allow the user to type dates in a cell, rather than edit the dates in a COMPLEX dax formula. If anybody can help. Thanks in advance.
    Tuesday, August 07, 2012 4:57 PM
  • I want to add my frustration.  MS 2003, parameterize pivot tables.  2007? 2010?  MS has lost its mind...Forum moderators deflect and confuse.  

    My excel objects will remain in 'Compatibility Mode' until the sun burns out, or, MS release a simple patch that will allow parameters to REMAIN!

    Please MS.  Stop this nonsense!

    

    Monday, September 03, 2012 1:25 AM
  • Hi,

    I have just started to consider PowerPivot as a BI option.  The tool is great in many ways but this thread points to a serious deficiency ... so just to add to the chorus of folks asking for pass thru SQL parameters (that do not require the end user to mess with code).  I have end users who need details of a particular customer (out of a table with millions of rows).  It is not practical to bring back all the rows and then filter for one customer out of millions.

    I get how you can go change the query manually but that is not great for the average end user.

    I echo the sentiment that this has been a clear requirement on this thread for years now.  Any plans?

    Cheers

    Wednesday, September 12, 2012 5:54 PM
  • The solution, I believe, is called "Sharepoint" :)

    -- This posting is provided "AS IS" with no warranties, and confers no rights

    Wednesday, September 12, 2012 7:52 PM
  • The solution is not SharePoint. We have been using SharePoint since version 2001 and we are currently using 2010. SharePoint may offer the ability to supply user defined parameters on the fly but it takes someone very knowledgeable with SQL Server and SharePoint to make that happen. Your normal Excel Power User is not going to have the required skillset that is required to make this happen. And the time to create the same parameterized query in Excel is surely going to take exponentially longer to create in SharePoint even for a veteran of both technologies such as myself.

    I understand that Microsoft doesn't have the code for MS Query anymore which is why they don't want to replace it but I can assure you that it isn't going to take as much time and resources to recreate a new version of MS Query as it did with PowerPivot which for some reason came before replacing what really needed to be replaced 5 to 8 years ago (MS Query).

    Unfortunately Microsoft has decided not to listen to anything that was mentioned in this thread. Excel 2013 will be exactly as it is now which is that it will not take user defined parameters from cells in the workbook. If PowerPivot is in fact meant for a different purpose then at least replace MS Query with something that is more modern and works with todays database technology and offers the ability to use user defined parameters!

    Sunday, November 11, 2012 6:50 PM
  • Please confirm to me. If i will design a PP workbook with a few filters to interact with excel output , the SharePoint 2010 version of this published workbook will not support the user interaction to change the value of the filters? Thanks

    Agata R.

    Tuesday, November 20, 2012 8:17 PM
  • That's exactly I was afraid of,thanks for clarification.

    Thanks.

    I know this is an old post, however, do you know if PowerPivot has this functionality yet??  I was able to take my Excel PowerPivot 2010 and link to a SQL 2008 Stored Procedure with 4 default Parameters.  However I can't figure out how to change the input parameters without going back in to the configuration.  My hope was to enable the user to select the parameters from somewhere on the excel worksheet and then would filter through to the SQL Stored Procedure. 

    Friday, February 08, 2013 9:07 PM
  • What we decided to do, as this is a glaring oversight is to expose a Parameters table via BDC. The end users can then set the "global parameters" for the site, (like date ranges for a quarter that they want to expose). They are able to make updates directly to the parameters then when the data is refreshed we are able to get a filtered view. Not perfect, but certainly better than having to open multiple sheets to reset very similar parameters.

     

    We did something like this, with the initial settings set to defaults so if the users were to delete the parameters they would simply get this current year. While it isn't 100% wonderful it gets us by, and enables our customer to surface only the data sets they are interested in making available. 

    Declare @datestart as datetime
    Declare @dateend as datetime
    Set @datestart='01/01/2013' 
    Set @dateend='01/10/2014'

    select @datestart = value from parameters where name = 'StartDate';
    select @dateend = value from parameters where name = 'EndDate';

    QUERY HERE.

    Tuesday, September 10, 2013 3:11 AM
  • What the heck is BDC?? How do end users set the 'global parameters'? As this is a 'glaring oversight' it would be nice to have explicit instructions on the implementation of this workaround. 

    For my organization this is critical. We currently pass parameters back from a locked cell(s) which limits data to current users sales territory. This allows us to use one file for all sales managers each getting their own data. 

    Tuesday, April 15, 2014 9:59 PM