count of projects within user access




I have a list of projects that is in selective access. I need to be able to get the count or number of projects per user. For example user A has access to 1 project. Total count of projects is 4.


When I do a count line item (formula = 1 ) my line item result if 4 even if I only have access to 1 project. How will I be able to get the to get the result or 1 for the count. See screenshot below :








  • Hello Ana,


    Aggregation of data is not impacted by filters and user access.


    If you need to aggregate data based on user access, it is not possible straight. You need to follow some workaround, that creating the selective access as a module and referring this module to do the calculations.


    You can import the data from this module into the User setup. 


    Selective access module ==> User Setup (Import process)

    Selective access module ==> Drive calculations




  • Ana22
    Hi Arun,

    Thanks but I'm confused on how to set up the selective access module with user set up? Do you mean I need to create a module with dimension of user and my project list?

  • Hi @Ana22 


    As @ArunManickam  said there is no direct way to do this.


    The only way I can think of is as follows 


    1. Export the users from Model Settings>Users in a CSV/TXT file.

    2. Create a module with user dimension and get the 'project selective access' field from the exported users file to a text field. Though using text is not the best practice, but because a user will have an access to multiple projects, and those will be separated by a comma, thats the only way.

    3. Using conditions, you will need to map the text with a  line item with users and project as 2 dimensions. You can skip this line item and pass directly to 4 as well.  Remember, Booleans are best in terms of performance while calculating.

    4. Build a line item which has convert the conditions in the above line item to number. So it will be if true then 1. Dimensions being users and project. Summary being 'SUM'. Again if you skip 3, then you can have only this line item. Just that if there are multiple conditions, try avoiding.

    5. In the final line item you will need to have it with the user dimension and get point it to the previous line which will aggregate the total across users.


    Let us know if you have any further questions