Sql-server – Give users permission to read subsets of data in tables in SSRS

I'd like to create a SSRS report which utilizes data from our Data Warehouse, DW. This DW has summarized, for example, the sales of 100 or so stores into one table FactStoreSale.

In the report I've managed to see which AD user who has started the report, by using the expression =User!UserID as an default value in a parameter.

Each AD user is included in a AD Security Group. This Group typically includes the manager Domain\first.lastname and the employees shared account Domain\Store1000User. The name of the Security Group is typically GRP_1000 (for StoreKey 1000).

Is it possible to allow users in a Security Group to only see data corresponding to that StoreKey, i.e. if some user included in the Security Group GRP_1000 uses the report, the resulting set will be:

Select Sales, Quantity FROM FactStoreSale
WHERE StoreKey = 1000

Note: I do not wish to create a report for each Store, as that would require 100 reports. Some managers can manage more than one store, so they may be included in multiple Security Groups and need the corresponding permissions.

I do not want to split up the underlying table FactStoreSale, even though it should be possible to split this up into 100 or so schemas, and give members of the Security Groups access to these scehmas accordingly.

A solution I have in mind is to create a table using SSIS which gets updated every day with the appearence:

SecurityGroup    StoreKey 
GRP_1000         1000
GRP_1001         1001
GRP_1002x        1002

and then use this table to reduce the amount of stores a user can see data from.

I do not know how I can utilize the Security Group in SSRS though, seeing as I have only found UserID in the Built-in Fields in SSRS, but nothing about their respective Security Groups.

I am also not sure if this is the optimal way to go, as it requires additional tables and manually updating the table (because I do not wish to automate this procedure yet to have a higher Control over this).

EDIT

I found a way to get the permission path, i.e. Group belonging to a user using SQL server and the Query:

EXEC xp_logininfo 'Domain\User.Name', 'all' 

I've tested in SSRS that I can replace 'Domain\User.Name' with my @User_id variable. I've tested to create a dataset with this query. This seems to give the correct data, but what it lacks is the StoreKey column which I thought could be Cast(RIGHT(Permission_path,4) as int).

I'd like to be able to have a variable called @StoreKey in SSRS where Available Values from Query would be something like:

Select Cast(RIGHT(Permission_path,4) as int) 
FROM EXEC xp_logininfo @User_id, 'all' 

, but naturally I can't make an EXEC in the FROM clause. I also tried to make a new Dataset in SSRS which would use data from another Dataset within the same Project but that did not work either (and I Googled that it is not possible).

Edit: final solution

What I did to implement this was to create a table of the appearence:

ADGroup     StoreKey
Group_HQ    1010
Group_HQ    1011
Group_HQ    1012
Group_HQ    1000
Group_1010  1010
Group_1011  1011

I then created a variable called StoreKey in SSRS where I used Available values from dataset and where I had a dataset where I selected StoreKey using a JOIN on the current user's AD Group using the Query provided by sepupic and my table I had created. Thus I only got the StoreKeys available to certain AD Groups.