locked
using multiple dataset in one report RRS feed

  • Question

  • in web application used .rdlc reports
    if i am having only one data source in the report its wroking fine with each filed expression a sfollows
    =Fields!CODE.Value
    all the records of the associated table as displayed

    but if i need to display records from multiplle datasets on a single report i have problem
    only the first recods get s displayed multiple no of times
    =First(Fields!CODE.Value, "PR_OPL")

     

    without using first & using multiple datasource how can i dislay all records

    as i need to using more than one table the reports

    its nota sub report

     

    sql= " Select  Name ,*  from EMP_OPL_PERSONAL where  OPL.CODE = PERSONAL.CODE";


    svk
    Wednesday, December 14, 2011 12:45 PM

Answers

  • Hi,

    You need to try something like below for multiple parameters

    ReportParameter[] para = new ReportParameter[2]; // Array for multiple parameter
    para
    [0] = new ReportParameter();
    para
    [0].Name = "parametername1";
    para
    [0].Values.Add("1");//any value

    para[1] = new ReportParameter();
    para
    [1].Name = "parametername2";
    para
    [1].Values.Add("1");//any value


    RsViewer.ServerReport.SetParameters(para);

    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Proposed as answer by Elvis Long Monday, December 26, 2011 7:10 AM
    • Marked as answer by Elvis Long Thursday, December 29, 2011 1:43 AM
    Monday, December 26, 2011 6:56 AM
  • Hi SRkeha,

    There is a related thread about pass multiple parameter values to ReportViewer control, please see:
    http://forums.asp.net/t/1338302.aspx/1

    For a new question, please post it in a new thread. It will benefit other community members who have the similar issue with you.

    Regards,
    Bin Long


    Bin Long

    TechNet Community Support

    • Marked as answer by Elvis Long Thursday, December 29, 2011 1:43 AM
    Monday, December 26, 2011 7:17 AM

