Storing values in an array 2

[cordelia]

I need help with how to store values in an array in Crystal Reports 10. The end result I'm trying to achieve is a column on a report which contains one or more names enclosed in double quotes.

Here's a sample of what I need the report to look like:
ID: TASK: START: FINISH: RESOURCES:
001 Install carpet 06/30/10 07/01/10 "John, Fred"

The line above is from the Details section of the report. I already have the report created but need to add the last column "Resources" to the report.

The values for the "Resources" column will be coming from a database table which stores time card information related to the data in the "Task" column. For each task there may be more than one person with time card entries.

I need to retrieve the names from any time cards for each task and display/format as shown above.

I'm thinking an array would be the best way, but am not sure how to implement the logic to capture this information for each record returned in the Details section.

Any help would be greatly appreciated! Hope this makes sense, if not I can provide further detail.

 

[IanWaterman]

How is the table which stores the time card info going to be related to the existing data.

It will cause duplications.

You could use a subreport in the detail line.

In the subreport a simple stringvar would enable you to build string you require.

@stringinsub

whileprintingrecords;
global stringvar list;

list:= list&{namestringfield}&", "

Don't forgrt to reset var in subreport header may also want to add some logic to stop "," being added to last name.

Ian

 

[cordelia]

The data for each task is stored in one database table while the time card info is stored in another table. The time card table has fields which reference back to the task table.

For example, based on the task number you could run a sql query and find out who did work (has time card entries) for that task.

I need the report to appear as shown in the example above as ultimately I'll be exporting the report to an Excel file, and each record in the Details section of the report will need to be a separate row in Excel.

Thank you for the suggestion of a subreport. I'll try playing around with that and see if I can get what I need.

 

[lbass]

You should be able to simply group on ID and then collect the name in a string per Ian's formula. You would just need a reset formula in the group header:

whileprintingrecords;
global stringvar list;
if not inrepeatedgroupheader then
list := "";

And a display formula in the group footer:

whileprintingrecords;
global stringvar list;
if len(list) > 1 then
left(list,len(list)-1)

Then move the detail fields to the group footer. You would then suppress the group header and detail section.

This should export fine.

-LB

 

[cordelia]

Thank you! This sounds like what I'm looking for. I have to step away from this report to complete some other assignments but will try your suggestions and let you know how it works out tomorrow.

Thank you again!!

 

[cordelia]

You guys are the best!!!

I followed your suggestion lbass and was able to get the results I needed...yay!

The final steps will be to remove the comma that appears at the end of the string list (as Ian suggested) and enclose the string list in quotes.

Thank you both so much for your help!

 

[cordelia]

Just an FYI regarding the last two items I was trying to accomplish...

To remove the comma from the end of the string, I modified the last line in the formula to display the string:
left(list,len(list)-2)

For the double quotes, I created a text box for the Resources column. In the text box I added two double quotes and inserted the formula to display the string between the quotes.

 

[lbass]

I assumed you used Ian's formula which would have meant my suggestion with the -1 would have removed the comma. You could modify the display formula and eliminate the need for a text box by doing the following:

whileprintingrecords;
global stringvar list;
if len(list) > 2 then
'"'+left(list,len(list)-2)+'"'

...although I think it's a little odd looking to add the quotes.

-LB

 

[cordelia]

I'm guessing there was a space present after the last comma in the string list since subtracting one from the length of the list left the comma still present, but subtracting two removed the comma.

I realize it seems odd to be displaying the string list with quotes. The report itself is just a means to get data from a Win32 application (which uses Crystal) into Excel in a specific format to ultimately import into MS Project.

...and yes, I know that process is not the ideal way to use MS Project. ;-) The request for this is coming from a higher level so I'm just doing what's been tasked of me.