Is it possible to limit access to limit access to dimension members, but include them in totals via TARGIT Security?

Scenario is a general ledger account hierarchy where accounts related to employee expenses (i.e. payroll) are grouped under a level called "Employee Expenses". The request is to use security role forced criteria to prevent users from seeing the individual employee expense accounts, but still allow users to see the total "Employee Expenses" and include all the details in totals. This was something that was possible in the SSAS environment by disabling access to the members, but then leaving the Enable Visual Totals box unchecked. I believe I can accomplish this in the front end via things like Visibility and maybe alternate account hierarchies, but this would require having multiple versions of the same report (one that has the employee accounts visible, and one that does not). Thanks!



1 comment
  • Hi Mark,

    You can achieve this by combining a couple of TARGIT features: 'Initial criteria' and 'Required criteria'.

    User1 can see details:

    User 2, in the same document and the same spot, can only see totals:


    • User1 and User2 are members of different Roles.
    • User2 is member of a Role that has an Initial criteria set to "Company = Casual Clothing Retail" and "Company = Casual Clothing Wholesale" ... or any other dimension with a similar pseudo criteria that ensures that no data is filtered away from User2. Make sure that you use a dimension that you would never use in the dashboard.
    • In the dashboard I have two crosstabs placed exactly on top of each other. At the bottom, the crosstab with the detail (month) data; on top of that, the crosstab with the total (year) data. When both crosstabs are enabled/visible, you will only be able to see the one with total data - the other is concealed behind it.
    • On the topmost crosstab, the one with totals, I have enabled 'Required criteria' by the Company dimension. The setting is to 'Hide the object' when required criteria hasn't been fulfilled.
    • The effect, when User1 logs in, the topmost crosstab will be hidden due to the required criteria not being fulfilled - and the underlying crosstab with details will be revealed.
    • The effect, when User2 logs in, the topmost crosstab will be visible due to the required criteria being fulfilled - and the underlying crosstab with details will be concealed.


    • This will work for Anywhere users that are not able to change the conditions. A Designer user will be able to circumvent these settings.
    • You should disable Excel export settings for the Detail crosstab.

    BR / Ole


Please sign in to leave a comment.

Didn't find what you were looking for?

New post