In our environment we have a Contract dimension with two levels:
Contract Type
Contract Type
Contract
Contract Name
Contract Code
Contract ID
A Contract Type can have one or more Contracts
A Contract Name can have one or more Contract Codes
A Contract Code can have one or more Contract IDs
Next to that we have a measure:
Volume
If I put it in a query and group by Contract Type it can look as follows:
Contract Type Contract Name Contract Code Volume
A A_First A_First_Code22 15
A A_First A_First_Code23 5
A A_Second A_Second_Code51 20
A A_Second A_Second_Code54 10
A 50
B B_First B_First_Code12 10
B B_First B_First_Code13 30
B 40
Total 90
However, if I remove the Contract Type column and group by Contract Name I get the following:
Contract Name Contract Code Volume
A_First A_First_Code22 15
A_First A_First_Code23 5
A_First 5
A_Second A_Second_Code51 20
A_Second A_Second_Code54 10
A_Second 10
B_First B_First_Code12 10
B_First B_First_Code13 30
B_First 30
Total 90
Now the total per Contract Name is no typing mistake! It always takes the last value. How can I solve this in Framework Manager?
The thing I thought of is: taking the Contract Name, Contract Code and Contract ID and creating several levels out of it. With Contract ID being the lowest level. That can then have all the rest of the attributes. But is there no other solution?
Just checked it with the Cognos sample model. The same happens if you use the GO Datawarehouse (analysis). Go to the Sales and Marketing (Analysis) --> Inventory --> Product and drag Product color, Product brand and all of the facts to the working area.
If you then group by Product color...the total is not calculated correctly.
Am I using the system in an incorrect way? Of have I found a bug in Cognos?
You try this:
total( [volume] for [contract_name])
this should work!!
Tried that as well and it doesn't work.
Further I believe the function should work :)
The thing is: we're doing this on a dimensionally modeled system. If I publish the business layer (which is relationally modeled) it DOES work as expected.
That's why I started fiddling around with the samples. Further I also created a service request out of it at Cognos. The guy overthere performed the same action and at his installation (8.4.1. whereas we have 8.4) it worked as expected.
Right now we're guessing it is a bug.
Can you look at the SQL being generated by the incorrect report - what grouping is being done?
MF.
I have taken a look at the query.
Here it is:
select "T0"."C0" "Contractnaam", "T0"."C1" "Contractcode", "T0"."C2" "Hoeveelheid_MJ", sum("T0"."C2") over () "Total_Hoeveelheid_MJ_", first_value("T0"."C2") over (partition by "T0"."C0", "T0"."C1") "Total_Hoeveelheid_MJ_5"
from (
select "T_DIM_CONTRACT"."CNTRCT_NAME" "C0", "T_DIM_CONTRACT"."CNTRCT_CODE" "C1", sum(case when "CONTRACT"."IS_QUANTITY"='Y' then "CONTRACT"."NUM_VAL" else NULL end ) "C2"
from "COG_DWH"."T_DIM_CONTRACT" "T_DIM_CONTRACT", "COG_DWH"."T_FT_CONTRACT" "CONTRACT"
where "T_DIM_CONTRACT"."CNTRCT_ID_SK"="CONTRACT"."CONTRACT_ID_SK"
group by "T_DIM_CONTRACT"."CNTRCT_NAME", "T_DIM_CONTRACT"."CNTRCT_CODE") "T0"
order by "Contractnaam" asc nulls last
If I run this query, I see the column TOTAL_HOEVEELHEID_MJ_ being calculated correctly and displayed in every single row of the query result.
I would expect to see the column TOTAL_HOEVEELHEID_MJ_5 to be filled with the total per CONTRACTNAAM. (And then the same value per group of CONTRACTNAAM)
Cognos support found the solution to the issue for me:
COGCQ00249307/PM10196 AGGREGATION ON GROUPED ATTRIBUTE OF A LEVEL PRODUCES INCORRECT
RESULTS.
Sub totals are incorrect when trying to total a grouped
attribute of a level in a DMR model in Query Studio or Report
Studio.
Further trying to change aggregation for summary rows does not
have effect in Query Studio.
An identical report in 8.3 SP3 or in 8.4.1 produces correct
results.
Workaround:
1) Create a separate level with colour businesskey and
membercaption.
2) Add 'ExpandAggrScopeBasedOnGroupBy' entry into
'CQEConfig.xml' configuration file. (Technote 1424837)
As a workaround, it is advisable to change a setting in the CQEConfig.xml (make a copy of CQEConfig.xml.sample and rename to CQEConfig.xml in the <installation directory>/configuration directory if it does not already exist).
Add a section called Transformations under the <component name="CQE"> section
---------------------------------------------------------------- as:
<!-- Description: Transformation entries control the generation of SQL by CQE. -->
<section name="Transformations">
<!-- Description: When both _businessKey and _memberCaption of a level result in key projections, then these key projections will be
included in the FOR/AT clause of an aggregate. (default(on)=1, off=0, choices=0,1) -->
<entry name="ExpandAggrScopeBasedOnGroupBy" value="0" />
<!-- -->
</section>