Export horizontally displayed records into excel

[Awat]

Hi,

I have the records displayed horizontally in one line using a variable: stringvar courses := courses + {course}+ " ";.

So, instead of
ID[tab][tab]Course
001[tab][tab]A
001[tab][tab]B
001[tab][tab]C

This is what I have now:

ID Course
001[tab][tab]A[tab]B[tab]C

However, the report needs to be exportable to excel and A,B,C need to be placed in different columns. So, in excel it should look like

ID[tab][tab]Course 1[tab][tab]Course 2[tab][tab]Course 3
001[tab][tab]A[tab][tab][tab][tab][tab]B[tab][tab][tab][tab][tab]C

Is there a way to make this request possible? The column names Course 1, Course 2, ... are not important because users can manually type them in after exporting to excel. Please help!

 

[hilfy]

What is the maximum number of courses possible for an ID?

There may be other ways of managing this, but here's one option:

1. Create a running total - Count of Course, reset on change of ID. I'll call this {#CourseCount}.

2. Create a formula that looks something like this:

StringVar Course1;
if {#CourseCount} = 1 then Course1 := {Course};
Course1

3. Create another formula for each of the possible courses beyond the first:

StringVar Course2;
if PreviousIsNull({ID}) or {ID} <> previous({ID}) then
Course2 := "";
if {#CourseCount} = 2 then Course2 := {Course};
Course2

Note that the first "If" statement sets the value to an empty string. This way if there is no CourseX for an ID, the value from the previous ID doesn't carry over.

4. Group by {ID} and put your data in the Group Footer section. Because of the running total for the course count, this won't work if you use the group header.

-Dell



A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Awat]

Hi Dell,

Thanks very much for your reply. I decided to make the report export all courses in one column and let the users change texts to columns in excel. Anyway, if I have time, I will experiment on each of your solutions to see how each one works.

Thanks.