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.
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:
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
Dont forget to click "Mark as Answer" on the post that helped you. Don't "Mark as Answer" if it is not your question.
Can someone please post an answer without posting a link to a link to a link?
Zenuke
Member
217 Points
296 Posts
report with multiple datasets
Apr 02, 2009 06:03 PM|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.
Zenuke
Member
217 Points
296 Posts
Re: report with multiple datasets
Apr 06, 2009 11:28 AM|LINK
no one has any input?edrstone
Member
4 Points
13 Posts
Re: report with multiple datasets
Apr 30, 2009 12:12 PM|LINK
Hi Zenuke,
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 5)
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/fe90b59b-9c8b-40d0-a12e-5846e592215b/
Regards,
edrstone
Zenuke
Member
217 Points
296 Posts
Re: report with multiple datasets
May 12, 2009 03:52 PM|LINK
RioDD
Member
487 Points
331 Posts
Re: report with multiple datasets
May 21, 2012 09:23 AM|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
Can someone please post an answer without posting a link to a link to a link?