I have created two reports one that shows debits (which works 100%), and one that shows debits against credits per day. It shows total debits and total credits per day, ie one line per day. In the SQL database I have a table for debits and another for credits. There are also various rates on each table. Debits and Credits can be bought and sold on a regular basis. In the reports we reflect the weighted averages of the days transactions SUM(rate * (bought - sold) / SUM(bought - sold)).
In the Debits report, I use the Debits dataset with the individual transactions as they appear in the DB. This approach works 100%. In the DebitsCredits report, I have created a View that combines the values from the two tables. Forcing me to join them per day (ie. getting totals per day, I lose the individual records). This approach gives me different results to the Debits report, wrong results, due to the fact that individual transactions "have beeen lost".
I thought of making use of the two individual tables, but cannot assign both datasets to the same RDLC table ReportItem. I also need to perform calculations between the two datasets (ie. creditamount - debitamount = balance).
Which other way can I bring the individual records of both table into the report and perform calculatins between them?
Many thanx.It's not the blowing of the wind that determines your destination, it's the set of the sail. J Rohn