Report Design: set maximum and minimum limit of gauge chart

set maximum and minimum limit of gauge chart

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6056
Printed Date: 05 May 2024 at 1:04am

Topic: set maximum and minimum limit of gauge chart

Posted By: thakuramit1981
Subject: set maximum and minimum limit of gauge chart
Date Posted: 13 Apr 2009 at 10:22am

The problem i am facing is :
I have a requirement in which the i need to display the data based on
1)completed Inspections.
2)total inspections.

The data is grouped in two levels district and county.

so the chart displays the completed inspections for all districts and compared with the total inspections.Then the chart further drill down to county level when the user selects the district, it generates the chart for all the counties.

The number of completed inspections and total inspections is different for each district and county. so the minimum and maximum limit should be shown independent for each district.

but currently it show a constant limit based on the highest value for the district.

suppose we have 6 districts., the data for:
district 1: completed inspection:2684,totalinspections :5184
district 2: completed inspection:3000,totalinspections :6000

I want to show the graph min: value for district 1 is 0 and max 5184
min value for district 2 is 0 and max 6000.

Could someone please help me as this is very urgent

Replies:

Posted By: DBlank
Date Posted: 13 Apr 2009 at 12:52pm

What is the data you are using to display in the chart (Chart Expert Data tab) and how do you have your Data Values set in the Chart Expert > Axes Tab > Data Values section?

Posted By: thakuramit1981
Date Posted: 13 Apr 2009 at 12:59pm

I using a group chart and advanced chart

in group chart : on change of district Id
show Summary for completed Inspections

In the advance chart

I am showing on change of district Id show
total inspection and completed inspections.

In the data section i have data axis set to auto range.

I can not set a manual limit as the data for each district is different

Posted By: DBlank
Date Posted: 13 Apr 2009 at 1:04pm

What about the setting for the Data Values in the Chart Expert > Axes Tab > Data Values section?

This is where you control your min and max...

It should auto change your min and max values if you have Auto Range set as True.

Posted By: thakuramit1981
Date Posted: 13 Apr 2009 at 1:10pm

I tried to put in the maximum and minimum value. but then that does not provide a solution as the maximum value remains constant.

i want the maximum value to be the value of total inspections for that district.

I want it like this: http://epic229.dep.state.fl.us/WasteFirst/Menu.do?menu=charts

this application is based on kawa charts, i am trying to convert it to crystal
if you click on the completed contractor inspection: you will know what i am trying to accomplish, I am using group charts to accomplish drill down

Posted By: thakuramit1981
Date Posted: 13 Apr 2009 at 1:13pm

I will be very grateful to you if you could help me solve this issue

Posted By: DBlank
Date Posted: 13 Apr 2009 at 2:59pm

Sorry but nothing is jumping out at me right now.

Note that you can only use drill down in a chart if you use the Data as a Group. As far as I am aware you cannot use the Advanced option in the chart and still drill down. If you must use the chart drill down you have to design the report around this.

I'll have to look at this some more tomorrow to see if I can come up with anything.

If anyone else has any ideas feel free to jump in.

Posted By: thakuramit1981
Date Posted: 13 Apr 2009 at 6:31pm

I am using a drilldown chart, it works fine , but i am not able to set the limits, that is what is causing trouble.The maximum limit is same for all districts, its should be different for each district.I.e the value of total inspections for each district.

I really appreciate your help and hope to find a resolution soon as this is a critical requirement.

Posted By: DBlank
Date Posted: 14 Apr 2009 at 7:43am

I think I know what may be going on here.

Are you placing this in the Report header or a Group Header?

If you place it into the report where there is only one Gauge per header it will aplly the individual range, if you place it in a header that would require it to have more than one gauge it applies the values from the gauge with the largest range to all of the gauges.

Make sense?

Posted By: thakuramit1981
Date Posted: 14 Apr 2009 at 7:49am

yes thats true i have it placed in the report header so it displays me 6 gauges for all 6 districts. This is the requirement too.

Is there a way we can handle this and set the limit based on actual value

Posted By: DBlank
Date Posted: 14 Apr 2009 at 8:01am

I do not use gauges often so I am not aware of a way to do this.

I suggest trying to movew it down a level and see if you can mimic the process you are looking...Ultimately when drilling down via the gauge you are getting to the same levels anyway. It would just display a little diferently with 1 per row rather than across and rows.

Posted By: thakuramit1981
Date Posted: 14 Apr 2009 at 8:10am

I do understand your point however The thing is that when i drill it down to individual level the limit changes.

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

Posted By: DBlank
Date Posted: 14 Apr 2009 at 9:06am

I know you said it is part of the scope to have this in the report header but perhaps you can skip that and give them what they need anyway.

You can add an extra Group Header section and place it there and let them drill down from there (are they going to know the difference as long as it functions as they want it?). You can conditionally suppress other sections of the group with drilldowngrouplevel=0 as the supression and mimic the process/look as if it were placed on the report header. The primary difference as i see it is that you will have a gauge per group header row rather than multiple gauges on on a single row in the report header. If you properly conditionally supress the other portions of the group it should be fine unless there is something else in the report that I ma not aware of that needs to be here as well.

Other than this I am not sure what else to try.

Posted By: thakuramit1981
Date Posted: 14 Apr 2009 at 10:30am

The reports has two level drill down, 1) district

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( mailto:%7b?Pm-@beginDate%7d - '{?Pm-@beginDate}' , 'MM/dd/yyyy')
                 AND to_date( mailto:%7b?Pm-@endDate%7d - '{?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( mailto:%7b?Pm-@beginDate - '{?Pm-@beginDate } ', 'MM/dd/yyyy')
                 AND to_date( mailto:%7b?Pm-@endDate%7d - '{?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(' mailto:%7b?Pm-@beginDate - {?Pm-@beginDate } ', 'MM/dd/yyyy')
                 AND to_date(' mailto:%7b?Pm-@endDate%7d - {?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 mailto:Pm-@begindate - Pm-@begindate and mailto:Pm-@endDate - Pm-@endDate are parameters passed

Posted By: DBlank
Date Posted: 14 Apr 2009 at 2:24pm

Again, Sorry but I do know of a way to handle this where it would display unique min/max range per item when displaying multiple items in the same pager or group header. There may be a solution but I do not know of one.

If anyone else knows a solution for this please chime in.

Regardless of this I'll give this another shot to try and help you...

You have two groups, District and County.

Place one chart ONLY using the District at the district header level.

Place another chart displaying county at the county header level.

Play around with the the conditionally supressing sections using the drilldowngrouplevel function so that the items are mimicking the process of displaying the grouping at a higher level.

Example

GH1a - Chart of Districts only

GH1b - other data about the county grouping (suppress as drilldowngrouplevel=0)

Gh2a - chart for Counties (Suppress drill down OK)

GH2b - Data about County ( Suppress as drilldowngrouplevel<2)

This is just an example and you may need to adjust it to see if you can get it to work for you.

Posted By: thakuramit1981
Date Posted: 17 Apr 2009 at 5:13am

I have already implemented this kind of approach but did not succeed in achieving what i desire.

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.