Forum Discussion
Paginated report dynamic subscription including indirect reports
Dear community,
I'm looking for a solution for the following case. I have a Power BI paginated report, for which I want to create a dynamic subscription. I want however for managers to receive data for all people beneath them (not only their direct reports, but also their indirect reports). I do NOT want to achieve this through RLS for various reasons. Is there any other way to achieve this?
Thanks in advance!
Hi, Anonymous ,
No I didn't submit an idea in the Ideas Forum. I've already done this about a different topic. Still waiting for feedback there. 🙂 I decided to ask instead an experienced colleague in our company and he offered me a working solution. The solution is to write the DAX query for each dataset in the following manner:
EVALUATEFILTER(
SUMMARIZECOLUMNS(
'Exams'[Employee Name],
'Exams'[Exam Name],
'Exams'[Exam Date],
'Exams'[HierarchyPath]'Exams'[Manager BambooHR ID]
),
PATHCONTAINS(Employees[HierarchyPath], @ManagerBambooHRID)
)
Here "HierachyPath" is a custom column, made in the semantic model holding the management chain for each person, received by using the PATH function.
This sucessfully produced a filter, showing both direct and indirect managers, without using RLS.
16 Replies
- tayloramy
Super User
Hi HinkaSt,
The best way to do this is RLS. I'm not aware of a nice way to do it without RLS.
Are you able to build a table flattens the org chart into a two column table?
For example, if person A reports to B and B reports to C, can you make a table that shows the followingEmployee Manager A B A C B C If so, you should be able to use dynamic subscriptions to make this work, but it will be messy and if the users have access to the report directly at all they will see all data.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
Hey, tayloramy ,
Thank you for the reply. I experimented with your suggestion before writing my initial post as it was the solution AI proposed. However, the result was that manager A, instead of receiving one email report about the two employees below him, received two separate email reports, each with info only about one employee. So either the approach doesn't work, or I'm missing some important detail.
In my case I don't want to give users access to the paginated report on Power BI service. We have a proper Power BI report, drawing data for the same semantic model, which I plan to include as a link in the attached pdf report. I don't want to use RLS, because we want the paginated email report to focus on info about your team, but we still want managers to be able to see info about the other teams if they wish, when they open the Power BI report. This won't work if there is RLS. It works without RLS if I set the manager as parameter, but this solution includes only for direct reports...
If someone has another trick up their sleeve, I'll be grateful. Otherwise, the only solution I can think of right now, if I want to include indirect reports is to create a separate semantic model with RLS just for the paginated report. It doens't optimal, though...
- tayloramy
Hi HinkaSt,
The flattening of the org chart and using the manager field as a parameter should work.
Before sending the emails, you might need to get a distinct list of managers.
FOr RLS, you can set up RLS for the pagianted report only and not for the Power BI report.If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
Hi HinkaSt ,
Could you please confirm if you've submitted this as an idea in the Ideas Forum? If so, sharing the link here would be helpful for other community members who may have similar feedback.
Regards
Hi, Anonymous ,
No I didn't submit an idea in the Ideas Forum. I've already done this about a different topic. Still waiting for feedback there. 🙂 I decided to ask instead an experienced colleague in our company and he offered me a working solution. The solution is to write the DAX query for each dataset in the following manner:
EVALUATEFILTER(
SUMMARIZECOLUMNS(
'Exams'[Employee Name],
'Exams'[Exam Name],
'Exams'[Exam Date],
'Exams'[HierarchyPath]'Exams'[Manager BambooHR ID]
),
PATHCONTAINS(Employees[HierarchyPath], @ManagerBambooHRID)
)
Here "HierachyPath" is a custom column, made in the semantic model holding the management chain for each person, received by using the PATH function.
This sucessfully produced a filter, showing both direct and indirect managers, without using RLS.Hi, Anonymous ,
No I didn't submit an idea in the Ideas Forum. I've already done this about a different topic. Still waiting for feedback there. 🙂 I decided to ask instead an experienced colleague in our company and he offered me a working solution. The solution is to write the DAX query for each dataset in the following manner:
EVALUATEFILTER(
SUMMARIZECOLUMNS(
'Exams'[Employee Name],
'Exams'[Exam Name],
'Exams'[Exam Date],
'Exams'[HierarchyPath]'Exams'[Manager BambooHR ID]
),
PATHCONTAINS(Employees[HierarchyPath], @ManagerBambooHRID)
)
Here "HierachyPath" is a custom column, made in the semantic model holding the management chain for each person, received by using the PATH function.
This sucessfully produced a filter, showing both direct and indirect managers, without using RLS.Hi HinkaSt ,
We really appreciate your efforts and for letting us know the update on the issue. Glad to know you got a working solution. Please continue using fabric community forum for your further assistance.
Regards
