Get distinct count of items based on another field

[infotech2]

I have a report that shows the cost of service manufacturers used, equipment type serviced, and the service request id. I would like to get a count of manufacturers in the report.

The ServiceTable is joined to the ServiceCostTable and they are setup by a one to many relationship therefore there is duplication in my report so I can't use the normal count method. When I try distinct count on the manf. field, it returns 1 for each manufacturer, which is wrong.


I've been racking my brain trying to figure out a way to make crystal give me a count of manf. based on unique service request ids. For example, if there are three rows for service request id 3340, it should only return 1 for a
count on that manf.

I'm trying to make a chart out of this count.

Thanks!

 

[fisheromacse]

can you do a distinct count on service ID?

 

[lbass]

You could concatenate the manufacturer field with the service request ID, if you want to count the manufacturer once per ID.

-LB

 

[infotech2]

Thanks for the suggestion LBass. I tried that, but the same thing happens when I do a distinct count. In my pie chart setup I add SRIDManf (the concatenated fields) to the "On Change Of" and to the Show Values. I set the summary options for a distinct count and it show 1 for item. If I do a regular count, it counts the manufacturers on every record rather than grouping the same service requests together and returning one for that group.


fisher - No, the distinct count on the srid doesn't seem to work. However, I have a group in the report called Vessels and I do a distinct count on change of VesselName to show a distinct count of ServiceRequestIDs and it works.

 

[lbass]

I'm not really following what you are trying to do. Can you show some mock data that shows the groups and details for some records (enough to illustrate the issue) along with the expected results and with report sections identified. Then explain what the chart is intended to show.

-LB

 

[infotech2]

VesselName SRID Service Total Manf. Equip.
Vessel - Group 1
Furuno - Group 2 (manf) $6,317.60
VDR - Group3 $6,317.60
Vessel 5862 $2105.86 Furuno VDR
Vessel 5862 $2105.86 Furuno VDR
Vessel 6051 $2105.86 Furuno VDR

The above example shoudl provide enough. I want to get a count of how many times Furuno was serviced. As you can see, 5862 shows up twice and 6051 shows up once. 5862 is one service so the count shoud only be one even though there are two rows for 5862.

I want the chart to show number of pieces of equipment in the report per manf.

 

[lbass]

I don't know what a Vessel is here, so can a specific service ID appear for other manufacturers? For other vessels? It seems likely that the service ID is specific to the manufacturer/vessel, so you should be able to chart (in the report header or footer) on change of manufacturer, just using distinct count of service ID. If this doesn't work, you need to explain why and/or show data that illustrates why.

-LB