Cross Tab Report Fields - How to access summarized 9

[msanders67]

I have a cross tab report created and I need to calculate some percentages. When I'm in the formula editor, I do not see the summarized fields available, which is something I need in order to calculate the percentages I want to add.

Does anyone know of a way to get to this data?

Thanks in advance,
Margo Sanders

 

[synapsevampire]

Please post technical inforamtion when requesting it, such as Crystal version, database used, example data and expected output.

In your case, you might mention how these summarized fields were created.

Cross-tabs are somewhat independent of your report (they use the same data set, but they aren't forced into using the same groupings, summaries, etc) and can create their own summaries.

You might have to build a manual cross-tab, or you may be able to use a cross-tab, but without knowing anything about your environment, data or the requirements, it's all too speculative.

-k

 

[kenhamady]

The summaries in a cross-tab can not be used in calculations to do percentages. There is some limited support for percentages in v9, but only precent of total.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[msanders67]

Thank you Ken - that is the conclusion I had drawn, but it helps to get an affirmation!

Margo

 

[shelby55]

Actually, I use percentages all the time in cross tabs though it does require a "work around". For example, if I want to know % of patients admitted through the ER, I create a total cases field, and then the "ThruER" field. Then I make a field called "ThruER%" which is If ThruER > 0 then ThruER/TotalCases*100 else 0. When placing "ThruER%" in the crosstab the summary type is "weighted average" and the field it is a weighted average of is "totalcases". Unless I'm not understanding the question, this should work.

 

[kenhamady]

Shelby,

That is a neat trick and will work in many situations. I will have to share that with my newsletter readers. Let me know (offline) if you if you would like to be credited by name rather than by handle.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[lbass]

Shelby, I was interested in your solution, but couldn't follow it. Could you be more specific about what you are using for the ThruER and TotalCases "fields"? I think they must be formulas, and in the latter case, a summary formula, but I couldn't use a formula with a summary in a crosstab...

-LB

 

[kenhamady]

LBass,

I played with this quite a bit to see what the limits are and I got it to work. There are no summary formulas used, but you need three numeric columns to work with.

1) The Total Field can be any numeric, like a Quantity field or a formula that adds several columns together to get a combined number. It can be a formula but doesn't have to be.

2) A conditional formula that is the first field but only in records that meet a condition. ie:
If <condition>
Then TotalField
Else 0

3) The percentage field, which is:
If ConditionFormula > 0
then ConditionFormula/TotalField*100
else 0

You put these three into the cross tab as summarized fields. The first 2 get summed and the last is a weighted average of the third weighted by the first.

The only limitation I found is that the condition field has to be a subset of the values in the total field. I couldn't, for instance, get this to work where I had Prior year, Current Year and I wanted a percentage increase. For that kind of percentage you might want to look at another thread that uses a different technque:

thread149-865741

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[lbass]

So "TotalField" means "FieldtoTotal"--that was what was confusing me. Thanks (*) for a very concise description, Ken, and thanks to shelby (*) for the original solution.

-LB