none
User!UserID not working in conjunction with Available Values

    Question

  • I have been banging my head against the wall over this now for a day or two.

    I also found a thread with my same problem but no conclusion (http://social.msdn.microsoft.com/forums/en-us/sqlreportingservices/thread/60DD8B17-48EF-473B-9C05-DD5BBEA8B84B)

    My problem is this. I am able to use the User!UserID as a default value, with NO available values set, and it detects the current user and runs the report without any trouble.

    However, we want to be able to provide the list of available people for users to choose from, but by default set it to themselves. Once I bring Available Values back into the picture, it no longer detects the current user.

    For the 'Default Value' in my parameter I have:

    =Cstr(LCase(Trim(User!UserID)))

    For the UserID (calculated field) populating my available values I have:

    =CStr(LCase(Trim(Fields!LoginUser.Value)))  

    Any idea what the heck I might be missing here?



    • Edited by determinism Monday, November 19, 2012 11:16 PM
    Monday, November 19, 2012 11:15 PM

Answers

  • Okay I have it solved.

    First of all, last week I was messing about with my data sets trying to get this to work in every different way I could think of.

    My answer lies in the OverrwriteDataSets and OverWriteDataSources properties and also making sure I first Trim and then convert to LCase.

    What I didn't realize is there were lingering filters on my data sets from last week when I was messing about. I had removed these filters and could no longer see them inside the shared data sets (this goes for any change to your shared data set, be it in your query, filters, whatever).

    If you don't have OverrwriteDataSets and OverWriteDataSources set to true, when you build and deploy your report, your data sets are never truly updated, and reporting services will reference and older version of it you may not want. This can really confound and confuse a troubleshooting process.

    I also changed the order of my string operators to Trim(Lcase(()) instead of Lcase(Trim(()) etc.

    Hope this helps someone down the road.

    Best of Luck,

    Craig



    • Marked as answer by determinism Tuesday, November 20, 2012 12:33 AM
    • Edited by determinism Tuesday, November 20, 2012 12:39 AM
    Tuesday, November 20, 2012 12:33 AM