locked
How do I show my powerpivot sheets? RRS feed

  • Question

  • I have all my data filtered on a sql query coming into powerpivot. I've created a nice dashboard but now I would like to be able to click on sheet 2 in my standard excel window and have the table from my powerpivot window displayed.

    It seems like this should be very easy and I'm just overlooking something.


    • Edited by DCDeez Tuesday, July 9, 2013 12:39 PM misspelling
    Tuesday, July 9, 2013 12:39 PM

Answers

All replies

  • As far as I know, there's no functionality to view data in Power Pivot model as TABLES in Excel sheets.

    A work-around would be to establish a connection to the data source in Excel via Data Tab - you could then show the data that the query returns in the excel sheet. Though you'll have to manage two connections to the data source (One from Power Pivot & one from Excel).


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

    Tuesday, July 9, 2013 4:39 PM
    Answerer
  • When you create a connection with "From Other Sources" and select "From SQL Server" you only have the option of pulling in the table directly or pulling in all tables. There is no option/text box to drop in a custom SQL Query.
    Tuesday, July 9, 2013 7:24 PM
  • When you create a connection with "From Other Sources" and select "From SQL Server" you only have the option of pulling in the table directly or pulling in all tables. There is no option/text box to drop in a custom SQL Query.

    Hi DCDeez,

    In PowerPivot, we can write a query to retrieve the expected data into PowerPivot model. Then, we can copy all of data into Excel worksheet. For more information about writing SQL Query using Table Import Wizard in PowerPivot, please see:
    http://sqlserverrider.wordpress.com/2012/08/10/write-sql-query-using-table-import-wizard-in-powerpivot/

    Best Regards,


    Elvis Long
    TechNet Community Support

    Friday, July 12, 2013 2:55 AM
  • When you create a connection with "From Other Sources" and select "From SQL Server" you only have the option of pulling in the table directly or pulling in all tables. There is no option/text box to drop in a custom SQL Query.

    Hi DCDeez,

    In PowerPivot, we can write a query to retrieve the expected data into PowerPivot model. Then, we can copy all of data into Excel worksheet. For more information about writing SQL Query using Table Import Wizard in PowerPivot, please see:
    http://sqlserverrider.wordpress.com/2012/08/10/write-sql-query-using-table-import-wizard-in-powerpivot/

    That's the problem. I need the sheet in the normal Excel window. Not the PowerPivot window. I already have the PowerPivot SQL Query working fine.

    There is no way to say Sheet 2 look at sheet whatever in the powerpivot window.

    I also tried to pull in my same custom query from the powerpivot window in the normal excel sheet but going to Data>From Other Sources>From SQL Server . Filled out the Server name and login credentials. Clicked next Selected my database and table. Clicked next and after you fill in your details there is no place for you to put a custom SQL Query.

    Friday, July 12, 2013 1:13 PM
    • Marked as answer by DCDeez Friday, July 12, 2013 1:56 PM
    Friday, July 12, 2013 1:55 PM