How to make multiple-valued in one parameter in Query Builder?

Dear all,

I have a problem over here, hope to get some good advices from you guys.

I use Query Builder in VS2005 where you could see some tables on top, queries on the middle and resultset on the bottom windows.

I have a statement like below:

SELECT...WHERE Booking.BookingType IN (@.Type1, @.Type2, @.Type3, @.Type4, @.Type5)...

However, I found out that there are too many parameters, I wish to have only ONE parameters in the list of IN() operator. Is there any way to do it? Thanks!

I would suggest giving a look to Jens Suessmeyer's SPLIT function; the definition of this function can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

You ought to be able to code up your lists as comma separated strings and create a query that

looks something like this:

Code Snippet

SELECT...

from Booking

join split (@.yourParm, ',')

on Booking.BookingType = splitValue

Another alternative is to pass your argument as XML and use SQL Servers XML ability to interpret the XML argument.

Another question: Are you trying to make a parameter similar to a multi-value parameter that is available in Reporting Services? If the answer to that question is "YES" then you need to understand that feature is only available in Reporting Services and is not in general available to Transact SQL.

|||

If you are trying to reduce the number of parameters, just remove them from inside the paretheses.

In fact, if you are ONLY testing for a single parameter, use equals instead of IN (...) -it may be a bit more efficient.

|||

Actually, what I wish to have is putting a list of values in the @.Param programmatically in my code, thus, I wish to have one single @.Param instead of multiple @.Param(s) in SQL. I used Query Builder GUI to build my SQL query in VS2005, however, I couldn't find way to just put one @.Param and set multiple values in it.

For e.g.

SELECT * FROM Booking WHERE Type IN ( @.Param );

C# Code:

....DataTable1.GetData("Lab, Meeting, Course");

Is this possible to do this?

Thanks!

|||

There is no direct way to do something like

Code Snippet

SELECT * FROM Booking WHERE Type IN ( @.Param );

in which @.param contains data such as 'Lab, Meeting, Course'. Again, I would suggest using the SPLIT function to accomplish your objective.

How to make comparison characteristics equal for both years (2004 vs.2005)?

I was trying to create a simple report to compare two years' sales by
region (2004 vs. 2005). We used to have 13 regions before 2005. Now we
only have 12 (DOM013 was removed starting 2005). How can I make DOM013
to appear in the comparison table even if we have no data for it in
2005?
Any help is appreciated.
KeithKeith,
Please, let me know which data region u r using?
If u r using chart the its quite straight forward to add region as data
fields and year as series fields.
If u r using dataset fields then probably u can use IsMissing property
of field.
Hope this helps.
Thanks,
Mahesh|||Mahesh,
Thanks for your reply! I am using TABLE. Basically, there are 3
columns: Region, 2004 Sales, and 2005 Sales. How to use this IsMissing
property? Can you give me an example?
Thanks,
Keith|||Keith,
Sorry for late reply. Was busy with other stuff.
Well, normally we use
= Fields!CaseStatus.Value
to assign value to text box
instead if you think there is possibilty that you will not get any data
for this field the you can check for value before using it
e.g
= iif(Fields!CaseStatus.IsMissing, "N/A", Fields!CaseStatus.Value)
now here your column will display the value if its returned by dataset,
else it will display "N/A"
hope this helps.
Cheers,
Mahesh