topic Power BI paginated report - displaying hierarchical relationship of rows in Report Server

Power BI paginated report - displaying hierarchical relationship of rows

S-2022 — Tue, 18 Feb 2025 01:54:55 GMT

I have a large dataset that feeds into Power BI to create paginated reports.

I want to use the same dataset and have Power BI display the hierarchical relationship of my data in a paginated report. If I can also have a calculated column showing a status that represents the child row status, that would be idea.

Basic example:

Task	Progress	Status
Make fruit salad		2/3 completed
bananas	not available
stone fruit	obtained
passionfruit	obtained

My actual data is more complex in that progress is measured as Not yet commenced, On track, Minor delay, Significant delay, Completed / BAU.

Open to suggestions as to how this could be conveyed for the over-arching task.

Re: Power BI paginated report - displaying hierarchical relationship of rows

Poojara_D12 — Tue, 18 Feb 2025 04:57:48 GMT

Hi S-2022

To display a hierarchical relationship in a Power BI paginated report, you can leverage the parent-child hierarchy pattern using row groups in SSRS (Power BI Report Builder). Your dataset should include an identifier for each task and a parent-child relationship if applicable. In the tablix (table/matrix) layout, create a hierarchical structure by grouping rows based on the parent task. For progress calculation, use an aggregate expression (e.g., counting child rows with specific statuses) to dynamically display progress at the parent level. The Status column can be computed using a custom expression, where the parent task derives its status based on child task completion—e.g., if all child tasks are "Completed," the parent is "Completed"; if some are delayed, it might be "Minor delay" or "Significant delay." You can achieve this by using an IIF() or SWITCH() function in SSRS expressions. For visual clarity, consider using conditional formatting (e.g., color coding) to highlight statuses dynamically. If your dataset supports it, a recursive hierarchy approach with self-referencing keys can further enhance the drill-down capabilities of the report. Let me know if you need help with DAX expressions or SSRS configurations to implement this!

Re: Power BI paginated report - displaying hierarchical relationship of rows

S-2022 — Tue, 18 Feb 2025 05:44:00 GMT

Hi - I'm pretty sure this answers the question, provided I (as a non-coder and PBI amateur) can implement it. Many thanks.

Re: Power BI paginated report - displaying hierarchical relationship of rows

Poojara_D12 — Tue, 18 Feb 2025 05:58:29 GMT

Happy to help 🙂
If possible consider subscribing my Youtube channel for basic to advance power BI and SQL related content, link given below.