Get Free Trial

Limiting report filter data using MDX

Darius Studdard asked on May 25, 2021

We are having the exact problem that was described in this thread:
In our case we have a number of data points that should be filtered based on Organization.  I’ve applied the following in the subquery of the datasource:
subquery: “select {[Organization].[Organization ID].&[@Ajax.ViewBag.OrganizationId]} on columns from [” + selectedItemNameNoSpaces + “]”
In this subquery OrganizationId is passed into our view and selectedItemNameNoSpaces is an argument containing the name of a selected data cube.  This seems to properly filter data in the grid on all levels but in the report filter there are selections being displayed that belong in other Organizations after we add other dimensions to the report.  You can see this in the screenshot attached where we are properly displaying only 2 Location Names in the grid but the report filter for Location Name contains all locations across all Organizations.
I’m going to attempt an approach where I intercept the query in the accelerator as suggested in the linked thread but I’m wondering if any other kind of workaround for this issue has been put in place since that thread was answered back in 2019.  I also noticed that the person in the thread who was attempting the accelerator approach wound up giving up and just disabling filtering in his application so I’m not feeling too optimistic but am giving it a try.  Any guidance you can give would be appreciated as this is a pretty big security issue within our application.

8 answers

Mykhailo Halaida Mykhailo Halaida Flexmonster May 27, 2021

Hi Darius,
Thank you for posting your question!
Just letting you know we’ve received your inquiry and are already working on it. We’ll return to you with a response as soon as possible.
Best regards,

Darius Studdard May 27, 2021

Ok Mykhailo thank you and I’ll look out for the response!

Mykhailo Halaida Mykhailo Halaida Flexmonster May 28, 2021

Thank you for giving us time to prepare an answer.
The behavior you’ve described is expected since this is how the subquery property works in Flexmonster – the component has no way of knowing how the data in the cube is interconnected, therefore it retrieves all members in other hierarchies.
That being said, have you considered using SSAS roles to filter the data instead of using subqueries?
Please let us know your thoughts.

Darius Studdard May 28, 2021

We haven’t looked into SSAS roles as an option.  I’m assuming this approach would allow us to take care of the filtering of both the grid data and the filter window data? Would this require each user or org to have a separate login to our OLAP server?  If so, this is definitely different than the approach that we take now.  
Also, is there any documentation on related to using flexmonster with SSAS roles?

Mykhailo Halaida Mykhailo Halaida Flexmonster May 31, 2021

Hi Darius,
Yes, the approach involving SSAS roles would allow you to control data filtering both on the cube side and then in the grid, with Flexmonster filters.
Here’s a tutorial on setting up roles in Flexmonster, referencing a relevant Microsoft tutorial:!roles.
Hope you find this helpful!

Mykhailo Halaida Mykhailo Halaida Flexmonster June 7, 2021

Hope you’ve had a great weekend!
I was wondering if you’ve had a chance to learn more about using roles in SSAS & Flexmonster – do you think this approach might work for you? 
We’d be happy to hear your feedback.
Best regards,

Darius Studdard June 9, 2021

Hi Mykhailo,
I did spend some time reading up on SSAS roles and while it seems like it could be viable, I do have a couple of questions/concerns.  

  1. I see that all roles defined on the cube are predicated on a valid Windows user identity.  So we assign windows ID’s to whatever roles we define.  For us, we use one generic windows ID as  the credentials for access to our OLAP cubes.  We don’t have access to any end user’s Windows ID.  Because of that, what would be the right approach for us to implement this effectively?
  2. How does the roles argument that gets passed in from Flexmonster work exactly?  Will passing in the name of previously created role through this parameter bypass whatever Windows authentication is necessary and automatically apply the filtering defined in the role?  
  3. Also just thinking ahead that creating a separate role for each of our Organizations and then having to do so every time we add a new Org could potentially be a lot of manual work that needs to be done on a recurring basis.

I’m thinking that maybe using our 1 Windows ID and assigning it to all of the roles that we create to filter by Organization may be an approach…And then we pass the name of whatever role we want to apply through the Roles parameter in Flexmonster.  But I’m really not sure if this will work.

Mykhailo Halaida Mykhailo Halaida Flexmonster June 11, 2021

Hi Darius,
Thank you for your response.
Addressing your first two questions, note that in order for Flexmonster to work with SSAS roles, you only need to set the role name in the dataSource object:

dataSource: { 
type: "microsoft analysis services",
proxyUrl: "",
catalog: "Adventure Works DW Standard Edition",
cube: "Adventure Works",
roles: "Sales Manager US"

This is enough for Flexmonster to retrieve the data accessible for the specified role – no additional authorization on the client-side is necessary. You can see how this works with our SSAS roles sample:
Speaking of your third point, yes, you might need to update your roles and permissions after particular cube changes – there’s no simpler way around it since your business logic has to be defined on some layer.
Hope this helps!
Best regards,

Please login or Register to Submit Answer