Hi all,
I have a problem grouping date which are in rows of a crosstab.
Please, see the following image
(http://s14.postimg.org/9gralvy99/aggregation_issue.jpg) (http://postimg.org/image/9gralvy99/)
As you can see, grouping date, the revenue result is not the sum of the revenue, as I expected. :-\
Does anybody help me to understand this behavior and how can I obtain the desidered sum?
Thanks very much in advance
Hi,
could it be possible that you forgot to attach your image?
Hunter
Hi,
I posted an image preview linked to the image. Can't you see the image?
Probably something to do with the firewall settings @ work. I'll check later today :)
Hunter
The full image is here:
http://postimg.org/image/9gralvy99/
Is this a relational or dimensional source?
Relational (on Cognos Bi 10.2 FP1)
use total to add the values
Aggregation using 'maximum' will take the largest value and use it as the revenue summary value
Hi,
thank you for your advice.
I edited the Revenue aggregation rule to "automatic" and the data element to total(...) but the result is not changed :-\
I also tried the Calculated aggregation rule without success.
Have you any other idea?
Thank you very much
did you try Total?
Quote from: Rosanero4Ever on 13 Nov 2013 02:43:22 AM
Hi,
thank you for your advice.
I edited the Revenue aggregation rule to "automatic" and the data element to total(...) but the result is not changed :-\
I also tried the Calculated aggregation rule without success.
Have you any other idea?
Thank you very much
Yes, Total aggregation rule doen't work for my target :-\
Hmmm... ok I'm looking at your image more closely. That seems to be correct...
21.945,35 is the total for 28/01/10 Q4 2009
Your logic seems to be out of place. I think you want the total for Q4 2009 right? The display should be like this:
Revenue
Q4 2009 22/01/10 3.128.368,07 ----> A
Q4 2009 28/01/10 21.945,35 ----> B
Total Q4 2009 A + B
Quote from: Rosanero4Ever on 13 Nov 2013 03:34:46 AM
Yes, Total aggregation rule doen't work for my target :-\
I would like group by maximum date for the Q4 2009. So the result shoud be the following
Revenue
Q4 2009 28/01/10 3150313,42 ----> A+B
I see..
create a Data Item using this:
maximum([Date])
then replace [Date] with your Data Item ... lets call it [Max Date].
you might need to set the Aggregate Function of [Max Date] to 'None'
your crosstab will look like this
[Revenue]
[Quarter] [Max Date] <#1234#>
*** or you can reverse [Quarter] and [Max Date]
Hi, I also tried this solution but unluckly it doesn't work because the total value reproduced is incorrect :-[
Is it so hard obtain this simple aggregation result? ???
Thank you very much for your time
Crosstabs do some unexpected things in the background when working on relational databases. Are you using DQM or CQM?
First, can you confirm that the date is actually a date/datetime/timestamp and not a string? Max usually will work on a string, but may also cause unexpected results. Also, are your results a derived resultset from another query, or is it pulling straight from the db?
Try reproducing this in a list. If it works there, then we can work on getting it in an crosstab.
List should be:
Quarter - No aggregation
Date - Max aggregation
Revenue - Total aggregation
Quote from: CognosPaul on 13 Nov 2013 05:35:01 AM
Are you using DQM or CQM?
DQM
Quote from: CognosPaul on 13 Nov 2013 05:35:01 AM
First, can you confirm that the date is actually a date/datetime/timestamp and not a string? Max usually will work on a string, but may also cause unexpected results. Also, are your results a derived resultset from another query, or is it pulling straight from the db?
My date is a datetime and it is derived stright from the db
Quote from: CognosPaul on 13 Nov 2013 05:35:01 AM
Try reproducing this in a list. If it works there, then we can work on getting it in an crosstab.
List should be:
Quarter - No aggregation
Date - Max aggregation
Revenue - Total aggregation
I reproduced the query using a List in Query Studio and it works well. ???
Thanks all
I just tried it on the sample Go Sales Query with package on CQM and it works fine:
<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Quarter" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Quarter]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Date" rollupAggregate="none" aggregate="maximum"><expression>[Sales (query)].[Time].[Date]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="4" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents/></crosstabCorner>
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Date" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab>
</contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-09T13:52:15.420Z" output="no"/></XMLAttributes></report>
My guess is that the DQM is effectively turning this into a cube. It's generating the data, finding the last date member, and returning that date's associated data.
But I'm using a relational view.
Cognos turn into a cube represantion with DMR also using CQM
I don't think I am using DQM as well and it was working fine.
How about if you put your data items in a query including your maximum computations, etc... then get a second query which source is your first query. Take only the data items you need and use this as a source of your crosstab?
Or you can pre compute your dimensions in 1 query, your numbers in a second query, join them in a third query to use for your crosstab?
The idea is to 'convert' your data to simple relational/non DQM mode?
Thats all the bad ideas I have for now :(
Hi,
I'm thinking just this work around, i.e. create two queries and join them.
I opened a PMR at IBM support; I will let you know about other solutions.
Thank you very much for your time.
Hi,
I opened an IBM call and the described behavior was classified as defect. So, I'll wait the APAR number.
In the meantime I solved using a join between two queries.
I hope this will help other developers.
Thanks all for advices.