I have created a query in Report Explorer with three columns:
ID is an Identifier with type Int64
Quote is an Attribute with type Char
Price is a Fact with type Decimal
Auto Group & Summarize is set to Yes for the query and Aggregate Function is None for ID, Count for Quote and Total for Price. When I choose the option to View Tabular Data, Cognos correctly presents the count of Quote for each ID, but instead of showing the sum of Price for each ID it's showing the sum of all Prices next to each ID. For example, I get this:
ID Quote Price
239 2 93,792,240.68
295 3 93,792,240.68
296 2 93,792,240.68
What I want is the total of the prices for each ID presented on each row and not the total of all prices.
Interestingly, if I change the the aggregate function of Price to Minimum or Maximum I get correct results (the minimum or maximum price for each ID). However, aggregating by Total or Average both seem to operate on every Price rather than just those with the specified ID.
Any thoughts on what I'm doing wrong here? Thanks.
Please try to create a new column with expression definition as total([price] for ID)
Thanks
Thanks for the suggestion. I tried that and I get the same results. In fact, I tested with total, average, minimum and maximum and using the aggregation functions in an expression had the same results as specifying the aggregation function as a property of the data item. By the way, when I did this I changed the aggregation method to calculated (in case that might be part of the problem).
Sure. Please create a new data item as i said above and keep the aggregate function as Automatic (Don't change anything for the new column).This should work i guess.
Thanks
Rk
I believe ID and price are coming from 2 different columns. Can you please check the join condition between the 2 tables? It looks like the join isn't correct or missing.
Hi,
It looks to me like you have a problem with the underlying metadata model the report is based on. If you go to Tools > Show Generated SQL/MDX, what does the Cognos SQL query look like? My guess is you have a "blind spot" in your model, but that's purely a guess...
Cheers!
MF.