Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone!
I'm new to Power BI/Power BI Report Builder but keen to learn new concepts and tips with these wonderful tools!
So, I'm trying to create a PDF report using Power BI Report Builder.
I have 3 datasets which look like this:
Employee:
Employee_id | Employee_name | Directors | TopManagement | MiddleManagement | FirstLineManagement |
1 | Bob | Alice | |||
2 | Ryan | Kimmy | Rosy | ||
3 | James | Alice | Ben | Gary | |
4 | Bryan | David | Will |
WorkGroups:
WG_id | EmployeeWG_id | ParentEmployee_id | WG_name |
1 | 2 | 4 | research_1 |
2 | 2 | 4 | research_16 |
3 | 1 | 14 | top_secret_research |
4 | 4 | 6 | top_secret_research |
5 | 4 | 6 | experts_research_2 |
Project:
Project_id | WGProject_id | Project_name |
1 | 4 | ultrasound_prjt |
2 | 4 | ovni_prjt |
3 | 5 | quantum_physics_prjt |
4 | 1 | infrared_prjt |
Basically, Employee is a table containing each employee along with their superiors. Each employee is part of one or more WorkGroups and each WorkGroup is assigned to one or more Project.
On Report Builder, I would like to have a table which displays each WorkGroup with the organisational hierarchy of employees being part of this group and also lists every project they're working on. So it would look like something like this for example:
top_secret_research | |
Employee | Project |
Alice | ultrasound_prjt |
ovni_prjt | |
Bob | ultrasound_prjt |
ovni_prjt | |
quantum_physics_prjt |
(the row under Alice and Bob should be merged with it but I'm not able to do it here..)
On Report Builder, I used a tablix which is bounded to the WorkGroups dataset. I'm displaying the WG_name on the first row and this cell is a group on WG_name. On the left part of the next row, I have a child group on the EmployeeWG_id which also has a recursive parent on ParentEmployee_id, so that it can displays the hierarchy. So far, everything works, but I'm struggling to understand how to dynamically add the corresponding projects on the right part of that row. I tried adding another child group but it returns every distinct project for each employee. I also tried changing the Tablix to a Matrix but it doesn't change anything.
I'm sharing a screen shot of my Tablix and the groups associated with it:
Here, the first expression under "Employee" allows me to get the Employee_name from the Employee dataset (with Lookup) and the second expression under "Projet" is supposed to return every project of each employee (with LookupSet)
I'm probably confused with grouping and recursive parent handling. I'd like to keep the datasets as they're if that's possible.
Thank you for your help and have a nice day!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
7 | |
5 | |
3 | |
3 |