Crystal Reports for Visual Studio: display 2 unrelated tables

Question

I ask for advice what would you suggest to display 2 unrelated tables in a single report (if needed with subreport), the first one with groupings and the second one "flat"?

Details:

I am developing a report with C# 4 and Crystal Reports for Visual studio. Data comes from a Stored procedure in Sql Server 2005, that returns 2 tables. I pass the data to the report with the .SetDataSource(Dataset) method and I checked that data in the dataset is correct.

I need to display the data from the first table in a quite complex form with different sections. This part works.

At the end of the report I need to print the data from the second table (no grouping, just a "flat" print). To do this I added a subreport.

The tables are not related. I added a Dummy field on them with the same value and used it to mimic a relation in the database expert; I configured the subreport link, based on the dummy field.

I have 2 problems.

The data in the main report is cross joined with the second table (I could expect that since they are related with a constant field)

The subreport is always empty (I checked several times that its table in datasource contains correct data). I tried different configurations of link and tried to place the subreport in different sections (It should be in the report footer). I set a border around the subreport: the border is printed but inside it is empty. I also setup a formula to count the records of both tables and put the count in the report: the count is > 0 for both.

Answer 1

Ideally speaking, without linked table not used in crystal report as reporting tools used to display the relevant data based on our criteria and display 2 different set of data without link does not mean as a report as well as tough to integrate in crystal report.

Tough to integrate means at any point you have to link the table either via union in sqlserver or at crystal report side, other wise it display as Cartesian.

As per my thoughts you can do it by some approach.

Approach 1 : You can get the data by union or unionAll with set of columns and filter the data where table's column does not have null value ie. http://www.maximumimpactsolutions.co.uk/blog/comments.asp?bd=118

 Table1col1 Table1col2 Table1col3 Table2Col1 Table2Col2 Table2Col3
 value      value      value      null       null       null  --table1data
 null       null       null       value      value      value      --table2data

Approach 2 : Second is sub-report, but still need the linked as you already done. But there is proper rule to be set (this is very very tedious job).

Approach 3 : Generate 2 report at c# side and merge into one PDF, then export it.

check this links.

Reporting on multiple tables independently in Crystal Reports 11

http://www.tek-tips.com/viewthread.cfm?qid=241195

http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=4756

http://www.codeproject.com/Questions/348959/Crystal-reports-for-multiple-tables

Answer 2

Your approach looks wrong.... At first if both are not releated I don't see any use in creating a dummy field and linking tables which will always provide cross join. Any way as per your descrition table 1 data is saperately displayed and table 2 data is saperately displayed

As per your description in main report you need data from table 1 and in footer you need data from table 2.

Try this approach:

1. Don't link tables

2. take table 1 in main report and show the required display.

3. Take sub report and in that take only table 2 and display in footer and make sure don't link main report and sub report nor link two tables.