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 12»»

Parameter Alias Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 12:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:35 AM
Points: 258, Visits: 663
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')

Post #1447687
Posted Monday, April 29, 2013 2:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 25, 2014 4:20 PM
Points: 152, Visits: 448
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.


  Post Attachments 
ParameterValueSample.jpg (8 views, 51.07 KB)
Post #1447765
Posted Tuesday, April 30, 2013 7:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:35 AM
Points: 258, Visits: 663
That is perfect! Thank you for the help. It is appreciated.
Post #1448032
Posted Tuesday, April 30, 2013 12:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:35 AM
Points: 258, Visits: 663
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?
Post #1448186
Posted Tuesday, April 30, 2013 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 25, 2014 4:20 PM
Points: 152, Visits: 448
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.
Post #1448210
Posted Tuesday, April 30, 2013 1:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:35 AM
Points: 258, Visits: 663
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.
Post #1448214
Posted Tuesday, April 30, 2013 6:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 25, 2014 4:20 PM
Points: 152, Visits: 448
If you can post a screenshot of the complete error message, I might be able to point to a possible resolution for the error.
Post #1448282
Posted Wednesday, May 1, 2013 3:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:41 AM
Points: 41, Visits: 165
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

Post #1448632
Posted Wednesday, May 1, 2013 7:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:35 AM
Points: 258, Visits: 663
SQL Server. As soon as it hits the first comma in ('4','10','30') it throws an error.
Post #1448661
Posted Wednesday, May 1, 2013 8:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 25, 2014 4:20 PM
Points: 152, Visits: 448
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.
Post #1448662
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse