This isn't a coding question, so much as it is a functionality question.
这不是一个编码问题,而是一个功能性问题。
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.
我对Powerpivot有点新意,我试图使用Powerpivot作为向我的团队而不是SSRS提交报告的方式,因为在我们公司中,我可以使用的唯一SSRS ReportServer由一个团队正式“拥有”。对我的团队怀有敌意。
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 - I know it is called Power*pivot* but because the PP window just shows the query results itself, I'm hoping there is a way to use it as an alternative to SSRS.
我已经编写了许多我们用于SSRS报告的SQL即席查询,但我一直试图将它们迁移到Powerpivot。对于某些报告,我已经能够将SQL查询粘贴到powerpivot窗口中并从中创建一个pivottable。但是,我希望能够让一些工作簿只包含查询的结果(它们不处理数字,因为它们中的一些是数据转储或大量文本字符串),所以它们看起来像powerpivot窗口的数据集,但它们可以被过滤,排序等。这是可能的,还是我只能使用pivottables?我似乎无法找到一种方法,我可以让Excel直接显示Powerpivot窗口中没有旋转它的内容 - 我知道它被称为Power * pivot *但是因为PP窗口只是显示查询结果本身,我我希望有一种方法可以用它作为SSRS的替代品。
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?
无论如何还要检查我们的Sharepoint服务器是否能够运行Powerpivot,以便我可以在那里部署报告供用户使用而无需在其计算机上安装Powerpivot,只需将其保存到Sharepoint并查看用户是否可以运行它?如果没有,让IT团队更新SP服务器以便它可以处理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. Thank you very much for your time.
任何帮助都表示赞赏,因为我处于无法联系IT或项目管理的位置,我不能对IT / BI / PM提出任何危险信号。非常感谢您的宝贵时间。
1 个解决方案
#1
0
It sounds like you just want to get the results of your SQL query directly into an Excel spreadsheet, where you can use the Excel filter and sort capability. It doesn't appear that you want to use any of the functionality that PowerPivot provides, so you can achieve what you described by using an existing Excel component, Microsoft Query. It's a bit of a clunky old interface but it works. Here's some instructions to use:
听起来您只想将SQL查询的结果直接导入Excel电子表格,在Excel电子表格中您可以使用Excel过滤器和排序功能。您似乎不想使用PowerPivot提供的任何功能,因此您可以使用现有的Excel组件Microsoft Query实现所描述的功能。这是一个笨重的旧界面,但它的工作原理。以下是一些使用说明:
In an Excel spreadsheet, click on the Data tab, select the "From Other Sources" button on the Get External Data area of the ribbon.
在Excel电子表格中,单击“数据”选项卡,选择功能区的“获取外部数据”区域中的“来自其他来源”按钮。
Select "From Microsoft Query"
选择“来自Microsoft Query”
On the "Choose Data Source" click OK to create a new data source
在“选择数据源”上,单击“确定”以创建新数据源
On the "Create new data source" window select the appropriate driver and database details for your data source
在“创建新数据源”窗口中,选择数据源的相应驱动程序和数据库详细信息
If using a SQL Server data source, don't select anything for the optional step 4 (select a default table).
如果使用SQL Server数据源,请不要为可选步骤4选择任何内容(选择默认表)。
Click OK to return to the Choose Data Source window, then OK again to select the new data source.
单击“确定”返回“选择数据源”窗口,然后再次单击“确定”以选择新数据源。
You're now prompted to choose columns for your query, but if you already have some SQL then click Cancel - this will display a prompt to continue editing in Microsoft Query - click Yes.
现在,系统会提示您为查询选择列,但如果已有某些SQL,则单击“取消” - 这将显示在Microsoft Query中继续编辑的提示 - 单击“是”。
The Add Tables window pops up, click Close
弹出“添加表”窗口,单击“关闭”
You're now in the Microsoft Query interface. If you click on the SQL button, you can paste in your SQL. Click the Return Data button to run the query, the results will be displayed in an Excel worksheet.
您现在位于Microsoft Query界面中。如果单击SQL按钮,则可以粘贴SQL。单击“返回数据”按钮以运行查询,结果将显示在Excel工作表中。
Once this is set up, you just need to click "Refresh Data" on the Data tab to re-run the query and update the spreadsheet.
设置完成后,您只需单击“数据”选项卡上的“刷新数据”即可重新运行查询并更新电子表格。
#1
0
It sounds like you just want to get the results of your SQL query directly into an Excel spreadsheet, where you can use the Excel filter and sort capability. It doesn't appear that you want to use any of the functionality that PowerPivot provides, so you can achieve what you described by using an existing Excel component, Microsoft Query. It's a bit of a clunky old interface but it works. Here's some instructions to use:
听起来您只想将SQL查询的结果直接导入Excel电子表格,在Excel电子表格中您可以使用Excel过滤器和排序功能。您似乎不想使用PowerPivot提供的任何功能,因此您可以使用现有的Excel组件Microsoft Query实现所描述的功能。这是一个笨重的旧界面,但它的工作原理。以下是一些使用说明:
In an Excel spreadsheet, click on the Data tab, select the "From Other Sources" button on the Get External Data area of the ribbon.
在Excel电子表格中,单击“数据”选项卡,选择功能区的“获取外部数据”区域中的“来自其他来源”按钮。
Select "From Microsoft Query"
选择“来自Microsoft Query”
On the "Choose Data Source" click OK to create a new data source
在“选择数据源”上,单击“确定”以创建新数据源
On the "Create new data source" window select the appropriate driver and database details for your data source
在“创建新数据源”窗口中,选择数据源的相应驱动程序和数据库详细信息
If using a SQL Server data source, don't select anything for the optional step 4 (select a default table).
如果使用SQL Server数据源,请不要为可选步骤4选择任何内容(选择默认表)。
Click OK to return to the Choose Data Source window, then OK again to select the new data source.
单击“确定”返回“选择数据源”窗口,然后再次单击“确定”以选择新数据源。
You're now prompted to choose columns for your query, but if you already have some SQL then click Cancel - this will display a prompt to continue editing in Microsoft Query - click Yes.
现在,系统会提示您为查询选择列,但如果已有某些SQL,则单击“取消” - 这将显示在Microsoft Query中继续编辑的提示 - 单击“是”。
The Add Tables window pops up, click Close
弹出“添加表”窗口,单击“关闭”
You're now in the Microsoft Query interface. If you click on the SQL button, you can paste in your SQL. Click the Return Data button to run the query, the results will be displayed in an Excel worksheet.
您现在位于Microsoft Query界面中。如果单击SQL按钮,则可以粘贴SQL。单击“返回数据”按钮以运行查询,结果将显示在Excel工作表中。
Once this is set up, you just need to click "Refresh Data" on the Data tab to re-run the query and update the spreadsheet.
设置完成后,您只需单击“数据”选项卡上的“刷新数据”即可重新运行查询并更新电子表格。