Report Design
 Crystal Reports Forum : Crystal Reports 9, X, XI, 2008, 2011 : Report Design
Message Icon Topic: NULL values (replace all) - how can I do it? Post Reply Post New Topic
Author Message
silesianeu
Newbie
Newbie


Joined: 29 Sep 2011
Online Status: Offline
Posts: 3
Quote silesianeu Replybullet Topic: NULL values (replace all) - how can I do it?
    Posted: 08 Jan 2012 at 5:28pm
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
IP IP Logged
sgtjim
Newbie
Newbie
Avatar

Joined: 23 Aug 2011
Online Status: Offline
Posts: 32
Quote sgtjim Replybullet Posted: 09 Jan 2012 at 12:07pm
I would try something like this,

WhilePrintingRecords;
If IsNull({Command.COL1}) Then Previous({Command.COL1})
Else {Command.COL1}

Make sure this formula is in the details row because "Previous" will pull the last row value for that column.

Although this seems like a band-aid to a bigger issue. There might be a way to link your tables or group the report differently to eliminate these nulls. What is the table structure and data type like? How are the tables linked?

Edited by sgtjim - 09 Jan 2012 at 12:11pm
IP IP Logged
silesianeu
Newbie
Newbie


Joined: 29 Sep 2011
Online Status: Offline
Posts: 3
Quote silesianeu Replybullet Posted: 10 Jan 2012 at 11:52am
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
IP IP Logged
sgtjim
Newbie
Newbie
Avatar

Joined: 23 Aug 2011
Online Status: Offline
Posts: 32
Quote sgtjim Replybullet Posted: 25 Jan 2012 at 10:40am
You can try and force the sort.

First Create a formula to evaluate Field2

If {Field2} <= 123 then 1
Else If {Field2} > 123 And {Field2} <= 643 then 2
Else If {Field2} > 634 And {Field2} <= 5453 then 3
Else
4

Then insert a group using this formula and used {Field2} instead or whatever you want the grouper to display.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.

FREE ADVANCED
Crystal Reports Training!

Plus, free bonus PDF cheat sheet! Advanced Crystal Reports Training Give Me Advanced Training!