Crosstab Calculated Member with multi columns

[mjailani]

HI,

I have Cross tab in crystal report with more than one column , when am adding the calculated member column "MAD" and edit the insertion formula to make it dynamic i user the below formula i found it in some article after long struggle.

CurrentColumnIndex=(GetNumColumns-1) 

and it's works fine , if there is better practice please advice.

My issue is how can i calculate how many "OK" in the row

i used below formula but no luck,

local numbervar i;
local stringvar X;
local numbervar count_OK;
for i := 1 to CurrentColumnIndex-1 do     
(     


     if GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex) = "OK" then   
     (     
         count_OK := count_OK + 1;     
     )     
);      


x:= totext(count_OK,'#');

also how can i make this calculated member column "MAD" is not grouped by other cross-tab column like below

Thanks

[abhilash_kumar · Accepted solution]

Hi Mustafa,

Modify the 'Calculation Formula' for the 'MAD' to:

local numbervar i;
local numbervar count_nums; 
for i := 3 to CurrentColumnIndex-1 step 2 do
(    
     if GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex)>0  then    
      (    
         count_nums := count_nums + 1;        
     )    
);    
count_nums;

You need to initialize the variable 'i' to 3 as there are three columns in your crosstab as well as skip one column in the for loop using 'step 2'.

Regarding your next question about displaying 'MAD' as the last column without any grouping - Yes that can done, however, not without having one of the subtotals showing up.

You can always suppress this subtotal, however, there is no way to remove the blank space it leaves behind.

Download ver.2 of that report from here:

https://drive.google.com/file/d/0B6ruN85cij3XSXpGLTJneUd3dU0/view?usp=sharing

The 'MAD' column on this report is to the end, however, notice the blank space before the first column.

-Abhilash

[mjailani]

thank you very much , am really appreciate your support,

just to understand , the formula will be

for i := N to CurrentColumnIndex-1 step N-1 do

where N is number of columns in the cross tab right ?

and how you suppress this subtotal and show blank.

also in case of i need if the value > 0 should count or sum field from the columns

ex. in the attached below report the first row only 2 columns is > 0 then in the MAD should sum the MADQty Column belong to it 30+40=70

is that possible ?

https://drive.google.com/open?id=0B1oQ0Dd7y3TcYV9tZnNWWVRudXc

and thank you again for the support.

[mjailani]

thank you very much for replay,

I worked around those blank cells by showing the number or 0 if null instead of "OK" and it's works fine ,

the other issue is i used formula below to count if the cell > 0 and it's working fine when there is one column only in the cross tab columns, when am add more then one columns it's calculating wrong.

also how can i make this calculated member column "MAD" is not grouped by other cross-tab column or at least by to top column

local numbervar i;
local numbervar count_nums; 
local numbervar X;
for i := 1 to CurrentColumnIndex-1 do       
(     
     if GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex)>0 then    
      (     
         count_nums := count_nums + 1;        
     )     
);    
X:=count_nums;

I re-attached the report if you need 🙂

https://www.mediafire.com/?31w3ln846oic6ao

[abhilash_kumar]

Hi Mustafa,

Your formula is correct, however, it doesn't work because of those 'blank' summary cells.

Those blank cells represent 'missing data' and unfortunately, crosstab calculated member formulas like these fail:

if GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex) = 'OK' then

It fails because it's NULL. Also, the usual 'Default Values for Nulls' option doesn't work with Calculation formulas in a Crosstab.

You'd need to account for such missing rows in the view.

-Abhilash

[mjailani]

Please any one can support ??

[mjailani]

i tried to uploaded but not accepting even after i changed the ext.

please download it from

http://www.mediafire.com/file/paf1ow9myzc9l72/Package.rpt

[abhilash_kumar]

Hi Mustafa,

Would you be able to attach the report 'with saved data' please?

-Abhilash