Limiting report filter data using MDX

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,
Mykhailo

Darius,
 
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.
 
Regards,
Mykhailo

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: https://www.flexmonster.com/doc/configuring-authentication-process-for-ssas-xmla/#roles.
 
Hope you find this helpful!
 
Regards,
Mykhailo

Darius,
 
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,
Mykhailo

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: "https://olap.flexmonster.com/olap/msmdpump.dll", 
    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: https://jsfiddle.net/flexmonster/7g82cnn3/.
 
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,
Mykhailo

Hello, Darius,
 
Thank you for reaching out to us and for explaining the case in detail.
 
As we understand, there are dimensions in your cube that are not connected to Organization/Location. Consequently, using roles or subquery limits the data to a specific Organization/Location on the grid. However, it doesn't filter out members in the filter pop-up for such dimensions (that are not connected to Organization/Location).
 
Flexmonster displays data that your cube provides. Hence, it can be expected that dimensions that are not connected to Organization/Location on your cube will display all members in the filter pop-ups because they were provided in your cube's response. At the same time, we understand that you would like to avoid restructuring your cubes to add connections to Organization/Location to all necessary dimensions. Therefore, our team is currently looking for possible workarounds that would help solve this issue from the client-side.
 
Currently, we are evaluating a solution that would allow hiding members in the filter pop-up for such dimensions. The general idea is to add a new property when selecting dimensions in Flexmonster's report configuration. This property will determine whether Flexmonster should hide or show unused members in the dimension's filter pop-up window. By unused members, we mean members that were not initially displayed on the grid (were not included in the data slice). Ideally, such an approach will hide unwanted members in the filters' pop-up window and, as a result, help solve the issue on your end without having to restructure your cubes.
 
Do you think such a solution would be suitable for your case?
 
We are looking forward to hearing your thoughts on this.
 
Kind regards,
Vera

Hello, Darius,
 
Thank you for your confirmation.
 
Our team will need some time to evaluate and research the best approaches for implementing this feature. We will share the results of our research with the ETA 22nd of March.
 
In the meantime, feel free to reach out to us should any questions arise.
 
Kind regards,
Vera

Hello, Darius,
 
Thank you for your reply.
 
We will keep you updated on the new property implementation process.
 
Regarding the renewal process, our team has contacted you via email.
 
We will keep in touch!
 
Kind regards,
Vera