Crystal Reports - Row Data into Separate Columns in Excel

Hello experts -

I am trying to take convert row data into columns and am running into problems.  I used some code awhile back and it works in the report it is currently in, but when I try to copy and paste into a new report, modify it, and then run it, I only get the results for all variables for the 1st set of results (name1, id1, sortname1).

Here is what I am trying to do:

Deceased     Contact     ContactID

Doe, John     Doe, Jane     0ZU5221
Doe, John     Snow, Kelly  0AA5897

I would like it to see it as follows:

Deceased     Contact1     Contact1ID     Contact2     Contact2ID

Doe, John     Doe, Jane     0ZU5221     Snow, Kelly     0AA5897

The code I have is below:

Group Header:

whileprintingrecords;
stringvar id1 := "";
stringvar id2 := "";
stringvar id3 := "";
stringvar id4 := "";
stringvar id5 := "";
stringvar id6 := "";
stringvar id7 := "";
stringvar id8 := "";
stringvar id9 := "";
stringvar id10 := "";
stringvar name1 := "";
stringvar name2 := "";
stringvar name3 := "";
stringvar name4 := "";
stringvar name5 := "";
stringvar name6 := "";
stringvar name7 := "";
stringvar name8 := "";
stringvar name9 := "";
stringvar name10 := "";
stringvar sortname1 := "";
stringvar sortname2 := "";
stringvar sortname3 := "";
stringvar sortname4 := "";
stringvar sortname5 := "";
stringvar sortname6 := "";
stringvar sortname7 := "";
stringvar sortname8 := "";
stringvar sortname9 := "";
stringvar sortname10 := "";

Group Details:

whileprintingrecords;
stringvar id1 := "";
stringvar id2 := "";
stringvar id3 := "";
stringvar id4 := "";
stringvar id5 := "";
stringvar id6 := "";
stringvar id7 := "";
stringvar id8 := "";
stringvar id9 := "";
stringvar id10 := "";
stringvar name1 := "";
stringvar name2 := "";
stringvar name3 := "";
stringvar name4 := "";
stringvar name5 := "";
stringvar name6 := "";
stringvar name7 := "";
stringvar name8 := "";
stringvar name9 := "";
stringvar name10 := "";
stringvar sortname1 := "";
stringvar sortname2 := "";
stringvar sortname3 := "";
stringvar sortname4 := "";
stringvar sortname5 := "";
stringvar sortname6 := "";
stringvar sortname7 := "";
stringvar sortname8 := "";
stringvar sortname9 := "";
stringvar sortname10 := "";


If {Command.HMCount} <> 0 then
    if id1 = '' then (id1 := {Command.HMNotificationID}; name1 := {Command.Name}; sortname1 := {Command.SortName}) else
    if id2 = '' then (id2 := {Command.HMNotificationID}; name2 := {Command.Name}; sortname2 := {Command.SortName}) else
    if id3 = '' and  {Command.HMNotificationID} <> id1 and {Command.HMNotificationID} <> id2 then (id3:={Command.HMNotificationID}; name3 := {Command.Name}; sortname3 := {Command.SortName}) else
    if id4 = '' and  {Command.HMNotificationID} <> id1 and {Command.HMNotificationID} <> id2 and {Command.HMNotificationID} <> id3 then (id4:={Command.HMNotificationID}; name4 := {Command.Name}; sortname4 := {Command.SortName}) else
    if id5 = '' and  {Command.HMNotificationID} <> id1 and {Command.HMNotificationID} <> id2 and {Command.HMNotificationID} <> id3 and {Command.HMNotificationID} <> id4 then (id5:={Command.HMNotificationID}; name5 := {Command.Name}; sortname5 := {Command.SortName}) else
    if id6 = '' and  {Command.HMNotificationID} <> id1 and {Command.HMNotificationID} <> id2 and {Command.HMNotificationID} <> id3 and {Command.HMNotificationID} <> id4 and {Command.HMNotificationID} <> id5 then (id6:={Command.HMNotificationID}; name6 := {Command.Name}; sortname6 := {Command.SortName}) else      
    if id7 = '' and  {Command.HMNotificationID} <> id1 and {Command.HMNotificationID} <> id2 and {Command.HMNotificationID} <> id3 and {Command.HMNotificationID} <> id4 and {Command.HMNotificationID} <> id5 and {Command.HMNotificationID} <> id6 then (id7:={Command.HMNotificationID}; name7 := {Command.Name}; sortname7 := {Command.SortName}) else
    if id8 = '' and  {Command.HMNotificationID} <> id1 and {Command.HMNotificationID} <> id2 and {Command.HMNotificationID} <> id3 and {Command.HMNotificationID} <> id4 and {Command.HMNotificationID} <> id5 and {Command.HMNotificationID} <> id6 and {Command.HMNotificationID} <> id7 then (id8:={Command.HMNotificationID}; name8 := {Command.Name}; sortname8 := {Command.SortName}) else
    if id9 = '' and  {Command.HMNotificationID} <> id1 and {Command.HMNotificationID} <> id2 and {Command.HMNotificationID} <> id3 and {Command.HMNotificationID} <> id4 and {Command.HMNotificationID} <> id5 and {Command.HMNotificationID} <> id6 and {Command.HMNotificationID} <> id7 and {Command.HMNotificationID} <> id8 then (id9:={Command.HMNotificationID}; name9 := {Command.Name}; sortname9 := {Command.SortName}) else
    if id10 = '' and  {Command.HMNotificationID} <> id1 and {Command.HMNotificationID} <> id2 and {Command.HMNotificationID} <> id3 and {Command.HMNotificationID} <> id4 and {Command.HMNotificationID} <> id5 and {Command.HMNotificationID} <> id6 and {Command.HMNotificationID} <> id7 and {Command.HMNotificationID} <> id8 and {Command.HMNotificationID} <> id9 then (id10:={Command.HMNotificationID}; name10 := {Command.Name}; sortname10 := {Command.SortName})

