I need to report on a table that has many columns where the data type is boolean "decision" values of true/false. I only need the "true" to be counted,
Example is:
EMPLOYEE answers inbound phone call and logs the
CALLER name. Each employee gets credit for a call as long as
CALLER is not blank & 1 decision field
D1,D2, or
D3 is true. This pivot reports the totals every week.
table data
| EMPLOYEE |
|
D1 |
D2 |
D3 |
| EmployeeA |
Tom |
FALSE |
TRUE |
FALSE |
| EmployeeA |
Nick |
FALSE |
TRUE |
TRUE |
| EmployeeB |
Henry |
TRUE |
TRUE |
TRUE |
| EmployeeB |
Scott |
TRUE |
TRUE |
FALSE |
| EmployeeC |
Mary |
FALSE |
TRUE |
FALSE |
| EmployeeC |
Susan |
TRUE |
FALSE |
TRUE |
If I were to filter column
D1 to true only which would count total of 3 (which is correct, 3 TRUE's) Then....
CALLER total would be 3, not 6.
D2 total
would be 2, not 5
D3 total
would be 2, not 3
pivot (what I am trying to have data report as)
|
CALLER
|
D1
|
D2
|
D3
|
| Emp A
|
2
|
|
2
|
1
|
| Emp B
|
2
|
2
|
2
|
1
|
| Emp C
|
2
|
1
|
1
|
1
|
| Total
|
6
|
3
|
5
|
3
|
How do I use the reports pivot or chart to total "true's" for each column and evaluate "false" as a null value or whatever...just not count it.
I had an idea to make hidden formula fields for every Boolean field and have the formula field evaluate the column and return a 1 for true and "blank" for false and then use the alternate formula field in reporting. Don't know how to do that?
If I could have the decision fields return 1 for true and "blank" for false and still be a "check box" in the form that would be great. I don't know how to do that?