April 29, 2013 at 12:13 pm
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')
April 29, 2013 at 2:25 pm
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.
April 30, 2013 at 7:42 am
That is perfect! Thank you for the help. It is appreciated.
April 30, 2013 at 12:11 pm
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?
April 30, 2013 at 1:12 pm
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.
April 30, 2013 at 1:28 pm
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.
April 30, 2013 at 6:37 pm
If you can post a screenshot of the complete error message, I might be able to point to a possible resolution for the error.
May 1, 2013 at 3:36 pm
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:
May 1, 2013 at 7:59 pm
SQL Server. As soon as it hits the first comma in ('4','10','30') it throws an error.
May 1, 2013 at 8:24 pm
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.
May 2, 2013 at 1:41 pm
Maybe the parameter type is not correct ?
Can you post all the screenshot of the parameter configuration please
And the table definition
Thanks
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
May 3, 2013 at 8:08 am
Here are the screen shots.
STATUS IN (@Status) --('4','10','30')
May 3, 2013 at 8:44 am
The parameter name is misspelled as "Stauts," which could be the main issue. Also, if the original status values are integers, then you should change the Data type of the parameter to integer as well, so that no implicit conversion needs to take place in the comparison operation.
May 3, 2013 at 9:53 am
Caught the mispelling after I posted the images. Fixed both issues and still get the comma error.
May 3, 2013 at 10:13 am
can you repost all the dataset and the query using this parameter ?
is the parameter used on a data region in the report ?
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply