Solved

Crystal Reports - Row Data into Separate Columns in Excel

Posted on 2012-03-13
8
431 Views
Last Modified: 2012-03-15
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;
0
Comment
Question by:lcallah93
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 37717448
What are you grouping on?

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

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 37718781
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
 

Author Comment

by:lcallah93
ID: 37719629
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 100

Expert Comment

by:mlmcc
ID: 37719977
WHy are all the 2nd and later notification fields suppressed?

mlmcc
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 37720056
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
 

Author Closing Comment

by:lcallah93
ID: 37720227
As always - a big THANK YOU!!  Darn that copy and paste - I love it, yet I hate it...
0
 
LVL 35

Expert Comment

by:James0628
ID: 37723526
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 37724789
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question