| Author |
Topic |
|
ZMike
Posting Yak Master
110 Posts |
 Posted - 2008-10-27 : 15:12:27
|
| I have the following query which seems easy enough. The query works fine in Reporting service under the data tab and I can use it and it works flawlessly. However once I go to preview or go to deploy the report it does not work correctly there. Is there something that I'm doing incorrectly ? The Parms work okay in the data tab but not on the actual preview or after it's deployed which doesnt make sense to me. Any help would be greatly appriciated. WHERE(SMREPPF.SMRPNB LIKE '%'+@Employee_Number +'%') OR(SMREPPF_1.SMNAM IN (@Supervisor)) OR(CF15PF.EHTXDL IN (@Department)) OR(SMREPPF.SMNAM LIKE '%'+@Employee_Name +'%') OR(SMREPPF.SMUSER LIKE '%'+@UserID +'%') OR(SMREPPF.SMAD3 LIKE '%'+@User_ID2 +'%') |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 2008-10-27 : 16:27:24
|
| Tara,Sorry about that. In the data tab the parms work correctly (All of them)However in preview mode I can get all of the LIKE Statments to work together or the two IN Statements. But once I combine The 4 parms that have Like in it and the other 2 parms that have IN most of the parms dont work. I will either get the 2 in statements or the 4 Like statements but they dont all work togeather. I do not get any errors it's just like it doesnt see all of the parms when they are all combined in a statementIf I break everything down individually they all work. If I keep the like statements and delete the IN statements it worksIf I delete keep the IN Statements and Delete the LIKE Statements then it works Does that make sense? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-28 : 01:53:54
|
seem like you want to make them optional. for that just set the default values of all parameters to null and select allow null value in parameters tab. then modify query as belowWHERE(SMREPPF.SMRPNB LIKE '%'+@Employee_Number +'%' OR @Employee_Number IS NULL) AND(SMREPPF_1.SMNAM IN (@Supervisor) OR @Supervisor IS NULL) AND(CF15PF.EHTXDL IN (@Department) OR @Department IS NULL) AND(SMREPPF.SMNAM LIKE '%'+@Employee_Name +'%' OR @Employee_Name IS NULL) AND(SMREPPF.SMUSER LIKE '%'+@UserID +'%'OR @UserID IS NULL) AND (SMREPPF.SMAD3 LIKE '%'+@User_ID2 +'%' OR @User_ID2 IS NULL) |
|
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 2008-10-28 : 08:12:16
|
| visakh16,Once again I think you've answered my question. I just had one more that goes along with this.@Department and @Supervisor I wanted to leave as a multi value and pull the information from a seperate dataset and leave them with a Available Values "From Query" with the data set that I wantDefault Valuses from the dataset also.My goal for those 2 fields are to have them as drop downs with the inital selection being "Select All" and then being able to narrow it down and also having the option of multi value.However when I set it up this way I get the following error."An expression of non-boolean type specified in a context where a condition is expected, near ','. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-28 : 08:33:13
|
then change query also to take them as multivalue ...(','+@Supervisor+',' LIKE '%,'+ SMREPPF_1.SMNAM + ',%' OR @Supervisor IS NULL) AND(','+ @Department + ',' LIKE '%,'+CF15PF.EHTXDL +',%' OR @Department IS NULL) AND.. |
|
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 2008-10-28 : 08:47:00
|
| WHERE(SMREPPF.SMRPNB LIKE '%'+@Employee_Number +'%' OR @Employee_Number IS NULL) AND(','+ @Supervisor +',' LIKE '%,'+ SMREPPF_1.SMNAM + ',%' OR @Supervisor IS NULL) AND(','+ @Department + ',' LIKE '%,'+ CF15PF.EHTXDL + ',%' OR @Department IS NULL) AND(SMREPPF.SMNAM LIKE '%'+@Employee_Name +'%' OR @Employee_Name IS NULL) AND(SMREPPF.SMUSER LIKE '%'+@UserID +'%'OR @UserID IS NULL) AND (SMREPPF.SMAD3 LIKE '%'+@User_ID2 +'%' OR @User_ID2 IS NULL)I still get the same error. When I run it like this I have the 2 drop downs @Department and @Supervisor and they're selected to "Select All"I still get the error -- Incorrect syntax near ','.I'm not sure what I've missed Basically it's like it's not seeing the Select all field. I have to chose something from both fields in order to get results even if Select All is used. Actually to elaborate a little more. I think it's not doing the comma between my multi values correctly. If I use one value it's okay. Once I use more than one value is when I get the error. ....Incorrect syntax near ','. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-28 : 10:28:10
|
| What all are comma seperated parameters here? i would expect only @Supervisor,@Department to be csv as per your query posted. |
|
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 2008-10-28 : 13:13:04
|
| I was thinking that they could type something in the test box via comma seperated if they wanted to see more than one record for the other fields. I recall you giving me a similar fix a few weeks ago that did that function. I'll have to go look it up. The query it self still run still seems to get caught up on the @Department and @Supervisor in preview mode. I dont get any errors in the build though . I still get the errorIncorrect syntax near ','.If I take those 2 fields out and run it then I dont get the syntax error. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-28 : 13:18:50
|
| how are you passing the values? as '1,2,3,..' or as '1','2','3',... |
|
|
|
|
Another Report Question