Numbering items

[camjam26]

Hello all,

I'm using CR XI connecting via an ODBC(RDO) to an Access database. Currently, to number items in my report I have set up a Running Total Field with a summary type of count. The field to summarize is Observations.ObservationID (type of number). It evaluates based on the formula {Observations.TemplateItem} (type of number) = 0 and never resets. The Observations.ObservationID is unique to each item record. The way the report currently displays is pretty simple such that it shows as follows (an example output):

10. Certificates of analysis for food products were maintained on file. (the sentence is taken from a memo field: Observations.Observation)

So nothing too fancy as you can see. However, each item is assigned a score as well (Observations.Score, which is a type of String). What I'm looking to do is create a recap/summary that can be inserted into the report. This would display the item numbers of those observations that contain a certain score. An example output:

Improvement Needed Items: #3, #5, #6
Serious Items: #9, #10
Unsatisfactory Items: #14

Here are the scores being used:

0)Comment
1)Minor Discrepancy
2)Improvement Needed
3)Serious
4)Unsatisfactory

Is there a way that I could enter the item numbers from the body of the report into this recap or possibly a better way to number the items to accomplish the recap goal (and if so, how to go about doing it)?

I appreciate any help possible. Please let me know if further information is needed.

-Chris

 

[lbass]

I think a crosstab would work. First use a record selection formula of:

{Observations.TemplateItem} = 0

Then create a formula:

whilereadingrecords;
stringvar ID;
numbervar counter;

if instr(ID,totext({Observations.ObservationID},"000")) = 0
//add/subtract zeros to equal the maximum length of the ID
then
(
ID := ID + totext({Observations.ObservationID},"000")+", ";
counter := counter + 1);
counter

Insert a crosstab and add this formula as your first row field. Add your description field as the second row field. Use {Observations.Score} as your column field, and then use distinctcount of {Observations.ObservationsID} as your summary field.

-LB

 

[camjam26]

Thanks for the response. Would it be possible to do this without the cross-tab? In addition, there are other scores associated with this that I don't need to be processed. They would be 0)Comment and 1)Minor Discrepency. So I'm assuming another formula may be needed.

 

[lbass]

Let's assume that your numbering running total is called {#all}. You could create a formula like the following to be placed in the detail section:

whileprintingrecords;
stringvar x;
stringvar y;
stringvar z;

if {Observations.Score} = "Improvement Needed" then
x := x + totext({#all},0,"") + ", ";
if {Observations.Score} = "Serious" then
y := y + totext({#all},0,"") + ", ";
if {Observations.Score} = "Unsatisfactory" then
z := z + totext({#all},0,"") + ", ";

Then in the report footer, use a formula like:
//{@display}:
whileprintingrecords;
stringvar x;
stringvar y;
stringvar z;

"Improvement Needed: "+left(x,len(x)-2) + chr(13)+
"Serious: "+left(y,len-2) + chr(13)+
"Unsatisfactory: "+left(z,len(z)-2)

Then format the display formula to "can grow".

If you were doing this at a group level, you would need to have a reset formula in the group header that set each string to "".

-LB

 

[camjam26]

Thanks again.

When trying to preview I am currently getting the error message: 'String length is less than 0 or not an integer'.

I have the formulas as you stated. Something else I may need to do or other information you need?

Thanks

-Chris

 

[lbass]

You must have some instances with no items, so try changing the display formula to:

//{@display}:
whileprintingrecords;
stringvar x;
stringvar y;
stringvar z;

"Improvement Needed: "+ (if len(x) > 2 then
left(x,len(x)-2) else "None") + chr(13)+
"Serious: "+(if len > 2 then
left(y,len-2) else "None") + chr(13)+
"Unsatisfactory: "+ (if len(z) > 2 then
left(z,len(z)-2) else "None")

-LB

 

[camjam26]

Thanks once more time. I'll best testing this out and see how it works for me. I'll be sure to let you know how it goes.

Take care,
Chris

 

[camjam26]

Just wanted to let you know that it looks as if this will work well for me. Thanks again for all the help.

 

[camjam26]

Things are still working well, but I was looking to see what may be the best way to bold "Improvement Needed: ", "Serious: ", and "Unsatisfactory: " in the @display formula (just to signify headings basically). Thanks again for the help.

 

[lbass]

Try:

//{@display}:
whileprintingrecords;
stringvar x;
stringvar y;
stringvar z;

"<b>Improvement Needed: </b>"+ (if len(x) > 2 then
left(x,len(x)-2) else "None") + chr(13)+
"<b>Serious: </b>"+(if len > 2 then
left(y,len-2) else "None") + chr(13)+
"<b>Unsatisfactory: </b>"+ (if len(z) > 2 then
left(z,len(z)-2) else "None")

Then right click on the formula->format field->paragraph->text interpretation->select HTML Text.

-LB

 

[camjam26]

Of course...something rather simple. Thanks again for putting up with me! That works fine.