row values as columns

[princessinthetower]

I want to produce an export to Excel that looks like this:

Accounting Paul, Sam, Pete
Marketing Sally, John, Mary, Karen

instead of this:

Accounting Paul
Accounting Sam
Accounting Pete

Marketing Sally
Marketing John
Marketing Mary
Marketing Karen

To get values all in one row, I know how to use 3 formulas to gather multiple values in different rows, concatenate them and format the final results in a group footer. However, in this report a linked address table has multiple addresses for each person's ID, which inevitably duplicates the details. Is there any way around this?

 

[Thadeus]

I'm sorry, I don't get the 'However' portion and your example data doesn't clarify that part.... If you elaborate I'm sure I can help you out.

~Thadeus

 

[princessinthetower]

Reports with more than one linked table with one-to-many relationships result in duplication of rows in the details section. Any attempt to concatenate the values of one field into one variable from multiple rows belonging to the same person will result in duplication like the following:

Accounting Paul, Paul, Sam, Pete, Pete
Marketing Sally, Sally, John, Mary, Mary, Karen

The question: how do you move certain rows to columns so that you have one row?

 

[lbass]

Change your accumulation formula in the details section to:

whileprintingrecords;
stringvar list;

if instr(list,{table.name}) = 0 then
list := list + {table.name}+ ", " else
list := list;

Then for your display formula in the group footer use:

whileprintingrecords;
stringvar list;
left(list,len(list)-2)

Your reset formula would go in the group (department) header:

whileprintingrecords;
stringvar list := "";

-LB

 

[Ngolem]

you don't really describe your report grouping though 2 of them are obviously Accounting and Marketing.

It sounds to me that you are perhaps looking at accumulating names in these departments for something like various cities for example for Group 1 Accounting as Group 2 and Marketing as Group 3

So your results would be displayed in the Group 3 footer (or report footer if there is no Group 3)

In the Group 1 header (or report header if only 2 groups)

place this formula

@Init

WhilePrintingRecords;

//If not in repeated Group header is required if in the Group 1 header

If Not inRepeatedGroupHeader then
(
StringVar Accounting := "";
StringVar Marketting := "";
)
else
"";


in the Group accounting header place this formula

@AccountingNames

WhilePrintingRecords;
StringVar Accounting ;

Accounting := Accounting + {Table.accountingname} + ", ";

in the Group Marketing header place this formula

@MarketingNames

WhilePrintingRecords;
StringVar Marketing ;

Marketing := Marketing + {Table.MarketingName} + ", ";

Then in the Group 1 footer (or report footer if only 2 groups) create 2 subsections

In subsection A of the footer

@DisplayAccountingnames

StringVar Accounting ;

"Accounting: " + left(Accounting, length(Accounting)-2);

In subsection b of the footer

@DisplayMarketingnames

StringVar Marketing;

"Marketing: " + left(Marketing, length(Marketing)-2);

Make each display formula the width of the page and enable the "Can grow" of each field....Of course the footers, details and headers for Accounting and Marketing would be suppressed unless your report is displaying info there.

You don't say the number or size of the names involved so be aware depending on the CR version you may be under a 254 char restriction which would require modification of these formulas....but this should give you an idea on what to do.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[princessinthetower]

Thanks for the reply. I already have this information but there is an additional complication with another linked table that results in duplication in the detail section and therefore duplication of the elements in the concatenation. I have solved the problem with an array. Thanks again.