Michael_D
asked on
Percentage in Crosstab
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(F LD1)|
| | |Sum(FLD2)|Sum(FLD2)|Sum(F LD2)|
| |------------------------- ---------- ---------- ---------- -|
| |SubGroup2 |Sum(FLD1)|Sum(FLD1)|Sum(F LD1)|
| | |Sum(FLD2)|Sum(FLD2)|Sum(F LD2)|
-------------------------- ---------- ---------- ---------- ---------- --
|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.
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(F
| | |Sum(FLD2)|Sum(FLD2)|Sum(F
| |-------------------------
| |SubGroup2 |Sum(FLD1)|Sum(FLD1)|Sum(F
| | |Sum(FLD2)|Sum(FLD2)|Sum(F
--------------------------
|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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So my "picture" should be like this
-------------------------- ---------- ----~~~--- ---------- ---------- -----
| | Cat1 |~~~| CatN |Total |
-------------------------- ---------- ----~~~--- ---------- ---------- -----
|Group 1|SubGroup1 |Sum(FLD1)|~~~|Sum(FLD1)|S um(FLD1)|
| | |Sum(FLD2)|~~~|Sum(FLD2)|S um(FLD2)|
| |------------------------- ----~~~--- ---------- ---------- ----|
| |SubGroup2 |Sum(FLD1)|~~~|Sum(FLD1)|S um(FLD1)|
| | |Sum(FLD2)|~~~|Sum(FLD2)|S um(FLD2)|
-------------------------- ---------- ----~~~--- ---------- ---------- -----
|Group 2| ..... and so on
--------------------------
| | Cat1 |~~~| CatN |Total |
--------------------------
|Group 1|SubGroup1 |Sum(FLD1)|~~~|Sum(FLD1)|S
| | |Sum(FLD2)|~~~|Sum(FLD2)|S
| |-------------------------
| |SubGroup2 |Sum(FLD1)|~~~|Sum(FLD1)|S
| | |Sum(FLD2)|~~~|Sum(FLD2)|S
--------------------------
|Group 2| ..... and so on
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)|S um(FLD1)|
| | |Sum(FLD2)|~~~|Sum(FLD2)|S um(FLD2)|
| | |Sum(FLD3)|~~~|Sum(FLD3)|S um(FLD3)|
| | |Avg (FLD3)|~~~|Avg (FLD3)|Avg (FLD3)|
| |------------------------- ----~~~--- ---------- ---------- ----|
| |SubGroup2 |Sum(FLD1)|~~~|Sum(FLD1)|S um(FLD1)|
| | |Sum(FLD2)|~~~|Sum(FLD2)|S um(FLD2)|
| | |Sum(FLD3)|~~~|Sum(FLD3)|S um(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.
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)|S
| | |Sum(FLD2)|~~~|Sum(FLD2)|S
| | |Sum(FLD3)|~~~|Sum(FLD3)|S
| | |Avg (FLD3)|~~~|Avg (FLD3)|Avg (FLD3)|
| |-------------------------
| |SubGroup2 |Sum(FLD1)|~~~|Sum(FLD1)|S
| | |Sum(FLD2)|~~~|Sum(FLD2)|S
| | |Sum(FLD3)|~~~|Sum(FLD3)|S
| | |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.
ASKER
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?