calculate percentage in map

[zhuolin]

Hi All,

I have a problem with doing calculation of amount percentage in fusion map, here is my dataset:WORK_STATE STATETOTAL PERSONNUMBER

STATE                                    VALUE

New South Wales                     4473
Australian Capital Territory      75713
Northern Territory                    1803
Tasmania                                 4537
Victoria                                     13289
Queensland                              45909
Western Australia                     4537
South Australia                         12362
 

 the result is grouped by 'state' with each state's total value number. I want the percentage value of each state to show on the map as the value expression.

like:   single state value / sum[all states value].

 

but I am getting the result as:

percentage1 =  state1 value / state1 value

percentage2 = state2 value / state1 + state2

percentage3 = state3 value / state1 + state2 + state3......................

I am not sure it is the problem of evalution time, but can we set that inside a map expression?

Hope anyone can help me out.

cheers

[cbarlow3]

If I understand correctly, you display state, value, and percentage in the detail and then have a fusion map in the summary.  Your detail band  might just be for troubleshooting purposes, but that's okay.  In the detail, I'm guessing that your percentage is a variable that is the value field divided by a variable you've created that is the sum of the values.  The trouble is, each detail needs to already know that grand total in order to do the division correctly.  If I display $F{state}, $F{value}, $V{TotCount}, and $V{Pct}, I can change the evaluation of $V{TotCount} from "Now" to "Report" and display the correct grand total, but even when I change the evaluation time on $V{Pct} from "Now" to "Auto" or "Report", it's now not using the correct individual values of $F{value}...it's only using the most recent for ALL details. Besides, you don't want to have to rely on the evaluation time, because that ties you to having to actually display the individual values (I think).

There are two solutions I know of:

1. Write a subreport or a List with a subdataset in your Title division that runs essentially the same query as your main query, but only return the total:  SELECT SUM(VALUE) AS GRANDTOT FROM STATEINFO.  Then return that value to a variable in your main report/dataset.  That way every detail record has access to the true total at all times.  Yes, that does mean that you're essentially doing the query twice: once to get a total and then again to get the details, which you compare to the total.

2. If your version of SQL supports it, you can use a "windowing" technique:

SELECT STATE, VALUE, SUM(VALUE) OVER () AS TOTCOUNT FROM ( previous version of SELECT statement )

This assumes that your previous version was returning distinct rows with State, Value pairs.

This second method means that each detail has a field (rather than a variable) called  $F{TOTCOUNT} that is available to it for division.  That same field is also available to the fusion chart.

Carl

[zhuolin]

Hi Carl,

I have tried something similar to your second advice yesterday. It did help solving the problem and it is better to feed simple data to a map.

 

Thanks for your reply. Very appreciate that.

 

[zhuolin]

I have other question no related to this topic. It is about scriptlet.

How many scriptlet can we define in one report?

Usually we have SCRIPTLET-->REPORT and we define a class to it. To use it, write: $P{REPORT_SCRIPTLET}.method();

 

Now I have defined SCRIPTLET-->myScriptlet. How can we use it in our report?

[mdahlman]

"I have other question no related to this topic."

Then please post a new topic.

Regards,
Matt