If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Incorrect Total when grouping

Started by jmsellner, 02 Mar 2010 09:36:17 AM

Previous topic - Next topic

jmsellner

I have a DMR package and I have created 2 queries

Query 1 - Region, Agency, Report #, # of Reports
Query 2 - Region, Agnecy, Report #, # of Referrals

On the page have
List 1 - Region, Agency, # of Reports, Group by Region, Agency and I defined a property for Report #.  When I do an aggregate Total for # of Reports, I get the correct # of Report by Agency and Region.

List 2 - Region, Agency, # of Referrals, Group by Region, Agency and I defined a property for Report #.  When I do an aggregate total for # of Referrals, I get the Total # of Referrals for the report for each Agency and Region.

I create these queries and list that same way.  I have compared the measure in the framework manager and they look the same and I have also compared the query properties and I don't see any difference.

Why doesn't the 2nd list Total correctly for the grouping and the first one does?


paddhu

QuoteOn the page have
List 1 - Region, Agency, # of Reports, Group by Region, Agency and I defined a property for Report #.  When I do an aggregate Total for # of Reports, I get the correct # of Report by Agency and Region.

List 2 - Region, Agency, # of Referrals, Group by Region, Agency and I defined a property for Report #.  When I do an aggregate total for # of Referrals, I get the Total # of Referrals for the report for each Agency and Region.

I think that is what it is ought to... What else do you want??? If you want the aggregate for # of Referrals, why have you set the property of the second list as Report # ?

blom0344

Quote
Query 1 - Region, Agency, Report #, # of Reports

That would mean a non aggregate as you are counting reports while fetching Report itself, thus returning a non-aggregate. However, while # of Reports will have the value of 1, the rollup against Region and Agency will ensure correct value for # or reports (by rollup, not by aggregate)

jmsellner

#3
Query #1 works correctly

Query #2 doesn't work correctly,

Region Agency #  Report # #_Of_referrals
A  B  A1 1
Total for Agency B 5 should be 1
A  C  B1 1
A  C  C2 1
A  C  C3 1
A  C  C4 1
Total for Agency C 5 should be 4
Total for Region A 5 This is correct
Total for Report 5 this is correct

Reason report is included is because a report can become a referral. #_of_referrals is my fact in the table.

I switched the rollup aggreagte function to none and it looks a little better on query #2, but it still isn't right.  Now it gives me a 1 for each to the Total's.  I tried setting the aggreagate function to Total, summarized, automatic, calculated and none and they all return the 1.