cognoscognos-determinants

Cognos Double Counting

I have the following table:

Billing Account Number  Credit Alert Number Account Balance Full Date
00005884                1-400WHA            13111.80        2013-08-12
00005884                1-4WTV4E            13111.80        2013-08-12
00005884                1-4TG3WJ            13111.80        2013-08-12
00005884                1-43GBO9            13111.80        2013-08-12
00005884                1-5X817T            13111.80        2013-08-12
00005884                1-4AFO7S            13111.80        2013-08-12
00005884                1-50PJWY            13111.80        2013-08-12
00017988                NULL                105.86          2013-08-12
00018713                NULL                118.00          2013-08-12
00020032                NULL                7316.06         2013-08-12

As you can see I have a repeating Billing Account Number as well as that Account Balance. When in Cognos I only bring in the Billing Account Number and the Account Balance it automatically sums up the Account Balance which is wrong. How do I setup my determinants to prevent double counting?

If I set the Regular Aggregate rule to be average for Account Balance measure then it'll work for most reports but still breaks on others that have columns selected etc.

Solution

  • Determiantns
    Determinants are set on framework manager.
    I don't know how your model looks like, but it is good practice to put determinants definitions on database layer (physical tables mapping and relation).
    You can find a great example in the great_outdoors_warehouse sample model. (located in c10_directory\webcontent\samples\models\great_outdoors_warehouse).
    Just make sure you have installed Cognos samples where you installed framework manager.
    In this model, under Database view->Go data->GO_TIME_DIME, right click on and choose Edit Definition. In the Determinants tab, you will see:
    enter image description here So, I guess you should put the Billing Account Number as the key, and then Account Balance as an attribute and mark it as group by.
    Just bare in mind, that order of the determinants do matter.
    So, you must put this defintion (Account Number) before the Uniquely Identified deteminant (if such exist).
    A great Cognos documentation regarding determinants can be found here:
    Framework manager user guide - determiantns

    Alternative solution
    A second option would be, change its usage type from fact to attribue: enter image description here AFAIK, by default it will not sum up in a list report.
    For saftey, you can change the aggregate rule to unsupported.