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
Yes you can do that.
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..)
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.
(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.
@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
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.
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).
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.
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:
- 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.
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>
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!
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.