Convert NULL with previous row in table

[silesianeu]

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? I searched in archive and FAQ but no effect

PS. I'm using CR2008

Best regards
SilesianEu

 

[lbass]

Replace each column with a formula like this:

whileprintingrecords;
numbervar col1;
if {table.col1}="" then
col1 else
col1 := {table.col1};

-LB

 

[silesianeu]

Thank you for quickly reply. It works OK

But, when I did add sort column i.e. COL2 it looks like that it retrieve data from DB and display column with NULL values at top page. Is there anything to do with it?

Best regards
SilesianEu

 

[lbass]

I didn't understand about the need to sort--you cannot sort on values that are dependent upon the sequence of records. Can you explain what kinds of sorts you want to do by showing some example sorts of the data you show above?

Nothing in your sample data shows that rows with nulls "belong" to the first row in a set--is there some other field that accomplishes this?

-LB

 

[silesianeu]

Hi,

There is a simple data returned by stored procedure:
EXEC sp_TEST1;
(first row - col name)

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 FIELD1 or FIELD2. For tests, I want sort second col and 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 (example). 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

 

[lbass]

Please answer this question:

Nothing in your sample data shows that rows with nulls "belong" to the first row in a set--is there some other field that accomplishes this?

How do YOU know which rows belong together?

-LB

 

[silesianeu]

If I understood you good, my answer is: no

 

[lbass]

And your answer to my last question?

-LB

 

[silesianeu]

I'm not sure, but there isn't rows belong together.

 

[lbass]

In your original sample, you wanted to assign col1 and col2 values to nulls in the following rows. How did you know that the populated col1 and col2 should appear in the following rows? If it is ONLY based on the sequence, then you cannot sort the rows in any meaningful way. To do that, you would have to have some field that the rows share, and you seem to be saying that there isn't one.

-LB