Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameter Alias


Parameter Alias

Author
Message
bpowers
bpowers
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 960
I created a data set to supply the data to populate a drop down parameter. However, the data is a smallint and I would like to give it an alias the end user would understand. For example, I am returning the Purchase Order Status for the drop down (4, 10, 30), but I would like for the user to see it as a varchar (Open, Aprroved, Requested). Any ideas on how I can pull this off? Here is some example code:


--Purchase Order
Declare @STATUS varchar(10)
Set @STATUS=@PO_STATUS

Select 'PO' as DocumentType
,ID as PONumber
from PURCHASE_ORDER
where PURCHASE_ORDER.STATUS=@PO_STATUS

--Line Type Parameter Dataset
SELECT DISTINCT STATUS
FROM PURCHASE_ORDER
WHERE STATUS IN ('4','10','30')


geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
Every parameter has a "Value" and a "Label." The "Value" is what the parameter passes to any filter or query variable reference to itself. The "Label" is what shows to the user as parameter choices. If all you have are three possible values, you don't really need a query for that. Just define the parameter options manually under the "Available Values" page and use the number for the "Value" and the text for the "Label." I have attached a screenshot of the setup.

The only time this might not be ideal is if occasionally one or two of the statuses are not valid options because there are no items with the corresponding status. In that case, just add a second column to your dataset query that uses a CASE statement to supply the text value for each corresponding integer value. Then use the calculated column as the "Label" and the Status column as the value.

SELECT DISTINCT STATUS, LABEL = CASE STATUS WHEN 4 THEN 'Open' WHEN 10 Then 'Approved' WHEN 30 THEN 'Requested' END
FROM PURCHASE_ORDER
WHERE STATUS IN ('4','10','30')



By the way, the label data type does not change the data type of the parameter value itself. It would still be INT.
Attachments
ParameterValueSample.jpg (8 views, 51.00 KB)
bpowers
bpowers
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 960
That is perfect! Thank you for the help. It is appreciated.
bpowers
bpowers
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 960
How do I correctly use the "Select All" option with this? I have multiple values checked in the parameter property, but when I execute the report it returns " incorrect syntax near ',' ". Any ideas?
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
I cannot say if the error you are receiving is directly related to the multivalue parameter option, but I can tell you that the only comparison operator that works with a multivalue parameter is the "IN" comparison. This is true whether you are applying a filter to a dataset or a tablix, or if you are putting the condition into a WERE clause directly inside a dataset query. You must always change the "=" to "IN" as the comparison operator.
bpowers
bpowers
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 960
The error is in relation to the parameter I have setup. I do have the "IN" clause in place within my dataset query. I am googling for a resolution, but still no luck. Thanks for the feedback. It is appreciated.
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
If you can post a screenshot of the complete error message, I might be able to point to a possible resolution for the error.
Mohamed I.
Mohamed I.
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 348
Hi,

What is the source connection type ?
Is it SQL SERVER or other databses like ACCESS ?

I know Access has issues when using multi value parameters

w00t !!!GOOGLE IS YOUR BEST FRIEND!!! w00t
bpowers
bpowers
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 960
SQL Server. As soon as it hits the first comma in ('4','10','30') it throws an error.
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
I'm not certain that the parameter value has been configured correctly. There should not be any list of values like that in the parameter setup. Each value/label pair needs to be on a separate line in the setup. A screenshot of the context really could go a long way in helping clarify where the problem is.
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