Can powerpivot be used to just show results rather than pivot them?

[voscilfuels]

I'm a bit new to Powerpivot and I'm attempting to use Powerpivot as a way to deliver reports to my team rather than SSRS because in our company, the only SSRS ReportServer I can use is officially "owned" by a team that is hostile to my team.

I have written numerous SQL ad-hoc queries that we are using for reports in SSRS, but been I've trying to migrate them to Powerpivot. For some of the reports, I've been able to paste the SQL query into the powerpivot window and make a pivottable out of these. However, I'd like to be able have some workbooks just contain the results of the query (they don't deal with numbers, as some of them are a data dump or a large collection of text strings), so that they look like the powerpivot window's dataset, but that they can be filtered, sorted, etc. Is this possible, or can I only use pivottables? I can't seem to find a way that I can just get Excel to directly display what is in the Powerpivot window without pivoting it.

Also is there anyway to check if our Sharepoint server is capable of running Powerpivot so that I can deploy reports there for users to use without having Powerpivot installed on their computers other than by saving it to Sharepoint and seeing if a user can run it? If not, how difficult would it be to have an IT team update the SP server so that it can handle PP?

Any help is appreciated, as I'm in a position where I cannot contact IT or project management, and I can't raise any red flags with IT/BI/PM.

 

[Tycho01]

Hey,
If you use a PowerView instead of a pivot table you'll be able to get unaggregated results. Have you tried that, or is that also not what you want?

 

[kealkil4]

I'm a bit new to Powerpivot and I'm attempting to use Powerpivot as a way to deliver reports to my team rather than SSRS because in our company, the only SSRS ReportServer I can use is officially "owned" by a team that is hostile to my team.

I have written numerous SQL ad-hoc queries that we are using for reports in SSRS, but been I've trying to migrate them to Powerpivot. For some of the reports, I've been able to paste the SQL query into the powerpivot window and make a pivottable out of these. However, I'd like to be able have some workbooks just contain the results of the query (they don't deal with numbers, as some of them are a data dump or a large collection of text strings), so that they look like the powerpivot window's dataset, but that they can be filtered, sorted, etc. Is this possible, or can I only use pivottables? I can't seem to find a way that I can just get Excel to directly display what is in the Powerpivot window without pivoting it.

Also is there anyway to check if our Sharepoint server is capable of running Powerpivot so that I can deploy reports there for users to use without having Powerpivot installed on their computers other than by saving it to Sharepoint and seeing if a user can run it? If not, how difficult would it be to have an IT team update the SP server so that it can handle PP?

Any help is appreciated, as I'm in a position where I cannot contact IT or project management, and I can't raise any red flags with IT/BI/PM.

If you are using Excel 2010 ... ignore this reply. If, however, you are using Excel 2013 ... there IS a solution. Using the Existing Connections in Excel, and clicking on the Tables tab, you can select data from any table in the "back-end" PowerPivot structure. This returns the full table into Excel as a normal table using DAX. You can subsequently edit the DAX Connection, and change the command type from "Table" to "DAX". You can then write a Dax statement to return any fields that you may require and they will appear in a "standard" table in the Excel Workbook. Here is a link to "www.sqlpass.org/bac/2014/Sessions/SneakPeeks.aspx" and view the "Introduction to Querying in DAX" by Marco Russo. It will tell you all you need to know.