locked
Passing parameter in subreport from the parent record RRS feed

  • Question

  • User1078915448 posted

    I am creating a report using sql reporting services (.rdlc) in asp.net web application. I am displaying master records using List because I need to display one record in one page. In each page now I want to display the child records in a table inside the same List control.

    For example: In mater records there will be Department Name, Department Location, Department Zip. In child records there will be Employee Name, Employee Id, Employee Salary, Employee Start Date. There is a link between Dapartment and Employee table by Department Id. I want to display one department record in one page and the all employee of that department in same page in a table. And next page of the report will have other department record and respective employees in a table.

    I am being able to display Department records in each page but not being able to display Employees records in a table for particular Department Id. For this I used List control. Please let me know if you have any solution.

     

    Wednesday, March 16, 2011 3:18 PM

Answers

  • User1078915448 posted

    Thanks for your reply. I  resolved it myself by writing the codes with using sub report.

     

    int SubReportPageNum = 0;
        DataTable dtDepartment = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Load_Records();
            }
        }
    
        void LocalReport_SubreportProcessing(object sender, SubreportProcessingEventArgs e)
        {
            //It goes in loop with the number of rows
            int deptId = Convert.ToInt32(dtDepartment.Rows[SubReportPageNum]["Dept_Id"]);
            e.DataSources.Add(new ReportDataSource("ReportDataSources_GetEmployeesByDeptId", GetEmployeesList (deptId)));
            SubReportPageNum = SubReportPageNum + 1;
        }
        private void Load_Records()
        {
            ReportViewer1.Visible = true;
            ReportViewer1.ProcessingMode = ProcessingMode.Local;
            ReportViewer1.LocalReport.DataSources.Clear();
            dtDepartment= GetDepartment();
            ReportDataSource reportDSHeader = new ReportDataSource("ReportDataSources_GetDepartment", dtDepartment);
            ReportViewer1.LocalReport.DataSources.Add(reportDSHeader);
            this.ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(LocalReport_SubreportProcessing);
            //ReportViewer1.LocalReport.DataSources.Add(reportDSDetail);
            this.ReportViewer1.LocalReport.Refresh();
        }
    
        private DataTable GetDepartment()
        {
            string conStr = ConfigurationSettings.AppSettings["ConnectionString"];
            SqlConnection sqlCon = new SqlConnection(conStr);
            DataSet dsHeader = new DataSet();
            SqlCommand objCmd = new SqlCommand();
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = "PROC_GetDepartments";
            objCmd.Connection = sqlCon;
            sqlCon.Open();
            SqlDataAdapter objDa = new SqlDataAdapter(objCmd);
            objCmd.ExecuteNonQuery();
            objDa.Fill(dsHeader, "Depts");
            sqlCon.Close();
            return dsHeader.Tables[0];
        }
        private DataTable GetEmployeesList(int deptId)
        {
            string conStr = ConfigurationSettings.AppSettings["ConnectionString"];
            SqlConnection sqlCon = new SqlConnection(conStr);
            DataSet dsDetail = new DataSet();
            SqlCommand objCmd = new SqlCommand();
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = "[PROC_GetEmployeesListByDeptId]";
            objCmd.Parameters.Add("@DeptId", SqlDbType.Int).Value = deptId;
            objCmd.Connection = sqlCon;
            sqlCon.Open();
            SqlDataAdapter objDa = new SqlDataAdapter(objCmd);
            objCmd.ExecuteNonQuery();
            objDa.Fill(dsDetail, "EmpList");
            sqlCon.Close();
            return dsDetail.Tables[0];
        }
        protected void ReportViewer1_ReportRefresh(object sender, System.ComponentModel.CancelEventArgs e)
        {
            Load_Records();
        }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2011 4:59 PM

All replies

  • User1471008070 posted

    From your information, I notice that you want to display a subreport in a main report within local mode report, this kind of the report is not like reporting service, of course the method of creating a subreport in the main report is different as well. Besides the main report datasource, you should add subreport's datasource to the main report as well. This walkthrough is focus this issue, you could click this link to get a detailed reference: http://cid-45bfd76b0f2c3808.photos.live.com/view.aspx/picture/Walkthrough-Displaying%20Sub%20report%20in%20Main%20report%20of%20local%20report%20using%20Report%20Viewer.docx

    Thanks,
    Challen Fu

    Wednesday, March 23, 2011 6:26 AM
  • User1078915448 posted

    Thanks for your reply. I  resolved it myself by writing the codes with using sub report.

     

    int SubReportPageNum = 0;
        DataTable dtDepartment = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Load_Records();
            }
        }
    
        void LocalReport_SubreportProcessing(object sender, SubreportProcessingEventArgs e)
        {
            //It goes in loop with the number of rows
            int deptId = Convert.ToInt32(dtDepartment.Rows[SubReportPageNum]["Dept_Id"]);
            e.DataSources.Add(new ReportDataSource("ReportDataSources_GetEmployeesByDeptId", GetEmployeesList (deptId)));
            SubReportPageNum = SubReportPageNum + 1;
        }
        private void Load_Records()
        {
            ReportViewer1.Visible = true;
            ReportViewer1.ProcessingMode = ProcessingMode.Local;
            ReportViewer1.LocalReport.DataSources.Clear();
            dtDepartment= GetDepartment();
            ReportDataSource reportDSHeader = new ReportDataSource("ReportDataSources_GetDepartment", dtDepartment);
            ReportViewer1.LocalReport.DataSources.Add(reportDSHeader);
            this.ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(LocalReport_SubreportProcessing);
            //ReportViewer1.LocalReport.DataSources.Add(reportDSDetail);
            this.ReportViewer1.LocalReport.Refresh();
        }
    
        private DataTable GetDepartment()
        {
            string conStr = ConfigurationSettings.AppSettings["ConnectionString"];
            SqlConnection sqlCon = new SqlConnection(conStr);
            DataSet dsHeader = new DataSet();
            SqlCommand objCmd = new SqlCommand();
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = "PROC_GetDepartments";
            objCmd.Connection = sqlCon;
            sqlCon.Open();
            SqlDataAdapter objDa = new SqlDataAdapter(objCmd);
            objCmd.ExecuteNonQuery();
            objDa.Fill(dsHeader, "Depts");
            sqlCon.Close();
            return dsHeader.Tables[0];
        }
        private DataTable GetEmployeesList(int deptId)
        {
            string conStr = ConfigurationSettings.AppSettings["ConnectionString"];
            SqlConnection sqlCon = new SqlConnection(conStr);
            DataSet dsDetail = new DataSet();
            SqlCommand objCmd = new SqlCommand();
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = "[PROC_GetEmployeesListByDeptId]";
            objCmd.Parameters.Add("@DeptId", SqlDbType.Int).Value = deptId;
            objCmd.Connection = sqlCon;
            sqlCon.Open();
            SqlDataAdapter objDa = new SqlDataAdapter(objCmd);
            objCmd.ExecuteNonQuery();
            objDa.Fill(dsDetail, "EmpList");
            sqlCon.Close();
            return dsDetail.Tables[0];
        }
        protected void ReportViewer1_ReportRefresh(object sender, System.ComponentModel.CancelEventArgs e)
        {
            Load_Records();
        }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2011 4:59 PM