It's because Iif is a function and all parameters are evaluated before the function itself is evaluated. Try something like

=Split(Iif(IsNothing(Fields!Col1.Value), "~~", Fields!Col1.Value), "~").GetValue(0)

BTW: The array object returned by Split is...

And to simplify things ...

select

'ActiveProd' = COUNT(CASE WHEN StatusID = 1 THEN 1 END),

'InactiveProd' = COUNT(CASE WHEN StatusID = 2 THEN 1 END),

'DiscontinutedProd' = COUNT(CASE WHEN StatusID = 3...

Instead of calculating a running total in T-SQL, you can use the RunningValue function in your report to calculate the running totals.

For highlighting certain values, just add an expression to...

Use the Split function, e.g.

=Split(Fields!Words.Value, "~").GetValue(1)

=Split(Fields!Words.Value, "~").GetValue(2)

=Split(Fields!Words.Value, "~").GetValue(3)

Peter Brinkhaus (5/13/2010)

---

select

binary_place_nbr, user_first_name,

(

select

count(*)

from

dbo.User_Registrations

...

Joe Celko (5/13/2010)

---

To generalize this, we can use a recursive CTE to get the subtree under a given node and use the previous query on it (untested):

WITH Subtree(node_nbr)

AS

(SELECT T1.node_nbr

...

Half answer accidentilly posted. Removed

Joe Celko (5/12/2010)

---

To answer your original question:

SELECT SUM(CASE WHEN binary_place_nbr % 2 = 0

THEN...

I suppose your are the author of it, otherwise...

SELECT UNITID, COUNT(UNITID) AS TOTAL

FROM IMSV7.COMP

WHERE COMPTYPE = '38'

GROUP BY UNITID

HAVING COUNT(UNITID) > 1

Just another major performance improvement. Create an index on the temporary table #Traverse on column Nodes and include user_first_name. Then make the join condition in subqueries to concatenate user_first_name SARGable....

One simple way the speed things up is to dump the outcome of the CTE into a temporary table and then use this table in the final solution. Using a...

mahbub422 (5/11/2010)

---

Dear Thanks for your excellent job. It's working nicely, but it's taking huge time to execute. I have 1200 records and it's taking around 50 seconds to compute. I...

Alexander de Rooij (5/11/2010)

---

Hi Peter,

This does exactly what I wanted, only thing is that my string is getting to long and all the text does not fit into 1...

Here's an example of string concatenation. Be sure to cast the text field to varchar(max) otherwise it won't work.

create table #ConcatTest(id int, v text)

insert into #ConcatTest

select 1, 'Aa' union all

select...