Associate a record name with a running total in Report Footer

[Former Member]

Please Help!

I have a report that returns records for the most recent order recieved by vendor.

The page header displays Vendor, Contact, Order #, and date

I did a group by Vendor

I used a running total on the order date

with a summary of minimum

that evaluates on change of group

Reset never

This returns the last order date for the vendor with the oldest order

How can I get the vendors name that is associated with the oldest order date to display in the Rport Footer.

[Former Member · Accepted solution]

Well,

1.Go to Insert menu bar and select Subreport.

2.Select second radio button i.e. Create Subreport

3.On the Subreport Expert dialog box select Crystal SQL Query

4.Enter the query I have provided.

[Former Member · Accepted solution]

Thanks again for all of your help. I believe my issue is that the trial version of CR8 does not let you edit or insert sql statements. Ill try your suggestion once my company purchases the full blown version.

[Former Member]

Well,

1.Go to Insert menu bar and select Subreport.

2.Select second radio button i.e. Create Subreport

3.On the Subreport Expert dialog box select Crystal SQL Query

4.Enter the query I have provided.

[Former Member]

Please follow the following steps: I tested it and it works.You dont need to use running total.

1. Add the Vendor,Contact,Order #, Order Date to Details Section (u can delete them if not needed from details section)

2. Create a Group on Vendor.

3. Add a Summary i.e. Minimum(Order Date)

4. Copy this summary to Group Header if you want or just let it be in the Group Footer Section

5. Add a SubReport in the Report Footer Section.

6.The Subreport Should be based on the Command like:

SELECT `Orders`.`Customer ID`, `Orders`.`Order Date`

FROM `Orders` `Orders`

WHERE `Orders`.`Order Date`=

(

SELECT MIN(`Orders`.`Order Date`)

FROM `Orders` `Orders`)

7. Refresh the Report and you will see that Report Footer section displays all the vendors with minimum order date along with their name.

One more thing to keep in mind is that more than one vendor might have the same minimum order date.

[Former Member]

Please follow the following steps: I tested it and it works.You dont need to use running total.

1. Add the Vendor,Contact,Order #, Order Date to Details Section (u can delete them if not needed from details section)

2. Create a Group on Vendor.

3. Add a Summary i.e. Minimum(Order Date)

4. Copy this summary to Group Header if you want or just let it be in the Group Footer Section

5. Add a SubReport in the Report Footer Section.

6.The Subreport Should be based on the Command like:

SELECT `Orders`.`Customer ID`, `Orders`.`Order Date`

FROM `Orders` `Orders`

WHERE `Orders`.`Order Date`=

(

SELECT MIN(`Orders`.`Order Date`)

FROM `Orders` `Orders`)

7. Refresh the Report and you will see that Report Footer section displays all the vendors with minimum order date along with their name.

One more thing to keep in mind is that more than one vendor might have the same minimum order date.

[Former Member]

Thanks for the help.

I don't understand the steps associated with inserting a sub report. Can you provide more details.

also,

I am trying to set up a rotation of the vendor I pick for the next order. Each new order should go to the vendor that has the oldest order date. It is strictly a rotation that makes sure that no vendor recieve preferential treatment. Each order is for the same comodity, an appraisal.

My report currently shows the date for the order with the oldest date. I just want to show the vendors name associated with that particular order.

[Former Member]

I have CRXI, (First off, copy the SQL statement provided)

open your report, and (Optional) insert another section where you want the subreport to run.

Click insert, about half way down you will see sub-report, click that. Use the wizard (if you want) and choose any

name you wish to call this report. When you click report wizard, you should be able to choose a data source, and select

add command, you will be able to plug in the SQL you copied earlier. Sub-Reports can be very handy, but they can at times slow down the processing ( All depends on what you are doing, and when)

[Former Member]

Thanks again for your help

I have a trial version of CR8

Doesn't seem to work the way you described. I don't see an add button. Not sure of where to cut and paste your quey