locked
Local report RRS feed

  • Question

  • User-305496339 posted

    Hi Guys,

    I was wondering has anyone ever used a form to enter values for the parameter values the report would use. How do I set a report to use a stored procedure and then filter the data displayed in the report based on the values the user enters on the aforementioned form. Unfortunately 2 of the parameter values are dates on the form, so I think I have to do some conversion of this format 03/20/2019 to 20-03-2019. Thanks !!!

    Tuesday, September 3, 2019 3:42 PM

Answers

  • User665608656 posted

    Hi rkrex,

    I was wondering has anyone ever used a form to enter values for the parameter values the report would use. How do I set a report to use a stored procedure and then filter the data displayed in the report based on the values the user enters on the aforementioned form. Unfortunately 2 of the parameter values are dates on the form, so I think I have to do some conversion of this format 03/20/2019 to 20-03-2019. Thanks !!!

    To achieve this function, first, you should create a report rdl file to show your data:

    You can refer to this link : Creating a Report Server Project (Reporting Services)

    Then on the current page, add two search boxes for date parameters and modify the expression of textbox to ensure that the format of the date display is what you expect:

    =Format(Fields!StartDate.Value,"MM/dd/yyyy")

    Then in your webform page, you can create the reportviewer control and bind it:

        <form id="form1" runat="server">
            <asp:TextBox ID="StartDate" runat="server"></asp:TextBox>
            <asp:TextBox ID="EndDate" runat="server"></asp:TextBox>
            <asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" />
            <asp:ScriptManager runat="server"></asp:ScriptManager>
            <rsweb:reportviewer id="ReportViewer1" runat="server" width="800px"
                processingmode="Local" >
                </rsweb:reportviewer>
        </form>
      protected void Button1_Click(object sender, EventArgs e)
            {
                //set Processing Mode of Report as Local   
                ReportViewer1.ProcessingMode = ProcessingMode.Local;
                //set path of the Local report   
                ReportViewer1.LocalReport.ReportPath = @"Tutorial\Tutorial\Report4.rdl";
                //creating object of DataSet dsMember and filling the DataSet using SQLDataAdapter   
                DataSet dsMember = new DataSet();
                string ConStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                SqlConnection con = new SqlConnection(ConStr);
                con.Open();
                SqlCommand com = new SqlCommand("DateBetSelect", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@StartDate", StartDate.Text.ToString());
                com.Parameters.AddWithValue("@EndDate", EndDate.Text.ToString());
                //Providing DataSource for the Report   
                SqlDataReader reader = com.ExecuteReader();
                List<DateBet> dateBets = new List<DateBet>();
                while (reader.Read())
                {
                    DateBet erpEntity = new DateBet();
                    erpEntity.Id = (int)reader["Id"];
                    erpEntity.Name = (string)reader["Name"];
                    erpEntity.StartDate = (DateTime)reader["StartDate"];
                    erpEntity.EndDate = (DateTime)reader["EndDate"];
                    //
                    dateBets.Add(erpEntity);
                }
                ReportDataSource rds = new ReportDataSource("DataSet1", dateBets);
                ReportViewer1.LocalReport.DataSources.Clear();
                //Add ReportDataSource   
                ReportViewer1.LocalReport.DataSources.Add(rds); 
                con.Close();
            }

    Here is my store procedure :

    Create PROCEDURE DateBetSelect @StartDate DATE, @EndDate DATE
    AS
    SELECT * FROM  DateBet WHERE  StartDate >= @StartDate AND EndDate <= @EndDate
    

    Here is the result:

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 4, 2019 5:19 AM

All replies

  • User665608656 posted

    Hi rkrex,

    I was wondering has anyone ever used a form to enter values for the parameter values the report would use. How do I set a report to use a stored procedure and then filter the data displayed in the report based on the values the user enters on the aforementioned form. Unfortunately 2 of the parameter values are dates on the form, so I think I have to do some conversion of this format 03/20/2019 to 20-03-2019. Thanks !!!

    To achieve this function, first, you should create a report rdl file to show your data:

    You can refer to this link : Creating a Report Server Project (Reporting Services)

    Then on the current page, add two search boxes for date parameters and modify the expression of textbox to ensure that the format of the date display is what you expect:

    =Format(Fields!StartDate.Value,"MM/dd/yyyy")

    Then in your webform page, you can create the reportviewer control and bind it:

        <form id="form1" runat="server">
            <asp:TextBox ID="StartDate" runat="server"></asp:TextBox>
            <asp:TextBox ID="EndDate" runat="server"></asp:TextBox>
            <asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" />
            <asp:ScriptManager runat="server"></asp:ScriptManager>
            <rsweb:reportviewer id="ReportViewer1" runat="server" width="800px"
                processingmode="Local" >
                </rsweb:reportviewer>
        </form>
      protected void Button1_Click(object sender, EventArgs e)
            {
                //set Processing Mode of Report as Local   
                ReportViewer1.ProcessingMode = ProcessingMode.Local;
                //set path of the Local report   
                ReportViewer1.LocalReport.ReportPath = @"Tutorial\Tutorial\Report4.rdl";
                //creating object of DataSet dsMember and filling the DataSet using SQLDataAdapter   
                DataSet dsMember = new DataSet();
                string ConStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                SqlConnection con = new SqlConnection(ConStr);
                con.Open();
                SqlCommand com = new SqlCommand("DateBetSelect", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@StartDate", StartDate.Text.ToString());
                com.Parameters.AddWithValue("@EndDate", EndDate.Text.ToString());
                //Providing DataSource for the Report   
                SqlDataReader reader = com.ExecuteReader();
                List<DateBet> dateBets = new List<DateBet>();
                while (reader.Read())
                {
                    DateBet erpEntity = new DateBet();
                    erpEntity.Id = (int)reader["Id"];
                    erpEntity.Name = (string)reader["Name"];
                    erpEntity.StartDate = (DateTime)reader["StartDate"];
                    erpEntity.EndDate = (DateTime)reader["EndDate"];
                    //
                    dateBets.Add(erpEntity);
                }
                ReportDataSource rds = new ReportDataSource("DataSet1", dateBets);
                ReportViewer1.LocalReport.DataSources.Clear();
                //Add ReportDataSource   
                ReportViewer1.LocalReport.DataSources.Add(rds); 
                con.Close();
            }

    Here is my store procedure :

    Create PROCEDURE DateBetSelect @StartDate DATE, @EndDate DATE
    AS
    SELECT * FROM  DateBet WHERE  StartDate >= @StartDate AND EndDate <= @EndDate
    

    Here is the result:

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 4, 2019 5:19 AM
  • User-305496339 posted

    Thanks for your help. But local reports have extension .Rdlc. Never mind you are creating a local report.

    Thursday, September 26, 2019 10:04 PM