locked
Placing the results of a Power Pivot SQL query under existing column titles in a sheet RRS feed

  • Question

  • Hello,

    A beginner's question if I may.  Can someone please point me to a resource that describes how to place the results of a Power Pivot SQL query underneath cells containing column titles that have been created in an existing spreadsheet?

    I can get nice SQL query results and just want to stick them under existing cells in the sheet.

    Thanks

    Monday, April 23, 2012 5:09 PM

Answers

  • Ok, I tried it out.  It appears I can do a custom SQL query in the SQL Server Management Studio or a program of some kind to make a temporary table, then import that table with the Data tab. 

    Thanks


    • Edited by WParkman Wednesday, April 25, 2012 4:03 PM
    • Marked as answer by Dummy yoyo Thursday, May 3, 2012 5:02 AM
    Wednesday, April 25, 2012 4:01 PM

All replies

  • So correct me if I am wrong, I am sure I am missing something, but it seems that this Power Pivot thing does not even let you place SQL query results into a spreadsheet unless you copy and paste them manually?

    Monday, April 23, 2012 10:19 PM
  • PowerPivot is not meant to be queried with SQL. This indicates that something is not accurate in the description of your issue.

    Can you please provide the query you are referring to?


    The Data Specialist (Blog)

    Tuesday, April 24, 2012 12:45 PM
  • This is my first attempt to use Excel to make a report.

    What I am trying to say is that I click the PowerPivot tab, then the "PowerPivot Window" launch button, then the From Database button, then the From SQL Server button, then I fill in all my connection information and connect to whatever database I am interested in, then there are two radio buttons, one for selecting tables, another for creating a query.  Either way, I can get an import of data, the screen shows my rows and columns from the query.   The "From Database" and other PowerPivot buttons still appear at the top of the screen.  What I was hoping to do was to have this exact data appear in a spreadsheet, where I have entered a bunch of header information in the cells at the top of the sheet.   I was hoping that this PowerPivot deal would allow me to dynamically make reports, maybe even let me enter criteria for my SQL query and then make the spreadsheet automatically.

    If this is possible, can you please point me to a reference that gives an example of how to do that?

    Thanks

    Tuesday, April 24, 2012 4:49 PM
  • Thanks for the clarification.

    Powerpivot allows you to embed an OLAP cube in your Excel file. The cube is meant to be queried through Pivot tables or dedicated Excel formulas. Since this is an OLAP source, it can also be queried through MDX statements, although this is not encouraged by Excel's UI.

    The refreshing of the underlying data cannot be set to be done automatically or through VBA. It also cannot be parameterized. Based on your requirements, Powerpivot is not necessarily the best choice.

    Good news is that Excel natively supports your scenario. You can import data from a SQL server from the DATA tab of the ribbon. You can also add parameters to your queries. The simplest way to add parameters to your queries is through an MSQuery import.

    Search for MSQuery + Parameter for detailed how-to resources.


    The Data Specialist (Blog)

    Wednesday, April 25, 2012 10:03 AM
  • Ok, I tried it out.  It appears I can do a custom SQL query in the SQL Server Management Studio or a program of some kind to make a temporary table, then import that table with the Data tab. 

    Thanks


    • Edited by WParkman Wednesday, April 25, 2012 4:03 PM
    • Marked as answer by Dummy yoyo Thursday, May 3, 2012 5:02 AM
    Wednesday, April 25, 2012 4:01 PM