September 7, 2011 at 11:06 am
Hi,
I have a parameter (InclCancel) where the labels for the user are "Yes" and "No", but the values are "INACTIVE" and "CANCELLED, INACTIVE", respectively. When I pull the values from the parameter into the data set query, it seems to be ignoring whatever was selected. A "Yes" selection gets the same results as a "No" selection. I've tried all kinds of CStr functions, with/without quotes, the numeric equivalents for the values - nothing seems to work.
Any suggestions?
Thanks,
Michele
September 7, 2011 at 11:35 am
how are those values for the parameter being populated? Are they from a dataset or did you manually create the values?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 7, 2011 at 11:36 am
Also, if you created those values and labels manually - did you use quotes around the values?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 7, 2011 at 11:48 am
The values were manually created.
I've tried quotes & no quotes around the values. I've tried the following for the "No" values:
CANCELLED, INACTIVE
"CANCELLED", "INACTIVE"
"CANCELLED, INACTIVE"
"CANCELLED" & ", " & "INACTIVE"
Cstr("CANCELLED") & ", " & Cstr("INACTIVE")
Cstr(CANCELLED) & ", " & Cstr(INACTIVE)
Cstr("CANCELLED, INACTIVE")
September 7, 2011 at 11:58 am
msoutherland (9/7/2011)
The values were manually created.I've tried quotes & no quotes around the values. I've tried the following for the "No" values:
CANCELLED, INACTIVE
"CANCELLED", "INACTIVE"
"CANCELLED, INACTIVE"
"CANCELLED" & ", " & "INACTIVE"
Cstr("CANCELLED") & ", " & Cstr("INACTIVE")
Cstr(CANCELLED) & ", " & Cstr(INACTIVE)
Cstr("CANCELLED, INACTIVE")
In the header of the report, place a textbox with an expression to display the Parameters!yourparam.value
Verify that the parameter is getting the correct value. If it is getting the correct value, then it is time to check the stored proc to make sure it matches criteria of the proc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 7, 2011 at 12:11 pm
Yes, I already checked to see if the value was being populated correctly. It is.
I'll check with someone else if they can find any glaring problems with the query, but I think it's the way that the value of the parameter is being treated. It's not a multi-value parameter so a JOIN won't help (at least I don't think it will).
Thanks for your help.
September 7, 2011 at 12:13 pm
Once the param is populated, what do you do with it next? Are you pulling info from another dataset? Is that info populated via adhoc query or stored proc?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 7, 2011 at 12:23 pm
It goes into another dataset/query. It's part of the WHERE clause.
SELECT
stuff
FROM
tables
WHERE
criteria AND
table.ID IN (SELECT table.ID FROM table where table.name NOT IN (:InclCancel))
The :InclCancel is the parameter. Essentially, I'm trying to pull all of the IDs from a table where the Names of the values aren't "INACTIVE" or "CANCELLED, INACTIVE" depending on what the user selected. I can also change this part of the query to use the IDs, but I was getting errors about the value not being a number/integer which is why I switched to the names/text.
September 7, 2011 at 12:31 pm
It appears that your query does not match your parameter.
You are comparing a field "name" to the value of your parameter. Is that correct?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 7, 2011 at 12:37 pm
No, I'm not doing that - at least I'm not intending to.
I need to pull all of the values from the table where the value of the field is not whatever is coming over from the parameter.
September 7, 2011 at 12:53 pm
msoutherland (9/7/2011)
No, I'm not doing that - at least I'm not intending to.I need to pull all of the values from the table where the value of the field is not whatever is coming over from the parameter.
This subquery may need modified then
SELECT table.ID FROM table where table.name NOT IN (:InclCancel)
You should find the field that equates to your Cancel Param to replace the table.name field.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply