How can I format sequenced numerical data that has gaps, in CR XI

[qu1ncy]

Hello,

Using Crystal Reports XI. Am trying to create the following format in a report. The data comes from an Oracle database table.

I have a list of digital images, each its own “microfilm id” scanned in batches. The batches are reviewed and at times some images deleted and rescanned at a later date with a new microfilm id. This process leaves gaps in the microfilm id number sequence within a batch which the users have asked us to identify.

For example, batch ABC-710 has image microfilm ids from 10049 20001 to 10049 20056. Microfilm ids 10049 20032 and 10049 20034 have been removed from the batch. Batch ABC-711 remains intact as scanned. The report users would like to see the following format grouped by batch when they run the report.

Batch: ABC-710
10049 20001 – 10049 20031
10049 20033 – 10049 20033
10049 20035 – 10049 20056
Batch: ABC-711
10049 20057 – 10049 20100

Users will be retrieving this data by date scanned as their parameter. How can I format the above in the Crystal Report they’ll be producing? Any assistance is much appreciated.

Thanks,
Quincy

 

[lbass]

Place this formula {@seq} in the detail section:

whileprintingrecords;
numbervar first;
numbervar last;
if onfirstrecord or
{table.batch} <> previous({table.batch}) then
first := {table.microfilm ID} else
if {table.batch} = previous({table.batch}) and
{table.microfilm ID} <> previous({table.microfilm ID}) + 1 then
first := {table.microfilm ID};
if
(onlastrecord or
{table.batch} <> next({table.batch})
)
or
(
{table.batch} = next({table.batch}) and
{table.microfilm ID} <> next({table.microfilm ID}) -1
) then
last := {table.microfilm ID};
totext(first,0,"") + " - " + totext(last,0,"")

Then in the section expert use a detail suppression formula like this:

evaluateafter({@seq});
whileprintingrecords;
numbervar first;
numbervar last;

last < first
or
(
last = first and
(
first = next({table.microfilm ID})-1 or
{table.microfilm ID} = next({table.microfilm ID})
)
);

-LB

 

[qu1ncy]

LBASS, thanks for your prompt reply! All fields coming over from the Oracle table are varchar.

I've had to convert the microfilm id fields to numbers using CDbl(). No errors found in the formula, but I'm getting "The string is non-numeric" error on the first
"CDbl({DOCMETA.XMICROFILM_ID})" in line: first := CDbl({DOCMETA.XMICROFILM_ID}) else ... when I run the report.
---------------------------------------------------------
whileprintingrecords;
numbervar first;
numbervar last;
if onfirstrecord or
{DOCMETA.XBATCHNAME} <> previous({DOCMETA.XBATCHNAME}) then
first := CDbl({DOCMETA.XMICROFILM_ID}) else
if {DOCMETA.XBATCHNAME} = previous({DOCMETA.XBATCHNAME}) and
CDbl({DOCMETA.XMICROFILM_ID}) <> CDbl(previous({DOCMETA.XMICROFILM_ID})) + 1 then
first := CDbl({DOCMETA.XMICROFILM_ID});
if
(onlastrecord or
{DOCMETA.XBATCHNAME} <> next({DOCMETA.XBATCHNAME})
)
or
(
{DOCMETA.XBATCHNAME} = next({DOCMETA.XBATCHNAME}) and
CDbl({DOCMETA.XMICROFILM_ID}) <> CDbl(next({DOCMETA.XMICROFILM_ID})) -1
) then
last := CDbl({DOCMETA.XMICROFILM_ID});
totext(first,0,"") + " - " + totext(last,0,"")

 

[qu1ncy]

Doesn't like the space in the microfilm ID? I need to retain the space in the final format.
Q

 

[qu1ncy]

LBASS, confirmed it is the space causing the error by using some microfilm IDs with no space in the Oracle test environment. The correct format will always be with a space after the 5th character.
I will attempt to reformat the microfilm id (no space)in a separate formula & pull that into your formula. The first 5 characters are always 5 in number. These consist of YYJJJ date as numeric. The last set of numbers can vary from 5 to 10 depending on the line of business.

If that doesn't work, we can employ a view on the oracle side and format a no space MID as a separate column.

Thanks,
Q

 

[lbass]

Create a formula like this:

//{@microfilmID}:
replace({DOCMETA.XMICROFILM_ID}," ","")

Substitute this in the formula for the field. Then in the last line, add the spaces back in:

left(totext(first,0,""),5) +" "+mid(totext(first,0,""),6)+ " - " + left(totext(last,0,""),5) +" "+mid(totext(last,0,""),6)

I apologize for not paying attention to the obvious datatype of your field--I tested it on a number field.

-LB

 

[qu1ncy]

