 Former Member

# To display multiple Median values in a report footer

How can i display median values in the report footer.

Could you specify the proper Crystal Coding to create an array to store the values of detail section and

then apply median on the sorted array. Untitled.jpg (97.1 kB)
10|10000 characters needed characters exceeded

### Related questions Former Member
Posted on Dec 31, 2013 at 06:26 PM

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

----------------------------------------------------

10|10000 characters needed characters exceeded
• Posted on Dec 29, 2013 at 09:45 AM

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

10|10000 characters needed characters exceeded
• Abhilash Kumar Former Member

The following line in the code is Not supposed to be the way you've interpreted:

numbervar array arr:={@values};

You need to create a separate formula that 'builds' the array. Then refer to the array variable in the above formula. The formula you've pasted above 'sorts' and 'finds out' the Median - that's it.

The line should only be:

numbervar array arr;

If you look at the thread again, Graham suggests something simpler for the crosstab.

-Abhilash

• Former Member
Posted on Dec 30, 2013 at 04:25 PM

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

10|10000 characters needed characters exceeded
• Former Member Former Member

Hi Phani

my solution doesn't use arrays . . . . unfortunately, after re-reading your post . . . my solution provides the mean - not the median

for January you need two formulas:

_detail_variables

jan_median

copy the code from my previous post into the formulas

drop the 1st formula into the details section

drop the 2nd formula into the report footer

if you are committed to using arrays, then refer to Abhi's posts