topic Re: Data Source Created. How to use with Paginated Report in Report Server

Data Source Created. How to use with Paginated Report

pawlowski6132 — Wed, 31 Mar 2021 18:43:43 GMT

So, I have successfully created a Data Source in Power BI Report Server. I'd like to use that when authoring a Paginated Report BUT, that option isn't available in the list of available Data Sources from Paginated Report or, so it seems.

What am I missing?

thanx in advance,

Joe

Re: Data Source Created. How to use with Paginated Report

v-deddai1-msft — Mon, 05 Apr 2021 02:22:17 GMT

Hi pawlowski6132 ,

Are you trying to connect to data source in Power BI Report Server by paginated report? It doesn't support to it. You can refer to https://docs.microsoft.com/en-us/power-bi/report-server/connect-data-sources for supported data source.

You can connect to dataset from power bi service. Please refer to https://docs.microsoft.com/en-us/power-bi/paginated-reports/report-builder-shared-datasets

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Re: Data Source Created. How to use with Paginated Report

pawlowski6132 — Mon, 05 Apr 2021 15:12:29 GMT

So, if we can't connect to a data source in Report Server via Paginated report or PBI Desktop, what is the use case for using a data source in Report Server?

Re: Data Source Created. How to use with Paginated Report

v-deddai1-msft — Tue, 06 Apr 2021 06:33:35 GMT

Hi pawlowski6132 ,

Please refer to https://docs.microsoft.com/en-us/sql/reporting-services/report-data/add-and-verify-a-data-connection-report-builder-and-ssrs?view=sql-server-ver15

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Re: Data Source Created. How to use with Paginated Report

pawlowski6132 — Tue, 06 Apr 2021 12:01:06 GMT

