0

I'm looking for the best approach to solve this kind of problem while reporting with SSRS

Most of my data in in MainTable, but I also have two other tables which may have data, LocationList and ExtraDetails. In SQL these are connected via Id and may have zero to many records.

I have no problem with MainTable data, but sometimes I have extra data in LocationList which needs to be listed under the row for MainTable. The same is true for ExtraDetails data.

I cannot figure out how to connect to the extra tables and show that data

I'm going for a report something like this: 

Id1   Name1    Address1 
Id2   Name2    Address2
       LocationList-1 for Id2
       ExtraDetails-1 for Id2
       ExtraDetails-2 for Id2 
Id3   Name3    Address3
       ExtraDetails-1 for Id3 
Id4   Name4    Address4

As a C# coder, what I way to do is get a count of LocationList for ID, and if count > 1 then show each LocationList entry. But I suspect this is the wrong approach for SSRS reports.

rwg
  • 915
  • 9
  • 12
  • Are you sure your question tags are correct? `datatables` tag is for jQuery datatables. If this question does not pertain to that, please edit the tags. It will get your question answered faster. However, if it is correct, we need more info. – Drakkainen Jul 25 '12 at 02:44

1 Answers1

0

The answer to this is to use SubReports. In this case I would create a new report for LocationList and a report for ExtraDetails, both using parameter input, something like this: select comment from LocationList where Id = @MainTableId. . I added a row to the main report and configured a cell for the SubReport (right click and insert a SubReport, roght click and configure the SubReport to use the LocationList or ExtraDetails report, and add the reference to resolve the parameter input. )

This was hard for me to find, when I didn't already know to look into SubReports for a solution.

rwg
  • 915
  • 9
  • 12