Making a report out of missing information 3

[eizatt]

Hi, I am trying to create a report that will give all the telephone numbers between 0001 and 9999. The problem is that only some of the number are in the database. I need the report to fill in the gaps of the missing numbers. For instance, let say that 0123, 0124, 0128 are in the database. I need the report to show all the numbers...

0123 (in database)
0124 (in database)
0125 (created by report)
0126 (created by report)
0127 (created by report)
1028 (in database)

I can't simply check to see if the previous number is in sequence because I need all numbers from 0001 to 9999. Can anyone help me out?

Thanks in advance.
Ed

 

[synapsevampire]

Takes a more advanced formula, and is dependent upon your version of Crystal as a formula in CR 8.5 or below can only output 254 characters.

The methodology I would use is to create the string on the fly of those missing using a formula in the details section, something like:

whileprintingrecords;
numbervar Start := {table.field}
numbervar End := next({table.field})
numbervar X;
Stringvar OutPut :="";

if End-start > 1 then
for X := Start+1 to End-1 do(
OutPut := Output+ totext(X,0,"&quot+" (created by report))
);
Output

-k

 

[eizatt]

Thanks for the help. I really appreciate it. It works great but is there a way to get it to output the number as a single line instead of creating a string with all the numbers strung together? I need it to output the missing number on a single detail line as if it really was in the database.

0123 (in database)
0124 (in database)
0125 (created by report)
0126 (created by report)
0127 (created by report)
1028 (in database)

Thanks again for the help.
Ed

 

[synapsevampire]

Try:

whileprintingrecords;
numbervar Start := {table.field}
numbervar End := next({table.field})
numbervar X;
Stringvar OutPut :="";

if End-start > 1 then
for X := Start+1 to End-1 do(
OutPut := Output+ totext(X,0,"&quot+"(created by report)"+chr(13))
);
Output

-k

 

[eizatt]

That was a nice try but it didn't work. It simply ignores everything after the first chr(13) so the only output I get is the first number in the loop. Do you have any other ideas?

Thanks,
Ed

 

[lbass]

Did you check "can grow" for the field? The variable will not take up new rows, and therefore needs to expand vertically within the row. You might want to place it in a second detail section b. I tested this a little because I thought it was an interesting solution and it seemed to work.

-LB

 

[dgillz]

Purdy slick stuff there SV.

I also found this intriguing and did my own testing and I did come up with one minor weakness. If there is a big gap between records in the database, the formula will create a string that goes over 254 characters which will return an error in Crystal 8.5

I am sure you could test this string as it approaches 254 characters and create a string array, but I am not so sure I would want to code that up. Time to upgrade to CR v9!


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[eizatt]

Thanks everyone for the help. LB, the "can grow" fixed the problem.

Ed