Forum Discussion
Is possible to use an external data source with the current user as a parameter?
Hello everyone!
I've been doing a couple days of research (including reviewing several posts on this board), and I wanted to pose the specific question to make sure I'm not missing something. Is there any way to use the user using the report (ie the dax function USERNAME) as a parameter for a SQL function or an API call?
In our current approach, we pre-calculate a large table that includes the information we need for all of the potential users of the report, but only a fraction of the users will actually use the report before the values need to be calculated again. A much better approach for us would be to calculate the data for the user when they access the report (ie when the user session starts).
Let me know if there is any other information you need, and thank you for your help!
4 Replies
Cgilb , I get an error message if I try and use this function in the query editor:
If I try to use this function in a custom SQL statement when I set up the source, I get the following error:
- GilbertQ
Super User
Hi Anonymous
Apologies I thought this was from within the report level which uses DAX.
I am not sure exactly how to pass this through to the underlying source.
So, I think I found something that works in the spirit of what you suggested. You can require using the user's credentials for a data source on the service, then in the SQL server function "CURRENT_USER" can pick up who is logged in for the request. It looks like it may be a viable approach, I just need to look at the options for reducing queries (we only need 1 refresh per user session) and if this approach works if you also have import data types.
- GilbertQ
Hi Anonymous
If you are using DirectQuery to your SQL Source I do think you could pass through the USERPRINCIPALNAME() to the query?
