Hello everyone,
I have a crosstab with Store Name down the left and Pay Date across the top. The center data is a query calculation, COUNT([Employee Name]). This returns a count of the number employees paid for each Pay Date per Store.
I have a Aggregate at the end to show the Average number paid. Is there anyway to make it only show the average without the detail? I would love to get it in a List format instead of a crosstab. I tried a query calculation for AVERAGE(Employee Name) but this did not work.
Thank you for any suggestions!
Not sure if I understood your requirement fully though, What if just 'CUT' the column [Pay Date] from the layout?
Does it help?
Quote from: PRIT on 15 Aug 2011 10:31:57 PM
Not sure if I understood your requirement fully though, What if just 'CUT' the column [Pay Date] from the layout?
Does it help?
Thanks for your reply. I have tried your method before- when you cut the column heading, the aggregate is automatically removed as well. But you have the right idea; if the aggregate column would remain this would be exactly what I was looking for.
Update: I unlocked the crosstab and removed the column heading and that left the average aggregate but when the report ran, all this did was remove the column headings for the detail -the numbers remained. If you try to cut the numbers, it says "You can't delete objects of type "Crosstab Intersection"".
I also tried change the properties pane of the Pay Date to Box Type None. This did the exact same thing as above.
Thanks again for any help!
I figured something out and wanted to share.
Instead of using an aggregate to calculate the average, I manually calculated it with query calculations. I used Count(Pay Date) to give me the total number of paychecks and Count(distinct Pay Date) to give me the total number of pay dates. Used a 3rd query calc to divide the 2 and bam- same average as the crosstab.