Grouping training hrs. performed in a month for each qual. that a person holds

[rhythmz]

I am sure this topic has been covered but was unable to find any posting that I could understand as being similar to my own issue. So I apologize if this is the case.

SCENARIO:
I am building a report based on SWAT training monthly stats. Here is a column\header breakdown:

1.) Group by DISTRICT
2.) Group by personnel type header (ie. CQB, Hostage) - a person can only be one type
3.) Group by squad position header (ie. Gunner, Sniper, etc.) - a person can be qual'd in multiple eqip's
4.) Group by Year column (ie. 2009, 2008)
5.) Group by Month column (ie. JAN, FEB, MAR)
6.) Person's Name||rank||ID_num column (ie. DOE J SGT 1234)
7.) Tracked Qualification for Person - person can be dual qual'd (ie. SHTGN, MP5, PISTOL, SCOPE)
8.) Total number of hrs. training performed for EACH tracked qual. in a specific month

***I will then obtain an average of all reported persons for a month and provide a standard-deviation from the average for each person as well as for the month compared to all other reported months (conditional color code based on set thresholds of tolerance). I can handle this fine outside of my issue***

ISSUE:

If a person is dual qualified (multiple qual's), when I attempt to display the total hrs. performed in a specific qual for the person, the total value doubles...then that double value displays for each of the person's quals for the month.

For instance:
You are a CQB (close quarters combat) squad as a Gunner  which requires you to do so many hours range time (rng_tm table column) to maintain your SHTGN & PISTOL (qual_type table column) qualification.

In JAN 2009, you did 15 hrs. SHTGN and 10 hrs. PISTOL range time. However, on my report you are showing 30 hrs. for both SHTGN & PISTOL row entries under your name.

QUESTION:

I ASSUME that I need to create a "total(rng_tm)" calc for each level of grouping I have. Do I need to build a calc for the lowest granularity and pass that function into a "total()" for the next level and so on? I have seen instances where a function is defined for multiple levels:

ie. total([rng_tm] for pers_type, sqd_pos, year, month, name, qual_type)

I do not know what this means and will this resolve my issue by providing multiple levels of granularity for each level I need to obtain for further averaging operations in the report? How do I eliminate the doubling of a total and then it replicating itself for just the quals within a certain person?

I surely hope I have spelled this out correctly and logically but will check back periodically to see if more info is needed for help. Thank you!

[blom0344]

1. Check tabular data in any case.
2. Check syntax of the SQL generated by Cognos.
3. Run trace against database to check what is executed)

4. The for option is only usefull when calculating totals on a higher level than data fetched. If your report is supposed to show details at the same grain as the tabular data-set , then the regular aggregate on the measure should be enough.

Too high totals in the tabular set are a clear indication that there is an issue with cardinality. Your fact should have N--1 cardinality with dimensions.

I suspect that 

a person can be qual'd in multiple eqip's

may be the real cause of the pain