CREATE PROCEDURE  rptCheckListReport

@iCompanyId BIGINT,
@szITSfromPersol VARCHAR(100),
@szGroupBy varchar(10),
@szGroupBy1 varchar(10),
@szQuery1 VARCHAR(500),
@szQuery2 VARCHAR(500),
@szQuery3 VARCHAR(500),
@szQuery4 VARCHAR(500),
@szPicHeight VARCHAR(50),
@szPicWeight VARCHAR(50)

AS

BEGIN
    SELECT
        distinct  i.szDescription AS InspectionName, UPPER((Select Top 1 c.szName From Criteria c Where c.pkID = m.iCriteriaId)) AS CriteriaName,
            m.nInpectionScore as InspectionMasterScore,cq.szQuestion AS CriteriaQuestion, cq.nMax AS CriteriaQuestionMax,
        (Select Top(1) szCompanyName From ADMIN.dbo.psAdCompany) AS CompanyName
    FROM


psIMLInspectionMaster m
        JOIN Inspection i ON m.iInspectionId = i.pkID
            JOIN CriteriaQuestion cq ON m.iCriteriaQuestionId = cq.pkID
            JOIN CriteriaQuestionGrading cqg ON m.iCriteriaQuestionGradingId = cqg.pkID
    WHERE   dInspectionDate between '' + ltrim(rtrim(@szQuery3)) + '' and '' + ltrim(rtrim(@szQuery4)) + '' + ltrim(rtrim(@szQuery1)) + '' + ltrim(rtrim(@szQuery2))
END

[HttpGet]
    public ActionResult CreateCheckListReport(long lngCompanyId, string szITSfromPersol, string strGroupBy,
        string strGroupBy1, string strQuery1, string strQuery2, string strQuery3,
        string strQuery4, string strPicHeight, string strPicWeight)
    {
        string Username = Convert.ToString(ConfigurationManager.AppSettings["username"]);
        string Password = Convert.ToString(ConfigurationManager.AppSettings["password"]);
        string Servername = Convert.ToString(ConfigurationManager.AppSettings["DSN2"]);
        string Databasename = Convert.ToString(ConfigurationManager.AppSettings["databasename"]);
        string type = Convert.ToString(ConfigurationManager.AppSettings["ReportType"]);

        if (strGroupBy.IsEmpty()) strGroupBy = "";
        if (strGroupBy1.IsEmpty()) strGroupBy1 = "";
        if (strQuery1.IsEmpty()) strQuery1 = "";
        if (strQuery2.IsEmpty()) strQuery2 = "";
        if (strQuery3.IsEmpty()) strQuery3 = "";
        if (strQuery4.IsEmpty()) strQuery4 = "";

        ReportDocument reportdocument = new ReportDocument();
        TableLogOnInfo crtablelogoninfo = new TableLogOnInfo();
        ConnectionInfo crConnectionInfo = new ConnectionInfo();
        Tables crTables;
        reportdocument.Load(Server.MapPath("~/Reports/rptCheckList.rpt"));
        crConnectionInfo.ServerName = Servername;
        crConnectionInfo.DatabaseName = Databasename;
        crConnectionInfo.UserID = Username;
        crConnectionInfo.Password = Password;
        crTables = reportdocument.Database.Tables;
        foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
        {
            crtablelogoninfo = crTable.LogOnInfo;
            crtablelogoninfo.ConnectionInfo = crConnectionInfo;
            crTable.ApplyLogOnInfo(crtablelogoninfo);
        }

        reportdocument.SetParameterValue("@iCompanyId", lngCompanyId);
        reportdocument.SetParameterValue("@szITSfromPersol", "Persol Systems Limited.");
        reportdocument.SetParameterValue("@szGroupBy", strGroupBy);
        reportdocument.SetParameterValue("@szGroupBy1", strGroupBy1);
        reportdocument.SetParameterValue("@szQuery1", strQuery1);                                     // and #tblRetOutletSumm.iDistrictID=8
        reportdocument.SetParameterValue("@szQuery2", strQuery2);
        reportdocument.SetParameterValue("@szQuery3", strQuery3);
        reportdocument.SetParameterValue("@szQuery4", strQuery4);
        reportdocument.SetParameterValue("@szPicHeight", strPicHeight);
        reportdocument.SetParameterValue("@szPicWeight", strPicWeight);

        ReportExportType ReportExportType = Helper.Helper.GetReportExportType(type);

        reportdocument.ExportToDisk(ReportExportType.FormatType, Server.MapPath("~/Reports/rptFiles/" + ReportExportType.ExportName));

        MoveImageFiles();

        return File(Server.MapPath("~/Reports/rptFiles/" + ReportExportType.ExportName), ReportExportType.ExportMimeType);
    }

****http://localhost:59139/home/CreateCheckListReport?lngCompanyId=1&szITSfromPersol=Persol&strGroupBy=Region&strGroupBy1=Inspection&strQuery1=&strQuery2=&strQuery3=2016-10-27&strQuery4=2016-10-27&strPicHeight=1&strPicWeight=1****