Solved

Crystal Reports - Row Data into Separate Columns in Excel

Posted on 2012-03-13
8
423 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
  • 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 34

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
 
LVL 100

Expert Comment

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

mlmcc
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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 34

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now