To add Percentage formulas in crosstab crystal report? 1

[sumitha]

We want to add a formula to a crosstab in crystal report.We want to find out how to add a percentage formula into the crosstab

 

[kenhamady]

You can't use Crosstab cells in formulas.
You can, however create 'manual' cross-tabs with conditional totals, and here you can get percentages.

Tell me about your cross-tab, and I will tell you if this solution will fit. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Guest_imported]

You can indeed add formulas to a cross tab. Simply choose format cross tab and then add the formula by clicking on new formula.

 

[kenhamady]

visitor233,

You CAN add a formula from within a cross-tab, but you can't add a formula TO a Cross-tab, meaning you can't use the cells of the cross-tab in a formula. The formula button simply adds a column to the report, just like Insert - Formula. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

I'll volunteer to provide a description of this problem - I ran into yesterday, and gave up. The problem was the Ratio in the report footer (report sections are indicated by the column on the left). The only way I could think of getting the ratio was with a subreport, which I rejected because of the extra processing time required. The State and Count fields I could populate with a cross tab. Other than rewriting the view on which this report is based, any suggestions?


The desired report output:

PH State Ratio Count
GH1 Salesperson: Bill Sykes
GH2 FL 50% 2
GH2 GA 50% 2
GF2 Total 4

GH1 Salesperson: Jill Sykes
GH2 FL 67% 4
GH2 TX 33% 2
GF2 Total 6

RF All Salespersons
RF FL 60% 6
RF GA 20% 2
RF TX 20% 2
RF Total 10
Malcolm

 

[kenhamady]

Not many good options.

You could hardcode each state as a conditional total, and then calc the % as a percent of grand total. Putting each in a subsection you could conditionally suppress sections that are zero, and thereby mock up a cross-tab.

If you could get the grand total to be a column in the results table (by changing the view) you could divide each detail by that number and then sum the results in the Cross-tab, giving you a percentage in the Cross-tab. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[morechocolate]

Ken:

I read through some of your responses as well as the Crystal Formulas on your website and I still
need a little help.

Here is what I want:

Section 1
(I now do this part using a subreport which displays in Report Header b)
Tot# Trd on TDate 100 50%
Tot# Trd Late 100 50%

Section 2

Trd on TDate Trd Late Tot
TrdSysA 75 25 100
%TrdSysA %TrdSySA %Tot # Trds


TrdSysB 75 25 100
%TrdSysB %TrdSySB %Tot # Trds


Tot 150 50 200
Note: The %TrdSysA in each column is the pertange of the total TrdSysA
Also I use a distinctCount as my Summary for the cross-tab.
Currently Section 2 excludes the percentages and there is a Section 3

TrdSysA 50% of Total
Trd on TDate 75 75%
Trd Late 25 25%
100

TrdSysA 50% of Total
Trd on TDate 75 75%
Trd Late 25 25%
100

Tot # Trds 200

If I could eliminate the subreport it would really help performance. The only difference between the subreport and the main report is the grouping.

Main Report Group
Grp1 TrdSys
Grp2 Process Type
Grp3 Trd#

Subreport Group
Grp1 Process Type
Grp2 Trd#

 

[kenhamady]

MC,

Is this question the same one that started this thread, or a similar one?

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[morechocolate]

Ken,

This is a similar question. I was trying to fully understand conditional totals in regards to manual cross tabs. I was just playing around with my current report, the one described above, but I was not getting the correct results. For example, the Trd on Date or Trd Late in the cross-tab I currently have are created using the summary field of distint count on the trade# field. This is because in our database a sell can be split into several pieces, but have the same trade# because it is part of the same trade.

Trying to create a manual cross tab, if I say
If @Processed (that is the formula that determines if Trd on TrdDate or Trd Late) = "Trd on TrdDate" = 1 else 0 and then do a summary on that field in Grp#1 of the main report, I do not get the correct results because it includes the sells records. Therefore, in my example above with TrdSysA, Trd on TrdDate can have 80 records, but 5 of those 80 can have the same trade#. Doing a distinct count I get 75 trades that were Trd on TrdDate for TrdSysA.

Can I use your method on condition totals for what I am trying to do? I think I have been looking at it so long now, I cannot see how to make this a manual cross-tab so I can get the percentages in there as well.

Thanks

morechocolate (Pam)

 

[kenhamady]

The idea of manual crosstabs works very easily for summing your regulare counting. Doing a distinct count with conditional totals is a bit trickier. The formula would be something like:

If 'your condition'
then {the field you distinct count on}
else ""

This assumes the field is a string. Now you can do a distinct count on this conditional column, but the "" (if it occurs) will also be counted as a value. You can subtract one from the total each time, but there is the chance that none of the records were "". So you have to check to see if there were any "" records in each total and only subtract one from the distinct count if there were. Can be done but takes quite a bit of work. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[morechocolate]

Thanks a lot Ken. I think I have it. I was being quite dense. I will try it out when I get to work on Monday.

 

[morechocolate]

Thanks Ken,

It worked. The display is not exactly like that of a cross tab, but I was able to display the data the way that I wanted to and I did not have to use a subreport.

mc

 

[ctwilliams]

I have a similar situation. I have a cross-tab report formatted like below, and would like to add a percentage row below the total row at the bottom (I want the percentage of each category):

	Cat1	Cat2	Cat3	Cat4	Total
Jan	1	3	5	3	12
Feb	5
Mar	3
Apr	3
May	2
Jun	6
Jul	3
Aug	5
Sep	3
Oct	5
Nov	2
Dec	1
Total	39
%

 

[kenhamady]

You will also have to use a manual cross-tab. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com