How to calculate percentage in crosstab crystal report

[former_member629712]

Hi,

I've requirement to create a cross-tab in report. But the problem is I don't know how to do calculation for both of this column in crosstab:

1. Open 30%

Formula: Total Open Status per Assignee / Grand Total * 100

Example (Michael) : 10/ 28 * 100 = 35.71%

2. % permonth

Formula: Grand Total for all status per Assignee / Grand Total for Status * 100

Example (Michael) : 11/ 28 * 100 = 39.29 %

I've encounter an error 'Argument #2 of 'GridValueAt' is out of range' using below code :

if GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("OPEN"), CurrentSummaryIndex)=0 then 0 else GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("OPEN"), CurrentSummaryIndex)/ GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Total"), CurrentSummaryIndex) * 100

Could someone teach me how to do the calculation and provide me with some sort of suggestion/solution to this issue?

Thank you.

[abhilash_kumar]

Replace Line#21 with:

If GridRowColumnValue("table.DOC_DATE", i) = s then

Regarding formula#2, does your crosstab have a field under "Columns" like in the attached screenshot:

If yes, then this is the field you need to use in the formula.

If you don't have a column then where are the status values being generated from?

-Abhilash

[former_member629712]

For formula 1:

I've tried it and encounter below error :

For formula 2,

I'm sorry. I don't really understand your question. Can you explain on this?

I've tried to use @FX_CRStatus field in formula 2 but get same error as per above.

Thank you.

[abhilash_kumar]

Hi Athi,

Not sure why you have the "for loop" at two places set to:

for i := 0 to getRowPathIndexof (s) - 0 do

It should be:

for i := 0 to getRowPathIndexof (s) - 1 do

For the second formula, have you surrounded the formula name in double-quotes, like so:

GetTotalValueFor("@FX_CRStatus");

-Abhilash

[abhilash_kumar]

I'm sorry, Line# 4 should be:

local dateTimevar s := GridRowColumnValue("table.DOC_DATE", CurrentRowIndex);

In Formula #2, you cannot use "AssignedID" as that's the "summary" field and not the "column" field of the crosstab. You need to use the field that shows values: closed, open, rejected.

-Abhilash

[former_member629712]

Formula 1:

Got same error but this time error in line#21 as red highlighted. It is we have to convert datetime to string?

Formula 2:

I've tried all the value (open/rejected/closed) you suggested but it's seems not working. For this column, I want to get the total percentage per assignee.

For example,

(Total (closed+open+rejected) per assignee / grand total of status for all assignee) * 100

Thanks

[abhilash_kumar]

Try this please:

1. Replace Line#4 in the first formula to :

local stringvar s := GridRowColumnValue("table.DOC_DATE", CurrentRowIndex);

2. In formula#2, you need to replace "GRAND TOTAL" with the actual database column that is used as the crosstab's column.

-Abhilash

[former_member629712]

I'm still facing error using formula you given.

1. Replace Line#4 in the first formula

2. In formula#2, you need to replace "GRAND TOTAL" with the actual database column that is used as the crosstab's column

- I've use AssignedID since I calculate total of AssigenedID

[abhilash_kumar]

Hi Athi,

Assuming you've already inserted the two calculation columns, here's what you need to do:

1. Right-click one of the summary cells under the "Open 30%" column > Calculated Member > Edit Calculation formula > Use this code:

local numbervar i;
local numbervar j;
local numberVar ctot;
local stringvar s := gridlabelat("Month field", CurrentRowIndex); //Replace "Month field" with the field you've used as the Row. Surround the field with double quotes and not the default curly braces - { }
local numberVar rtot := gridvalueat(CurrentRowIndex, GetColumnPathIndexOf("OPEN"), CurrentSummaryIndex);
If CurrentRowIndex = 0 then
(
    for i := 0 to getRowPathIndexof(s)-1 do
    (
        for j := 0 to CurrentColumnIndex-1 do
        (
            ctot := ctot + gridValueAt(i, j, CurrentSummaryIndex);
        );
    );
IF ctot = 0 then 0 Else rtot % ctot;
)
Else
(
    for i := 0 to getRowPathIndexof(s)-1 do
    (
        If gridlabelat("Month field", i) = s then
        (
            for j := 0 to CurrentColumnIndex-1 do
            (
                ctot := ctot + gridValueAt(i, j, CurrentSummaryIndex);
            );
        );
    );
IF ctot = 0 then 0 Else rtot % ctot;
);

2. Right-click one of the summary cells under the "% per month" column > Calculated Member > Edit Calculation formula > Use this code:

local numbervar i;
local numbervar j;
local numberVar ctot;
local stringvar s := gridlabelat("Month field", CurrentRowIndex); //Replace "Month field" with the field you've used as the Row. Surround the field with double quotes and not the default curly braces - { }
local numberVar rtot := GetTotalValueFor("Field Used as crosstab's column"); //Replace "Field Used as crosstab's column" with the field you've used for the Column. Surround the field with double quotes and not the default curly braces - { }
If CurrentRowIndex = 0 then
(
    for i := 0 to getRowPathIndexof(s)-1 do
    (
        for j := 0 to CurrentColumnIndex-1 do
        (
            ctot := ctot + gridValueAt(i, j, CurrentSummaryIndex);
        );
    );
IF ctot = 0 then 0 Else rtot % ctot;
)
Else
(
    for i := 0 to getRowPathIndexof(s)-1 do
    (
        If gridlabelat("Month field", i) = s then
        (
            for j := 0 to CurrentColumnIndex-1 do
            (
                ctot := ctot + gridValueAt(i, j, CurrentSummaryIndex);
            );
        );
    );
IF ctot = 0 then 0 Else rtot % ctot;
);

-Abhilash

[former_member629712]

Hi abhilash.kumar,

Thank you for your reply. I've tried your script but a prompt error appear. Need your assistance on this.

Thanks.

[abhilash_kumar]

The screenshots you've attached aren't showing up. Could you please upload them again?

-Abhilash

[former_member629712]

Hi,

Could you see the attachments? I've encounter error for both script you have provided.

Error 1: A date-time required here.

My date format: 28/8/2020 12:00:00 AM

Error 2: This is not the name of a grid group for the field being formatted.

Thanks.

error-1.png

error-2.png

[abhilash_kumar]

Sorry, the attachments aren't showing up.

You may also upload the .rpt file "with saved data" to a shared drive and post the link here.

-Abhilash

[former_member629712]

My applogize. I can't share the .rpt file, my workplace didn't allow to share it since it's the data is confidential. I hope you can understand.

Btw, I tried to attached screenshot in previous comment.

Thanks

struc-2.png

struc-1.png

[former_member629712]

Hi,

Could please anyone help me to solve this problem?

Thanks