• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pull data into PowerPivot based on Username

V

Vivek D

Member
I have a view in a SQL Server Database that I have tied to a PowerPivot table (in Excel 2010) and built a dashboard based on that.
I have another table that has UserNames and list of Accounts they have access to.

I want to be able to pass the dashboard template to all users. When they click say a Refresh button, Power Pivot should go and fetch only the data for the Accounts they have access to and display it in the dashboard.

I know there is a Username() function and I realize I can build security within Powerpivot to ensure user only sees what he should see - Method explained here.

However, instead of getting all data and then showing only what user should see, I ideally want PowerPivot to only fetch the data that is relevant so that the data model itself has only that data.

Is there a way to achieve that?
 
Last edited:
I had to avoid using Power Query because in Excel 2010 there is no Load to Data Model option. You have to load it to an excel table and then load into Power Pivot and if you make any changes in Power Query then you have to pretty much redo everything in PowerPivot.

Given that, I can't really go the Power Query route. Is there any other alternative?
 
Hmm, let me think on that. I use Power Query heavily and or Access to do heavy lifting instead of Excel/Power Pivot.
 
Try method explained in the link. I haven't had the time to test it though.
Excel extras: PowerPivot – using USERNAME() to create ‘my’ reports

What I normally do, though it probably won't work directly with PowerPivot Data Model, is to use User info stored in Local Computer's environment variable (Active Directory User info), which I tie back to Database record.

Example use in VBA.
Code: 
Dim UserName as String
UserName = Environ("userdomain") & "\" & Environ("username")
 
That method in the link filters at a report level i.e. it needs to have all data in the model and then filter based on Username. I wanted to avoid having all the data in the model as it's a financial report and everyone is concerned about data security.

So when you say you use the environment variable, how exactly do you tie it back to the database record?
 
In the database you'd need to create user table using AD login info. If there isn't one already.

Then using vba you pass on the environment variable to SQL statement querying database (Data connection). So user can only query data with their own user name (Joined or in the table itself).

Something like below
Code: 
Set conn = wb.Connections("SQL_Sales")
    query = "SELECT * FROM tblSales WHERE saleUser=" & "'" & UserName & "'"
    conn.OLEDBConnection.CommandText = query
    conn.Refresh
 
K. So that brings up the next problem which is that there is no way to refresh the PowerPivot Model without opening the PowerPivot Window in Excel 2010.

It's like Microsoft purposely kept these big annoying limitations so people would upgrade to 2013. I personally have 2013 installed but I'd have to ask every user to upgrade if they have to use the dashboard which is what I was trying to avoid.
 
Yeah. I saw that but looks like it won't work with linked tables.

So to get data based on username I have to pull data into a table and then link it to a power pivot table but for the above to work I have to use a non-linked power pivot table. No solution that will give me both and be user-friendly. :mad:
 
You must log in or register to reply here.
Back
Top