代码之家 › 专栏 › 技术社区 › SherlockSpreadsheets

带SSRS数据集连接的PowerQuery

power-bi-report-server powerquery powerbi reporting-services

SherlockSpreadsheets · 技术社区 · 6 年前

我想用一个 SSRS dataset as a datasource for PowerQuery ,或者[ ]或者[ ]. 报表服务器版本为 [SSRS 2016年] [ Microsoft SQL Server 2016(SP2-CU3)(KB4458871)-13.0.5216.0(X64)-企业版 ].

我的目标是最终用户可以通过 PowerQuery(Excel或PBIDesktop)。用户连接并生成数据集。

升级到PBIRS是将PQ连接到SSRS报告数据集的唯一方法吗?升级到PBIRS能解决这个问题吗?

"Generate Data Feeds from a Report (Report Builder and SSRS)": Docs-MSFT/SQL/SQL Server/Reporting Services/Report Builder/ 以及|步骤: "Reporting Services ATOM Data Feeds": mssqltips 2136 ).

PQ无法连接到。ATOMSVC,它的ODATA连接需要URL而不是文件。 upgrade to PBIRS (PowerBI Report Server) 从SSRS 2016服务器。

来自odata feed的excel pq-网址.png

在线资源

用户可以基于SSRS数据集构建自己的PowerBI报告

(未来状态):这将是PowerBI ReportServer的有效URL。

http://myssrsname/Reports/api/v2.0

http://myssrsname/Reports ... works for me to access the report portal

http://myssrssname/Reports/api/v2.0 ... HTTP ERROR 404: No webpage was found for the web address

https://docs.microsoft.com/en-us/power-bi/report-server/access-dataset-odata

(如何):在Power BI中探索新的SSRS 2017 API

https://blog.crossjoin.co.uk/2017/10/15/exploring-the-new-ssrs-2017-api-in-power-bi/

如何升级,SSRS到PBIRS

(下载):PowerBI ReportServer,180天免费试用:

https://powerbi.microsoft.com/en-us/report-server/

(如何):从SSRS迁移到Power BI报表服务器

https://deevita.com/2018/10/24/migrating-from-ssrs-to-power-bi-report-server/

https://www.mssqltips.com/sqlservertip/5000/install-and-configure-power-bi-report-server-and-power-bi-desktop/

(PRICING-PBIRS):POWER BI报表服务器:一个现代化的内部BI解决方案

https://www.blue-granite.com/blog/power-bi-report-server-revisited

具有活动软件保证(SA)的SQL Server企业版

(定价-PBI Pro):

https://powerbi.microsoft.com/en-us/pricing/

每个用户/mo 10美元

0 回复 | 直到 6 年前

Tholitz_Reloaded 6 年前

这正是我现在所做的,您需要使用Power Query SDK在Visual Studio中创建Power BI自定义数据连接器,并通过SSRS 2017 rest API连接到SSRS,您可以通过API调用访问数据集,下面是一个示例:

http://<SSRS_Server>/reports/api/v2.0/Datasets(f973d010-2052-49ad-ac69-ae66d0324e03)/Model.GetData

这是我的示例代码:

section Asia.PQ.SSRS_Connector;

[DataSource.Kind="Asia.PQ.SSRS_Connector", 
Publish="Asia.PQ.SSRS_Connector.Publish"]
shared Asia.PQ.SSRS_Connector.Feed = Value.ReplaceType(SSRSConImpl, type function (url as Uri.Type) as any);

DefaultRequestHeaders = [
     #"Accept" = "application/json;odata.metadata=minimal",  
     #"OData-MaxVersion" = "4.0"

];

SSRSConImpl = (url as text) =>
    let
        body= "",
        source = Web.Contents(url, [ Headers = DefaultRequestHeaders, 
        Content=Text.ToBinary(body)]),
        json = Json.Document(source)
    in 
        json;