All replies

  • Hi,

    You can use multiple dataset in one report. As you mentioned more than one table in report is also possible. For second dataset take one table and remove first function. Use simply Fields!CODE.Value.

    Hope this helps, if I understood your question correctly.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Wednesday, December 14, 2011 12:55 PM
  • no cant use it as i get error

     

    The Value expression for the textbox ‘FNAME’ refers to the field ‘FNAME’.  Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

     

    i have multiple datasources

     ReportViewer1.LocalReport.DataSources.Add(rdS);
     ReportViewer1.LocalReport.DataSources.Add(rdS1);

    if i just use

    Fields!CODE.Value

    i get the value from only one datasource


    svk
    • Edited by SRekha Wednesday, December 14, 2011 1:04 PM
    Wednesday, December 14, 2011 1:02 PM
  • Hi,

    Are you able to see two datasets in report? if yes then drag / drop one table control rather 2nd table in your case on report. Goto Properties -> DatasetName -> Select second dataset.

    Then use above give expression without using first.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Wednesday, December 14, 2011 1:24 PM
  • i think u have not understood the issue

    i am able to get 2 dataset

    but with 2 datasets i need to specify the datasource in the expression  else it gives aggreate error

    if i need to specify the datasource in the expression i need to use first without which it gives syntax error

    and if i specify first i get only the first record repeated till the recordcount

    i hope i am clear

     


    svk
    Thursday, December 15, 2011 9:08 AM
  • Hi,

    If I understood correctly now (I hope) then you mean to say:

    1. You have 1 table control in report where you where showing data from dataset1
    2. Now you added 2nd dataset to report and you are trying to show data from 2nd dataset in the same table.

    If this is true then this is little problematic as you can not show data from two datasets in one table control. As you mention you have to use some kind of aggregate like first, sum etc when you access 2nd datasource.

    One table can have only one dataset binded.

    I hope this help.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Proposed as answer by zenzoro Thursday, December 15, 2011 9:52 AM
    Thursday, December 15, 2011 9:26 AM
  • ya u r right in understanding

    i do need data from 2 different dataset in a report

    does it mean its a issue in reporting services

    as i will need data from multiple table in my one report

    is there any other way round

     

    or rather than using reporting servcies , is crystal the solution for such issues


    svk
    Thursday, December 15, 2011 9:31 AM
  • Hi,

    Recommended approach would be returning only one data set from source with required data.

    New feature is introduced in SQL 2008 R2 called LOOKUP and LOOKUPSET which allow you to get data from another dataset. But as you are using RDLC, this is not available in RDLC.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Thursday, December 15, 2011 9:46 AM
  • do suggest me the best option as i am new to reporting services

    frm whtever info i got abt reporting services and as i am not able to getthe needed result i feel crystal report wuld be good

     


    svk
    Thursday, December 15, 2011 9:57 AM
  • Hi,

    As I said first option is recommended.

    There is difference between Server Side Reporting (.RDL files) and Client side reportin (.RDLC Files)

    Please refer: http://dinesql.blogspot.com/2010/11/reporting-services-difference-between.html

    You can also refer book called as "Client Side Reporting" by Wrox publication.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Thursday, December 15, 2011 10:19 AM
  •  

     

    but that too is not working as for each  table i get different datasources

     

    let me knw if this way is correct

    i created a new dataset all.xsd and put all tables in the xsd

    & trying to use it

    i hope it the right way suggested by u


    svk
    • Edited by SRekha Thursday, December 15, 2011 10:57 AM
    Thursday, December 15, 2011 10:49 AM
  • Hi,

    Are you saying 1 Dataset (XSD) and inside it multiple data tables, if yes then this will not work as you will again get multiple datasource in report.

    Instead, you should have only one data table in XSD. You must be having two different SQL's to fill two data table, you need to club these two sql and get data using single sql and fill single data table.

    Hope I explained it correctly.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Thursday, December 15, 2011 11:41 AM
  • i have 4 tables & 4 datasets(xsd files)

    in report1 i need records from 2 tables using join

    xsd1= employee

    fields = name, add, deptid

    xsd2 = dept

    fields = deptid, deptname

    so in my report i need

    name, add, deptname

    how can it be done using reporting service

     

     


    svk
    Friday, December 16, 2011 11:37 AM
  • Hi Rekha,

    Try to change query of XSD 1 to include department name.

    Something like

    SELECT E.Name, E.Add, D.DeprtId, D,DeptName FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E. DeptId = D.DeptId

    This is what i suggesting in my previous reply.

    Hope this helps.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Friday, December 16, 2011 12:34 PM
  • ok

     

    will try & let u knw

    but wht if i have one more xsd to be in the same report but not having any join

    i mean i have a table which will contain only one record ie it will have the partyname & address in the table which i will need on allthe reports

    i have a xsd for it

    how do i use it


    svk
    Friday, December 16, 2011 1:01 PM

  • as u had said in the previous post to make changes in xsd file as

    SELECT E.Name, E.Add, D.DeprtId, D,DeptName FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E. DeptId = D.DeptId

    its working fine

     

    querys & issues reg the reporting services

    1)i have a datatable which has only company info & will be used as heading in all reports & well as few page.

    this tbl has a single record

    but along with the above details in the report page i need to have the company details on top of the page similar to heading

    2) in my aspx page i have few dropdown controls with data & a report viewer

    i need to pass on the data as parameters from this controls so that the data in teh report is displayed accordingly

     

    3) in my development system i have this application . i have created the xsd file

    the aspx pages takes the datasource from webconfig file

    when i use the same application from another system (production server) i get error for datasource , i get the development system datasource instead of production server which it shld take in automatically

    how can it be done

     

     

     

     


    svk
    Saturday, December 17, 2011 7:22 AM
  • querys & issues reg the reporting services


    1)I have a datatable which has only company info & will be used as heading in all reports & well as few page. this tbl has a single record but along with the above details in the report page i need to have the company details on top of the page similar to heading -  Take one textbox in report header aread and add expression as =FIRST(Fields!CompanyName.value, "datasetname") change dataset name to your dataset name.

    2) in my aspx page i have few dropdown controls with data & a report viewer i need to pass on the data as parameters from this controls so that the data in teh report is displayed accordingly - Create parameters in report and pass value to report parameter from aspx code. You can get losts of articles on net about this.

    3) in my development system i have this application . i have created the xsd file the aspx pages takes the datasource from webconfig file when i use the same application from another system (production server) i get error for datasource , i get the development system datasource instead of production server which it shld take in automatically. - You can store the connection string in web config file, which you are using to fetch data from report XSDs.

     


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Saturday, December 17, 2011 10:50 AM
  • reg point 1 i already have it

    =FIRST(Fields!CompanyName.value, "datasetname")

    but if i use it in the page header i get

    The Value expression for the textbox ‘textbox39’ refers to a field.  Fields cannot be used in page headers or footers.

     

    The Value expression for the textbox ‘textbox39’ uses a scope parameter for an aggregate function.  The scope parameter cannot be used in page headers or footers

     

    but if i use it in BODY i dont get any error msg but the valueis not displayed in teh  textbox


    svk
    Monday, December 19, 2011 6:04 AM
  • HI,

    Plz refer: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/9c5f9311-84cd-4f53-98a7-027fdcd0ee28


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Monday, December 19, 2011 8:50 AM
  • i tried the llink & other things but no value displayed in the textbox
    svk
    Wednesday, December 21, 2011 12:35 PM
  • Hi,

    but if i use it in BODY i dont get any error msg but the valueis not displayed in teh  textbox

    Have you verified that does your query returns some record by executing it in SQL Server Management Studio.

    You can also check that placing table control on report and show company data in it.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Wednesday, December 21, 2011 12:39 PM
  • yes i do have data

    & query return data even in the dataset

    i mean in xsd file when i use preview data i get the data

     

    when place din a table i get the name of the field but not the data or the value


    svk

    • Edited by SRekha Wednesday, December 21, 2011 12:51 PM
    Wednesday, December 21, 2011 12:45 PM
  • Hi,

    Can you just try to show this data into report using table control.

    and If you see data on report then

    =FIRST(Fields!CompanyName.value, "datasetname")

    This expression should work in body of report .

     


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Wednesday, December 21, 2011 12:53 PM
  • no i dont get

     


    svk
    • Edited by SRekha Wednesday, December 21, 2011 1:08 PM
    Wednesday, December 21, 2011 1:06 PM
  • Hi,

    Take one table control on report. Bind this company dataset to it and run report. If you see records on report 

    then following will also work in body section

    =FIRST(Fields!CompanyName.value, "datasetname")


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Wednesday, December 21, 2011 1:26 PM
  • can u heard about the Lookup, LookupSet or MultiLookup?

    you can get the data from mutiple dataset using the function "Lookup" which returns the single value from mutilple dataset.

    Wednesday, December 21, 2011 6:30 PM
  • hi

     

    iam still not able to unserdtand where the data goes

    as in the dataset.xsd file i get the data preview

    & from the same dataset when i drag in report i dont get the data

    how do i check where i data vanishes


    svk
    Thursday, December 22, 2011 6:22 AM
  • Hi,

    It is very hard to figure out problem, however you can check couple of things:

    1. Check your query, run it in SSMS. Does that accepts any parameter?
    2. Check whether you have any filters on dataset at report level. Right click dataset -> Properties -> filters.

    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Thursday, December 22, 2011 6:48 AM
  • hello Asim

    i am sorry

    it was a mistake on my part

    as the query used in the code was for some other dataset

    i changed the query & got it working

    Thnaks a lot

    now my query abt passing parameters is pending

    i need to pass values from asp.net controls to the report & get the report accordingly along with the paramters displayed in the report

    currently in the .aspx page i have dropdown with a selected value

    below it is the report viewer attached to  a report



    svk
    Thursday, December 22, 2011 7:11 AM
  • Hi,

    Plz refer: http://msdn.microsoft.com/en-us/library/aa964126%28v=sql.90%29.aspx


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Thursday, December 22, 2011 7:25 AM
  • i didnt find any parameter passing in the link
    svk
    Thursday, December 22, 2011 8:08 AM
  • Hi,

    http://www.codeproject.com/KB/reporting-services/ReportViewer2005.aspx

    http://odetocode.com/Articles/156.aspx


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Thursday, December 22, 2011 8:19 AM
  • hi you can you use the below link for how to pass the parameter value from code:

    http://forums.asp.net/t/1639519.aspx/1

    http://www.codeproject.com/Questions/143074/Passing-report-parameter-values-from-ReportViewer?display=Print

    http://forums.asp.net/t/1597578.aspx/1

     

    If you use mutiple tables value in your report.

    1)     you can join the two table and create a single query for that.

    2)     otherwise if you create two dataset then use the reporting service "Lookup"        Function which helps u to get the value from second dataset.

     

    Regards,

    Sanjay Barai

    Thursday, December 22, 2011 6:21 PM

  • i have this code

     protected void cmdok_Click(object sender, EventArgs e)
            {
                ReportParameter p = new ReportParameter("month", month);
                this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { p });
           


            }

     

    build action is content

     

    its fine when passing single paramter value but how can i pass multiple parameters

    • Edited by SRekha Saturday, December 24, 2011 9:32 AM
    Saturday, December 24, 2011 8:55 AM
  • Hi,

    You need to try something like below for multiple parameters

    ReportParameter[] para = new ReportParameter[2]; // Array for multiple parameter
    para
    [0] = new ReportParameter();
    para
    [0].Name = "parametername1";
    para
    [0].Values.Add("1");//any value

    para[1] = new ReportParameter();
    para
    [1].Name = "parametername2";
    para
    [1].Values.Add("1");//any value


    RsViewer.ServerReport.SetParameters(para);

    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Proposed as answer by Elvis Long Monday, December 26, 2011 7:10 AM
    • Marked as answer by Elvis Long Thursday, December 29, 2011 1:43 AM
    Monday, December 26, 2011 6:56 AM
  • Hi SRkeha,

    There is a related thread about pass multiple parameter values to ReportViewer control, please see:
    http://forums.asp.net/t/1338302.aspx/1

    For a new question, please post it in a new thread. It will benefit other community members who have the similar issue with you.

    Regards,
    Bin Long


    Bin Long

    TechNet Community Support

    • Marked as answer by Elvis Long Thursday, December 29, 2011 1:43 AM
    Monday, December 26, 2011 7:17 AM