Recently, while working on SSRS one thing that struck my mind is that we develop web/windows based custom solutions for our clients. And In various types of applications our client has requirements for facility to be able to take report printouts. In most of the cases we provide PDF reports but there are cases where we had to write specialized functions to generate the same report in word or other formats. SSRS reports provide facility wherein a user can directly export the report in different formats.

While working in VB6.0 I used to develop reports in Crystal reports and those reports used to be a part of my VB project, being called on clicking of certain buttons, passing parameters, providing different queries at runtime etc.

This made me think that, can't I do the same in ASP.net applications using SSRS which is in more demand today. So, I explored a lot and googled a bit to achieve my task and I was able to do so. And knowledge is always good to be shared so In this post I am going to discuss on how to call sql reports from front end(Asp.net web application in my case).

Steps:

  1. Create a web application project.
  2. Add a report
  3. Create dataset and parameters(if needed) in the report
  4. Define the layout of the report
  5. Add a web form
  6. Add a report viewer control onto the page
  7. In the code behind, Create a dataset and populate it with data to be displayed in Report
  8. Define report datasource and pass dataset to report data source created
  9. Pass report datasource to the report viewer.
  10. Execute the page.

 

Adding Report and defining Dataset and Parameters and Report layout


  • Right click on Solution Explorer and click on add new item
  • Select Report and Add a report.

 

  • In the Report Data Pane click on New -> Dataset
  • Set the name of the dataset, Create a connection to your database, specify the data source and table/stored procedure/query to fetch the data.

  • Once the Dataset has been created you will see the fields on the pane under the dataset(Customers in my case)

  • Define report parameters if needed.

  • Drag and drop fields and set the layout of report

 

Add Webform with report viewer control placed on it and in the code behind file write code as:

 

 

 

public partial class ReportViewer : System.Web.UI.Page
    {
        SqlConnection con;
        SqlCommand cmd;
        SqlDataAdapter dad;
        ReportDataSource rds;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

                switch (Session["ReportName"].ToString())
                {
                    case "Customers":
                        CustomersReport();
                        break;
                    case "CustomersWithParameters":
                        CustomersReportWithParameters();
                        break;
                }


                

            }
        }

        protected void CustomersReport()
        {
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorksDWConnectionString"].ConnectionString);
            cmd = new SqlCommand();
            cmd.CommandText = "SELECT * from DimCustomer";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            dad = new SqlDataAdapter();

            /* Initializing the object of Dataset created in the project */
            Customers cust = new Customers();
            dad.SelectCommand = cmd;

            dad.Fill(cust, cust.DimCustomer.TableName);

            this.ReportViewer1.LocalReport.DataSources.Clear();


            /***************************************************************/
            /*************************************************************** 
            /* setting up the report data source for the report called..****/
            /***************************************************************/
            /***************************************************************/

            rds = new ReportDataSource();
            rds.Name = "dstCustomers";   //name of the dataset that you created in the report
            rds.Value = cust.DimCustomer;

            this.ReportViewer1.LocalReport.DataSources.Add(rds);

            this.ReportViewer1.LocalReport.ReportPath = "Customers.rdlc";   //path of the report to be called
            this.ReportViewer1.LocalReport.Refresh();
        }

        protected void CustomersReportWithParameters()
        {
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorksDWConnectionString"].ConnectionString);
            cmd = new SqlCommand();
            cmd.CommandText = "SELECT * from DimCustomer";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            
            dad = new SqlDataAdapter();            
            
            ///* Initializing the object of Dataset created in the project */
            //Customers cust = new Customers();
            //dad.SelectCommand = cmd;

            //dad.Fill(cust, cust.DimCustomer.TableName);

            DataSet cust = new DataSet();
            dad.SelectCommand = cmd;
            dad.Fill(cust, "DimCustomers");

            this.ReportViewer1.LocalReport.DataSources.Clear();


            /***************************************************************/
            /*************************************************************** 
            /* setting up the report data source for the report called..****/
            /***************************************************************/
            /***************************************************************/

            rds = new ReportDataSource();
            rds.Name = "dstCustomers";   //name of the dataset that you created in the report
            rds.Value = cust.Tables[0];

            ReportParameter rptPrmGroupBy = new ReportParameter("rptPrmGroupBy", "Gender", true);
           
            this.ReportViewer1.LocalReport.DataSources.Add(rds);
           
            this.ReportViewer1.LocalReport.ReportPath = "CustomersWithParameterPassing.rdlc";   //path of the report to be called
            this.ReportViewer1.LocalReport.SetParameters(rptPrmGroupBy);
            this.ReportViewer1.LocalReport.Refresh();
        }
    }

 

 

Note:

  • The name of dataset provided in the ReportDataSource should be same as the name of dataset you set in the report.
  • In my Example I have used AdventureWorksDW database and DimCustomers Table.

 

Hope, This post is useful.

Happy Reporting!!

CallingSQLReportFromFrontEnd.rar (467.45 kb)