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
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..
please check in the model level might be u have given aggregate property of that data item as sum...........
"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..
In the data item property if it is taking total remove that to none and rollup to automatic then check...................