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

giving sum() result in sales i wqant sales details by store

Started by gouravatalkar, 12 Jul 2010 04:24:18 AM

Previous topic - Next topic

gouravatalkar

I am using one dimension table 'store' and fact table 'sales' i am using query item from the sales table and store name from store table but the amount sales giving the total sales in every row. i have see the query generated it is like this:

select sum("FACT_SALES"."AMOUNT_SALES") "AMOUNT_SALES"
from "BISPSALES"."FACT_SALES" "FACT_SALES"

it automatically taking sum i dont want to use sum how to solve it please help me

blom0344

Looks like something is not quite right in the model. If proper joins are missing the statements will be processed independantly resulting in the entire sum of the fact for every row. Normally a fact will have an aggregate set against it resulting in the sum. From Report Studio you should be able to infulence this, but it looks like you have different issue at hand..

mvjcognos

please check in the model level might be u have given aggregate property of that data item as sum...........

blom0344

"Amount_Sales" sounds pretty much like a fact to me, it would be perfectly logical to set the aggregate to sum. In the unlikely case that you would want detail rows returned, then you can fix this in Report Studio by setting "Auto Group & Summarize" to No.
This will remove all Aggregates , thereby returning the detail set..

mvjcognos

In the data item property if it is taking total remove that to none and rollup to automatic then check...................