locked
report with multiple datasets RRS feed

  • Question

  • User-1778311173 posted

    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. 

     

    Thursday, April 2, 2009 2:03 PM

All replies

  • User-1778311173 posted

     

    no one has any input?
    Monday, April 6, 2009 7:28 AM
  • User-1555842583 posted

    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

    Thursday, April 30, 2009 8:12 AM
  • User-1778311173 posted
    No i have not. I only see reports dealing with one table or if you need other things you add the subreports which is just awful to work with.
    Tuesday, May 12, 2009 11:52 AM
  • User-1556678718 posted

    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

    Monday, May 21, 2012 5:23 AM