Passing Parameters and Loading Crystal Report Programmatically

1. March 2010 11:55

C# SQL Server  10 Comments

Reporting is an important part for every application. Crystal reports are widely used and also available in Visual Studio for reporting purposes. I personally never like designing application interfaces and working on web designs, it needs good designing skills which I lack and so I never put my hands on designing anything, but sometimes we have to. In crystal reports when you create a report using wizard or just adding a blank report to the project and then fetching data using code (after designing), the report works fine on the machine where you have designed and develop it and the problem occurs when you have to deploy or distribute your application with reports on multiple machines or clients. Of course, the connection string or you can say in simple words the server name, database name, user name and password is different than the name you used in your connection string while you test and make your application ready to be distributed. Therefore, to overcome this problem I wrote a class which will help me to achieve this in one line and also keep my code neat and clean. So this simple class will let you set the connection for your report dataset and let your reports work properly without any problems.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;


namespace ReportExportDemo
{
    class Reports
    {
        static TableLogOnInfo crTableLogonInfo;
        static ConnectionInfo crConnectionInfo;
        static Tables crTables;
        static Database crDatabase;

        public static void ReportLogin(ReportDocument crDoc, string Server, string Database, string UserID, string Password)
        {
            crConnectionInfo = new ConnectionInfo();
            crConnectionInfo.ServerName = Server;
            crConnectionInfo.DatabaseName = Database;
            crConnectionInfo.UserID = UserID;
            crConnectionInfo.Password = Password;
            crDatabase = crDoc.Database;
            crTables = crDatabase.Tables;
            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
            {
                crTableLogonInfo = crTable.LogOnInfo;
                crTableLogonInfo.ConnectionInfo = crConnectionInfo;
                crTable.ApplyLogOnInfo(crTableLogonInfo);
            }
        }

        public static void ReportLogin(ReportDocument crDoc, string Server, string Database)
        {
            crConnectionInfo = new ConnectionInfo();
            crConnectionInfo.ServerName = Server;
            crConnectionInfo.DatabaseName = Database;
            crConnectionInfo.IntegratedSecurity = true;
            crDatabase = crDoc.Database;
            crTables = crDatabase.Tables;
            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
            {
                crTableLogonInfo = crTable.LogOnInfo;
                crTableLogonInfo.ConnectionInfo = crConnectionInfo;
                crTable.ApplyLogOnInfo(crTableLogonInfo);
            }
        }
    }
}

This class contains ReportLogIn method which is overloaded which lets you choose the type of datasourde you want to connect to. The first method in the class requires SQL Server authentication to log-on to the server and the second method is used when the server is configured on windows authentication, similar to Integrated Security = true.

Sample usage of the Reports class is as follows. I have used AdventureWorks database for this example. If you dont have adventure works database then you can download the sample database from Microsoft's website or from codeplex or you can create your own report and check it with this example.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;


namespace ReportExportDemo
{
    public partial class frm_main : Form
    {
        public frm_main()
        {
            InitializeComponent();
        }

        private void btn_prvrpt_Click(object sender, EventArgs e)
        {
            int Cust_Id = Convert.ToInt32(txt_customerid.Text);
            //string Cust_Id = txt_customerid.Text.Trim();

            //Initialize report document object and load the report in the report document object
            ReportDocument crReportDocument = new ReportDocument();
            crReportDocument.Load(Application.StartupPath+"\\Reports\\AdventureCustReport.rpt");
            
            //login to the server to get details from the server and populate to the report           
            Reports.ReportLogin(crReportDocument, "MX\\SERVER", "AdventureWorks", "sa", "pass#w0rd1");
            
            //Pass parameter to the report object
            crReportDocument.SetParameterValue("id", Cust_Id);

            //To create PDF from the crystal report
            crReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, "CustomerReport" + Cust_Id + ".pdf");
            
            //Preview the generated PDF
            System.Diagnostics.Process.Start(Application.StartupPath + "\\CustomerReport" + Cust_Id + ".pdf");
        }
    }
}

Note the namespaces I have used in the above code and also how I have used the Reports class ReportLogin method. For more clarification of the code download the sample application with the report below.

One important thing that you have to keep in mind while designing report is to set the parameter. After you have set the parameter and you use your report with the above code, the report will then populate the whole lot of records in the table trrespective of the parameter you pass to the report. So to avoid this and to view only the record of your choice you needs to set the formula in the crystal report. the formula goes like this:

Sample Syntax:

{<Table's Column Name>}={?<Parameter Name>}

In my report this formula looks something like this:

{CustomerID}={?ID}

Download: ReportExportDemo.zip (55.76 kb)

Currently rated 3.5 by 2 people

Comments (10)

ernst gruber ernst gruber
4/15/2010 5:06:45 PM #

Great Post, best I found for setting database programmatically. You saved me a lot of recerche. Greetings Ernst

Jitender Jitender
9/23/2011 4:55:35 PM #

Great Post...Helped me a lot to get rid of updating each crystal report

thuan thuan
9/27/2011 9:06:38 AM #

that great! it really helpful! thanks so much! :)

dell dell
2/17/2012 10:46:15 PM #

Crstal reports from sql query string http://csharp.net-informations.com/crystal-reports/csharp-crystal-reports-sql-query.htm

Firoz Mohamed Firoz Mohamed
9/9/2012 8:16:11 PM #

i just want to print a value in my report ... i have loaded values to dataset and its coming in the report, when i add parameter field it stops showing the datas in dataset and instead it shows only the value that i have passed is there any solution for this .. plz help

Prashant Prashant
9/10/2012 5:08:17 AM #

If you are setting a parameter for the report and loading a report on the basis of the parameter then you also need to set the parameter value to match the column. I have implemented in the solution above. Try that. If that doesn't help then let me know.

Saikat sarkar Saikat sarkar
1/23/2015 7:30:55 AM #

After using this code many time crystal report wants provider i(ex-sqlserver,oracle etc.).How to come out from this problem?Otherwise this code is helpful.

Ganesh Ganesh
8/1/2015 1:02:36 AM #

Great !...Thanks so much... best I found for setting database programmatically. You saved me a lot of work..

madmax madmax
6/17/2016 3:24:29 PM #

This is a great post. Just a couple of questions: 1. I think this code might work to access other database. How do you indicate what driver needs to be loaded? 2. Do you need a datasource defined in the system? Thank you

Prashant Prashant
6/26/2016 3:30:27 PM #

@madmax 1. I am not referring to any DB drivers here. It is just the simple credentials for the report to log in and populate the dataset. 2. Yes, there should be a datasouce defined in the report which will populate the records from the server.

Add Comment

Visit blogadda.com to discover Indian blogs Computers Blogs