How are the aggregate details being calculated on a click through report?

Hi -

I need help figuring out what setting I need to tweak to get the correct calculations for the default aggregate attributes for the related entities of the one I am drilling into. Right now it is calculating the total across all for every row and not slicing by sub-customer.

Example:

I have a customer with a one-to-many relationship to incidents. Both have a count aggregate that is part of the default aggregates for the entity. There are 58 rows in my table. If I run a report with CustomerName and #Incidents, I correctly get different sub totals for each customer, totalling to 58 for the grand total. However, if I run a summary report on customers and drill into the customers using click through, the #Incidents is displayed but it is 58 for all customers - every row.

If I go into the defaultDetailAttributes of the Customer and add the #Incidents to it and run the previous test, then the correct number of incidents are shown for the customer, then the incorrect number of incidents follow (from getting the aggregates from the children).

The query generated is huge and I am sure it has something to do with my OptionalMany relationships between the tables, but I can't understand why...

Can anyone help me out?

Thanks in advance,

Toni Fielder

I had a similar issue from before. I'm assuming you are using multiple datasets in this one table? I really struggled to find another way to dish this stuff out correctly, but the only efficient way I could get what I wanted (the same thing you are looking for) was to create a giant stored procedure combining the tables I was planning on using. Another way I found (the less efficient way) was to create subreports for each subcategory and call the information into the subreports that each subcategory was grouped by. This way will really increase the amount of time your report will be rendering for, but if you cannot do a stored procedure then this may be your only choice. Hope I could help.|||

Thanks for the message - I think I have a bit of a different situation.... I am constrained by using models and report builder - but I think custom click-through reports might be the equivalent of what you had to do.... unfortunately this has to be a generic model that is sold to multiple customers so custom click through reports has to be my last choice....

I now have another question that is related. I used my smallest object to try and figure out how this stuff is working. I changed all it's relationships (incorrectly) to One-One so it had no many relationships (or optional) from which to grab other aggregates. And I am getting all manner of aggregates from different entities in my model that really have nothing to do with my base entity. It has to be getting them from some extended relationship (all entities are interrelated - like they all share the users table under the covers to get Insert/Update information) but I am thoroughly confused at the moment.

Thanks for the help everyone.