Return PDF/TXT/XLS/PXLS to browser in ASP.NET MVC using crystal report

This example is using SAP crystal report for Visual studio. Can get a copy here for free.

Below sample show how to generate different file type format report to the browser dynamically.

 

In Controller:

public ActionResult PrintReport(string filters, string fileType)
 {
  var page = FilterPage(new Page<StaffOrder>(), filters);

  report = ReportUtil.GetReport<StaffOrder, StaffOrderReport>(StaffOrderModel.PropertyNames, page.result.OrderBy(x => x.StaffName).ThenBy(z => z.Id).ToList());
  ReportUtil.AssignBasicInfo(report, "Staff Order Report", WebUtil.GetUserDetails().username);

  return ReportUtil.GenerateReport(report, fileType);

 }

 

In ReportUtil class:

using System.Globalization;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Web.Mvc;
using Microsoft.Office.Interop.Excel;

namespace STDSInternal
{
 public class ReportUtil
 {
 public static TK GetReport<T, TK>(string[] names, IList<T> items)
 where T : class
 where TK : ReportClass, new()
 {
 var report = new TK();
 report.Load();
 report.SetDataSource(GetDataTable<T>(names, items));
 return report;
 }
public static System.Data.DataTable GetDataTable<T>(string[] names, IList<T> items)
{
 var dt = new System.Data.DataTable();
 dt.Columns.AddRange(GetDataColumns(names));
 foreach (T item in items)
 {
 var objs = new List<object>();
 foreach (var name in names)
 {
 PropertyInfo pi = item.GetType().GetProperty(name);
 var obj = pi.GetValue(item);
 objs.Add(DoConversion(obj));
 }
 dt.Rows.Add(objs.ToArray());
 }
 return dt;
 }

public static DataColumn[] GetDataColumns(string[] columnNames)
 {
 return columnNames.Select(name => new DataColumn(name)).ToArray();
 }

public static FileStreamResult GenerateReport<T>(T report, string fileType) where T : ReportClass
 {
 var contentType = WebConstants.APPLICATION_PDF;
 switch (fileType)
 {
 case "txt":
   report.ExportOptions.ExportFormatType = ExportFormatType.Text;
   contentType = WebConstants.TEXT_PLAIN;
   break;
 case "xls":
   report.ExportOptions.ExportFormatType = ExportFormatType.Excel;
   contentType = WebConstants.TEXT_XLS;
   break;
 case "pxls":
   report.ExportOptions.ExportFormatType = ExportFormatType.ExcelRecord;
   contentType = WebConstants.TEXT_XLS;
   fileType = "xls";
   break;
 default:
   report.ExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
   fileType = "pdf";
   break;
 }
 return new FileStreamResult(report.ExportToStream(report.ExportOptions.ExportFormatType), contentType)
 {
 FileDownloadName = report.SummaryInfo.ReportTitle + "." + fileType
 };

}

 

In WebConstants class file:

public static readonly string APPLICATION_PDF = "application/pdf";
public static readonly string TEXT_PLAIN = "text/plain";
public static readonly string TEXT_XLS = "text/xls";

 

 

 

 

 

Advertisements

Repeat page header in each page – Crystal Reports

By default, crystal report will automatically display the page header in each page. But if there is more than one report, where now I have few subreports in a report where each subreport contain its own page header, it will not display the page header in a new page. I have tried to use this method and it works to display the page header for each subreport in each page.

cr

In each of the subreports,

1. add a formula field.

cr22. Put in this formula in the formula field as below

cr3

3. Group by the formula field

cr44. Put all the columns to the group header section

cr5

Each page header will be display nicely in each page.

Manual Cross Tab Dynamic Crystal Report

Lately I’m been involve in a lot of crystal reports and one of them is a cross-tab dynamic report which will grow vertically and horizontally. There are a lot of ways to do it. And I think the most easiest way is to use the cross-tab function in the Crystal report which directly pump in all your data and let the crystal report do the rest. Unfortunately, it is too late for me to realized there is such function to use, and I used the way of coding which will faced some challenges in the midst of preparing the format of data before putting it into crystal report and display it.

OK. Here is just something to share for those who wanna use such method to display your data. This is what I’ve done, and may not be the most efficient way to do. Btw, I’m using MVC c# and nHibernate for this case.

Below is the View in my database, which you can see where the report will be display according to do order ValueDate, BrokerName, and TradePLCcyCode.

data in database
data in database

And Below is the Crystal report format where data will be populated into and be display. I have created a sub report for this to make ease if next time there is a need of adding new extra report to this report. And the below diagram shows the section where data will be displayed. I have fix it to 10 rows for Currency, 6 Columns for Broker and Total. And yeah, it is group by ValueDate. These fields are also define in my dataset file(.xsd).

crystal report
crystal report
subreport
subreport

Now, we go to the coding part. Before I pump in my data from view to the report, I have to convert all the data from view into a datatable before assigning them row by row into an object. Below is the datatable generated from the view where all data are populated into. As you can see there are different ValueDate which we have to group them later.

datatable
datatable

Now is the challenging part where I have to split this ‘huge’ datatable into sections of smaller datatable to be displayed. So What I’ve done is, firstly I split it by ValueDate. It depends on how many ValueDate in my datatable.

split by value date
split by value date

Above code split the ‘huge’ datatable into smaller lists of datatables with different ValueDate. And from here, I will loop through the number of distinct ValueDate. However, the rows and columns may exceed what I’ve set in the Crystal report, so I have to split these smaller lists of datatables into the sections that fit in the report.

How to know how many section for my rows and columns? As above, I have set 6 rows and 10 columns. So a loop function is use to loop through the ‘huge’ datatable to do the following process:

1. splitting it into smaller datatables. It will split by rows then by columns.

E.g. table is 12 X 20, it will split into 4 smaller tables.

table is 12 X 10, it will split into 2 smaller tables.

2. assigning each smaller datatables in an object.

3. assigning all objects into a list of object.

After that, will pass this list of object into the crystal report to be print.

Below is a sample of the report generated.

 Capture3