Solved

Percentage in Crosstab

Posted on 2004-09-28
4
792 Views
Last Modified: 2010-08-05
Hi, Experts

We are using CR 8.5 and MS SQL Server 2000

I have a crosstab in the report.
It looks like this

--------------------------------------------------------------------
|                              |    Cat1     |    Cat2      |Total        |
--------------------------------------------------------------------
|Group 1|SubGroup1 |Sum(FLD1)|Sum(FLD1)|Sum(FLD1)|
|           |                 |Sum(FLD2)|Sum(FLD2)|Sum(FLD2)|
|           |--------------------------------------------------------|
|           |SubGroup2 |Sum(FLD1)|Sum(FLD1)|Sum(FLD1)|
|           |                 |Sum(FLD2)|Sum(FLD2)|Sum(FLD2)|
--------------------------------------------------------------------
|Group 2| ..... and so on


FLD1 - is number of total attempts to do smth.
FLD2 - is number of fails

I need instead of those fields show the Percentage of success so if FLD1= 100 and Fld2=3 I need to Display 97%

But I can't use totals in formula in Crosstab. Is there is a way to do it?

Thanks in advance.
0
Comment
Question by:Michael_D
  • 2
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 12172207
In order to accomplish your goal in CR 8.5, you need to create a manual crosstab.  You can't use the wizard.

1)  Create groups as you have
2)  Create Column Labels
3)  Create formulas that will be placed in the detail section of the report.  These formulas will be something like:

@Cat1_Fld1
If
  Category = Cat1
Then
  FLD1
Else
  0

Based on your example, you'll need to create 4 formulas:  Cat1_Fld1, Cat2_Fld1, Cat1_Fld2 and Cat2_Fld2.  Each formula will be similar to the one shown above.

4)  Right-click on each of these formulas--once they're in the detail section--and Insert Summary.  You're going to Sum the values (assuming FLD2 and FLD2 have numeric values) both the subgroup and the primary group.

5)  Hide or suppress the detail section.

6)  Create two formulas at the subgroup level, each of which will calculate the percentage for its respective column.  Each formula will look something like this:

@Cat1_subgroup_percentage
If
  Sum(@Cat1_Fld1,subgroupfield) <> 0
Then
  ((Sum(@Cat1_Fld1,subgroupfield) - Sum(@Cat1_Fld2,subgroupfield)) / Sum(@Cat1_Fld1,subgroupfield)) * 100
Else
  0

7)  Next you'll create formulas for the Row totals at the subgroup level.  The row totals formulas will look like this (alternate, you can just right-click on the Fld1 and Fld2 fields and Insert summary in the appropriate group footers):

@Subgroup_fld1_row_total
//Assuming that there are only two categories
//you can simply take a sum of FLD1, which will contain values
//for each category
Sum(FLD1,subgroupfield)

@Subgroup_fld2_row_total
//Assuming that there are only two categories
//you can simply take a sum of FLD2, which will contain values
//for each category
Sum(FLD2,subgroupfield)

8)  Next, you'll create a row percentage similar to the following:

@Subgroup_row_percentage
If
  Sum(Fld1,subgroupfield) <> 0
Then
  ((Sum(Fld1,subgroupfield) - Sum(Fld2,subgroupfield)) / Sum(Fld1,subgroupfield)) * 100
Else
  0

9)  You'll need to repeat these steps to create summaries at the primary group level and at the Grand Total Level

~Kurt

0
 
LVL 13

Author Comment

by:Michael_D
ID: 12172669
Thank you very much, Kurt.
I know how to do it If I only have fixed number of columns. The only advantage of crosstab  is being dynamic. I can't use your method because I want to be able to enlarge  my crosstab dynamicly based on data in the table.
You told that this is impossible in CR 8.5. Can I Do it with CR9 or 10?
0
 
LVL 13

Author Comment

by:Michael_D
ID: 12172695
So my "picture" should be like this

----------------------------------------~~~----------------------------
|                              |    Cat1     |~~~|    CatN      |Total        |
----------------------------------------~~~----------------------------
|Group 1|SubGroup1 |Sum(FLD1)|~~~|Sum(FLD1)|Sum(FLD1)|
|           |                 |Sum(FLD2)|~~~|Sum(FLD2)|Sum(FLD2)|
|           |-----------------------------~~~---------------------------|
|           |SubGroup2 |Sum(FLD1)|~~~|Sum(FLD1)|Sum(FLD1)|
|           |                 |Sum(FLD2)|~~~|Sum(FLD2)|Sum(FLD2)|
----------------------------------------~~~----------------------------
|Group 2| ..... and so on
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12175063
Actually, there is something you can use formulas in crosstabs in 8.5.  In your scenario, you want to calculate the percent of successful attempts, correct?  I don't know how your data is structure, so I'm going to make some assumptions for the sake of the example.

FLD1 identifies an attempt and is Counted in the Crosstab
FLD2 identifies a failure and is a boolean of some sort (yes/no, 1/0, etc..)

In the Format Cross-Tab dialogue box, click the <New Formula> button.  You will create a formula that will help you calculate the successful attempts.  It may look something like this:

@Successful_Attempts
If
  {table.field2} = No//indicating that the 'Failed attempt' flag is no, therefore a 'Success'
Then
  1
Else
  0

Next, insert this new formula field (@Successful_Attempts) into the Summarized fields twice - once to be summed as the number of successful attempts and once to be averaged.  Your report will look something like this (@Successful_Attempts is referred to as FLD3):

----------------------------------------~~~----------------------------
|                              |    Cat1     |~~~|    CatN      |Total        |
----------------------------------------~~~----------------------------
|Group 1|SubGroup1 |Sum(FLD1)|~~~|Sum(FLD1)|Sum(FLD1)|
|           |                 |Sum(FLD2)|~~~|Sum(FLD2)|Sum(FLD2)|
|           |                 |Sum(FLD3)|~~~|Sum(FLD3)|Sum(FLD3)|
|           |                 |Avg (FLD3)|~~~|Avg (FLD3)|Avg  (FLD3)|
|           |-----------------------------~~~---------------------------|
|           |SubGroup2 |Sum(FLD1)|~~~|Sum(FLD1)|Sum(FLD1)|
|           |                 |Sum(FLD2)|~~~|Sum(FLD2)|Sum(FLD2)|
|           |                 |Sum(FLD3)|~~~|Sum(FLD3)|Sum(FLD3)|
|           |                 |Avg (FLD3)|~~~|Avg (FLD3)|Avg  (FLD3)|
----------------------------------------~~~----------------------------

The downside is that you can't really format the average to look like a true percentage.  It will come out looking like .97 instead of 97%.  The upside is that you can basically calculate percentages at the column level.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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