How to create dynamic reports....

protected void CreateReport(String QID)

{

string myConnectionString = "";
if (myConnectionString == ""){
myConnectionString =
WebConfigurationManager.ConnectionStrings["PrakashArtsConnectionString"].ConnectionString;}

try{
//Only Remove From sql Query DisplayMaster.DisplayHeight, Width,Sqft................SqlConnection myConnection = new SqlConnection(myConnectionString);
String sql = "SELECT DisplayMaster.DisplayLocation, DisplayMaster.DisplayMedia, DisplayMaster.DisplayQuantity,DisplayDetails.DisplayWidth,DisplayDetails.DisplayHeight, DisplayDetails.DisplayListPrice, QuotationDetails.QuotedPrice, DisplayDetails.AvailableFromDate, DisplayDetails.Direction, DisplayMaster.Description, DisplayMaster.Bytes FROM QuotationDetails INNER JOIN Quotations ON QuotationDetails.QuotationID = Quotations.QuotationID INNER JOIN DisplayDetails ON QuotationDetails.DisplayDetailsID = DisplayDetails.DisplayDetailsID INNER JOIN DisplayMaster ON DisplayDetails.DisplayID = DisplayMaster.ID where QuotationDetails.QuotationID=" + QID;

DataSet objDs1 = new DataSet();
DataSet objDs2 = new DataSet();myConnection.Open();

SqlDataAdapter dAdapter = new SqlDataAdapter(sql, myConnection);dAdapter.Fill(objDs1,
"tab");myConnection.Close();
sql =
"SELECT QuotationProductsDetails.ProductQuantity, QuotationProductsDetails.ProductRate, Products.ProductName from QuotationProductsDetails, Products WHERE QuotationProductsDetails.ProductCode=Products.ProductCode AND QuotationID=" + QID;myConnection.Open();
dAdapter =
new SqlDataAdapter(sql, myConnection);dAdapter.Fill(objDs2,
"tab1");myConnection.Close();

if (objDs1.Tables[0].Rows.Count > 0){
Session[
"objDs1"] = objDs1;
XtraReport1 report = new XtraReport1();
XRLabel label1 = new XRLabel();label1.Text =
"Dear "+ Fname.Text + " " + Lname.Text;label1.Width = 900;

XRLabel label2 = new XRLabel();label2.Text = ClientCompanyName.Text;
label2.Width = 900;

XRLabel label3 = new XRLabel();label3.Text = ClientAddress.Text;
label3.Width = 900;

XRLabel label4 = new XRLabel();label4.Text = ClientCity.Text+
" "+ClientState.Text;label4.Width = 900;

XRTable tb = new XRTable();report.Bands[DevExpress.XtraReports.UI.
BandKind.ReportHeader].Controls.Add(tb);
XRTableRow row1 = new XRTableRow();
XRTableCell cl1 = new XRTableCell();cl1.Text = label1.Text;
row1.Cells.Add(cl1);

XRTableRow row2 = new XRTableRow();
XRTableCell cl2 = new XRTableCell();cl2.Text = label2.Text;
row2.Cells.Add(cl2);

XRTableRow row3 = new XRTableRow();
XRTableCell cl3 = new XRTableCell();cl3.Text = label3.Text;
row3.Cells.Add(cl3);

XRTableRow row4 = new XRTableRow();
XRTableCell cl4 = new XRTableCell();cl4.Text = label4.Text;
row4.Cells.Add(cl4);
tb.Rows.Add(row1);
tb.Rows.Add(row2);
tb.Rows.Add(row3);
tb.Rows.Add(row4);


XRPivotGrid pivotGrid = new XRPivotGrid();pivotGrid.Appearance.Lines.BorderColor= System.Drawing.
Color.Black;
//pivotGrid.OptionsView.HideAllTotals();pivotGrid.OptionsView.ShowRowGrandTotals = true;pivotGrid.OptionsView.ShowColumnGrandTotalHeader =
true;pivotGrid.OptionsView.ShowRowTotals =
true;pivotGrid.Appearance.GrandTotalCell.BackColor = System.Drawing.
Color.Transparent;pivotGrid.Appearance.GrandTotalCell.BorderColor = System.Drawing.
Color.Transparent; pivotGrid.Width = 1300;
pivotGrid.Appearance.FieldHeader.BackColor = System.Drawing.
Color.DarkBlue;pivotGrid.Appearance.FieldHeader.ForeColor = System.Drawing.
Color.White;report.Bands[DevExpress.XtraReports.UI.
BandKind.Detail].Controls.Add(pivotGrid);pivotGrid.DataSource = objDs1;
pivotGrid.DataMember =
"tab";
//Pivot fields......................................................................................................................XRPivotGridField fieldDisplayLocation = new XRPivotGridField("DisplayLocation", PivotArea.RowArea);fieldDisplayLocation.Caption =
"Location";fieldDisplayLocation.Width = 80;

XRPivotGridField fieldDisplayMedia = new XRPivotGridField("DisplayMedia", PivotArea.RowArea);fieldDisplayMedia.Caption =
"Media";fieldDisplayMedia.Width = 60;

XRPivotGridField fieldDisplayWidth = new XRPivotGridField("DisplayWidth", PivotArea.RowArea);fieldDisplayWidth.Caption =
"Wd";fieldDisplayWidth.Width = 30;

XRPivotGridField fieldDisplayHeight = new XRPivotGridField("DisplayHeight", PivotArea.RowArea);fieldDisplayHeight.Caption =
"Ht";fieldDisplayHeight.Width = 30;

XRPivotGridField fieldDisplaySQFT = new XRPivotGridField("DisplaySQFT", PivotArea.RowArea);fieldDisplaySQFT.Caption =
"SQFT";fieldDisplaySQFT.Width = 40;

XRPivotGridField fieldDisplayQuantity = new XRPivotGridField("DisplayQuantity", PivotArea.RowArea);fieldDisplayQuantity.Caption =
"Qty";fieldDisplayQuantity.Width = 26;

XRPivotGridField fieldDisplayListPrice = new XRPivotGridField("DisplayListPrice", PivotArea.DataArea);fieldDisplayListPrice.Caption =
"List Price";fieldDisplayListPrice.Width = 85;

XRPivotGridField fieldQuotedPrice = new XRPivotGridField("QuotedPrice", PivotArea.DataArea);fieldQuotedPrice.Caption =
"Display Price";fieldQuotedPrice.Width = 120;

XRPivotGridField fieldAvailableFromDate = new XRPivotGridField("AvailableFromDate", PivotArea.RowArea);fieldAvailableFromDate.Caption=
"Availablity";fieldAvailableFromDate.Width = 66;

XRPivotGridField fieldDescription = new XRPivotGridField("Description", PivotArea.RowArea);fieldDescription.Width=82;

XRPivotGridField fieldDirection = new XRPivotGridField("Direction", PivotArea.RowArea);fieldDirection.Caption =
"Face/Panel";fieldDirection.Width = 200;

XRPivotGridField fieldDiscount = new XRPivotGridField();fieldDiscount.Area =
PivotArea.DataArea;fieldDiscount.FieldName =
"Discount";fieldDiscount.Options.ShowGrandTotal =
true;fieldDiscount.Options.ShowCustomTotals =
true;fieldDiscount.Options.ShowValues =
true;fieldDiscount.SummaryType = DevExpress.Data.PivotGrid.
PivotSummaryType.Sum;fieldDiscount.TotalsVisibility =
PivotTotalsVisibility.CustomTotals;fieldDiscount.Width = 55;
fieldDiscount.Caption =
"Discount";
//XRPictureBox Image = new XRPictureBox();//Image = pivotGrid.FindControl("Bytes", true) as DevExpress.XtraReports.UI.XRPictureBox;//XRPivotGridField fieldByte = Image as DevExpress.XtraReports.UI.PivotGrid.XRPivotGridField;//XRPivotGridField fieldBytes = new XRPivotGridField("pivotGrid.FindControl("Bytes", true) as DevExpress.XtraReports.UI.XRPictureBox", PivotArea.RowArea);　
// Add these fields to the pivot grid.pivotGrid.Fields.AddRange(new PivotGridField[] { fieldDisplayLocation, fieldDisplayMedia, fieldDisplayQuantity, fieldDisplayWidth, fieldDisplayHeight, fieldDirection, fieldAvailableFromDate, fieldQuotedPrice });pivotGrid.CustomCellDisplayText +=
new PivotCellDisplayTextEventHandler(pivot_CustomCellDisplayText);　
　

// Product services details band........................................................................................XRPivotGrid pivotGrid1 = new XRPivotGrid();
//pivotGrid1.OptionsView.HideAllTotals();pivotGrid1.OptionsView.ShowRowGrandTotals = true;pivotGrid1.OptionsView.ShowRowTotals =
true;pivotGrid1.Width = 900;
pivotGrid1.Appearance.FieldHeader.BackColor = System.Drawing.
Color.DarkBlue;pivotGrid1.Appearance.FieldHeader.ForeColor = System.Drawing.
Color.White;
// pivotGrid.Appearance.FieldHeader.Font;report.Bands[DevExpress.XtraReports.UI.BandKind.ReportFooter].Controls.Add(pivotGrid1);pivotGrid1.DataSource = objDs2; ;
pivotGrid1.DataMember =
"tab1";
XRPivotGridField fieldProductName = new XRPivotGridField("ProductName", PivotArea.RowArea);
XRPivotGridField fieldProductRate = new XRPivotGridField("ProductRate", PivotArea.RowArea);fieldProductRate.Caption =
"Rate/Unit";
XRPivotGridField fieldProductQuantity = new XRPivotGridField("ProductQuantity", PivotArea.RowArea);
XRPivotGridField fieldTotalPrice = new XRPivotGridField();fieldTotalPrice.Area =
PivotArea.DataArea;fieldTotalPrice.FieldName =
"TotalPrice";fieldTotalPrice.Options.ShowGrandTotal =
true;fieldTotalPrice.Options.ShowCustomTotals =
true;fieldTotalPrice.Options.ShowValues =
true;fieldTotalPrice.SummaryType = DevExpress.Data.PivotGrid.
PivotSummaryType.Sum;fieldTotalPrice.TotalsVisibility =
PivotTotalsVisibility.CustomTotals;fieldTotalPrice.Width = 130;
fieldTotalPrice.Caption =
"(Rate/Unit X Quantity)";
//XRPivotGridField RowTotalPrice = new XRPivotGridField();fieldTotalPrice.Area = PivotArea.DataArea;
//fieldTotalPrice.FieldName = "RowTotalPrice";// PivotGridField field = pivotGrid.Fields["TotalPrice"];//field.CustomTotals.Add(pivotGrid1.PivotSummaryType.Sum);//field.TotalsVisibility = PivotTotalsVisibility.CustomTotals; pivotGrid1.Fields.AddRange(new PivotGridField[] { fieldProductName, fieldProductRate, fieldProductQuantity, fieldTotalPrice });pivotGrid1.CustomCellDisplayText +=
new PivotCellDisplayTextEventHandler(pivot1_CustomCellDisplayText);
// report.Bands[DevExpress.XtraReports.UI.BandFactory.].Controls.Add(pivotGrid1);// Get its PDF export options.PdfExportOptions pdfOptions = report.ExportOptions.Pdf;
// Set PDF-specific export options.pdfOptions.Compressed = true;pdfOptions.ImageQuality =
PdfJpegImageQuality.Highest;pdfOptions.NeverEmbeddedFonts =
"Tahoma;Courier New";pdfOptions.DocumentOptions.Application =
"Test Application";pdfOptions.DocumentOptions.Author =
"DX Documentation Team";pdfOptions.DocumentOptions.Keywords =
"XtraReports, XtraPrinting";pdfOptions.DocumentOptions.Subject =
"Test Subject";pdfOptions.DocumentOptions.Title =
"Test Title";
// Set the pages to be exported.pdfOptions.PageRange = "1, 3-7";report.ExportToPdf(Server.MapPath(
@"Images/Quotation.pdf"));
}
}

catch(SqlException){
ASPxLabel1.Text = GetGlobalResourceObject(
"Messages", "20012").ToString();}
}