Solved

Crosstab Percentage Crystal reports

Posted on 2009-07-01
8
2,691 Views
Last Modified: 2013-11-15
I have a cross tab report in CR 10 that shows the following data.

APPNAME                   MET    NOT MET     TOTAL
APPLICATION1            10        90            100
APPLICATION2            85        22            107
APPLICATION3            20        40            60

I need to display the percentage below each value.. ie
APPLICATION1  10% 90% 100%

I have selected the "show as a percentage" of option in the crosstab but the percentage never add up to 100% and with some data it shows 0%

I've read that there is a bug in crystal with this function, but have tried using a formula to show percentage but this is not working either.. I can post screen shots if required.

thanks
Craig.
0
Comment
Question by:cjonline
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24756392
what are the formulas for calculating the met and nonmet values?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24759415
I haven't seen anything that indicates a problem and I have built crosstabs that work that way with no problems.

I agree.  Need to see the formulas and perhaps some sample data.

mlmcc
0
 

Author Comment

by:cjonline
ID: 24761178
Hi,
Here is the formula for MET/NOT MET

if isnull({sp_SAIC_ResponseTime;1.SAIC_CONTACTED}) then 2
else
if (isnull({sp_SAIC_ResponseTime;1.responsetime}) or {sp_SAIC_ResponseTime;1.responsetime} = 0) then
1
else
if {@BusinessMins} <= {sp_SAIC_ResponseTime;1.responsetime} then
1
else
0

I have attached a snapshot of the crosstab, as you can see its only totalling to 99%
also I have attached a screenshot of the crosstab details.
thanks

crosstab1.bmp
crosstab2.bmp
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Accepted Solution

by:
cjonline earned 0 total points
ID: 24764143
Thanks, I've found another of doing it...

Craig.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24766080
The problem is that you aren't displaying any decimals in the cross tab.  

For instance in the CRM line the values are
  93       1    5
They probably are really
  93.2    1.4     5.4

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24769102
FWIW, I think mlmcc is basically right and the numbers don't appear to add up because you're not seeing the decimal places.  However, there are two additional complications:

 1) CR appears to be truncating the decimals, instead of rounding.  For example, 1 / 17 (last column total on the WEB row) would be 5.88 %, which should clearly round to 6 %, but CR shows 5.

 2) Even if you wanted to show the decimals, you can't.  You can try to change the field format to show decimal places, but CR 10 simply refuses to show them on a count or distinct count that's shown as a % of the total in a cross-tab.  I don't know why, but it won't do it.  When I managed to force it to show decimal places, they were 0, so apparently the values are rounded (or truncated) internally.


 Just out of curiosity, what other way did you find?  It could help someone else in the future with a similar problem.

 James
0
 

Author Comment

by:cjonline
ID: 24770790
Hi,
I created 3 formula's :

- SLAMetCounter

if {@SLA Met} = 1 then
1
else
0

 - SLANotMetCounter
if {@SLA Met} = 0 then
1
else
0

and

- Crosstab%

 if {@SLAMetCounter} > 0
    then {@SLAMetCounter}/DistinctCount ({@Callid})*100
    else
 if {@SLANotMetcounter} > 0
    then {@SLANotMetcounter}/DistinctCount ({@Callid})*100
       else 0


Then I summarized in the crosstab with - Percent of Sum of (@Crosstab%)
This give me the correct percentage with the decimal places and no rounding.

Craig.
0
 
LVL 34

Expert Comment

by:James0628
ID: 24776658
Thanks for sharing.

 I take it that the @SLAMetCounter and @SLANotMetcounter counts occur in separate groups in the cross-tab (the "SLA Met" and "SLA Not Met" columns).  The @Crosstab% formula tries to handle both and I don't see how that could work unless it was only going to have one count or the other each time it was evaluated.

 James
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question