Skip to Content
avatar image
Former Member

Crosstab Calculated Member with multi columns

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

capture.jpg (44.0 kB)
capture2.jpg (49.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    Feb 10, 2017 at 10:25 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06, 2017 at 02:39 PM

    Hi Mustafa,

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

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 06, 2017 at 08:16 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 09, 2017 at 06:31 AM

    Please any one can support ??

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 09, 2017 at 12:37 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 09, 2017 at 06:00 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 13, 2017 at 01:02 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded