locked
Sub report query based on main report (Microsoft Report Viewer) RRS feed

  • Question

  • User640089599 posted

    Hi

    I searched the forum but couldn't find anyone with the same problem with me.  They were close but didn't help me solve my issue.

    I'm trying to learn how to make the sub-report get data from SQL Server based on a primary key passed from the main-report.

    An example:
    Main report displays all student names and the sub-report would display all their contact phone numbers.

    A visual:
    Bobby Smith

    Home Phone: 555-1212
    Cell Phone: 555-2323
    Work Phone: 555-3434

    Jane Doe

    Home Phone: 555-2020
    Cell Phone: 555-3030
    Work Phone: Null

    Ginger Doe

    Home Phone: 555-5062
    Cell Phone: 555-9586
    Work Phone: 555-2356

    SQL TABLES:
    Student
    ------------------------------------
    Student ID   |   Name
    ------------------------------------
    1                | Bobby Smith
    2                 | Jane Doe
    3                 | Ginger Doe

    Phone
    ------------------------------------
    Phone ID     |     Student ID    |        Number       |    Type
    ------------------------------------
    1                 |        1              |        555-1212      |  Home
    2                 |        1              |        555-2323      |  Cell
    3                 |        1              |        555-3434      |  Work
    4                 |        2              |        555-2020      |  Home
    5                 |        2              |        555-3030      |  Cell
    6                 |        2              |        555-5062      |  Home
    7                 |        3              |        555-9586      |  Cell
    8                 |        3              |        555-2356      |  Work

     

    What I have working:

    I have the main report listing all the student's names by creating a dataset (.xsd) in my App_Code folder and adding a TableAdapter with the query "Select * from Student".  I then created a report (.rdlc) and dragged and dropped the Name field from the dataset.  This all works fine.

    Now I'm trying to add the sub-report to list the student's phone number and that is where I'm stuck.

    I started doing the same thing as the main form.  First create my dataset (.xsd) in my App_Code folder and adding a TableAdapter.  I tried putting the query as "Select * from Phone where Phone.StudentID = @StudentID". I used @StudentID because I want to be able to send a variable to get the results (that's what I'm thinking).  Then I try to create the report (.rdlc) and drag the phone fields from the dataset.  I also try to set the Parameters and now I'm totally lost because the sub-report bombs all the time with the message "error subreport could not be shown".  Any help would be appreciated.

    I know I can use a table or matrix to accomplish this, but I want to learn to use SubReports.  THANKS!!!

    Monday, June 8, 2009 6:02 PM

Answers

  • User-1734134863 posted

    Hi,

    From you description, do you want to achieve a report which looks like this?

    If so, please check the following procedures.

    1. Create a main report and use a table control to include the information of the student table. Alike create a sub report and use a table control to include the information of the phone table.

    2. Add a new parameter "StudentID" to the subreport. Then set the a filter for the subreport to render the information according to the StudentID. It looks like this.

     

    3. In the main report, right click the detail row of the table, add a new row below. Drag a subreport control inside that new row. Then set the subreport property. It looks like this.

    4.In order to bind the data to the sub report, we have to add all the relative datasource to the main report. It looks like this.

    5.Use the following code display the report in ReportViewer.

    .aspx file

     <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
            Font-Size="8pt" Height="400px" Width="100%">
            <LocalReport ReportPath="MainReport.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
                        Name="TestDataSet_Student" />
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource2"
                        Name="subReportDataSet_Phone" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
            SelectMethod="GetData"
            TypeName="subReportDataSetTableAdapters.PhoneTableAdapter">
        </asp:ObjectDataSource>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            SelectMethod="GetData" TypeName="TestDataSetTableAdapters.StudentTableAdapter">
        </asp:ObjectDataSource>

     

    .cs file

     protected void Page_Load(object sender, EventArgs e)
        {
            ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(SetSubDataSource);
            this.ReportViewer1.LocalReport.Refresh();

        }
        public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
        {
            e.DataSources.Add(new ReportDataSource("subReportDataSet_Phone", "ObjectDataSource2"));
        }

     

    6.Try to run the report.

     

     

    Please feel free to let me know if I’ve misunderstood anything.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 11, 2009 3:47 AM
  • User-1734134863 posted

    Hi,

    Please try to use the following code.

       Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf SetSubDataSource
            ReportViewer1.LocalReport.Refresh()
        End Sub

        Protected Sub SetSubDataSource(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)
            e.DataSources.Add(New ReportDataSource("subReportDataSet_Phone", "ObjectDataSource2"))
        End Sub

     

    I look forward to receiving your test results.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 15, 2009 3:47 AM

