asked on January 17, 2018
we have a problem with the amount of data the report filters are using.
the OLAP Cube we are using have products and sales and branches, both products and sales have link to a particular branch where they are used.
when creating the MDX we use a filter by branch that generates correctly the output with only data related to the branch we use in the MDX.
problem is starting when we try to use any of the filters in the generated report, they contain products for all branches not just the products returned by the MDX. (attached image is for report that have MDX filtering only London products but filter displays Vienna products also)
Is there any way to define how much and what data to be used by the report filters ?
best regards,
Nikolay
⋅ January 17, 2018
Hello Nikolay,
Thank you for your question. It seems that subquery property should solve the case. Please find more information in our docs - http://www.flexmonster.com/doc/data-source/?hilite=%22subquery%22 . Also, here is a jsfiddle example - http://jsfiddle.net/flexmonster/hcckvwx0/10/. As you can see, there is only the 2008 year available for filtering for the Delivery Date.Calendar field.
Please let us know if the information above was helpful.
Regards,
Dmytro.
⋅ January 18, 2018
from the looks of it we need to add additional filtering by "Branch" for each dimension we use then the filter options in the report actually show only the data we need, we kinda hoped that we can filter the "Branch" dimension only and then all the rest of the dimensions having a link to the "Branch" dimension will be filtered automatically.
Anyway, works fain as is, just few additional lines to define filters.
⋅ January 19, 2018
Hello Nikolay,
Thank you for the update. We are glad that the case is seamed to be resolved.
Please let us know if there is anything else we can help you with.
Regards,
Dmytro.
⋅ March 2, 2019
Hello,
Is there any progress on this? I'm using the Angular implementation (ng-flexmonster) of the Flexmonster tool and 'subquery' is apparently not a part of 'DataSource' (yet?). I would love to know how Nikolay got around this issue, since I tried setting the filter property of columns, rows, AND the reportFilters to all have the same members and I still get more data in the filter dialog then I want/need.
⋅ March 4, 2019
Hello Jon,
Thank you for writing to us.
The latest available version of ng-flexmonster, which is 2.6.13-1 for now, does include subquery property in the DataSource object.
Please see the example of how to use subquery property with Flexmonster: https://jsfiddle.net/flexmonster/ej8j4jmg/.
Let us know if everything works fine for you.
Regards,
Dmytro
⋅ March 4, 2019
Hi Dmytro,
Unfortunately, the "subquery" option did not work for me. The table filters still showed values that should not be included in the returning data set. Is there another workaround that I can use to make sure that only the filtered data values show up in the table filters? It's a security issue at my company.
Thanks,
Jon
⋅ March 5, 2019
Hello Jon,
Thank you for your feedback.
The use case you described sounds like the one that should be solved with subquery property. Could you please check if the MDX query is written correctly and the subquery property is in the right place?
As for data security, it is possible to configure user roles on the cube. That should allow hiding some data for the users with low access permissions. Please follow the guideline from our docs: https://www.flexmonster.com/doc/configuring-authentication-process-for-ssas-xmla/.
Let us know in case of any other question.
Regards,
Dmytro
⋅ March 22, 2019
Hi Dmytro,
I think my question can be better exemplified if I modify the JsFiddle you made earlier above (https://jsfiddle.net/dftcv973/). In this example, I simply changed out "Account Type" for the column you had initially. Here, on the grid, we can see that there are only two results that come through for the given geography "United States" that was part of the subquery. But if we click on the filter button next to "Account Type", it lists what I gather is ALL of the account types in the database/OLAP cube structure. I guess what I'm asking is can the subquery affect all other dimension filters as well? So that if we filtered by "United States", if I clicked on the filter button for "Account Type", it would only show "Expenditures" and "Revenue" as possible options? Or is that somehow an OLAP cube configuration thing?
Thanks so much for your time,
Jon
⋅ March 26, 2019
Hello, Jon,
Thank you for your answer.
We would like to inform you that our component is designed to show all available members in the filter window. Therefore, please specify how critical is this for you. If it is an important feature for your project, there is an ability to modify what is shown in the filter window. To do that, you need to be using the Accelerator as a DLL (https://www.flexmonster.com/doc/referencing-accelerator-as-dll/). It allows modifying the list of members shown in the filter window. However, this would require some coding on your side.
Please let us know if you need more details.
Regards,
Tanya
⋅ March 26, 2019
Hi Tanya,
That's the answer we eventually came to also. As it stands right now, I'm interrupting the "DiscoverMembers" response, removing the members I don't want to show up in the filters dialog, and updating the "all" and "count" properties before continuing to send the response back to the UI. Does this sound correct?
Thank your for your time yet again,
Jon
⋅ March 28, 2019
Hello, Jon,
Thank you for your response.
Yes, you are right. By following the steps you described, you will customize which data will be available in the filters to the end user.
Please let us know if everything works for you and if you have further questions.
Best Regards,
Vera
⋅ April 6, 2019
Hi all,
Just a bit of a follow-up. We found out that if you straight-up remove members as they go through the DiscoverMembers response stage, the mapping with the Execute stage doesn't update. Apparently the ordinals and level ordinals get thrown off and the returned data is slightly off in it's coordinates. I just wanted to ask if there is any discussion for offering an option to "filter the filters", as it were, in the near future. My company deals with reports that are multi-tier at times (multiple columns and rows), which complicates matters when we find ourselves in a situation where we want to only show user-role approved values in the table filters to the client. DiscoverMembers is a good place to start, but then updating the ordinals and trying to map the changes over to an exponentially complicated "columnlist" and "rowlist" in Execute seems nigh impossible. Thanks for your help in this, but we had to eventually just turn table filters off because of the issue. Please let me know if any changes to this feature is in the works.
Thanks again!
Jon
⋅ April 9, 2019
Hello, Jon,
Thanks for your follow-up.
Currently, there are no plans on changes to this feature. As a possible workaround, you can set subquery for multiple hierarchies at once. Please have a look at the following sample: https://jsfiddle.net/flexmonster/rph75nm6/. The members are limited for Geography as well as for Category. You can apply report-specific subqueries to achieve the necessary members be showing in the filtering window.
Hope it helps.
Regards,
Tanya
