We help IT Professionals succeed at work.

How to Cache Crystal Report Results

David_zu
David_zu asked
on
Hi Experts,

I am currently using Crystal Report in ASP.NET page. I use CrystalReportViewer object to display result to user. My problem is, when the user try to export report or change page, the report will be reload and query database again. If the query spends a lot of time, user will take very long time when change page or export report. Is there any way to cache the results?

I am also trying to use CrystalReportSource object. It has cache feature, but I don't know how to pass over the user defined parameters to it at runtime. If anyone can show me a sample. It will be really appreciated.

Here are some more details.

Crystal Report :
The Crystal Report is rpt file. Parameters are passed from ASP.NET page to RPT file. The parameters are used as input of stored procedure. Out put of stored procedure is a table.

ASP.NET Page :
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="False" GroupTreeImagesFolderUrl="" Height="1202px"
                OnInit="CrystalReportViewer1_Init" ToolbarImagesFolderUrl="" ToolPanelWidth="200px" Width="1104px"
                ToolPanelView="None" EnableDatabaseLogonPrompt="False" EnableParameterPrompt="False" OnError="CrystalReportViewer1_Error" 
                ReuseParameterValuesOnRefresh="True" OnLoad="CrystalReportViewer1_Load" Visible="true" />

Open in new window


Back End Code :
// Click button to show report
protected void btnViewReport_Click(object sender, EventArgs e)
        {
            LoadReport();
        }

// The event will be trigged whenever user click 'next page' or export report
protected void CrystalReportViewer1_Load(object sender, EventArgs e)
        {
                LoadReport();
        }

private void LoadReport()
        {
            try
            {
                ReportDocument rpt = new ReportDocument();
                rpt.Load(Server.MapPath(@"~\Test.rpt"));
                rpt.SetDatabaseLogon(conn.UserID, conn.Password, conn.DataSource, conn.InitialCatalog, false);
                rpt.SetParameterValue("@loginname", (HttpContext.Current == null) ? "" : User.Identity.Name);
                rpt.SetParameterValue("@strDate", DateTime.ParseExact(pickStrDate.Value, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));
                rpt.SetParameterValue("@endDate", DateTime.ParseExact(pickEndDate.Value, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));
                rpt.SetParameterValue("@dateRange", -1);
                rpt.SetParameterValue("@companyID", (lstCompany.SelectedIndex == -1) ? 0 : int.Parse(lstCompany.SelectedValue));
                CrystalReportViewer1.ReportSource = rpt;
            }
            catch (Exception)
            {
            }
        }

Open in new window

Comment
Watch Question

Top Expert 2011
Commented:
Add the ReportDocument instance to a session variable and set CrystalReportViewer1.ReportSource to use this variable instead of using ReportDocument instance directly
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
To pass parameters this link has code that shows how.  About 1/4 the way through the article

http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

mlmcc

Author

Commented:
It works great. Thank you for your suggestion.