SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


user!userID used to build report parameter selection list


user!userID used to build report parameter selection list

Author
Message
Jane Matheson-154829
Jane Matheson-154829
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2018 Visits: 185

When trying to user user_user!id to limit the input of report paramter's default values I get the following error:

The report parameter ‘employee_id’ has a DefaultValue or a ValidValue that depends on the report parameter “NT_USER”. Forward dependencies are not valid.

How can I use this global user!userid in a dataset that is used to built a report paramter selection list.

NT user in my error is the name of the report paramter I gave the default value of user!userid.


stevefromOZ
stevefromOZ
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15842 Visits: 3757

Are you trying to have a parameter available to the users so that they can select another user but it defaults to themselves? Or are you just trying to limit the results for another parameter list to those relevant only to the current user (ie they can't select another user and thereby change the subsequent parameter values)? If it's the latter, then you could use a dynamic query string in your dataset e.g. instead of just Select * from Mytable where user_id = @parm1
you would have ="select * from mytable where user_id = '" & user!userID.Value & "'"
Note am assuming an MSSQL datasource hence the single quotes around the user!userID.Value



Steve.
Jane Matheson-154829
Jane Matheson-154829
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2018 Visits: 185

OK, I'm frustrated. If I take to long to write my reply it disspaears when I hit preview....

Let me try this again.

Your exactly right with the second option - I'm trying to limit hte selction to only what is valid based on the logged in user.

I am trying to dynamic SQL but am still having a few issues. If I try to use User!UserID direclty in the query I get an error:

If I try to assign it to a query parameter that is mapped to a report paramter I get another error.

I have actually managed to crash visual studio now with one thing I was trying and am having difficulty getting my proejct back up.

Please anymore advice is greatly appreciated.


Jane Matheson-154829
Jane Matheson-154829
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2018 Visits: 185

OK, more information.

The error I'm getting when trying to use a parameter in the dynamic query used for the drop down selection:

The expression for the query ‘hardcoded_selectionquery’ contains an error: [BC30037] Character is not valid.

I can take the query parameter out and hardcode my selection list and it works. My problem may be parameter related in general as I cannot seem to figure out how to get a parameter to work at all in the dataset when it is used as input to another parameter.

Any help greatly appreciated.


stevefromOZ
stevefromOZ
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15842 Visits: 3757

Firstly, thanks for confirming that I'm not going insane - the 'losing postings' is happening to me quite a bit, and the time out seems to be reasonably short. I've taken to writing the msg, select all then copy, so if i goes MIA i can paste it back in again.

With your dynamic query (assuming MSSQL as a datasource), you need the whole statement in double quotes and an equals at the start (ie outside the first double quote). Remember that your userid needs to be seen by your MSSQL datasource as a string, so it need single quotes. Also remember that unless you let VS do the line wrapping, you need to close off each line with a dble quote and then start the next line with an ampersand and then another double quote. Remember to leave your lat character of the previous line as a space, or the first character of the new line. A really simple example (that works) is below:

= " SELECT ' " & User!UserID & " ' AS Col1"

I added some additional spaces in there to make the different quotes obvious, normally you'd have no space between the (MSSQL string encapsulating) single quote and the (VB.Net) string encapsulating double quote. Remember when using dynamic SQL it's easiest to write/generate a dummy query first to populate the field list and then mod it to be what you really want. Also, it looks like you already have but in case, ignore the .Value I added to the UserID in my first post, it's not required.

If you still have issues can you post your dynamic query?

HTH,



Steve.
Jane Matheson-154829
Jane Matheson-154829
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2018 Visits: 185

I'm not sure what I did different OTHERTHAN I noticed you said tht the line must end with a space so I carfully placed the string back in and hit return after the last ".

It works now!!!!! This is great. I evidently wasn't holding my breath or crossing my eyes or something right.

Thank you for being so patient and explicit to help me get this working.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search