If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Solved - Take an average in Crosstab without showing detail?

Started by FirstStruck, 15 Aug 2011 12:18:35 PM

Previous topic - Next topic

FirstStruck

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!

PRIT AMRIT

Not sure if I understood your requirement fully though, What if just 'CUT' the column [Pay Date] from the layout?

Does it help?


FirstStruck

#2
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!

FirstStruck

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.