Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

user!userID used to build report parameter selection list Expand / Collapse
Author
Message
Posted Thursday, May 19, 2005 3:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, 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.

Post #183939
Posted Thursday, May 19, 2005 6:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, December 17, 2014 6:34 PM
Points: 1,846, Visits: 3,575

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.
Post #183964
Posted Friday, May 20, 2005 6:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, 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.

Post #184086
Posted Friday, May 20, 2005 8:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, 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.

Post #184125
Posted Friday, May 20, 2005 8:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, December 17, 2014 6:34 PM
Points: 1,846, Visits: 3,575

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.
Post #184144
Posted Friday, May 20, 2005 9:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, 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.

Post #184152
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse