How to create crystal reports in asp.net mvc

It is easy to create crystal reports in asp.net web form applications, but in MVC its bit difficult than web forms since MVC does not have user controls and events. There is no code behind file either. So today i am going to explain how to create a crystal report in asp.net MVC.

First, create a MVC application project, and then create InvoiceHeader class and InvoiceDetails class inside your model folder as below.

public class InvoiceHeader

    {

        public int InvoiceId { get; set; }

        public string InvoiceNo { get; set; }

        public string AccountNo { get; set; }

        public string CustomerName { get; set; }

        public string BillingAddress { get; set; }

        public DateTime InvoicedDate { get; set; }

        public decimal SubTotal{get;set;}

        public decimal DiscountAmount { get; set; }

        public decimal TaxAmount { get; set; }

        private decimal _InvoiceAmount;

        public Decimal InvoiceAmount

        {

            get

            {

                _InvoiceAmount = SubTotal - DiscountAmount + TaxAmount;

                return _InvoiceAmount;

            }

            set

            {

                if (_InvoiceAmount == 0)

                    _InvoiceAmount = 0.0M;

                else

                    _InvoiceAmount = value;

            }

        }

        public List<InvoiceDetails> Details { get; set; }

       

    }

public class InvoiceDetails

    {

        public int InvoiceId { get; set; }

        public int ItemID { get; set; }

        public string ItemCode { get; set; }

        public string Description { get; set; }

        public decimal UnitPrice { get; set; }

        public int Quantity { get; set; }

        public decimal DiscountAmount { get; set; }

        public decimal TaxAmount { get; set; }

        public decimal TotalAmount { get; set; }

    }

Then create a file named “InvoiceDAL.cs” inside a folder called DAL. There i have added a method called getAllInvoices() to get invoice data .I just put some dummy data and return a list of invoices to show in crystal report .

public List<InvoiceHeader> getAllInvoices()

        {

            List<InvoiceHeader> invoiceList = new List<InvoiceHeader>();

            //here add some invoice headers with invoice details

            //You can download the source code and find this file  if you want

            //invoiceList.Add(new InvoiceHeader{InvoiceId=1, .....,,Details=detailslist.....)

            return invoiceList;

        }

You can get data from database as you want. Here i want to show how to create a crystal report in MVC. So i am not going to make this demo with database connections etc. But i am following MVC architecture well.

After creating model class and data layer ,create a folder named "Reports" inside the solution and add new data set item .(right click the folder->add ->new item ->select DataSet from the dialog window) name it as InvoiceDtSet.xsd .This is the custom data set which we are going to map with crystal report later.

Then add two data tables (InvoiceHeaderTbl and InvoiceDetailsTbl) to InvoiceDtSet dataset and add columns as you want to show in crystal report. Make sure to put correct data types for columns. (Right click column ->properties ->change Data type property)

 Data set should looks like this.

Here we can put relationship between tables .But it s not necessary.

Then create a controller. I have named it as reportController.cs .Then create a action method inside it and create a view where you want to put a button to show crystal report. 

       public ActionResult Index()

        {

            return View();

        }

In the "Index.aspx" view page put this code lines to create a button and "PrintInvoice()" java script method.

<label>Click here to get all invoices</label>

<input type="button" onclick="PrintInvoice()" value="Print"/>

<script type="text/javascript">

function PrintInvoice()

        {

            $.ajax({

                    url: '<%=ResolveUrl("~/")%>Reports/InvoicePrint',

                    //data: JSON.stringify(oParam),if you want to pass data you                       can pass as json object here

                    type: 'POST',

                    contentType: 'application/json;',

                    dataType: 'json',

                    success: function () {

                     window.open("<%=ResolveUrl("~/")%>Reports/MyInvoices.aspx");

                    }

                });

               

           }

</script>

I assume you have good javascript/jquery knowledge .Here I have implemented Ajax request to get data by server side controller.

Then add these two methods to your “reportcontroller”.

Here what i am doing is filling the data set and put it in to the session.

        /// <summary>

        ///puting filled dataset in to session

        /// </summary>

        public void InvoicePrint()

        {

            this.HttpContext.Session["rptSource"] = getInvoice();

        }

        /// <summary>

        /// This method is for getting invoices and filling the dataset which we are going to             asign to the crystal report

        /// </summary>

        /// <returns></returns>

        private DataSet getInvoice()

        {

            InvoiceDtSet invdt = new InvoiceDtSet();

            try

            {

                InvoiceDAL dal = new InvoiceDAL();

                List<InvoiceHeader> results = dal.getAllInvoices();

                results = results.OrderByDescending(x => x.InvoiceNo).ToList();

                foreach (InvoiceHeader inv in results)

                {

                                  /// filling invoiceheader data table

                    invdt.InvoiceHeaderTbl.AddInvoiceHeaderTblRow(inv.InvoiceId,                                 inv.InvoiceNo, inv.AccountNo, inv.CustomerName, inv.BillingAddress,                           inv.InvoicedDate, inv.SubTotal, inv.DiscountAmount, inv.TaxAmount,                           inv.InvoiceAmount);

                    if (inv.Details != null && inv.Details.Count > 0)

                    {

                        foreach (InvoiceDetails invDet in inv.Details)

                        { 
                                           /// filling invoiceDetails data table

                         invdt.InvoiceDetailsTbl.AddInvoiceDetailsTblRow(invDet.InvoiceId,                            invDet.ItemID, invDet.ItemCode, invDet.Description,                                          invDet.UnitPrice, invDet.Quantity, invDet.DiscountAmount,                                    invDet.TaxAmount, invDet.TotalAmount); 

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                throw new Exception(ex.Message);

            }

            return invdt;

        }

Then create a new web form inside Reports folder. Name it as "MyInvoices.aspx".Drag a "crystal report viewer control" from tool box and put it to "MyInvoices.aspx" and go to its code behind file. "Page_Load” event should like this.

**Note: You must have install Crystal reports in your computer.

**Note: You must add references to  CrystalDecisions.CrystalReports.Engine (It is under References->.Net )

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using CrystalDecisions.CrystalReports.Engine;

namespace crystalreportswithMvc.Reports

{

    public partial class MyInvoices : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            InvoiceDtSet rptSource =                                                         (InvoiceDtSet)System.Web.HttpContext.Current.Session["rptSource"];

            ReportDocument rptDoc = new ReportDocument();

            // Your .rpt file path will be below

            rptDoc.Load(Server.MapPath("~/Reports/InvoiceReport.rpt"));

            //set dataset to the report viewer.

            rptDoc.SetDataSource(rptSource);

            InvoiceViewver.ReportSource = rptDoc;

        }

    }

}

Here what i am doing is  taking  the dataset which i put earlier in to the session "rptSource" and assigning  it as data source to the report document.

 rptDoc.Load(Server.MapPath("~/Reports/InvoiceReport.rpt"));

This line of code loads the crystal report to report document.

And then report document is assigned as the crystal report viewer control's report source. "InvoiceViewver" is the name of crystal report viewer control in the web form.

InvoiceViewver.ReportSource = rptDoc;

**Note: Be careful with paths/URLs of files**

So far we didn't create a crystal report. Now add a new crystal report item under Reports folder and name it as "InvoiceReport.rpt". Then choose crystal report wizard and then choose the data source under "project Data ->ADO.net DataSet" .You have to choose the data set which we have already created ("InvoiceDtSet"). Then choose both tables InvoiceHeaderTbl and InvoiceDetailsTbl and finish.

Now we have a crystal report bound with our dataset. Then drag and drop fields from Field Explorer ->database fields-> invoiceHeaderTbl as you want. You can design the report view as you wish.(I am not going to explain how to design crystal report here because here i am talking only about integration of crystal report with MVC. )

This picture depicts the arrangement of invoiceHeadertbl fields.

**Then right click on a section and go to section expert and choose details section, go to Paging tab and put     a tick in front of ”end of section" property. If you don't do this all invoices will show in a one page.

How add a sub report inside crystal report to show invoice item details?

Right click in the middle of the details section and choose insert -> subreport .This sub report is to show invoice item details. In the dialog box appeared choose "Create a sub report with the report wizard” option and put a name for the sub report and click report wizard button. Then select “InvoiceDetailsTbl” and click finish.

Then right click on sub report and click edit sub report and design the invoice details as you want. Below picture will show the arrangement of the sub report.

Then we have to link invoiceheader and invoiceDetils by InvoiceID. To do this right clicks on subreport in main report ->Choose "change subreport links”. Then in the dialog box choose the field to link from invoiceHeaderTbl and choose matching field from subreport to link.

Now one InvoiceHeader shows only related InvoiceDetails in report view.

Ok, now you can run your project and go to  the relevant action method and click on the button created and view the crystal report .According to above code example  go to "http://localhost:62848/reports/index"(port may vary) and click "print"  button.

You will see the crystal report like this.

**Only thing you have to remember is to link sub report and main report carefully.

Difference between Asp.net web forms and MVC in integration of crystal report is in MVC we have to create separate “.aspx” page and load it using a java script. We have to do this because MVC does not have events to fire.

If you want to load crystal report in the same page (Page with the print button -index.aspx) only thing you have to do is create an Iframe and set its src to the "/Reports/MyInvoices.aspx".To do this you have to change the java script little bit.

<iframe id="ReportFrame" src="" width="100%" height="1000px" style="border:none">
</iframe>

<script type="text/javascript">
       function PrintInvoice() 
          {   
           $.ajax({
            url: '<%=ResolveUrl("~/")%>Reports/InvoicePrint', 
            //data: JSON.stringify(oParam),if you want to pass data you can                               pass as json object here
            type: 'POST', 
            contentType: 'application/json;',
            dataType: 'json',
         
            success: function () {
           $("#ReportFrame").attr("src"," <%=ResolveUrl("~/")%>Reports/MyInvoices.aspx");                                     }             
               });              
           }
</script>

Hope you got an idea about integration of crystal reports on MVC framework well. You can download the source code of this example from below link .If you have any question or  comments about this article please feel free to post them as comments.

You can download source code from here.

(Go to the above link and choose File->download to download the zip file)