Power Pivot is one of the best faeture in excel. Though PowerPivot can be used to serve different purposes, however; this post only include: how you can run your SQL code into Powerpivot and manipulte the data into the excel?
If you use Excel Power Pivot as front end tool then by implementing this process you can avoid asking developer a new excel sheet each time the data is updated.
Before you start you must need to have Powerpivot enable in your excel, if so then your excel Tabs are like below:
Fig 1: Different tabs in the excel
If you don't find the tab 'POWERPIVOT' in your excel then you need to work a little more to add that. Please follow the below link to make it work:
Step 1 :Make connection with your data source
Fig 2: Manage option under POWERPIVOT
After you click the manage window you will find a popup window which like below, from there you will have option to connect with data source.
Fig 3. Connect with the data source
You need to follow the wizard to complete the connection.
Step 2: PowerPivot Design
After you establish connection with the data source, then click on Design tab as like below
Fig 4: Design Tab from powerpivot manager
Under the design window you wil find following Tabs like picture and you need to choose 'Table properties'
Fig 5: Design- table properties
And then you need to click Table properites where you will write/paste the SQL code to generate the data directely from the data source.
Fig 6: Add SQl to the table property window
After putting the SQL code in the above window, Please click 'validate' button before you click the 'Save' button; it will validate your SQL code. Now press 'Save' button then SQL query will run and get the data from the database and populate the data in your excel.