Technical Questions
 Crystal Reports Forum : Crystal Reports 9, X, XI, 2008, 2011 : Technical Questions
Message Icon Topic: Perecentage summary in crosstab Post Reply Post New Topic
Author Message
aleplgr
Newbie
Newbie


Joined: 09 Dec 2006
Online Status: Offline
Posts: 23
Quote aleplgr Replybullet Topic: Perecentage summary in crosstab
    Posted: 14 Dec 2006 at 7:58am
Hi, there's a bug when you try to show the percentage summary using decimals in a crosstab, it shows the decimal fiels with a zero value. The bug is reported in the document I post here. They also propose a solution for it. But this solution doesn't work (or I can't make it work) when the rows and columns of the cross tab are defined using Specific Order (in this case the groups are defined by the user) and the solution they suggest here doesn't work.
 
How can I do to fix this decimals problem? because I need to show decimals in the Percentage field.
 
 
 

10/4/2002 3:24 PM Copyright 2002 Crystal Decisions, Inc. All Rights Reserved. Page 1

Crystal Reports

Displaying PercentOfCount and/or PercentOfDistinctCount

summaries in a cross-tab

Overview

In a Crystal Reports (CR) 9 cross-tab, the summary options,

PercentOfDistinctCount and PercentOfCount, do not display data correctly.

Any numbers after the decimal point are rounded down to zero.

This document will explain why this decimal precision issue occurs and will

provide workarounds to display the data correctly.

Contents

INTRODUCTION............................................................................................2

WHY DO INCORRECT VALUES APPEAR?........................................................2

DISPLAYING PERCENTOFCOUNT/PERCENTOFDISTINCTCOUNT SUMMARIES..2

PercentOfDistinctCount Summaries ............................................................3

Set up the report................................................................................................. 3

Create a formula ................................................................................................ 4

Create a Cross-tab.............................................................................................. 4

PercentOfCount Summaries.........................................................................4

Create a formula ................................................................................................ 5

Create a Cross-tab.............................................................................................. 5

CONTACTING CRYSTAL DECISIONS FOR TECHNICAL SUPPORT......................5

Crystal Reports 9

10/4/2002 3:24 PM Copyright 2002 Crystal Decisions, Inc. All Rights Reserved. Page 2

Introduction

In CR 9, there is a limitation with displaying PercentOfCount or

PercentOfDistinctCount summary values in a cross-tab. The decimal portion

of the number is rounded down to zero and therefore displays what appears to be

an incorrect value.

This document will:

explain the reason for this issue.

provide a workaround to display a PercentOfDistinctCount in a crosstab

with correct decimal precision.

provide a workaround to display a PercentOfCount in a cross-tab with

correct decimal precision.

Why do incorrect values appear?

This is a known issue and has been assigned track ID ADAPT00104013.

Inserting a field into the Summarized Fields box of a cross-tab, and choosing to

display as a PercentOfCount summary or PercentOfDistinctCount summary

will display the values without decimal precision. For example, if the value

should be displayed as 35.64%, the cross-tab will display the value as 35.00%.

This issue has been addressed in a hot fix. The solution to fix this problem is to

display zero decimal places. For example, if the value should be displayed as

35.64%, the value will instead be displayed with no decimals as 35%.

Why does this solution not address the underlying problem? The reason the

exact decimal precision is not fixed is because CR stores PercentOfCount and

PercentOfDistinctCount summaries as integers. To display the decimal

portions of the value, the data-type would need to be changed from an integer to

a floating point. If such an architectural change were made to CR and released

as a product update, reports created and saved using a build of CR with this

update applied could become corrupted if opened using a build of CR without

the update applied. This would render these reports unusable and corrupted

reports would need to be recreated. Therefore, it was decided not to fix the

underlying issue but to round the numbers with zero decimal places instead.

There are workarounds to display the exact decimal precision in a cross-tab.

Displaying PercentOfCount/PercentOfDistinctCount

Summaries

Both of the following examples:

Crystal Reports 9

10/4/2002 3:24 PM Copyright 2002 Crystal Decisions, Inc. All Rights Reserved. Page 3

use the Access sample database, Xtreme.mdb, which is installed with

Crystal Reports.

are based on the Customer table.

have the Customer.Country field filtered to show only Australia.

PercentOfDistinctCount Summaries

To correctly display a PercentOfDistinctCount summary, summarize a formula

field that displays the PercentOfDistinctCount rather than summarizing a

database field in the cross-tab

To make this formula, the report needs to be grouped on the same fields as the

cross-tab’s row and column fields.

Note: If it is not possible to modify the grouping of the main report, the cross-tab can be displayed in a subreport, which is set

up with the appropriate grouping and is only used to display the cross-tab.

However, cross-tabs in subreports do not display virtual pages. If the cross-tab grows larger than the width of the page,

data to the right of the page width will not be displayed. There is no workaround for this issue.

For more information on Subreports refer to CR’s White Paper; scr_subreports.pdf at:

http://support.crystaldecisions.com/docs

Set up the report

The report must be grouped on the same fields used in the cross-tab. For

example, if the cross-tab needs to display the PercentOfDistinctCount of the

Customer ID field with a Country row and Region column, perform the

following steps:

1. The first group is based on the cross-tab’s ‘row’ field. On the Insert menu,

click Group. From the top drop-down box select Customer.Country. Click

OK.

2. The second group is based on the cross-tab’s ‘column’ field. On the Insert

menu, click Group. From the top drop-down box select Customer.Region.

Click OK.

3. Insert the field that requires the PercentOfDistinctCount summary to your

details section. On the View menu click Field Explorer. Expand

Database Fields then expand Customer. Add the Customer.Customer ID

field to the Details section.

4. Right-click the Customer.Customer ID field and click Insert > Summary.

From the Calculate this summary drop-down box click Distinct count.

From the Summary location drop-down box click Group #2:

Customer.Region. Select the Show as a percentage of check box and

click Group #1: DistinctCount of Customer ID.

Crystal Reports 9

10/4/2002 3:24 PM Copyright 2002 Crystal Decisions, Inc. All Rights Reserved. Page 4

Create a formula

The next step is to create a formula, which displays the

PercentOfDistinctCount.

1. Create a formula and name it Distinct_Count.

2. From the Report Fields box double-click the Summary field created in the

previous steps. The formula will appear as:

PercentOfDistinctCount ({Customer.Customer ID}, {Customer.Region}, {Customer.Country})

3. Click the Save button then click the Close button.

Create a Cross-tab

The final step is to create a cross-tab to use the formula created in the previous

steps as the summarized field.

1. On the Insert menu click Cross-Tab. The Cross-tab Expert dialog box

will appear.

2. From the Available Fields box move Customer.Country to the Rows box.

3. From the Available Fields box move Customer.Region to the Columns

box.

4. From the Available Fields box move the @Distinct_Count formula, created

in the previous steps, to the Summarized Fields box.

5. Click the @Distinct_Count formula in the Summarized Fields box then

click the Change Summary button. From the Calculate this summary

drop-down box click Maximum.

6. Click the OK button to close the Cross-Tab Expert dialog box then insert

the cross-tab in the Report Header section.

7. Suppress every other section in the report.

Now when the report is previewed, the PercentOfDistinctCount summary in

the cross-tab will appear with correct decimal accuracy.

PercentOfCount Summaries

To correctly display a PercentOfCount summary, summarize a formula field

that displays a value of 1 in the cross-tab instead of summarizing a database

field. When this formula is summed it will display the same total as a count of

the field would return. Unlike a PercentOfCount summary, a PercentOfSum

summary will display correct decimal precision.

Unlike the previous example, the report

does not need to be set up with the

same grouping as the cross-tab in order

to display the correct value.

Crystal Reports 9

10/4/2002 3:24 PM Copyright 2002 Crystal Decisions, Inc. All Rights Reserved. Page 5

Create a formula

The first step is to create a formula, which outputs a value of 1.

1. Create a formula and name it Count. Enter the following:

WhileReadingRecords;

1

2. Click the Save button then click the Close button.

Create a Cross-tab

The final step is to create a cross-tab that uses the formula created in the

previous steps as the summarized field.

1. On the Insert menu click Cross-Tab. The Cross-tab Expert will appear.

2. From the Available Fields box move Customer.Country to the Rows box.

3. From the Available Fields box move Customer.Region to the Columns

box.

4. Add the @Count formula created in the previous steps to the Summarized

fields box.

5. Click the @Count formula in the Summarized Fields box then click the

Change Summary button. The Edit Summary dialog box will appear.

6. From the Calculate this summary drop-down box click Sum.

7. Check the Show as a percentage of check box and, from the drop-down

box click Total: Sum of Customer ID.

8. Select the Column radio button.

9. Click the OK button to close the Edit Summary dialog box then click the

OK button to close the Cross-Tab Expert dialog box.

10. Insert the cross-tab in the Report Header section.

Now, when the report is previewed the PercentOfCount summary in the crosstab

will appear with correct decimal accuracy.

Contacting Crystal Decisions for Technical Support

We recommend that you refer to the product documentation and that you visit

our Technical Support web site for more resources.

Self-serve Support:

http://support.crystaldecisions.com/

Crystal Reports 9

10/4/2002 3:24 PM Copyright 2002 Crystal Decisions, Inc. All Rights Reserved. Page 6

Email Support:

http://support.crystaldecisions.com/support/answers.asp

Telephone Support:

http://www.crystaldecisions.com/contact/support.asp

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.