Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-18 : 07:57:47
|
| Hamada writes "I make a reports using SQL reporting services 2000. this kind of reports requires a parameters that could be null, more than one value, or can be just match values. So, the trick i did to overcome the null value is to make "case when .. else .. end" at the where condition like :SELECT USERS.USER_FIRST_NAME ,USERS.USER_LAST_NAME, CASE WHEN lower(USER_GENDER) = 'm' THEN 'Male' ELSE 'Female' END AS USER_GENDER, USERS.USER_EMAIL, USERS.USER_PHONE, USERS.USER_CREATION_DATE, USERS.USER_COUNTRYFROM USERS INNER JOINWHERE /* *** Here is the magic code *****/(USERS.USER_GENDER LIKE CASE WHEN @GENDER = 'm' THEN 'M' WHEN @GENDER = 'f' THEN 'F' WHEN @GENDER = 'M' THEN 'M' WHEN @GENDER = 'F' THEN 'F' ELSE '%' END)***My problem is: How could i put more than one value at the where clause "where USER_GENDER in ('M','F','N')"?***Another problem, I wanna execute stored procedure at the SQL reporting service 2000, or at least execute it at a function?" |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-18 : 14:14:48
|
| Well, let's see if I can help you out a little bit... First, the CASE statement you have could probably be simplified to something like:WHERE Users.User_Gender = ISNULL(UPPER(@GENDER), Users.User_Gender)For your other questions, you can put multiple values in the WHERE clause using the IN statement like you're showing. Perhaps if you combine that with my ISNULL(UPPER()) format above it will meet your needs. Also see this article on CSV strings for more ideas.What do you mean you want to execute the stored procedure at the Reporting Service? Execution of stored procedures happen inside the SQL Server. You can put the call to the stored procedure in your DataSet in Reporting Services, and in fact this is the recommended approach. Am I missing something in your question?---------------------------EmeraldCityDomains.com |
 |
|
|
gcowhsu
Starting Member
38 Posts |
Posted - 2005-08-19 : 13:24:54
|
| for your first question you can do it the way that was suggested. There is another way as will by using the custom code.It is VB programming and you can return a string that looks like a='b' or a='c' or a='d'your ds query would be="select a from users where " & Code.GetString()after everything runs you would haveselect a from users where a='b' or a='c' or a='d'I have the same response to the second question. Just create a new dataset and tell it to run a stored procedure instead of text. I think it gives you a dropdown of which sp you want it to run, you just have to pass in the variables, if any |
|
|
|
|
|
|
|
|
Reporting service problem