private void ShowReport() { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); string FilterQuery = string.Empty; //DataTable dtSetHeader = null; //DataTable dtSetData = null; //DataTable dtTotalInvoiceNo = null; try { //string query = "Select NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'"; DataTable dtSetHeader = new DataTable(); DataColumn dc = new DataColumn(SessionKeys.NAME); dtSetHeader.Columns.Add(dc); dtSetHeader.Rows.Add(Session[SessionKeys.NAME].ToString()); //dtSetHeader = obj.GetData(query); string CustomerGroup = string.Empty; string Customer = string.Empty; string BU = string.Empty; if (DDLCustGroup.SelectedIndex > 0) { CustomerGroup = DDLCustGroup.SelectedValue.ToString(); } else { CustomerGroup = ""; } if (DDLCustomers.SelectedIndex > 0) { Customer = DDLCustomers.SelectedValue.ToString(); } else { Customer = ""; } if (DDLBusinessUnit.SelectedIndex >= 1) { BU = DDLBusinessUnit.SelectedItem.Value.ToString(); } else { BU = ""; } //FilterQuery = "EXEC SP_PARTYWISESALESUMMARY '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','" + CustomerGroup + "','" + Customer + "','" + BU + "'"; FilterQuery = "[SP_PARTYWISESALESUMMARY_TI_Version]"; //"SP_PARTYWISESALESUMMARY"; //+ Session["SiteCode"].ToString() + "','" //+ Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','" //+ Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','" //+ CustomerGroup + "','" //+ Customer + "','" //+ BU + "'"; //FilterQuery = " SELECT SITEID, INVOICE_NO, SIW.CUSTOMER_NAME,C.CUST_GROUP," // + " isnull(BOXQty,'0') as Box, isnull(PCSQTY,'0') as PCS, isnull(BOXPCS,'0') as [TotalBoxPCS], BOX as TotalQtyConv, SIW.LTR, LINEAMOUNT, DISC_AMOUNT, SEC_DISC_AMOUNT, DISC, TAX_CODE, TAX_AMOUNT, ADDTAX_CODE, " // + " ADDTAX_AMOUNT, AMOUNT,TD_Per,PE_Per,tdvalue FROM ACX_SALESUMMARY_PARTY_ITEM_WISE SIW" // + " INNER JOIN AX.INVENTTABLE INVT ON SIW.PRODUCT_CODE = INVT.ITEMID " // + " LEFT JOIN [ax].[ACXCUSTMASTER] C on SIW.Customer_Code = C.CUSTOMER_CODE" // + " where SITEID = '" + Session["SiteCode"].ToString() + "' and INVOICE_DATE >=" + // " '" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "' and INVOICE_DATE <='" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' " + // " ORDER BY INVOICE_DATE ASC , INVOICE_NO ASC "; List <string> param = new List <string>(); List <string> paramVal = new List <string>(); param.Add("@SiteId"); paramVal.Add(Session["SiteCode"].ToString()); param.Add("@StartDate"); paramVal.Add(Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd")); param.Add("@EndDate"); paramVal.Add(Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd")); param.Add("@customergroupname"); paramVal.Add(CustomerGroup); param.Add("@customername"); paramVal.Add(Customer); param.Add("@BUCODE"); paramVal.Add(BU); DataTable dtSetData = obj.GetData_New(FilterQuery, CommandType.StoredProcedure, param, paramVal); //dtSetData = new DataTable(); //dtSetData = obj.GetData(FilterQuery); string queryTotInv = " Select Count(Distinct INVOICE_NO) as InvoiceNo FROM ACX_SALESUMMARY_PARTY_ITEM_WISE " + " where SITEID = '" + Session["SiteCode"].ToString() + "' and INVOICE_DATE >=" + " '" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "' and INVOICE_DATE <='" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' group by CUSTOMER_NAME"; DataTable dtTotalInvoiceNo = obj.GetData(queryTotInv); LoadDataInReportViewer(dtSetHeader, dtSetData, dtTotalInvoiceNo); } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } }