• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Insert the same field twice (continued)

This question follows this one: http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21837369.html

I have a date field in my report and I want it to look like this:

John   12   1/1/01     13   1/3/01
Bill      12   2/1/03
Tom                        13   3/1/04

Is this possible?
Frodoman maybe you can help again?:)

Thanks
0
cadkins
Asked:
cadkins
  • 6
  • 4
1 Solution
 
mlmccCommented:
You could try this

Add a group to the report on the NAME field
Add a sort on the number field with the 12s and 13s
Add formulas to the report

In the report header
Name - DeclVars
Formula
Global StringVar G_Footer := '';

In the group header
Name - ResetVars
Formula
Global StringVar G_Footer;
G_Footer := '';

In the group
Name - BuildFooter
Formula
Global StringVar G_Footer;
if {NumberField} = 12 then
   G_Footer := "12     " & {DateField};

if {NumberField} = 13 then
   if G_Footer = '' then
      G_Footer = "               13    " & {DateField}
   else
      G_Footer = G_Footer & "      13    " & {DateField};
''

In the group footer
Name - DispFooter
Formula
Global StringVar G_Footer;
G_Footer

Group footer will have
{NameFIeld}         {@DispFooter}

mlmcc
0
 
mlmccCommented:
Add WhilePrintingRecords; as the first line of each function

In the report header
Name - DeclVars
Formula
WhilePrintingRecords;
Global StringVar G_Footer := '';

mlmcc
0
 
cadkinsAuthor Commented:
I did exactly what you told me but it only shows the 12s and their dates.
One other thing. Even if this works my understanding is that everything will be in one column (e.g. 12   1/1/01     13   1/3/01). Is it possible to have 4 columns? I want to export this report to excel and it will be more helpful if that data is in 4 columns instead of 1.

Thanks!
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mlmccCommented:
I believe you are correct about the single column

Try this
Change this formula
Name - BuildFooter
Formula
Global StringVar G_Footer;
if {NumberField} = 12 then
   G_Footer := "12 " & ToText({DateField},"dd/mm/yyyy";

if {NumberField} = 13 then
   if G_Footer = '' then
      G_Footer = "              13 " & ToText({DateField},"dd/mm/yyyy")  '13 spaces before the 13
   else
      G_Footer = G_Footer & " 13 " & ToText({DateField},"dd/mm/yyyy");
''

Add 4 formulas to the group footer

Name - Col1
Formula
WhilePrintingRecords;
Global StringVar G_Footer;
Left(G_Footer,2)

Name - Col2
Formula
WhilePrintingRecords;
Global StringVar G_Footer;
Mid(G_Footer,4,10)

Name - Col3
Formula
WhilePrintingRecords;
Global StringVar G_Footer;
Mid(G_Footer,14,2)

Name - Col4
Formula
WhilePrintingRecords;
Global StringVar G_Footer;
Right(G_Footer,10)

Put those formulas in the footer.

mlmcc
0
 
cadkinsAuthor Commented:
I get 4 columns now but it still shows only the 12s and their dates(the first 2 columns...3rd and 4th column are always empty).
And something else...i used mm/dd/yyyy and I always get 00 for mm.(eg. 00/12/2004)
Thanks
0
 
mlmccCommented:
Try MM or nn.  mm may be for minutes

Did you get any 13s in the string or was it always 12 date nothing or just nothing?

Are the 12 and 13 in the same field?

mlmcc
0
 
mlmccCommented:
Can there be multiple 12s or 13s for a given name?

What SQL are you using?

mlmcc
0
 
cadkinsAuthor Commented:
MM works fine.
I get 12 & date or nothing (if there is no 12).
12 and 13 are in the same field.
There can be only one 12 and one 13 for a given name.
I am using SQL Server 2000.

Thanks
0
 
cadkinsAuthor Commented:
I figured out how to do it in a completely different way! I still gave you the points because you tried to help me:)

Thanks!
0
 
mlmccCommented:
What worked?

Glad i could help

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now