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.
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.
hm...might be an issue with the fm modell wont it? check the modell for cardinalites/ joins, you might find there a hint