Hi,

I have problem with NULL values in report. There is query taken from stored procedure and I cannot change source. For understand, I put sample data:
COL1  COL2  COL3
1        643     NULL
NULL  NULL   varchar1
NULL  NULL   abcdef2
2        5453   NULL
NULL  NULL   agsdg
NULL  NULL   53fwf
NULL  NULL   thbd4
3        123     NULL
NULL  NULL   yngtr
4        6744   NULL
NULL  NULL   mmdgh

I want replace NULLs in 1 and 2 column like that:

COL1  COL2  COL3
1        643     NULL
1        643     varchar1
1        643     abcdef2
2        5453   NULL
2        5453   agsdg
2        5453   53fwf
2        5453   thbd4
3        123     NULL
3        123     yngtr
4        6744   NULL
4        6744   mmdgh

You see that I replaced in cols first and second value in row previous to the next to last with value. I need it for sort for cols1 or 2 or 3 - and I understand that I must replace these NULLs with values.

I tried use one Formula Field:

stringVar x1 := {Command.COL1};
if ({Command.COL1} <> '') then
x1 := {Command.COL1}
else
x1 := Previous({Command.COL1});
x1

But unfortunatelly it replace only first row with value previous row. How can I do that it replace for all rows until to row with next value?
Of course I set Report Options that "Convert Database NULL Values to Default" and "Convert Other NULL Values to Default".

It is possible to achieve this effect?

PS. I'm using CR2008

Best regards
SilesianEu



Edited by silesianeu - 08 Jan 2012 at 5:30pm

Hi,

Thank you for reply. Table structure I don't know, I've got only stored procedure. Your solution works OK, but I have problem sorting.

There is a simple data (I did smaller and simpler for tests) returned by stored procedure:
EXEC sp_TEST1;

FIELD1  FIELD2  FIELD3  FIELD4  FIELD5
1       643     NULL    NULL    NULL
NULL    NULL    text111 abcdefg 3.43443
NULL    NULL    ergvaesvaw esvvd  4.000000
2       5453    NULL    NULL    NULL
NULL    NULL    fasgfa  rdssd   62.2000
NULL    NULL    dfawef  f2wews  44.0000
NULL    NULL    dfawef  f244s   44.0000
3       123     NULL    NULL    NULL
NULL    NULL    weweee  3gwewrf 149.630000
NULL    NULL    wfewd   5eefv   1.000000
4       675     NULL    NULL    NULL
NULL    NULL    rrrrrr  eqgve   2.000000

All of them are nvarchar type and I want sort by FIELD2. As you see, values in 2 col is:

FIELD2
643
NULL
NULL
5453
NULL
NULL
NULL
123
NULL
NULL
675
NULL

I put your solution for col1 and col2 into Formula Field and I put these into Details. It looks like that finally:

-------------------------------------------------------
Details [@FIELD1] [@FIELD2] [FIELD3] [FIELD4] [FIELD5]
-------------------------------------------------------

In Preview we have like that:

FIELD1    FIELD2    FIELD3    FIELD4    FIELD5
1   643  NULL    NULL    NULL
1   643  text111    abcdefg    3.43443
1   643  ergvaesvaw    esvvd    4.000000
2  5453  NULL    NULL    NULL
2  5453  fasgfa    rdssd    62.2000
2  5453  dfawef    f2wews    44.0000
2  5453  dfawef    f244s    44.0000
3   123  NULL    NULL    NULL
3   123  weweee    3gwewrf    149.630000
3   123  wfewd    5eefv    1.000000
4   675  NULL    NULL    NULL
4   675  rrrrrr    eqgve    2.000000

At this moment, we cannot sort, then I put [FIELD1] and [FIELD2] near [@FIELD1] [@FIELD2]. Next I add sort by "Sort Record Expert" button and Select FIELD2 to sort. In Preview I see strange values:

FIELD1    FIELD2    FIELD3    FIELD4    FIELD5
NULL    NULL    fasgfa    rdssd    62.2000
NULL    NULL    dfawef    f2wews    44.0000
NULL    NULL    dfawef    f244s    44.0000
NULL    NULL    text111    abcdefg    3.43443
NULL    NULL    ergvaesvaw    esvvd    4.000000
NULL    NULL    weweee    3gwewrf    149.630000
NULL    NULL    wfewd    5eefv    1.000000
NULL    NULL    rrrrrr    eqgve    2.000000
3   123  NULL    NULL    NULL
2  5453  NULL    NULL    NULL
1   643  NULL    NULL    NULL
4   675  NULL    NULL    NULL

I want that it returns in properly sorted order:

3   123  NULL    NULL    NULL
3   123  weweee    3gwewrf    149.630000
3   123  wfewd    5eefv    1.000000
1   643  NULL    NULL    NULL
1   643  text111    abcdefg    3.43443
1   643  ergvaesvaw    esvvd    4.000000
4   675  NULL    NULL    NULL
4   675  rrrrrr    eqgve    2.000000
2  5453  NULL    NULL    NULL
2  5453  fasgfa    rdssd    62.2000
2  5453  dfawef    f2wews    44.0000
2  5453  dfawef    f244s    44.0000

Generally I want "Bind Sort Control" on Col2 Description but it cannot be added into Formula Field and I assign into FIELD2 top page header.

Best regards
SilesianEu