But then it  contradicts the main chart where its drill downed from.

 

The first chart displays 0-8000 for all districts , then when you click on the particular district to drill down it shows individual data for that district and the limit changes accordingly to the value for that district i.e 0-6000

or 0-3000 whatever  district is selected.

 

so the data integrity is lost. what should i do ? plssssssssssss advise

2) county.

 

The problem is if i include the the chart on group header district then it displays me the data for all counties.

 

How do i create a comparison chart like completed inspections to Total Inspections and grouped by district and county.

 

I am pasting the query:

 

SELECT (CASE WHEN fa.activity_closed_date between to_date('{?Pm-@beginDate}', 'MM/dd/yyyy')
                 AND to_date('{?Pm-@endDate}', 'MM/dd/yyyy')  
            AND fa.activity_result_id IS NOT NULL 
                 AND fa.activity_def_id = 10
                 AND fa.activity_status_id = 3  
                 AND fa.primary_value like '%Annual Site Inspection%'
           THEN 1 ELSE 0  END)  AS completed,
        (CASE WHEN fa.activity_result_id NOT IN (2, 5) 
                 AND fa.activity_closed_date between to_date('{?Pm-@beginDate} ', 'MM/dd/yyyy')
                 AND to_date('{?Pm-@endDate}', 'MM/dd/yyyy')  
            AND fa.activity_result_id IS NOT NULL 
                 AND fa.activity_def_id = 10
                 AND fa.activity_status_id = 3  
                 AND fa.primary_value like '%Annual Site Inspection%'
           THEN 1 ELSE 0 END) AS inCompliance, 
         (CASE WHEN fa.activity_result_id IN (2, 5) 
                 AND fa.activity_closed_date between to_date(' {?Pm-@beginDate} ', 'MM/dd/yyyy')
                 AND to_date(' {?Pm-@endDate}', 'MM/dd/yyyy')  
                AND fa.activity_result_id IS NOT NULL 
                 AND fa.activity_def_id = 10
                 AND fa.activity_status_id = 3  
                 AND fa.primary_value like '%Annual Site Inspection%'
           THEN 1 ELSE 0 END) AS outOfCompliance, 
      (SELECT COUNT(*) FROM stcm_snap.facilities f 
            WHERE f.fsc2_fac_stat_id LIKE 'OPEN%' 
            AND fa.district_id = f.oc2_office_id
            ) AS facilityCount,
         (SELECT COUNT(*) FROM stcm_snap.facilities f 
            WHERE f.fsc2_fac_stat_id LIKE 'OPEN%'  
             AND f.cc_county_id = fa.county_id) AS facilityCount1,
         cc.county_id AS countyId, cc.name  AS countyName,fa.district_id,fa.district_name
    FROM stcm_snap.mv_facility_activities fa, bis_lib_snap.county_codes cc,
         stcm_snap.county_district_codes cdc
   WHERE  cc.county_id = cdc.cc_county_id
     AND fa.county_id(+)  = cc.county_id
     AND cc.county_id NOT IN (0, 99) 

 

 

Where Pm-@begindate and Pm-@endDate are parameters passed

 

I will have to place an group chart in the district groupheader because it allows me to drilldown further but this returns me data for the counties in the district.If i use an advance chart in the district header i would get individual data but  then cannot drill down further

 

I spoke to about 10-12 crystal report experts with quite a good amount of experience in crystal reports and they all were having the same statement that this is a pure limitation of crystal reports and there is no way we can set the range of the chart depending on a exact value of the database field and also to set maximum value of the range different for each group record.

 Even if it is an individual chart, it never displays the exact value of the field , it is always having  limit which is higher than the maximum value of the field .

I guess crystal reports charting feature is not flexible enough.