Cross Tab Functions

[former_member587427]

I am fairly new to Crystal Reports and I am using Crystal 2016, however I am doing very well with self teaching.

I have created a cross tab and it is formatted exactly how I want it. However, I need to add a column with a tricky calculation.

Rows: Users (about 70 unique)

Columns: Call Result (Answered, Abandoned, and Voicemail) & Total per User

Summary: Total of each Call Result (Answered, Abandoned, and Voicemail) & Grand Total

I need to have a COLUMN that displays a percentage of Call Result ANSWERED against the grand total of ANSWERED calls.

For example:

Pete answers 5 calls

Mary answers 10 calls

Paul answers 25 calls

Grand Total of answered calls: 40

The column would display:

Pete 13%

Mary 25%

Paul 62%

One of my primary problems is that I am not able to use the formulas generated by the cross tab expert.

[abhilash_kumar · Accepted solution]

Hi Shanna,

Please try these steps:

1. Right-click the header cell of the last column of the crosstab > Select Calculated Member > Insert Column.

A blank column with zero values should be inserted at the end.

2. Right-click the blank header cell of this new column > Calculated Member > Edit Header Formula > Use this code:

"Percentage"

3. Right-click the blank header cell of this new column > Calculated Member > Edit ColumnValue Formula > Use this code:

"Percentage"

4. Right-click one of the zero values in this new column > Calculated Member > Edit Calculation Formula > Use this code:

local numbervar tot;
local numbervar i;
for i := 0 to GetNumRows-2 do
(
    tot := tot + GridValueAt(i,GetColumnPathIndexOf("Answered"),CurrentSummaryIndex);
);
If tot = 0 then 0 else
GridValueAt(currentRowIndex,GetColumnPathIndexOf("Answered"),CurrentSummaryIndex)%tot

-Abhilash

[former_member587427 · Accepted solution]

Abhilash,

Thank you for all of your help.

Shanna

[former_member587427]

I am so sorry that my responses are all over the place. This is my first time using the SAP forum and I am not seeing a reply or comment button., only the "YOUR ANSWER" section.

Anyways, yes that is correct, my report is no longer returning any data even though the data source is still connected.

[former_member587427]

Hi Abhilash,

Oh my goodness, I think I completely wiped out the report! Apparently I already had the option marked to save the data, but I clicked it again for assurance, clicked save, and closed. I opened it up and now it's gone! Of course, once I clicked that option for assurance I was actually turning the option off... Anyways, I'm assuming that this is the end of the road with the assistance on my project, for now. Unless there is a magical fix... I tried refreshing the report and that did not work.

Thank you for all of your time and excellent help.

Shanna

[abhilash_kumar]

So, when you refresh the report it doesn't return any data?

-Abhilash

[former_member587427]

Thank you, that worked out great for the % issue.

The top screenshot is from an excel spreadsheet that I created with the same dataset. The second screenshot is the Crystal Report.

1. The calculation doesn't come out the same

2. I'm struggling to get the first three columns in Crystal to format to no decimal (same as the total column), while keeping % format in the correct column. I researched and attempted different formulas and had no luck. Is there a solution for that?

Thank you for your continued assistance,

Shanna

[abhilash_kumar]

Could you please attach the report with saved data? To save with data, go to File > Check 'Save Data with report' > Save.

I'd recommend that you upload to Google Drive or another secured site and share the URL here.

-Abhilash

[abhilash_kumar]

Hi Shanna,

I see that the percentages are correct. Could you please point to the row that shows incorrect percentage?

Also, to add a percentage sign, here's what you need to do:

1. Right-click one of the values in the percentage column > Format Field > Number tab > Customize > Currency Symbol tab > From the "Position" label's drop-down choose the option has the symbol at the end of the number

2. Click the formula button beside "Currency Symbol" > Use this code:

If GridLabelAt("field_used_as_the_crosstab_column", CurrentColumnIndex) = '% of Answered Calls' then '%' else ''

Replace "field_used_as_the_crosstab_column" with the correct database field that is used in the crosstab's column. Make sure that the field is surrounded by double quotes like in the formula above and not the usual curly parenthesis

-Abhilash

[former_member587427]

Hi,

I didn't realize that I could paste an image of what I am working with! I've included three screenshots, to help provide visuals.

Thank you for both of your answers, I tried both methods and was unsuccessful. However, following the instructions provided from Abhilash I was able to get pretty far.

% of Answered Calls is the column that I inserted (referenced as "Percentage"), this was successful

However, the calculation was not. I also need it to display with a % symbol.

Thank you,

Shanna

l

l

l

l

l

[DellSC]

Or, instead of calculating the total, you could do this:

GridValueAt(currentRowIndex, GetColumnPathIndexOf("Answered"), CurrentSummaryIndex) % GetTotalValueFor("<user group alias>, 1)

This assumes that "Answered" is the first summary value - if it's not, you'll need to change the number. Also, You can get the correct value for <user group alias> by going to Functions >> Grid Value >> Row or Column Names in the formula editor.

-Dell