Report Studio Amounts showing different in Crosstab report vs List Report

[jeromefang]

Hi Cognos Experts,

I'm facing a really weird issue with my Amount measures between a Crosstab report and List report.  The following summarizes what I've done.

(1)  I created 2 pages in Report Studio and I named them "Summary" and "Detail"
(2)  Page 1 is Summary and this has the Cross-tab report format.
(3)  Page 2 is the Details so it's in a List report format.
(4)  Both reports are using the Same query.
(5)  In the Summary report I basically only have 2 dimensions, Country and Cost Center.  Then I have Months in the columns.  In this Crosstab, I dropped my USD amount Data item into the measures.
(6)  In the detailed report I have a lot more other fields apart from the 4 in Summary.

To isolate my report for analysis, I added report filters to pull only for 1 month, 1 Cost center and 1 Country.  The Summary report gives me a total of $1837 while the Detailed report gives me a total of $938. Hence, it seems to me that the Summary report total is a double of $938. 

I tried playing around the Aggregate Function and  Rollup Aggregate function.  I tired different combinations of NONE, TOTAL and AUTOMATIC but all still gives me the same result.  Hence, appreciate if any experts can provide some insights to how I can resolve my issue? 

Many Thanks in advance for all your help.

[Lynn]

Hi Cognos Experts,

I'm facing a really weird issue with my Amount measures between a Crosstab report and List report.  The following summarizes what I've done.

(1)  I created 2 pages in Report Studio and I named them "Summary" and "Detail"
(2)  Page 1 is Summary and this has the Cross-tab report format.
(3)  Page 2 is the Details so it's in a List report format.
(4)  Both reports are using the Same query.
(5)  In the Summary report I basically only have 2 dimensions, Country and Cost Center.  Then I have Months in the columns.  In this Crosstab, I dropped my USD amount Data item into the measures.
(6)  In the detailed report I have a lot more other fields apart from the 4 in Summary.

To isolate my report for analysis, I added report filters to pull only for 1 month, 1 Cost center and 1 Country.  The Summary report gives me a total of $1837 while the Detailed report gives me a total of $938. Hence, it seems to me that the Summary report total is a double of $938. 

I tried playing around the Aggregate Function and  Rollup Aggregate function.  I tired different combinations of NONE, TOTAL and AUTOMATIC but all still gives me the same result.  Hence, appreciate if any experts can provide some insights to how I can resolve my issue? 

Many Thanks in advance for all your help.

Your summary is not exactly double and it seems a little too large a difference to chalk it up to rounding, so perhaps don't read too much into that aspect of the situation.

Should we assume this is a relational data source?

I would suggest you review the tabular data and the generated SQL for the query feeding your two layout containers. You can copy this query to look at the query and the data, then remove elements one by one to assess the differences. I suspect that one of the additional elements in your list report is introducing a join that is causing data to drop out.

Of course this wouldn't be the first time a poorly constructed Framework Model is to blame for unpredictable results, so talking with the modeler is also a good idea.

[jeromefang]

Hi Lynn,

Thanks so much for the reply.  I did some analysis from your advice on the join and I did see a join to another table in the same package.  However, after I remove the join, the results remain the same.  My Crosstab still shows $1837 and the List report still gives me of $938. 

I would attribute this issue to potentially a Data Modeling issue.  Hence, I'll be having a chat with my Developer sometime this to analysis further. 

Thanks again for your help.   :D