on 02-05-2015 4:18 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.