Cognos 10.2.1 DQM and Crosstabs calculations
Posted on 2014-09-03
I have the strangest thing happening in a Cognos 10.2.1 Fixpack 3 Report Studio report. I have 2 queries that count male and female students (based on different criteria). I am joining the 2 queries based on Gender and Term. I am attempting to add the Male students (MSQ1 + MSQ2) together and female students (FSQ1 + FSQ2) together and list these totals in separate columns in the crosstab (Male Total / Female Total columns). I created a new data item called Total by Gender ([Student_Count_Query1] + [Student_Count_Query2]) then I used this as the measure of my crosstab. What I am seeing is the new data item (Total by Gender) is rendering a result that appears to be concatenating the values ([Student_Count_Query1] and [Student_Count_Query2]) together. For example I have 1086 male students from Query 1 and 99 male from Query 2 and my Total by Gender for Male students shows as 108699 in the crosstab (the same type of result happens in the women's column as well). I am not sure if I am doing something wrong with the changes that have been made when using DQM supported reports (because the above method works just fine when I run the same exact report using a CQM based version of the package). Just wondering if anyone has seen anything like this and if so what did you do to get around it. I am experimenting with some things (aggregation settings / data item expression, etc...) and have not had any success to this point. Any ideas welcome. Thanks.