LB- Thanks for the additional info & time spent on this. I'll update post as soon as we have a opportunity to make the changes.

Q

 

[qu1ncy]

LBass, Works very nicely! I apologize for taking this long to get back to this post.

Created the formula @Microfilm_ID as:
------------------------------------------
replace({DOCMETA.XMICROFILM_ID}," ","")
------------------------------------------
Updated SEQ formula as recommended:
When running report on a chosen date parameter, got "Number is required" on {@Microfilm_ID} in the above.
Changed @Microfilm_ID to:
----------------------------------------------------
ToNumber(replace({DOCMETA.XMICROFILM_ID}," ","") )
----------------------------------------------------
Lastly, sorted by Microfilm ID ascending.
The final bit is adding a "Count" column for each of the records.
BATCH MICROFILM ID RANGE IMAGE COUNT
HSERVS2-00009903 10049 50361 - 10049 50420 60
HSERVS2-00009904 10049 50421 - 10049 50450 30
HSERVS2-00009907 10049 50541 - 10049 50577 37
HSERVS2-00009908 10049 50601 - 10049 50660 60

I'll give this Count column a whirl and if I run into a difficulty, I'll update this post. Your assistance with this is much appreciated.

Q

 

[qu1ncy]

Not successful at retrieving the image count per batch.
Microfilm ID consists of 2 parts, Julian date & series number separated by a space. (e.g. 10049 50361). Lbass assisted with:
-----------------------------------------------------------
@MICROFILM_ID: ToNumber(replace({DOCMETA.XMICROFILM_ID}," ","") )
-----------------------------------------------------------
To retrieve the image count per batch I'm looking for the count of images by subtracting the highest
series number in a microfilm id from the lowest in any given batch (+1). E.g. below: (50420 - 50361)+1
In the following example:

BATCH MICROFILM ID RANGE *COUNT
HSERVS2-00009903 10049 50361 - 10049 50420 60
HSERVS2-00009904 10049 50421 - 10049 50450 30

Steps taken: Created
------------------------------------------------------------
@MAX_SERIESNUM: ToNumber(Right({DOCMETA.XMICROFILM_ID},(Length({DOCMETA.XMICROFILM_ID})-(Instr({DOCMETA.XMICROFILM_ID},chr(32)))))).
------------------------------------------------------------
to retrieve the upper end series number "50420" from batch HSERVS2-00009903 in the above eg.

To retrieve the lower end series number "50361" and enable a subtraction to yield the count, have to (?) introduce
grouping by batch {DOCMETA.XBATCHNAME}.
------------------------------------------------------------
@MIN_MICROFILMID: MINIMUM({DOCMETA.XMICROFILM_ID},{DOCMETA.XBATCHNAME})
@MIN_SERIESNUM: ToNumber(Right({@MIN_MICROFILMID},(Length({@MIN_MICROFILMID})-(Instr({@MIN_MICROFILMID},chr(32))))))
------------------------------------------------------------
Used field varchar {DOCMETA.XMICROFILM_ID} to pull series number (which varies in length) instead of @MICROFILM_ID, because, ahhh, I don't
know how to do it using @MICROFILM_ID.

Lastly, perform the count:
----------------------------------------------------
@IMAGE_COUNT: ({@MAX_SERIESNUM}-{@MIN_SERIESNUM})+1
----------------------------------------------------
Problems: Adding the grouping alone is resulting in multiple entries for a single
batch and doesn't maintain the previous format. Returns inaccurate count.

Suggestions? Thanks.
Q

 

[lbass]

Change the formula to:

whileprintingrecords;
numbervar first;
numbervar last;
stringvar rangex;
if onfirstrecord or
{table.batch} <> previous({table.batch}) then
first := val({@microfilm_ID}) else
if {table.batch} = previous({table.batch}) and
val({@microfilm_ID}) <> val(previous({@microfilm_ID})) + 1 then
first := val({@microfilm_ID})
if
(
onlastrecord or
{table.batch} <> next({table.batch})
)
or
(
{table.batch} = next({table.batch}) and
val({@microfilm_ID}) <> val(next({@microfilm_ID}))-1
) then
last := val({@microfilm_ID});
rangex := left(totext(first,0,""),5) +" "+mid(totext(first,0,""),6)+ " - " + left(totext(last,0,""),5) +" "+mid(totext(last,0,""),6);
numbervar cnt := val(mid(totext(last,0,""),6))-val(mid(totext(first,0,""),6))+1;
rangex & " " & cnt

-LB

 

[qu1ncy]

Hello LB,
Removed the ToNumber() on formula @Microfilm_ID, and the result worked beautifully!
Your help with this is very much appreciated! (and educational)

Thanks,
Q