Calculating percentage in a crosstab

[Former Member]

I have a Crystal report with a crosstab that contains two columns of numbers.  These numbers are "distinct counts".

I would like to generate three more columns:

1.     Sum of the two columns (I created a calculated member for this column, which is working and giving me the correct value)

2.     1st column percentage of the total.

3.     2nd column percentage of the total.

What formula(s) do I need to use to do this?

Thanks.

Field1	Field2	Total	Field1 % of Total	Field2 % of Total
10	90	100	10%	90%

[abhilash_kumar · Accepted solution]

Hi Cheryl,

Here's what you need to do:

1) Right-click the header cell for the calculated member you've already inserted > Select Calculated Member > Click 'Edit ColumnValue Formula' and type this text:

"Total"

2) Right-click the Total column and select Calculated Member > Insert Column.

3) Edit this new column's Header text and give it a name

4) Repeat Step 1 for this new column and also give it a unique columnvalue. Something like:

"Field 1%"

5) Edit the column's Calculation Formula and use this code:

if GridValueAt(CurrentRowIndex, CurrentColumnIndex-3, CurrentSummaryIndex) = 0 then

    0

else

    (GridValueAt(CurrentRowIndex, CurrentColumnIndex-3, CurrentSummaryIndex)/GetTotalValueFor("Database_Field_Used_as_Column")) * 100

Replace "Database_Field_Used_as_Row" with the field you've used as the column in the Crosstab. Notice how it's enclosed in double-quotes.

6) To insert another column, right-click the 'Field 1%' column and repeat steps 2 to 5.

7) The Calculation Formula for this new column would be:

if GridValueAt(CurrentRowIndex, CurrentColumnIndex-3, CurrentSummaryIndex) = 0 then

    0

else

    (GridValueAt(CurrentRowIndex, CurrentColumnIndex-3, CurrentSummaryIndex)/GetTotalValueFor("Database_Field_Used_as_Column")) * 100

If this doesn't work, please send me the .rpt file with data as it's easier to work on an existing report.

-Abhilash

[former_member198519 · Accepted solution]

Hi Abhilash,

I too had a similar kind of issue. In my case I was not able to use the GetTotalValueFor() properly. it was giving me error of "Constant Expression required".

So i used the below formula:

if GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(1), CurrentSummaryIndex) = 0 then

    0

else

    (GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(1), CurrentSummaryIndex)/Gridvalueat(CurrentRowIndex,GetColumnPathIndexOf(), CurrentSummaryIndex))*100

- Kuldeep

[abhilash_kumar · Accepted solution]

Thanks, Kuldeep. You would still need to change that code to:

if GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Field1_Header_Label"), CurrentSummaryIndex) = 0 then

    0

else

    (GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Field1_Header_Label"), CurrentSummaryIndex)/Gridvalueat(CurrentRowIndex,GetColumnPathIndexOf(), CurrentSummaryIndex))*100

Where "Field1_Header_Label" is the column label for Field 1.

-Abhilash

[former_member198519 · Accepted solution]

Thansk Abhilash

[Former Member · Accepted solution]

Abhliash,

That worked perfectly!

Just to tweak the display, how can I make the percentage show 2 decimal points (example, 50.00%)?

And how can I add the % sign to the displayed result?

Thanks.

[abhilash_kumar · Accepted solution]

To add two decimals, simply right-click the summary cells > Number tab > Choose one of the formats with two decimals.

To add a Percentage Sign, please follow the solution I posted in this thread:

https://scn.sap.com/message/15492094

-Abhilash

[Former Member · Accepted solution]

Getting close.  Your solution worked.

However, two more things:

1.     It appears adding the two decimal spaces is just adding zeros (giving me round percentages).  Is there any way to display the actual value (example:  50.23%)?

2.     Is there a way to make the decimal spaces be column specific (just like you did with the percent signs)?  I only want the extra decimal spaces on the last 2 columns (same as percentage signs).

Thanks again.

[abhilash_kumar · Accepted solution]

For 1 and 2, here's what you need to do:

1) Remove the formula you added to display the % sign

2) Get inside the Calculation Formula you entered for 'Field 1%'

3) Comment or remove it > Just type in 0 (zero) - make sure zero is entered

3) Right-click one of the summary cells > Format Field > Common tab > Click the formula button beside 'Display String' and use this code:

if GridRowColumnValue("Database_Field_Used_as_Column", CurrentColumnIndex) IN ["Field1%","Field2%"]

then

(

    if GridValueAt(CurrentRowIndex, CurrentColumnIndex-3, CurrentSummaryIndex) = 0 then

    '0'

else

    totext((GridValueAt(CurrentRowIndex, CurrentColumnIndex-3, CurrentSummaryIndex)/GetTotalValueFor("Database_Field_Used_as_Column")) * 100,2) & "%"

)

else

    totext(CurrentFieldValue,0)

"Field1%" and "Field2%" are the 'ColumnValues' you added based on my first reply (Step #4)

-Abhilash

[Former Member · Accepted solution]

Works perfectly!

Thanks again!

[former_member205840]

Hi Warner,

When you have only two columns, you can very well create a standard report and arrive the rest of the columns using formulas.

Still you want to go for cross tab then you need to manipulate with first two cells only

Thanks,

Sastry

[Former Member]

If I can't use the third column of my crosstab, can I create a formula field for the percentage and use that in the column?

If so, how do I create that formula?

Thanks.