Work around Calculated Field limitation
I have a pivottable I want to publish on the web. But the
web components does not support calculated fields. Unfortunately, the calculated fields in my pivottable are the heart of the report. So now I'm trying to think of a way around it. Here's the scenario: The data for the pivottable is like this: Instance Name Pass Fail 123 Joe 1 0 124 John 1 0 125 Bill 0 1 126 Joe 1 0 127 Bill 1 0 128 Lucy 0 1 The pivottable pulls in the Name in the Row area and then Sums the Pass and Fail fields in the Data area. I include the Instance field in the range for the pivottable so users can drill down and see those numbers. So the pivottable looks like: Name Pass Fail Joe 2 0 John 1 0 Bill 1 1 Lucy 0 1 I add a calculated field that displays the percentage of Pass for each Name: Name Pass Fail % Pass Joe 2 0 100% John 1 0 100% Bill 1 1 50% Lucy 0 1 0% Of course I can't do this because the web version will not support that calculated field. Does anyone know another way to get the result? I can't put the formula in the database query because there are no aggregate functions there (because each record is made unique by the Instance field). I tried doing the formula on each record and then using the pivottable to average those percentages, but the results are not accurate. Any ideas? tod |
Work around Calculated Field limitation
Dump the Pass field into the data area 3 times.
Then set the field options as :Sum/Count/Average Name the fields Passed/Taken/Success% that should solve it. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tod" wrote: Instance Name Pass Fail 123 Joe 1 0 124 John 1 0 125 Bill 0 1 126 Joe 1 0 127 Bill 1 0 128 Lucy 0 1 |
All times are GMT +1. The time now is 01:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com