Summary totals in report studio not adding up correctly C10.1

[cognosgirl_2012]

Hi, Hopefully this is a basic question.

I have a report in report studio that looks a bit like this.

ID Desc Item Name Cost

1 ABC A Mr Smith 20
1 ABC A Mrs Smith 20
2 DEF B Mr Jones 40
2 DEF B Mrs Jones 40

Becuase I have more than one name per booking, it brings back a second line with the same information on, after grouping, i have got it to return one line of data so it looks like this.

ID Desc Item Name Cost

1 ABC A Mr Smith/Mrs Smith 20

2 DEF B Mr Jones/Mrs Jones 40


This reflects more accuralty as the total booking cost for Mr&Mrs Smith is £20 and not £40.

When I now try to total the Cost column, it is adding up the £20 twice, one each for Mr & Mrs Smith to show a £40 total, rather than £20, and its doing this for every customer where there is more than one name on a booking. Example of what my report is currently doing...

ID Desc Item Name Cost


1 ABC A Mr Smith/Mrs Smith 20

2 DEF B Mr Jones/Mrs Jones 40

Total Summary: 120.00

£120 is wrong (£20+£20+£40+£40) as it should show £60.00 (£20+£40)
How can I make report studio add up what is showing, rather than all the detail?
All help greatly appreciated. Thank you.

[Lynn]

Is this a relational or a dimensional source? Is the data actually stored that way with duplicated facts per name, or is it duplicating because of a join between two tables (one with the booking information and another with  the names)?

If it is relational and you have one table with the distinct booking information and a second with the names then you could use a list for the booking info (ID, Desc, Item, and Cost) and then put a repeater in the list for the names so you can arrange them as you indicate. Connect the two with a master/detail relationship. It isn't likely to give you stellar performance though.

I'm sure there are other options so maybe others will chime in with additional ideas.

[charon]

hm...might be an issue with the fm modell wont it? check the modell for cardinalites/ joins, you might find there a hint