How to get info from subreport

[SmokeEater]

I have created a report with a subreport that shows part numbers and the vehicles that they fit on. The main report shows the part number and vehicle it can be used on as well as other usefull info. The subreport shows any other vehicles that the part can be used on. My problem is that I want to be able to suppress parts that have multiple vehicles, how do i get this info from the subreport? The report is done using Crystal 9

 

[kskid]

If you have a main report that has the part and vehicle, why would you need a subreport with the same part number with many vehicles? If you are wanting the same vehicle only, why not pass the vehicle as a subreport link as well.

What is your database?

-lw

 

[SmokeEater]

The report is run to find out what parts fit on a certain vehicle. If the report is run for one vehicle then the fact that some parts have multiple applications is missed. The report is run to determrine what parts to dispose of when a vehicle is being disposed of. The reason for the sub report is to show what other vehicles this part is used on so that those parts are not disposed.


The database is Oracle 8

 

[kskid]

Got it. Could you provide a sample of your data and what you are expecting as output?

 

[SmokeEater]

Part Number Vehicle
123 1
123 2
123 3
456 1
789 1

the report has to show part numbers 456 and 789 but not 123 when vehicle 1 is selected

 

[kskid]

I assume that this is the subreport. If you have a group by part, then insert a distinct count of vehicles per part.

Go to section expert, check the suppress box, click on the x-2 button and enter the following formula

{DistinctCount of Vehicles} > 1

In effect, this section will remain suppressed if the count is greater than 1, which is a true statement.

-lw

 

[SmokeEater]

My Report Currently Looks Like

Main Report
Vehicle 1
Part Number 123
Sub Report
Other Applicable Vehicles
2
3
456
789

what I need to have happen is Part Number 123 not to appear because it has caused the sub report to fire. I don't want the Part Number 123 or the sub report to appear. If the report is run only for vehicle 1 the {distinctcount of Vehicle} will always equal 1 on the main report

 

[lbass]

I'm with kskid. Remove the subreport, and allow the multiple vehicles into the report. You can either suppress the unwanted part, or better yet, group on part, and then go to report->selection formula->GROUP and enter:

distinctcount({table.vehicle},{table.part}) = 1

Then using a running total if you want to count the parts that are now displayed in the report (this will omit the parts that no longer display, while an inserted summary would include them).

-LB

 

[SmokeEater]

The multiple vehicles are available to the main report. Where the problem lies is if only one vehicle is selected in the record selection formula then even the DistinctCount funtion only returns 1. The sub report was the only way I was able to pull the other vehicles out. Is there any way to refer to the subreport, in a formula, on the main report?

 

[SmokeEater]

Place a formula in the sub report

//Declare Shared Variable 
Shared NumberVar VehicleCount;

//Assign count of models per part to the shared variable
VehicleCount :=DistinctCount ({Table.Vehicle});

if VehicleCount < 1 then VehicleCount = 0;

In the main report place a formula

//Retrieve value from sub report
Shared NumberVar VehicleCount;

VehicleCount

 

[lbass]

So you're all set? I don't know how to interpret your last post.

-LB

 

[SmokeEater]

Sorry for not being clear. What my last post is showing is how to share information from the sub report with the main report, instead of the usual main report with sub report via linking.

I showed the formulas to answer the question "...Is there any way to refer to the subreport, in a formula, on the main report?"

Thank You for your and kskid's help