cancel
Showing results for 
Search instead for 
Did you mean: 

Adding Columns

Former Member
0 Kudos

I have 5 fields. Each person will have 5 SQs each.

[ID] [Last Name] [First Name] [SQ Number] [SQ Name]

Each person will have 5 SQs each. The [SQ] field is a number from 1-5 and the [SQ Name] is a text field. I would like the report to display like this


[ID] [Last Name] [First Name] [SQ 1] [SQ Name] [SQ 2] [SQ Name] [SQ 3] [SQ Name] [SQ 4] [SQ Name] [SQ 5] [SQ Name]


Currently it displays as:


[ID] [Last Name] [First Name] [SQ 1] [SQ Name]

[ID] [Last Name] [First Name] [SQ 2] [SQ Name]

[ID] [Last Name] [First Name] [SQ 3] [SQ Name]

[ID] [Last Name] [First Name] [SQ 4] [SQ Name]

[ID] [Last Name] [First Name] [SQ 5] [SQ Name]


Please advise,


-Sandra

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

You're going to group by person - either ID or Name, depending on what order you want to display the records in.  If you're grouping by name and you could have two people with the same name, you'll want to create a formula that concatenates the ID onto the end of the name and then group on that formula. Assuming that the ID is a number, that formula might look something like this:

{MyTable.LastName} + ", " + {MyTable.FirstName} + "-" +ToText({MyTable.ID}, 0, "")

To get the data that you're looking for, you'll need to use variables and do something like this following:

1.  Create a formula that will clear the variables.

{@ClearVars}

WhilePrintingRecords;

StringVar SQ1Name := "";

StringVar SQ2Name := "";

StringVar SQ3Name := "";

StringVar SQ4Name := "";

StringVar SQ5Name := "";

Put this formula in the group header section and suppress the section (the formula will still run.)  This is assuming that the numbers are indeed just 1 through 5.

2.  Create a formula that will set the variables.

{@SetVars}

WhilePrintingRecords;

if {MyTable.SQ} = 1 then SQ1Name := {MyTable.SQName} else

if {MyTable.SQ} = 2 then SQ2Name := {MyTable.SQName} else

if {MyTable.SQ} = 3 then SQ3Name := {MyTable.SQName} else

if {MyTable.SQ} = 4 then SQ4Name := {MyTable.SQName} else

if {MyTable.SQ} = 5 then SQ5Name := {MyTable.SQName};

""

Put this formula in the details section and suppress the section.

3.  Create a formula for each variable that will display the variable.  The formulas will look like this:

{@SQ1}

WhilePrintingRecords;

StringVar SQ1Name

{@SQ2}

WhilePrintingRecords;

StringVar SQ2Name

etc.

Put the person data in the group footer in the format that you're looking for.  The SQ Number fields can just be Text boxes with the numbers 1 through 5 in them and you'll use the final set of formulas to get the name.

-Dell

Former Member
0 Kudos

Hello,

I get an error when entering #2 of your instructions. Also, Where do the {@SQ1} formulas go in the report? I also forgot to mention that each person will have 5 different SQs out of 32 potential SQs. I don't know if that makes a difference.

abhilash_kumar
Active Contributor
0 Kudos

Hi Sandra,

Formula #2 should be:

if {MyTable.SQ} = 1 then StringVar SQ1Name := {MyTable.SQName} else

if {MyTable.SQ} = 2 then StringVar SQ2Name := {MyTable.SQName} else

if {MyTable.SQ} = 3 then StringVar SQ3Name := {MyTable.SQName} else

if {MyTable.SQ} = 4 then StringVar SQ4Name := {MyTable.SQName} else

if {MyTable.SQ} = 5 then StringVar SQ5Name := {MyTable.SQName};

"";

The @SQ1 formula would go in the Group Footer and that is where you would display the ID, Name and all the SQs. You  would need to place the database fields and formula fields beside one another.

-Abhilash

Former Member
0 Kudos

That worked! Thank you so much! Dell and Abhilash! You're both lifesavers!

DellSC
Active Contributor
0 Kudos

Ok.  I missed something in the formula.  Also, since the SQ numbers may vary, you'll need to do some tweaks.  Here's what it may look like (assuming that the SQ field is a number):

{@ClearVars}

WhilePrintingRecords;

StringVar SQ1Name := "";

StringVar SQ2Name := "";

StringVar SQ3Name := "";

StringVar SQ4Name := "";

StringVar SQ5Name := "";

NumberVar SQ1 := -1;

NumberVar SQ2 := -1;

NumberVar SQ3 := -1;

NumberVar SQ4 := -1;

NumberVar SQ5 := -1;

NumberVar SQCount :=0;

{@SetVars}

WhilePrintingRecords;

StringVar SQ1Name;

StringVar SQ2Name;

StringVar SQ3Name;

StringVar SQ4Name;

StringVar SQ5Name;

NumberVar SQ1;

NumberVar SQ2;

NumberVar SQ3;

NumberVar SQ4;

NumberVar SQ5;

NumberVar SQCount;

If SQCoun= 0 then

(

  SQ1 := {MyTable.SQ};

  SQ1Name := {MyTable.SQName};

)

else if SQCount  = 1 then

(

  SQ2 := {MyTable.SQ};

  SQ2Name := {MyTable.SQName};

)

else if SQCount  = 2 then

(

  SQ3 := {MyTable.SQ};

  SQ3Name := {MyTable.SQName};

)

else if SQCount  = 3 then

(

  SQ4 := {MyTable.SQ};

  SQ4Name := {MyTable.SQName};

)

else if SQCount  = 4 then

(

  SQ5 := {MyTable.SQ};

  SQ5Name := {MyTable.SQName};

) ;

""

The final set of formulas for the name will remain the same, but you'll also add a set like this:

{@SQ1}

WhilePrintingRecords;

NumberVar SQ1

-Dell


Former Member
0 Kudos

No I have an issue with the record count. Crystal Reports tells me there are 2255 records but when I export to excel it only shows me only 451 records.

DellSC
Active Contributor
0 Kudos

That's because you're reading 5 records for each person with the different SQ numbers but only showing/exporting one record per person.  451 * 5 = 2255, so your counts are correct.

-Dell

Answers (0)