Can to calculate percentage?

[posywang]

Hi,
I am new to Cognos Report Studio and would like to get some help on calculation below.

Resource   Month   CurrMth   Forecast Area   Current Area   Hours   Percentage
Amy   3   4   Finance   Finance   80   80%
Amy   3   4   Finance   Finance   20   20%
Amy   5   4   Finance   Finance   40   100%
Amy   5   4   Bond   Finance   60   100%
John   5   4   IT   IT   50   100%
John   7   4   IT   IT   50   100%


I realized the rule is a bit complicated than what I posted earlier so I modified the post.

If CurrMth is greater than Month, Current Area column will be used; otherwise, Forecast Area column will be used.

The part that I figured out is total([Hours] for [Resource],[Month],[Current Area])/total([Hours] for [Resource],[Month])

I tried to use if statement but failed. I was trying to use the following:

if([CurrMth]>[Month]) then (total([Hours] for [Resource],[Month],[Current Area])/total([Hours] for [Resource],[Month]) else
(total([Hours] for [Resource],[Month],[Forecast Area])/total([Hours] for [Resource],[Month])

How can this be done? Let me know if there's any additional question. Thanks!

[Lynn]

Hi,
I am new to Cognos Report Studio and would like to get some help on calculation below.

Resource   Month   Hobby   Hours   Percentage
Amy   Jan   Fishing   80   80%
Amy   Jan   Movie   20   20%
John   Jan   Fishing   50   100%
John   Feb   Gardening   50   100%

I'd like to get percentage of time spent on each hobby per resource per month. For example, Amy spent 80% on Fishing in January, and 20% on movie. John spent 100% of his time on Fishing in Jan, and 100% on Gardening in Feb.

How can this be done? Let me know if there's any additional question. Thanks!

Are you reporting with a relational package or a dimensional package?

If it is relational take a look at the total function with a "for" clause:

Code Select Expand


[Hours] / total ( [Hours ] for [Resource], [Month] )


[posywang]

It works!!!! I create a new query using the same expression and it works. Not sure why but at least it works now.