Thanks to @Abhilash Kumar , Former Member
and folks.I figured out the answer.......................
For this we need to create two formulas
Formula1# save the record information in the arrayform [note: Variables should be declared as Shared type]
Formula2# sort the array and apply the manual median formula.
------------------------------------------------------------------------------------------------------
Formula1 as follows. we can also drop this formula in detail section for clarification
------------------------------------------------------------------------------------------------------
Whileprintingrecords;
shared numbervar array jan_value;
shared numbervar jan_counter;
IF
(
monthname(table.year)= "Jan"
)then
(jan_counter:= jan_counter + 1;
Redim Preserve jan_value[jan_counter];
jan_value[jan_counter] :=table.value;
)
else
0
--------------------------------------------------------------------------------------
formula2 as follows and it needs to be dropped in report footer only
--------------------------------------------------------------------------------------
Whileprintingrecords;
Shared numbervar array jan_value ;
Shared numbervar jan_counter;
numbervar Jcounter1;
numbervar Jcounter2;
numbervar Jtemp_arr;
numbervar J_middle;
numbervar Jmedian;
for Jcounter1 := ubound(jan_value) to 1 step -1 do //This loop sorts the array
(
for Jcounter2 := 1 to Jcounter1 - 1 do
(
if jan_value[Jcounter2] >jan_value[Jcounter2 + 1] then
(
Jtemp_arr := jan_value[Jcounter2];
jan_value[Jcounter2] := jan_value[Jcounter2 + 1];
jan_value[Jcounter2 + 1] := Jtemp_arr;
)
);
);
if ubound(jan_value) mod 2 = 1 then //this finds the Median
(
J_middle := (ubound(jan_value)+1)/2;
Jmedian := jan_value[J_middle];
)
else
(
J_middle := ubound(jan_value)/2;
Jmedian := (jan_value[J_middle] + jan_value[J_middle+1])/2
);
------------------------------------------------
Finally the issue is resolved
----------------------------------------------------
Hi
Best way to do this is
--Insert same report as sub report in your report footer.
--Open the sub report and insert Median values at group footer level by right clicking on value and insert summary--Median
--Suppress all detail and other sections except Group footer.
--Now go in main report section expert and based on Flag value suppress report footer.
Thanks,
Sastry
Hi Phani
Are you committed to using an array for your report? It may be just as easy to write a few formulas to collect the data in variables.
For example . . .
Create a formula named _detail_variables:
Whileprintingrecords;
Shared numbervar jan_value;
Shared numbervar feb_value;
Shared numbervar mar_value;
. . . . //other months
Shared numbervar jan_counter;
Shared numbervar feb_counter;
Shared numbervar mar_counter;
. . . //other months
If ([month]=”jan” and [flag]=”TRUE) then
(jan_value:= jan_value + [value];
jan_counter:= jan_counter + 1);
If ([month]=”feb” and [flag]=”TRUE) then
(feb_value:= feb_value + [value];
feb_counter:= feb_counter + 1);
If . . . . //other months
drop the above formula into the details section.
Then create 12 “median” formulas – one for each month.
@jan_median
Whileprintingrecords;
Shared numbervar jan_value;
Shared numbervar jan_counter;
If jan_counter>0 then
jan_value / jan_counter else 0
drop these 12 formulas into the report footer
Add a comment