Using User!UserID in SSRS to access data in SSAS Cube

Question

I am running SQL 2012 and I have a SSAS multidimensional cube that holds measures for our retail business. I have created an SSRS report that pulls the data from the SSAS cube. I want the user to be able to run the report and only pass the users specific store revenue, customer count, ect, on the SSRS report that they are viewing. I have created a parameter from the cube. Under the report parameter that was created from the cube I put in under default values, specific values, this expression for the value ="[DimUserTable].[Username].[UserID].&[" + User!UserID + "]", the report will not run. It gives the error;

For more information about this error navigate to the report server on the local server machine, or enable remote errors

Query execution failed for dataset 'Revenue'. (rsErrorExecutingCommand)

Does anyone have any idea how I can accomplish getting only the users store information to show on the report they are viewing?

Answer 1

You only need to pass the user id to the parameter.. your query should take care of the filtering using the parameter. Something like

where [DimUserTable].[Username] = @userid

I would use the following as the default value for the userid parameter

=User!UserID.Substring(User!UserID.LastIndexOf("\")+1)

Of course, this depends on what your cube holds for userid value. If it does NOT include the domain or machine name.. use the above.. else just use =user!userid