cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Paginated Report as a drill down report

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

 

 

7 REPLIES 7
Highlighted
Advocate I
Advocate I

Re: Paginated Report as a drill down report

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

 

Highlighted
Helper V
Helper V

Re: Paginated Report as a drill down report

@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 

Highlighted
Frequent Visitor

Re: Paginated Report as a drill down report

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. 

Advocate I
Advocate I

Re: Paginated Report as a drill down report

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

Highlighted
Frequent Visitor

Re: Paginated Report as a drill down report

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!

 

 
 
Highlighted
Frequent Visitor

Re: Paginated Report as a drill down report

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

Highlighted
Helper V
Helper V

Re: Paginated Report as a drill down report

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)

 

 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.