Thanx for taking the time to reply. I should have been more pointed, what if any is the use case in the Power Platform ecosytem for datasource in report server? (I'm beginning to get the feeling that the answer is, "Absolutely nothing.")

I looked at the article. I have no idea what Report Builder is.

thanx again.

Re: Data Source Created. How to use with Paginated Report

v-deddai1-msft — Wed, 07 Apr 2021 02:06:03 GMT

Hi pawlowski6132 ，

Sorry, I am not familiar with the feature. But I have found an idea:https://ideas.powerbi.com/ideas/idea/?ideaid=3f3c572b-2f0a-4f77-9381-35a59b4cc1cc

We're not planning to make this feature available in Power BI Report Server. We are looking at making available an API to allow you to change the data source programatically, however, to make movement between servers easier.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Re: Data Source Created. How to use with Paginated Report

d_gosbell — Thu, 08 Apr 2021 02:32:42 GMT

There seems to be a lot of confusion on this thread between paginated reports and pbix reports.

On Power BI Report Server data sources can ONLY be used by paginated reports and you have 3 options for connecting a report to a shared data set.

1. After deploying a report you can click on the "..." menu for that report, then click on "Manage", then in the Data Sources tab you can change any of your embedded connections to use one of the shared connections on the server. This is handy if you have an existing shared connection with the connectionstring/username/password already saved.

2. From Report Builder you can choose the option to use a Shared connection. Then you click browse, the first time you will need to enter "http://<servername>/reportserver" in the name area and click Open and you should then see the folders on your Report Server and you can navigate to and select one of the Data Sources you have already created.

3. If you are using SSDT to author your reports you can created both the shared data sources and your reports within your report project, then when you deploy that project both the reports and the data sources in that project will get deployed.

Re: Data Source Created. How to use with Paginated Report

AbhiSSRS — Mon, 12 Apr 2021 04:58:50 GMT

Hi! If not Report Builder , which tool are you using for Paginated ? Are you working with paginated from SSDT?

Re: Data Source Created. How to use with Paginated Report

AbhiSSRS — Mon, 12 Apr 2021 06:08:30 GMT

pawlowski6132 / CST,

below idea has been closed so unable to post there but you may use a workaround if that suits you!

https://ideas.powerbi.com/ideas/idea/?ideaid=3f3c572b-2f0a-4f77-9381-35a59b4cc1cc

Instead of a shared source please parameterize your connection string to source (eg SSAS) with 2 report parameters(hidden params) and these params should be hidden and populated with another dataset on your report which access a table in your source with these values. that table can be changed as per environment (Dev/QA/Prod) so that your report automatically accesses the environment without you having to change the connection on every paginated that you will develop!

<ConnectString>="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=" & Parameters!CubeDatabaseName.Value & ";Disable Prefetch Facts=True;ReturnCellProperties=True;Integrated Security=SSPI"</ConnectString>

Re: Data Source Created. How to use with Paginated Report

pawlowski6132 — Mon, 12 Apr 2021 09:34:00 GMT

Hi, thanx for the info. So, I'm with you and was trying Option 2 but, that's where I got stuck and the reason for my post. This is what I see:

And, no option to connect to dastasource on report server:

Re: Data Source Created. How to use with Paginated Report

pawlowski6132 — Mon, 12 Apr 2021 09:36:53 GMT

Further, according to Dedmon:

"Are you trying to connect to data source in Power BI Report Server by paginated report? It doesn't support to it."

Re: Data Source Created. How to use with Paginated Report

pawlowski6132 — Mon, 12 Apr 2021 09:37:45 GMT

I am using Paginated Report Builder

Re: Data Source Created. How to use with Paginated Report

pawlowski6132 — Mon, 12 Apr 2021 09:46:04 GMT

Thanx. But, I'm still not making myself clear. Sorry.

1. I have a Data Source I built in Power BI Report Server:

I can't find a way to use/connect to these data sources in Power BI Paginated Report Builder (see my images above) or Power BI Desktop.

I think it's not possible in Power BI Desktop but, should be in PBI Paginated Report Builder. But, it's not clear, to me at least, how.

FWIW, I have both versions of PBI Desktop too: "Normal" and "Report Server Edition" along with Paginated Report Builder.

Re: Data Source Created. How to use with Paginated Report

d_gosbell — Mon, 12 Apr 2021 10:33:32 GMT

pawlowski6132 - I see the issue now. Just like there are 2 versions of Power BI Desktop, there are also 2 versions of Report Builder and you are using the version built for powerbi.com not the on-prem version. v-deddai1-msft is still confusing the issue between Power BI Desktop and Report Builder.

If you open the home page of your Report Server portal, in the top right corner is a downloads icon which has a menu that will take you to the correct version of both Power BI Desktop and Report Builder to use with your version of Power BI Report Server.

Or if you are on the Jan 2021 release you can get the latest version of the on-prem Report Builder from the following link

https://www.microsoft.com/en-us/download/details.aspx?id=53613

When you use this version you will see the option for using shared datasets as in the screenshots I posted earlier.

Re: Data Source Created. How to use with Paginated Report

AbhiSSRS — Mon, 12 Apr 2021 10:42:30 GMT

yes, you would not be able to use shared data sources in Power BI ReportBuilder, they are there to support SSRS RDL reports. One workaround in to create your rdl in SSRS using SSDT/Report Builder and then once you have the shared DS connected you can open it up in Power BI report builder to work further. There is no fundamental difference between Power BI rdl and SSRS rdl as long as you do not wish to use Power BI Report builder Live connections with Power BI datasets or you are building them for service.

If you are hosting on Power BI report server, please go ahead and use SSDT to builde rdl and then switch that over to Power BI report builder or build it completely from SSDT

Re: Data Source Created. How to use with Paginated Report

pawlowski6132 — Mon, 12 Apr 2021 10:43:38 GMT

Ahhhhh. Yes. It all makes sense now. Or, at the very least, I understand the issue. Wow, to be honest, for a dummy like me, this can be hard to follow. Or, unintuitive at best.

Anywho, thanx so much for clarity.

Re: Data Source Created. How to use with Paginated Report

pawlowski6132 — Mon, 12 Apr 2021 10:44:52 GMT

thanx much for a good alternate solution, much appreciated.

Re: Data Source Created. How to use with Paginated Report

pawlowski6132 — Mon, 12 Apr 2021 11:38:54 GMT

Got it. You're so smart. 😁

And thank you.