Paginated Report as a drill down report

[GuestUser]

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 !!

[dpFr33]

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

[moons]

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. 

[dpFr33]

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

[littlemojopuppy]

@dpFr33 thank you!  This helped tremendously!  🙂

[moons]

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!

[moons]

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

[GuestUser]

Hi @moons 

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

[GuestUser]

@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