Example of one of the Group Footers:

whileprintingrecords;

stringvar name1;

name1;
lcallah93Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
What are you grouping on?

I assume the three fields always have data or the record doesn't exist?

mlmcc
0
James0628Commented:
It may be hard to figure out without seeing all (or at least more) of your formulas and where they're placed.  Can you post the report (with data, if possible)?

 A few thoughts:

 I would put a copy of the formula that's in the group header in the report header too (so that it's in both places).  It seems that global variables sometimes need to be declared in the report header.  It may depend on exactly how you use them, and if you're getting values from some of the variables, then I don't think that's your problem, but no harm in putting that formula in the report header too.


 It seems like your second IF block should check for HMNotificationID <> id1, in the same way that the third block, for example, checks for HMNotificationID <> id1 and HMNotificationID <> id2.  Without that check, it seems like you could end up with the same values in id1 and id2.


 This might all be "simpler" (much shorter and easier to read) with arrays.


 When checking to see if this is a new HMNotificationID (HMNotificationID <> id1 and HMNotificationID <> id2 and ...), you could make that much shorter by using IN.  For example, for id10, you could replace:

    if id10 = '' and  {Command.HMNotificationID} <> id1 and {Command.HMNotificationID} <> id2 and {Command.HMNotificationID} <> id3 and {Command.HMNotificationID} <> id4 and {Command.HMNotificationID} <> id5 and {Command.HMNotificationID} <> id6 and {Command.HMNotificationID} <> id7 and {Command.HMNotificationID} <> id8 and {Command.HMNotificationID} <> id9 then

   with

  if id10 = '' and
   not ({Command.HMNotificationID} IN [ id1, id2, id3, id4, id5, id6, id7, id8, id9 ]) then


 James
0
lcallah93Author Commented:
I changed the code to reflect the shorter statements - I am always in favor of efficiency!  I also put a copy of the reset formula in the report header.  The report with the saved data is attached.

Thank you for helping me out!

Lisa
13839-Deceased-HMNotification-FA.rpt
0
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

mlmccCommented:
WHy are all the 2nd and later notification fields suppressed?

mlmcc
0
mlmccCommented:
There are 2 issues

1.  The details formula is resetting the variables to "" thus id1 is always "" when you test.
Delete the assignment and just declare them
Group Details:

whileprintingrecords;
stringvar id1 := "";
stringvar id2 := "";
etc

Open in new window


Should be
Group Details:

whileprintingrecords;
stringvar id1;
stringvar id2;
etc

Open in new window


2.  Move the display formulas to the group footer

See attached report

mlmcc
13839-Deceased-HMNotification-FA.rpt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcallah93Author Commented:
As always - a big THANK YOU!!  Darn that copy and paste - I love it, yet I hate it...
0
James0628Commented:
You know, I'd noticed in that first post that those variables were being cleared, but somehow it seemed to make sense at the time.  Or at least, it didn't seem wrong.  :-)  Don't know what I was thinking.

 James
0
mlmccCommented:
I saw it also and didn't think about it.  I guess I thought that was the initial formula or was in the group header.

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.