I am using an expression statement b/c i need a multi value select and the IN clause will not work in regular query. Here is the statement i am using, except i am getting a cannot set the command test for dataset error. I think it make be around the null values but not sure. Thanks, Kenny ="SELECT `itron_data`.`order_num`, `itron_data`.`lot_num`, `itron_data`.`ert_type`, `itron_data`.`itron_num`, `itron_data`.`operator`, `itron_data`.`program_date`, `itron_data`.`program_time`, `itron_data`.`num_dials`, `itron_data`.`pcomp_value`, `itron_data`.`drive_rate`, `itron_data`.`cent_indicator`, `itron_data`.`customer`, `itron_data`.`configuration` FROM `pack`.`itron_data` WHERE Fields!program_date.Value >= Parameters!startDate.Value And Fields!program_date.Value <= Parameters!stopDate.Value Or Parameters!startDate.Value = '' And Fields!order_num.Value = UCase(Parameters!orderNumber.Value) Or Parameters!orderNumber.Value = '' And Fields!lot_num.Value = UCase(Parameters!lotNumber.Value) Or Parameters!lotNumber.Value '' And Fields!itron_num.Value IN ("+JOIN (Parameters!ERTNumber.Value,",")+") Or Parameters!ERTNumber.Value = '' Or (Parameters!startDate.Value = '' And Parameters!stopDate.Value = '' And Parameters!orderNumber.Value = '' And Parameters!lotNumber.Value = '')"

i forgot to put that i am using MSS Report builder to design

Hello Kenny, From your problem description, we can see that you encounter the error "cannot set the command text for dataset 'xxxxx'." I doubted that the query string is not correct. Please note the following place. WHERE Fields!program_date.Value >= Parameters!startDate.Value The Field!program_data.Value and Parameters!startDate.Value are two variables in the query expression, but it is used in the WHERE clause directly. I consider that we need add quotes (") after WHERE and use string concatenation (+ or &) to splice the string. The issue may be solved. Hope my answer is helpful to you. Regards, EdwardEdward Zhu TechNet Community Support

OK, i have simplified things so i can make sure i understand. When i execute this query i get error query execution failed for dataset. Can you ehlp me understand what else i am missing ="SELECT * FROM itron_data WHERE Fields!order_num.Value="& Parameters!orderNumber.Value &"" Thanks, Kenny

Hello Kenny, I think that Fields!order_num.Value is not a field name in MySQL table, itron_data, and it is a field variable in the dataset. Therefore, I think the expression can be written as following: = "SELECT * FROM itron_data WHERE " & Fields!order_num.Value & "=" & Parameters!orderNumber.Value If I misunderstand, please forgive me and provide me more information about the table structure, dataset fields name, and parameters. Regards, EdwardEdward Zhu TechNet Community Support

you are correct about the table field name. I have made the changes however now i get the following error message The expression used for the parameter getData in the dataset getData refers to a field. Fields cannot be used in query parameter expressions. thanks

Hello Kenny, Can you post out the query again? Since you have correct the expression, it may involve into some new problem. I need the latest query string for further troubleshooting. Thank you in advance. Regards, EdwardEdward Zhu TechNet Community Support

="SELECT * FROM itron_data WHERE Fields!order_num.Value = Parameters!orderNumber.Value here is the query. Thanks, Kenny

Sorry, that was one of the other queries i was testing with. Here is the one i am actually using currently. = "SELECT * FROM itron_data WHERE " & Fields!order_num.Value & "=" & Parameters!orderNumber.Value

Hello Kenny, Thanks for your response. Based on my experience, the cause of this error is that you are using the fields in another data set. For example, we have a tablix, which binds to data set A. However, we are using the fields in data set B in the expressions in the tablix. It will cause this kind of issue. I recommend you whether you encounter this kind of issue. Meanwhile, if possible, please post out the detail information about this issue, such as where you use the expression, and where the fields are from. Regards, EdwardEdward Zhu TechNet Community Support

I have 1 data source that is pointing to a datasource on my SharePoint site. Which points to my MySQL server. Then i have 1 dataset located under my datasource. This is where i am trying to use the expression. Query type = text. I am using the expression wizard. I do notice for my categories i have field(dataset) and datasets. Under the datasets the values are First(order_num). Under fields i have order_num. So if i add that to my expression it looks like this. First(Fields!order_num.Value, "dataset") Thanks, Kenny

Hello Kenny, Thanks for your clarity. I consider the problem into two scenario. If there are several datasets in the report, and we add a new one to refer to the fields in other datasets, We need use the expression as First(Fields!order_num.Value, "dataset"). Since there are several records returned in the other dataset, we need use the First, Last or other aggregation function to convert it to a single value. Meanwhile, in order to identify the dataset, we need involve the name of the dataset.If we want to use the field in the same dataset, It is quite impossible to achieve, since all fields in the same dataset is generated by the query expression. We cannot get the value before we launch query. If so, it will cause various problems. I hope my explanation is helpful to your scenario. Regards, EdwardEdward Zhu TechNet Community Support