topic Re: Paginated Report as a drill down report in Report Server

Paginated Report as a drill down report

GuestUser — Fri, 28 Feb 2020 06:40:49 GMT

Hi ,

Is there a way where we can link a paginated report( as a drill down report may be ) on a normal power bi summary report (on report server)

If yes, please guide with some steps on how to achieve the same

Thanks !!

Re: Paginated Report as a drill down report

dpFr33 — Sat, 29 Feb 2020 15:58:56 GMT

Hi,

Yes you can do that.

Example :

Report A Power BI report : Global sales for all customers (for example a grid with Customer Name, Customer ID, Sales amount...)

Report B SSRS classic paginated report : Sales for one customer (detailed by year, month... etc..)

Step 1:

In report B (SSRS) add report parameter @CustomerID that filter the SSRS dataset. Add what you want to your report page : charts, grids by year etc..

Publish your report to PBRS.

Test it on PBRS portal by passing CustomerID parameter in URL.

http://<yourserver>/reports/report/<Folder>/ReportB?CustomerID=1

(1 or an other value). Just for the test 😉

Step 2 :

In Report A (Power BI Destkop RS), you need to generate an URL to the SSRS report : http://<yourserver>/reports/report/<Folder>/ReportB?CustomerID=<someValueHere>

For a simple test, just add an URL button action and enter the URL previously tested.

Press the button (Ctrl+Click within Power BI Desktop RS) and verify that your browser successfully open the URL.

You also can obtain a dynamic URL with calculated DAX measure or calculated column depending your model.

Let me know if I can give you more details.

Regards,

Denis

Re: Paginated Report as a drill down report

GuestUser — Mon, 02 Mar 2020 09:07:53 GMT

dpFr33 :Thanks for your inputs !!

Currently we are using Live Connection - SSAS Tabular Model to connect to power BI

and Report Builder to create paginated reports .

I m not familiar with URL parameters logic . Will be checking online for docs. Also It will be helpful if you can share some links which shows how to use power bi as summary rpeort and paginated as drill down report ..so that I can try and check for myself

Thanks

Re: Paginated Report as a drill down report

Anonymous — Tue, 09 Jun 2020 19:48:40 GMT

Hi dpFr33 - how are you passing the parrameter in your example? I'm new to Power BI so it is possible i did not understand how you passing the parameter value. In your example: http://<yourserver>/reports/report/<Folder>/ReportB?CustomerID=<someValueHere> how do I pass power BI value where CustomerID= <someValueHere>

I have a requirement that needs to drill through the report power BI chart/report to paginated report that reside in same workspace or different workspace. Paginated report should capture all filters from PowerBI report/chart.

I'm looking for something like SAP BO OpenDoc where parameter can pass to child report.

Thanks.

Re: Paginated Report as a drill down report

dpFr33 — Wed, 10 Jun 2020 09:50:48 GMT

Hi,

You need :

- One Power BI Report : here, ReportA

- One SSRS report : here, ReportB

- SSRS Report server URL. For example http://<yourserver>/reports/

- The full report pathname to your SSRS report : http://<yourserver>/reports/report/<Folder>/ReportB

Test this URL with your default browser (IE, Chrome, Firefox, Safari).

Parameters concepts:

Parameter is an expression: <AField> <criteria> <AValue>

- AField is the fieldname. For example: CountryCode

- criteria : =, <>, >, <, <=, >=, IN ( ) ..

- AValue : a number, integer, string, representing the value your are look for.

Example : CountryCode=FR

Report B (SSRS):

Very important: Add SSRS parameter in your Report B. If you don't do that, you can't pass parameter from Power BI Report.

Example:

Add a parameter CountryCode.

Power BI report can pass parameter to SSRS report:

To pass parameters expression to SSRS, you must always use the same syntax:

http://<yourserver>/reports/<reportFolder>/ReportB?<AField>=<someValueHere>

For example:

http://SRVBI/reports/Analytics/GlobalSales?CountryCode=FR

Where:

- SRVBI is your server name

- Analytics : the folder

- GlobalSales: the report name (without rdl extension).

- ? : Always add the ? symbol after the report name to pass the first parameter expression

- CountryCode=FR : the parameter expression

How to pass parameter from Power BI report to SSRS report?

1. Create a measure

Create a DAX measure to concatenate the report URL with the criteria value.

Example:

GoToSSRS_URL := CONCATENATE("http://SRVBI/reports/Analytics/GlobalSales?CountryCode=", SELECTEDVALUE( Countries[CountryCode] ) )

Where Countries is the table name and CountryCode, the field name.

SELECTEDVALUE() will return the current context value.

2. Using Action in Power BI.

If you never use Action, read this: https://docs.microsoft.com/fr-fr/power-bi/create-reports/desktop-buttons

And then? Simply use Url web action with your measure.

Before trying to pass more parameter, try just with one parameter.

In order to pass multiple criterias, use &

http://<yourserver>/reports/<reportFolder>/ReportB?<AFieldA>=<someValueHere> & <AFieldB>=<anotherValueHere>

Regards,

Denis

Re: Paginated Report as a drill down report

Anonymous — Tue, 16 Jun 2020 20:53:19 GMT

Thanks dpFr33 for the response. I was able to pass the single value from filter or slicer and it wroks perfectly! I'm getting hard tiem to pass the multiple values. It would be great if you can send the syntax for multi values, I tried to do like you mentioned but DAX is not taking it.

I'm trying to convert this URL to DAX but unable to do so yet: https://serverrp:DateNumber=2020 & Region=NW this URL works fine when directly run in browser.

Thanks for the help!

Re: Paginated Report as a drill down report

Anonymous — Tue, 30 Jun 2020 12:23:13 GMT

Got it resolved. It was DAX that I was struggling but all set now. Thanks!

Re: Paginated Report as a drill down report

GuestUser — Tue, 30 Jun 2020 13:34:22 GMT

Hi Anonymous

It would be helpful if you can mention what u did to get the issue solved with some examples etc..(dax code etc)

Re: Paginated Report as a drill down report

littlemojopuppy — Thu, 12 Aug 2021 15:22:35 GMT

dpFr33 thank you! This helped tremendously! 🙂