none
How to use two dataset on SQL Server report

    Question

  • Hi Guys,

     

    I just designed a report using SQL Server Reporting 2005 in which I have used two datasets. I am able to bind fields of one dataset but other dataset fields are not visible. When I try to bind the field of other dataset it gives me error that the field is not found. If I bind the field by using the Aggregate function it is bind correctly because we specify the dataset name with field in the function. But I do not want to use Aggregate function. How can I use two dataset for single report?

     

    Waiting your response

    Friday, February 22, 2008 10:40 AM

Answers

  • Hi,

    Multiple datasets in one report will work. But two datasets in one table (Data Region) can’t be accepted. If so, you have three options:

    1.       Combine the dataset by using the Join fundamentals. More information, see Join Fundamentals

    2.       Using the sub-report. But a sub-report in the table cell may help you.

    3.       Put the data you need in a hidden parameter. In the table, refer the value of parameter like : = Parameters!data1.value

    Hopefully this helps.

    Monday, February 25, 2008 11:56 AM

All replies

  • Hi ,

    Yes you can use two datasets in a single report .

    But in a single component like (single table / matrix etc) ,you can' t refer to two datasets.As it allows to give the datset name of only one dataset.

    Rather you can refer to one dataset and the aggregrates of the second dataset.

    But if you want to get the data not the aggreagrate value then do like

    1-Create two tables.

    2- For one table select dataset1,and for the second table select dataset2.

    3-Try to adjust the two tables in such a way that in the priview it will seem to be one table.

     

    Hope this will work fine.

    Anyway please let me know if you get the solution.

    Thanks

    Mahasweta

     

    Friday, February 22, 2008 11:28 AM
  • Hi,

    Multiple datasets in one report will work. But two datasets in one table (Data Region) can’t be accepted. If so, you have three options:

    1.       Combine the dataset by using the Join fundamentals. More information, see Join Fundamentals

    2.       Using the sub-report. But a sub-report in the table cell may help you.

    3.       Put the data you need in a hidden parameter. In the table, refer the value of parameter like : = Parameters!data1.value

    Hopefully this helps.

    Monday, February 25, 2008 11:56 AM
  • If there is a DataSet (XSD in VS 2008) that has 2 tables and knows the relationship (one to many) between those 2 tables, why is not possible to have fields from both the tables in one Table in the report?

     

    Shyam

    Thursday, April 17, 2008 1:00 PM
  • Its not possible that one report item(table, matrix,list.. etc) can use two different datasets.

    Most people will suggest you to merge them into one dataset but there may be situations where you may need to use  two dataset in one data region.

    You can proceed as follows:

    1. create table1 with first dataset1

    2. create hidden parameters of all the fields of other dataset.

    Note: The Actual and Default value for them must be set.

    3. refer the value of the parameter as Parameter!data.value wherever required.

    Wednesday, November 30, 2011 12:08 PM