Topic
  • 1 reply
  • Latest Post - ‏2013-11-14T19:48:02Z by OpherB
tmhoory
tmhoory
1 Post

Pinned topic Calculate An Average for Rolled Up Data in Report Studio

‏2013-11-14T13:23:12Z |

Hi Experts. 
I need your help ASAP 
I have a report which shows the work instructions per hour (total work instructions/total hours) per picker. The report then calculates a summary at the bottom 

 

Picker  Work Ins/hr 
picker1 2
picker2 3
picker3 4
average  3


I then need to pull the average work instructions per hour for all the pickers in another report without grouping it per picker. i.e, show value 3 in a singleton. If I use the same data item as in the detailed report it gives me a different value to that in the detail report. I suspect because it is a calculated value that is why it changes. I suppose a FOR/WHILE loop would have done the trick but my research tells me I can't use that in Cognos 
I have tried every trick in my bag with no success. 
Please help me as this report is due tomorrow otherwise my head is on the block.

  • OpherB
    OpherB
    664 Posts

    Re: Calculate An Average for Rolled Up Data in Report Studio

    ‏2013-11-14T19:48:02Z  

    I don't entirely understand what you are trying to do, but it seems to me that you need to define how you want the total and average calculated by nesting the functions and defining where you want the aggregation done. Otherwise, Report Studio will do default aggregation at the lowest level of detail.

    Something like this should work, but you'll need to test with your data to see what exactly is needed:

    average( total([work] for [picker]) for report)

    Good luck,
    Opher