Post reply

How to create SSRS reports where my data source is SSAS cubes and these reports will have the filtered data based on the user who has logged in?

  • February 16, 2011 at 9:44 pm

    #388880

    I want to create SSRS reports where my data source is SSAS cubes and these reports will have the filtered data based on the user who has logged in.

    My requirement is that : I do have a mapping table where I have mapped the username with the userid.

    My fact table contains the userid's of various persons and the respective data allocated to them.

    1)How should I capture the username of the user who has logged in to the reports and pass it to the SSAS cubes?

    2)How to filter the data from the cubes based on the user login from cubes?

    Its urgent. Pls help

  • March 1, 2011 at 3:50 pm

    #1293271

    Use the built-in User!UserID field and assign this to a parameter of the dataset!!!

    pe @user-id = User!UserID

    however in MDX you need to map the User!userID to a MDX member using the StrToMember function.

    However to do so the User!UserID needs to be transformed to a string presentation of the dimension member.

    This must be done in the form: [Dimension_Name].[Attribute/hierarchy_name].[membername]

    The string expression will be something like this = "[Employee].[Employee_login_id].&[" + User!UserID + "]"

    I do not know how users login to reportserver (windows, forms etc). Mostly it looks like domain\User

    and second you need to set up a dimension or dimension attribute that contains the UserID's

    Quickest thing to do is

    Use default reportserver MDX select parameter and create a default parameter (do not select multiple values).. On the parameter_list of the report a new parameter is created edit this one make it a hidden one and use

    above expression to change the default value of the parameter..

    Regards Kees.

  • October 11, 2018 at 12:44 pm

    #2009225

    I know this post is a few years old but I wanted to share;
    its just ="["+User!UserID+"]" you don't have to add the = "[Employee].[Employee_login_id].&[" + User!UserID + "]" information in on the SSRS report. I wrestled with this for two days and I finally got it to work with just ="["+User!UserID+"]"

    In the default values, specific values, the expression for the value you have to put in is ="["+User!UserID+"]" in order to get it to work

    Steps:
    Created Parameter from the cube.
    under properties for the report parameters
    General -
    Data Type = Text
    Allow Blank Values = unchecked
    All Null Value = unchecked
    Allow Multiple values = unchecked
    Choose Hidden

    Available Values -
    None

    Default Values -
    Specific Values -
    Value Expression - ="["+User!UserID+"]"

    Leave Advanced as default
    Run report!

    MCSE SQL Server 2012\2014\2016

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply