Last post May 21, 2012 09:23 AM by RioDD
Apr 02, 2009 06:03 PM|Zenuke|LINK
Hopefully I explain this correctly.
I am trying to create a report where each Item I am reporting on has data that is spread across two different sets of data that don't relate in anyway except for the one key field.
I'll try to explain the layout here.
1st: there is the main data for each item which consists of a single row from the database with X number of columns. There can be X number of items returned from the SP for this dataset
2nd: there is finacial data for each item that consists of multiple rows of data for each item. Each row is a specific type of data. This comes from a seperate SP
The only linking factor is a column called 'ref_num'
I am trying to figure out the easiest way to get these sets of data into a report. My first approach was a single RDLC with a list that would put the data from #1 into boxes. In the list there was a subreport that send the current Ref_num as a parameter
to the sub report. the sub report showed the data from #2 based on that paramter.
So each page on the report would do show this. I am trying to find a way to do this without the subreport and hopefully do this in a more dynamic way where I can use .NET to place the items into the report and not have to pre-define the fields in the report.
So far I don't get how I can get these 2 sets of almost unrelated data to show up on the same page of the report without that sub report to load the data.
Apr 06, 2009 11:28 AM|Zenuke|LINK
Apr 30, 2009 12:12 PM|edrstone|LINK
I am also trying to find a similiar solution. I have one dataset that returns multiple rows of people. My other datasets have multiple rows for each row in the first dataset that can be 'linked' via a person_id column that exists in both datasets. For
example, one of my other datasets queries a table of phone numbers for each individual in dataset 1. So the end result would be a report page that displays a person with their basic information (i.e. address, marital status, birthdate, etc) as well as multiple
phone numbers and other many to 1 type of results for that individual.
I have not found an 'easy' solution to this yet. Most of my research has indicated the best way to handle this, if possible, is via creating a new view or stored procedure that returns a result set that you can use as one dataset and then use grouping
to show the results they way you want. Another way that I am seeing is to use sub-reports but it seems to be the most complicated and resource hungry solution.
Did you find an easier way to do this?
Here are some links that helped me come to my conclusion thus far:
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/ab379738-7e34-47d8-b756-90802f850c37/ (specifically bullet
May 12, 2009 03:52 PM|Zenuke|LINK
May 21, 2012 09:23 AM|RioDD|LINK
You don't need subreports in order to display your data. I had similar problem and here is what i did:
1. I put everything in 1 select :
Select * from (Select first query) as b left outer join (Select second query) as c on b.primaryid=c.foreignid
this give me evrything in one table so all the data is in 1 dataset
2. In order to avoid duplicates I used grouping
- first table displayed and the list grouping by the column that was b.primaryid
-second table 2 groupings, same as in the first and that what was c.primaryid