Displaying 1 Row from a multi-instance table 2

[yaz1np]

Help, I am creating a crystal report that is using two tables, one of the tables “Demo” is a multi-instance table (Can contain more than one record per ID) I need to display the last or only record per ID number from the Demo table. I am using crystal 7.0 but I also have crystal 9.0 if this can not be done in 7

Thanks

 

[kskid]

1. Group on the ID.
2. Insert summary on {table.date} choosing maximum
3. move the {table.date} from the detail section to the group level and suppress the detail

That should give you the latest record

 

[synapsevampire]

Try grouping by the ID and placing your fields in the group footer, this will display the last row for that group.

If you need summaries, it'll be a little trickier, but still simple.

-k

 

[synapsevampire]

Since you have a date field (good assumption on Wicthita's part, I tend to get annoyed with posts that post nothing technical nor environmental hence requiring guesswork), you can make this a tad more elegant by going into the Report->Selection Formulas->Group and placing:

{table.date} = maximum({table.date},{table.id})

-k

 

[yaz1np]

Thank you for your help. Setting the maximum on the date field helps to show the last field for a given ID number and I used a subreport to link the additional agencies to the main agencies to get a total for the whole comapny. But of course they now want to see it group by the volume field which is in the demo table.

I know how to group on a the volume field from the demo table to display those agencies that have 4999 or less, between 5000 to 19999 and 20000 or more. But I do not know how to get the get the the last record per ComapnyID (main report) and the subtotal that I am passing from the subreport to be the grouping fiqure. Help!!

 

[synapsevampire]

You might want to post example data and expected output for this one.

"But I do not know how to get the get the the last record per ComapnyID (main report) and the subtotal that I am passing from the subreport to be the grouping fiqure"

This sounds like you want to group based on something in a subreport, which cannot be done.

Not sure what a grouping figure is, does that mean group field?

-k

 

[yaz1np]

I am using two tables Name and Demographics.

From the name table I am displaying company name and company Id
From Demographics I am using Transport Volume.

I have two types of Members that I am pulling for to get the total transport volume for the main agency.

ACC – Agency
ASA – Agency Service Area

ASA is linked to the Agency by a field in the name table called CO_ID

I need to take the Agency transport volume last record in the Demographics Table and add it to all of the ASA transport volume recodes in the Demographics Table. I will use this number for the grouping and display the data as below:

Raw data:
Name Table Demographics Table
ID Company Name Transport Volume CO_ID
123 Rural/Metro Medical 2986
123 Rural/Metro Medical 3862
485 Cayuga County 987 123
278 Town of Sullivan 1630 123

Example date of how the output should look like:

5,000 to 19,999 Transports
ID#123 Rural/Metro Medical Services Total transport volume 6479

 

[lbass]

Here is one solution, if you can use SQL Expressions (might not be available in 7.0). First, create a regular formula {@group}:

if isnull({Demographics.Co_ID}) then {Name.ID} else {Demographics.Co_ID}

Insert a group on {@group}. This will cluster the parent and child company IDs together. Next insert a group on {Name.ID}. Then go to the SQL Expressions editor and enter:

(select max(AKA.`Date`) from Name AKA where AKA.`ID` = Name.`ID`)

Substitute your exact field names for "Date" and "ID". If your table is named "Name" then that's all you should have to change. Leave "AKA" as it is since it represents an alias name. The punctuation you need to use might vary depending on your datasource (I think).

Next go to report->edit selection formula->record and enter:

if {Name.ID} = {@group} then
{Name.Date} = {%max} else true

This will return only the maximum record for the {Name.ID}. Now go into your report and right click on {Demographics.TransportVolume} and insert a summary (sum) on it at the {@group} level.

Next go to report->topN/group sort->{@group} tab and choose "sum of {@group}" and choose ascending. This will now order your groups by summary amount. Drag the groupname into the group footer for {@group} and then suppress the details and GH2 and GF2.

While you can't create groups on these sums, you can give the appearance of groups by creating a formula like the following:

whileprintingrecords;
stringvar y;
stringvar z := z + y;
numbervar counter := counter + 1;

y := if sum({Demographics.TransportVolume},{@group}) in 0 to 10000 then "0 to 10,000" else
if sum({Demographics.TransportVolume},{@group}) in 10001 to 20000 then "10,001 to 20,000" else
if sum({Demographics.TransportVolume},{@group}) in 20001 to 30000 then "20,001 to 30,000" else
"30,001 or greater";
if instr(z,y) = 0 then counter := 1 else counter := 0;
y

Since your other fields are in the Group #1 footer, place this formula in the Group #1 header. Then go to the section expert->Group Header #1->suppress->x+2 and enter:

whileprintingrecords;
numbervar counter = 0;

This will eliminate the header for groups with the same heading, and will give the appearance of an outer group.

-LB