locked
RDLC newbie questions (hopefully)...connecting dataset to report that contains multiple data tables RRS feed

  • Question

  • Hi All,

    We are post-deployment with a serious reporting issue that's causing us to rethink our reporting solution.  We're considering moving from what we have to SQL Server Reporting Services (client side).

    I have spent the past couple of days getting up to speed on this feature and seeing if we can easily migrate it into our existing application.  However, I've hit a couple of stumbling blocks and was hoping perhaps someone here could either tell me the solution or point me in the right direction.

    I'm not having any problems creating basic reports (e.g. flat data). 

    However, I am having problems creating reports where there are related tables in a dataset. 

    The way it works with our existing solution is that I get a dataset (which contains several data tables) and point it to the report's datasource.  That report expects those tables and I have defined table-relationships in the report which process and display the information correctly.

    I'm not having as much luck with RDLC. 

    I can go into futher detail about how I'm creating the report, but let me just ask these general questions first:
    1.  Can I set a dataset containing multiple datatables equal to a property on an RDLC report and that RDLC report know how to treat and display the data?
    2.  Is there a better/smarter than this to get a field selection from my datasource (remember, this information is coming from a stored procedure so connecting directly to the database is not an option):
        a.  In code, populate dataset
        b.  In code, write dataset schema to xml (e.g. an xsd file)
        c.  In Visual Studio, add the XSD file to project
        d.  Use fields from XSD file to drag and drop fields on report

    A fancy example would be nice too.  I've googled like crazy the past couple of days and downloaded as many samples as I can find (including the ones on ftponline.com, gotreportviewer.com, "Tudor's WebLog", and several others).  However, I have yet to find one that uses grouping and related datatables.

    Thank you so much (if, for nothing else, reading this post Big Smile  )
    Wednesday, September 12, 2007 6:50 PM

Answers

  • Reporting services does not support taking dataset objects with multiple data tables. Each dataset in an RDLC actually maps to a DataTable. However your RDLC can have multiple datasets defined within it so you shouldn't need to modify your application code a great deal to get this working.

     

    Simply execute your current stored proc that loads multiple result sets into a .NET Dataset and then map the individual data tables to the relevant report datasets.

     

    Here are a couple of threads with sample code

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1008247&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1516398&SiteID=1

     

    Alternatively search these forums for "ReportDataSource" for other examples.

     

    Hope this helps.

    Wednesday, September 12, 2007 7:05 PM

All replies

  • Reporting services does not support taking dataset objects with multiple data tables. Each dataset in an RDLC actually maps to a DataTable. However your RDLC can have multiple datasets defined within it so you shouldn't need to modify your application code a great deal to get this working.

     

    Simply execute your current stored proc that loads multiple result sets into a .NET Dataset and then map the individual data tables to the relevant report datasets.

     

    Here are a couple of threads with sample code

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1008247&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1516398&SiteID=1

     

    Alternatively search these forums for "ReportDataSource" for other examples.

     

    Hope this helps.

    Wednesday, September 12, 2007 7:05 PM
  •  Adam Tappis - IMGroup wrote:

    ...RDLC can have multiple datasets defined within it so you shouldn't need to modify your application code a great deal to get this working....



    Wow.  This little nugget has helped me get further in the past hour than I have searching the past couple of days.

    One question though:  How do you relate the keys between the two datatables? (e.g. PersonID on Person matches PersonID on Order)


    Wednesday, September 12, 2007 7:42 PM
  • You can't. An RDLC is not synnonymous to a typed or untyped dataset. An RDLC is a report definition file, which contains multiple datasets used to populate various aspects of a report (parameters, lists, tables, matrices). The datasets are unrelated apart from when using cascading parameters e.g. param1 populated by dataset1 and then dataset2 using param1 to filter values. Datasets usually contain a query definition and are populated by RS executing these queries. When using local reports, the data can be populated by the application.

    Wednesday, September 12, 2007 10:49 PM