Limiting Filter selection via parameter

Question

Hello,

I am currently working on a use case where I need to dynamically limit selection possibilities in filters based on the current user. For example, different users should have different sets of filter options depending on their username (pre-selected or able to select themselves)

Initially, I tried building a dynamic parameter using a "Logical SQL Expression" with the existing "User Name" parameter, but it hasn't worked as expected. The system doesn't seem to recognize or apply the dynamic condition properly (it only applies a "hardcoded" name).

I have the following questions:

* Has anyone successfully implemented dynamic filters based on the logged-in user? If so, how did you approach it?

* Is there a way to build role-specific or user-specific filters that work consistently across dashboards?

Any help, insights, or examples would be greatly appreciated!

Thanks in advance!

Caroline Blanchard · Answer

Does the column Manager User name exactly match the value in User Name? For us, for example, Manager user name column is like "Blanchard, Caroline" versus Display Name column is "Caroline Blanchard". We had to use a custom calc to flip things around because the match has to be exact.

What do you mean use it directly, like dropping the parameter in a filter? I think you have to connect the parameter to you data somehow for it to actually filter the report.

I have not successfully referenced Parameters in a logical SQL statement. I tend to get issues with validating the logical SQL with a "datatype mismatch" when I put a parameter into the logical SQL.

Something that has worked for me, however, is creating a custom calculation with a parameter and using that column in the column section of the parameter. Here's an example:

So here I take the user's person number and I reference the user name parameter. This means that whatever the user's department name is, is the only value that shows up in the parameter because that's the only thing that's been replaced.

Then I reference the 'Replace' custom calc in the parameter setting the value equal to first available value which will only have that specific user's department name. From there I just bind this parameter to a Department Name filter.

JT_DC_2023 · Answer

Hi Caroline,

thanks a lot for the reply. Initially, I tried applying the filter Manager's user name and binding it to the User Name parameter. However, the filter doesn't automatically apply when other users log in, even though it should.

I also tried using the User Name parameter directly, but I am encountering the same issue. Have you faced this problem before?

Also are you able to reference parameters in a logical SQL statement. I tried this as well but it does not seem to work for me either.

Caroline Blanchard · Answer

Hi @JT_DC_2023, our organization has successfully used parameters to create dynamic filters of the logged in user using the binding parameters feature. What we did was put in a filter from the out of the box data for example, Display Name out of workforce core and bound to the out of the box parameter Display Name. This successfully filters the data in the report to the users session variable display name. Let me know if this works for you, or if you have any other questions!

On your second question on role specific filters across dashboards, I'd recommend looking into custom security based on groups, this is something we've implemented as well. We put people into groups based on job roles and department so when certain individuals open up reports they are prefiltered to their specific department data within the dashboard.