All replies

  • User-1734134863 posted

    Hi,

    From you description, do you want to achieve a report which looks like this?

    If so, please check the following procedures.

    1. Create a main report and use a table control to include the information of the student table. Alike create a sub report and use a table control to include the information of the phone table.

    2. Add a new parameter "StudentID" to the subreport. Then set the a filter for the subreport to render the information according to the StudentID. It looks like this.

     

    3. In the main report, right click the detail row of the table, add a new row below. Drag a subreport control inside that new row. Then set the subreport property. It looks like this.

    4.In order to bind the data to the sub report, we have to add all the relative datasource to the main report. It looks like this.

    5.Use the following code display the report in ReportViewer.

    .aspx file

     <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
            Font-Size="8pt" Height="400px" Width="100%">
            <LocalReport ReportPath="MainReport.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
                        Name="TestDataSet_Student" />
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource2"
                        Name="subReportDataSet_Phone" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
            SelectMethod="GetData"
            TypeName="subReportDataSetTableAdapters.PhoneTableAdapter">
        </asp:ObjectDataSource>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            SelectMethod="GetData" TypeName="TestDataSetTableAdapters.StudentTableAdapter">
        </asp:ObjectDataSource>

     

    .cs file

     protected void Page_Load(object sender, EventArgs e)
        {
            ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(SetSubDataSource);
            this.ReportViewer1.LocalReport.Refresh();

        }
        public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
        {
            e.DataSources.Add(new ReportDataSource("subReportDataSet_Phone", "ObjectDataSource2"));
        }

     

    6.Try to run the report.

     

     

    Please feel free to let me know if I’ve misunderstood anything.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 11, 2009 3:47 AM
  • User640089599 posted

     Hi.  Thank-your for the reply.  I'm having an issue converting line 7 to VB. 

    The error message is: "'Public Event SubreportProcessing(sender As Object, e As Microsoft.Reporting.WebForms.SubreportProcessingEventArgs)' is an event, and cannot be called directly. Use a 'RaiseEvent' statement to raise an event."

    1    Imports Microsoft.Reporting.WebForms
    2
    3 Partial Class rptStudentPhoneList
    4 Inherits System.Web.UI.Page
    5
    6 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    7 ReportViewer1.LocalReport.SubreportProcessing += New SubreportProcessingEventHandler(SetSubDataSource)
    8 Me.ReportViewer1.LocalReport.Refresh()
    9 End Sub
    10
    11 Public Sub
    SetSubDataSource(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)
    12 e.DataSources.Add(New ReportDataSource("subReportDataSet_Phone", "ObjectDataSource2"))
    13 End Sub
    14 End Class

     
    Friday, June 12, 2009 9:43 AM
  • User-1734134863 posted

    Hi,

    Please try to use the following code.

       Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf SetSubDataSource
            ReportViewer1.LocalReport.Refresh()
        End Sub

        Protected Sub SetSubDataSource(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)
            e.DataSources.Add(New ReportDataSource("subReportDataSet_Phone", "ObjectDataSource2"))
        End Sub

     

    I look forward to receiving your test results.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 15, 2009 3:47 AM
  • User640089599 posted

    Thank-you VERY MUCH!!!!

    It worked!!!!

    Monday, June 15, 2009 12:08 PM
  • User475058188 posted

    Do you know of a way that this could be used for a many to many relationship? I have a table in between the two tables that i need to display data from, but I dont know how to get that data out since there is a different path to the data. Thanks.

    Friday, July 31, 2009 10:23 AM
  • User640089599 posted

    You'll have to elaborate on what you are trying to do. An example would help.

    You might even want to start a new post to get more visibility since the original question has already been answered.

    Cool

    Friday, July 31, 2009 11:06 AM
  • User1781299949 posted

    it is fully understandable. but it doesn't work for me. Error: Subreport could not be shown.  this error appear all the time.

    Sunday, September 13, 2009 10:54 AM
  • User-1734134863 posted

    Hi,

    it is fully understandable. but it doesn't work for me. Error: Subreport could not be shown.  this error appear all the time.

    Please confirm that you have added all the relative datasource to the main report.

    In addition, in order to facilitate other community members in search-related information, please write your question in a new thread, thanks for your cooperation.

     

     

    Sunday, September 13, 2009 9:58 PM
  • User-1377897029 posted

    Hi,

    Thank you for your help. I am facing problem to add two Report Datasources to Report. I used Report Wizard to design the report and unable to get the Report Datasource window.

    Please help me. 

    Saturday, December 12, 2009 3:27 AM
  • User120353699 posted

    I'm curious if this is version specific; i am using vsw2005 and do not see an options button when adding my datasource to the reports so used the report parameters but get an error that "a value expression used for report parameter 'ID' refers to a field.  Fields cannot be used in report parameter expressions."  As a result, the subreport property doesn't find a parameter 'ID'.

     

    Thanks!

     

    ejo

    Friday, February 5, 2010 12:58 PM
  • User1217847845 posted

    how to display page header for report viewer ? any suggestion ?

    its not getting displayed.

    Monday, September 20, 2010 10:26 AM
  • User-1283088242 posted

    This response was very helpful (thank you), but I just want to add one thing.

    In the parent report you have to right click on the subreport control and add the parameters to the subreport properties just as was done on the subreport itself. Without the parameters in both places it will not work.

    I'm making this note for anybody in the future who may be having trouble.

     

    Victor Del Prete

    Saturday, November 27, 2010 11:53 PM
  • User1064543573 posted

    Hello,

    Can you please tell me how can i this achieve in vs2010?There is different concept in it.I have 3 tables. which is related to it.Quote,QuoteConfiguration,QuoteProducts.

    Quote is parent table and QuoteCOnfiguration is child table of Quote and QuoteProduct is child table of QuoteConfiguration.

    In Quote  i need quotenumber and created date,in quoteconfiguration i need its name and in product table i need products name,qty,price quoteconfiguration name wise.

    Now can you please ealaborate how can i generate the report.

    How can i create subreport for the same,or is there any other solution to achieve this.


    reply me soon

    thanks

    sam

    Monday, December 20, 2010 7:16 AM