Post reply

SSRS

  • June 1, 2009 at 2:55 pm

    #205577

    When I click on the report on the report viewer I need to prepopulate the fields based on the user ID of the person who ran the report.

    As soon as the end user click on the report the user ID should fetch parameters for that user.

    For example we have cascading parameters for division,region,area,district,territory. Territory being the lowest level.

    We have different groups like sales representatives,area managers,directors etc.

    When the sales representatives logon thay should not see the parameters division,area,region,district but only the lowest level territory should be seen.

    Is there any way that when sales representatives logon they should get the values of the territory being the lowest level based on the user ID.

    Any help would be highly appreciated.

    Thanks,

    Sasi

  • June 2, 2009 at 2:55 am

    #1003233

    Hi,

    we cannot write expressions for visibility of Report parameters.

    You can do one thing..create three different reports for sales representatives,area managers and directors group..

    n depending on userid redirect to corrosponding report...

    Feel free to ask any doubts regarding this issue.

  • June 2, 2009 at 3:13 am

    #1003238

    Hi,

    If you create table with two columns sometable (domainusername, salesterritory) you can catch available territories for user logged on.

    Create dataset for populating parameter values, query should be: select salesterritory from sometable where domainusername = @UserName and in “Parameters Tab” for parameter @UserName put value =User!UserID.

    Good luck 🙂

  • June 2, 2009 at 8:19 am

    #1003428

    Thanks for your reply but how do you redirect to the report based on userid.

  • June 2, 2009 at 10:43 am

    #1003524

    sasi1234 (6/2/2009)

    Thanks for your reply but how do you redirect to the report based on userid.

    I have almost same situation. Store manager can see only own store, regional manager few stores and me (and few others) can see it all. I have only one report and I populate parameters values with dataset as I explain in post above.

  • June 2, 2009 at 9:50 pm

    #1003749

    Redirection can b done on front end...

    For example.. i have a site on which user logs in and i have two user groups PM and TM.

    I created two aspx pages pm.aspx and tm.aspx.look n feel wise both pages are same.

    If PM logs in he is redirected to pm.aspx page and on this page there is a link to report.For this link give report server url of PMs report.

    If TM logs in he is redirected to tm.aspx page and on this page link to a report is provided.For this link give report server url of TMs report.

  • June 3, 2009 at 6:28 am

    #1003924

    Dont know if this will help but...

    I had a sales rpt from our CRM that basically everyone in the company needed to see, but it

    was imperative that it be restricted similar to what you are saying....Execs can see all, Reg Mgrs only

    their region(s) and salesreps only their sales.

    I really only wanted 1 report to handle all of it as otherwise I'm copying and pasting to new report and so

    on.

    So, in SSRS I used a combo of the userid logged in and the folder from which they accessed the rpt.

    Then I had a proc that took those 2 variables and decided what to show in the user parameters based

    upon their "level". Worked beautifully!

    One thing I should point out is that you can't preview the functionality of using the folder from within BIDS...

    deploy to your test box and test from there.

    And again, the 1 and only rdl file was deployed to several folders and security handled the rest via AD and

    my proc.

  • June 4, 2009 at 9:12 am

    #1004712

    You can attack this on a bunch of levels, and many have already been discussed.

    If the one set of reports covers your needs you can limit their content by controlling the parameter lookups as described above, but the parameters still show even when they can't be changed.

    If you need to have separate reports for different groups you can use AD groups and report-level or folder-level security to hide what shouldn't be seen. That can get tedious to manage over time and it's easy to make mistakes.

    If you have the option of launching from an application rather than Report Manager you can get around a lot of these issues. We have both the field hierarchy problem and some groups that see different reports as well. We have separate folders in RS that hold the reports for each role so that when we make the web service call they only see reports they're allowed to see. (Subreports are Hidden so users don't get distracted.) All the visible top level reports have common parameters so when a user chooses one the app can just fill those in a standard manner and open the report in a separate window and not care exactly which report it was. Further, when you launch reports that way you can fill and hide some parameters so users don't even know they're there. If you use the user's information (either as a parameter or @username) you get even more control.

    [font="Arial"]Are you lost daddy? I asked tenderly.
    Shut up he explained.    [/font]
    - Ring Lardner

  • June 10, 2009 at 7:38 am

    #1007392

    Hi,

    I had the similar issue and the link below helped me.

    http://bisqlserver.blogspot.com/2006/11/user-based-parameters-in-reporting.html.

    Hope this helps you too. Let me know if you need more info.

    Thanks,

    Latha.

  • June 13, 2009 at 9:44 pm

    #1009228

    TiffinyJannetBlack,

    I think rhe folder folder security works.Can you please tell me how could I set the parameters based on the folder.

    Thanks

  • June 15, 2009 at 6:27 am

    #1009587

    Sure!

    Add a new parm and for the property it is a Global (like the userid global), make sure

    it is a hidden parm.

    Then, in your stored proc, pass in the userid and the foldername.

    It will fail if you try to preview in BIDS, so deploy to your test box to a couple diff

    folders so you can test what info should show based on folder the user

    accessed the rpt from.

    I did something like this in my proc

    create procedure sp_myproc

    @user varchar(10),

    @folder varchar(20)

    as

    begin

    declare @sql varchar(max)

    if @folder = 'Exec' --execs see it all

    begin

    set @sql = 'select * from table'

    end

    if @folder = 'Customer Service' --only see their items

    begin

    set @sql = 'select * from table where csrname = ' + '''' + @user + ''''

    end

    exec (@sql)

    end

    this way the dataset itself is restricted based upon what they are "allowed" to view in the report.

    worked very very well for my CRM reporting - 1 report serves Execs, Business Unit VPs, Regional Mgrs, Sales Reps

  • June 15, 2009 at 9:41 am

    #1009737

    Thanks a lot,it worked

Viewing 12 posts - 1 through 11 (of 11 total)

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