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
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".
Joined: 23 Aug 2011
Online Status: Offline
Posts: 32
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?
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:
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