RDLC C# 2005 multiple datasets in one Table ReportItem

Question

Good day

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.

Hennie Smit · Answer

Thanx Sam Yes the tables are tables in the same SQL 2005 database. For simplicity I have called the tables Debits and Credits but they are not since the program I am busy with is for Foreign Exchange. The datasource is a TableAdapter and the source is a SQL Query (View). Thanx.

Sam Hobbs · Answer

Note that use of the term dataset in this context would likely confuse most developers. You explained your use of the term dataset but you did not answer the important questions. So I will be more specific in my questions. What are the tables? I think you are saying you have a Debit and a Credit table; is that correct? Are they both in the same database? You say you have created a data source; is the data source a TableAdapter? I must confess that I know nothing about reports in VS so I might not be able to help any further. What do you use for the data source? Is it a stored procedure or is it a SQL query?

Hennie Smit · Answer

Thanx Sam The reason why refer to dataset is that under the menu Report | DataSources you select one or more tables from your database to serve as datasources. When right-clicking in the rdlc on 1 of the items and selecting properties you are asked to supply a Dataset name which is one of the selected database tables. I am familiar with creating vies/stored procedures where multiple tables are joined into a single view. That is the problem becuase when I join the SQL tables to view I lose the individual records from the tables and only get the summary based on the joining criteria. I need to view the indiviual records from each original SQL table in order to perform calculation on them. Performing the calculation in SQL and then again in the WinApp gives garbage results. Thanx

Sam Hobbs · Answer

I am not sure I understand some things. I am not sure what you mean by dataset; perhaps you mean table when you say dataset. In .Net the term dataset has a specific meaning; a dataset is essentially a database. So do you need to access multiple databases? Probably you mean you need to access multiple tables. Are you familiar with joining tables? Perhaps that is what you need to do.