Report Scripting

You can use scripts in Reports either to calculate fields or to apply conditional formatting.

Please view the scripting samples provided in the DBxtra Tutorial - 6 - SCRIPTING SAMPLES folder of the DBxtra sample project.

All samples are set to Visual Basic and all scripts are se to: OnBeforPrint

Report scripting Samples

1.- Invoices {Calculated Field}

This sample describes how to create a calculated field, and their summary.

Labels with script code: [lblProductTotal] and [lblInvoiceTotal].

Scripts

[lblProductTotal]

' Declaring a global variable

Dim InvoiceTotal As Double = 0.0

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Declaring a local variable, wich calculates the

' Product Total [(UnitPrice - Discount) * Qty]

Dim ProductTotal As Double = (DetailReport.GetCurrentColumnValue("Unit_Price") - DetailReport.GetCurrentColumnValue("Discount")) * DetailReport.GetCurrentColumnValue("Qty")

' Assign the Product Total to this label (lblProductTotal)

' with currency format.

CType(sender, XRLabel).Text = String.Format("{0:C}", ProductTotal)

' Plus the current Product Total, to Invoice Total

InvoiceTotal += ProductTotal

End Sub

[lblInvoiceTotal]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Assign the Invoice Total to this label (lblInvoiceTotal)

' with currency format.

CType(sender, XRLabel).Text = String.Format("{0:C}", InvoiceTotal)

' Restart the Invoice Total variable, to next invoice.

InvoiceTotal = 0.0

End Sub

2.- Invoices {Borders Styles}

This sample (based on Scripting Sample 1) describes how to change the border style when a criteria becomes true.

When [Qty] is less than 15, then row field borders are Red, background is Yellow and font italic.

When [lblInvoiceTotal] it's greater than $300 the label borders are shown in red.

Labels with script code: [lblProductTotal] and [lblInvoiceTotal].

Scripts

[lblProductTotal]

' Declaring a global variable

Dim InvoiceTotal As Double = 0.0

Dim ItalicFont As Font = New Font("Arial", 8, FontStyle.Italic)

Dim RegularFont As Font = New Font("Arial", 8, FontStyle.Regular)

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Declaring a local variable, wich calculates the

' Product Total [(UnitPrice - Discount) * Qty]

Dim ProductTotal As Double = (DetailReport.GetCurrentColumnValue("Unit_Price") - DetailReport.GetCurrentColumnValue("Discount")) * DetailReport.GetCurrentColumnValue("Qty")

' Assign the Product Total to this label (lblProductTotal)

' with currency format.

CType(sender, XRLabel).Text = String.Format("{0:C}", ProductTotal)

' If Product Quantity is less than 15, fill the

' row fields borders, backcolor in yellow and font type italic

If DetailReport.GetCurrentColumnValue("Qty") < 5 Then

' Set the border color

fieldUnitPrice.BorderColor = Color.Red

fieldQty.BorderColor = Color.Red

fieldDiscount.BorderColor = Color.Red

' Set the border style

fieldUnitPrice.Borders = DevExpress.XtraPrinting.BorderSide.Left + DevExpress.XtraPrinting.BorderSide.Top + DevExpress.XtraPrinting.BorderSide.Bottom

fieldQty.Borders = DevExpress.XtraPrinting.BorderSide.Top + DevExpress.XtraPrinting.BorderSide.Bottom

fieldDiscount.Borders = DevExpress.XtraPrinting.BorderSide.Right + DevExpress.XtraPrinting.BorderSide.Top + DevExpress.XtraPrinting.BorderSide.Bottom

' Set the back color

fieldUnitPrice.BackColor = Color.Yellow

fieldQty.BackColor = Color.Yellow

fieldDiscount.BackColor = Color.Yellow

' Set the current font style

fieldUnitPrice.Font = ItalicFont

fieldQty.Font = ItalicFont

fieldDiscount.Font = ItalicFont

Else

' Set the border style

fieldUnitPrice.Borders = DevExpress.XtraPrinting.BorderSide.None

fieldQty.Borders = DevExpress.XtraPrinting.BorderSide.None

fieldDiscount.Borders = DevExpress.XtraPrinting.BorderSide.None

' Set the back color

fieldUnitPrice.BackColor = Color.Transparent

fieldQty.BackColor = Color.Transparent

fieldDiscount.BackColor = Color.Transparent

' Set the current font style

fieldUnitPrice.Font = RegularFont

fieldQty.Font = RegularFont

fieldDiscount.Font = RegularFont

End If

' Plus the current Product Total, to Invoice Total

InvoiceTotal += ProductTotal

End Sub

[lblInvoiceTotal]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Assing the Incoice Total to this label (lblInvoiceTotal)

' with currency format.

CType(sender, XRLabel).Text = String.Format("{0:C}", InvoiceTotal)

If InvoiceTotal > 300.0 Then

CType(sender, XRLabel).BorderColor = Color.Red

CType(sender, XRLabel).Borders = DevExpress.XtraPrinting.BorderSide.All

Else

CType(sender, XRLabel).Borders = DevExpress.XtraPrinting.BorderSide.None

End If

' Restart the Invoice Total variable, to next invoice.

InvoiceTotal = 0.0

End Sub

3.- Invoices {Bitmaps}

This sample (based on Scripting Sample 1) describes how to show / hide a bitmap when a criteria becomes true.

Labels with script code: [imgOffers], [imgNew], [lblProductTotal] and [lblInvoiceTotal].

Scripts

[imgOffers]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

Select Case DetailReport.GetCurrentColumnValue("Id_Product")

Case 2, 5, 8, 12

CType(sender, XRPictureBox).Visible = True

CType(sender, XRPictureBox).Location = New Point(125, 0)

Case Else

CType(sender, XRPictureBox).Visible = False

End Select

End Sub

[imgNew]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

If DetailReport.GetCurrentColumnValue("Id_Product") > 50 Then

CType(sender, XRPictureBox).Visible = True

Else

CType(sender, XRPictureBox).Visible = False

End If

End Sub

[lblProductTotal]

' Declaring a global variable

Dim InvoiceTotal As Double = 0.0

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Declaring a local variable, wich calculates the

' Product Total [(UnitPrice - Discount) * Qty]

Dim ProductTotal As Double = (DetailReport.GetCurrentColumnValue("Unit_Price") - DetailReport.GetCurrentColumnValue("Discount")) * DetailReport.GetCurrentColumnValue("Qty")

' Assign the Product Total to this label (lblProductTotal)

' with currency format.

CType(sender, XRLabel).Text = String.Format("{0:C}", ProductTotal)

' Plus the current Product Total, to Invoice Total

InvoiceTotal += ProductTotal

End Sub

[lblInvoiceTotal]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Assign the Invoice Total to this label (lblInvoiceTotal)

' with currency format.

CType(sender, XRLabel).Text = String.Format("{0:C}", InvoiceTotal)

' Restart the Invoice Total variable, to next invoice.

InvoiceTotal = 0.0

End Sub

4.- Invoices {Criteria Omiting}

This sample (based on Scripting Sample 3) describes how to omit criteria to summarize the quantities when the product condition is a new product. That criteria is calculated in [lblProductTotal].OnBeforePrint() function, assigned to NewProductsTotal variable and used in [lblNewProductsText].OnBeforePrint() function.

Labels with script code: [lblNewProductsText], [imgOffers], [imgNew], [lblProductTotal] and [lblInvoiceTotal].

Scripts

[lblNewProductsText]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' If this invoice have new products

If NewProductsCount > 0 Then

CType(sender, XRLabel).Visible = True

CType(sender, XRLabel).Text = String.Format("New Products Sold: {0} Total New Products: {1:C}", NewProductsCount, NewProductsTotal)

NewProductsCount = 0

NewProductsTotal = 0.0

Else

CType(sender, XRLabel).Visible = False

End If

End Sub

[imgOffers]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

If DetailReport.GetCurrentColumnValue("Id_Product") > 50 Then

CType(sender, XRPictureBox).Visible = True

Else

CType(sender, XRPictureBox).Visible = False

End If

End Sub

[imgNew]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

Select Case DetailReport.GetCurrentColumnValue("Id_Product")

Case 2, 5, 8, 12

CType(sender, XRPictureBox).Visible = True

CType(sender, XRPictureBox).Location = New Point(125, 0)

Case Else

CType(sender, XRPictureBox).Visible = False

End Select

End Sub

[lblProductTotal]

' Declaring a global variable

Dim InvoiceTotal As Double = 0.0

Dim NewProductsTotal As Double = 0.0

Dim NewProductsCount As Integer = 0

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Declaring a local variable, wich calculates the

' Product Total [(UnitPrice - Discount) * Qty]

Dim ProductTotal As Double = (DetailReport.GetCurrentColumnValue("Unit_Price") - DetailReport.GetCurrentColumnValue("Discount")) * DetailReport.GetCurrentColumnValue("Qty")

' Check the summary criteria

If DetailReport.GetCurrentColumnValue("Id_Product") > 50 Then

NewProductsTotal += ProductTotal

NewProductsCount += 1

End If

' Assign the Product Total to this label (lblProductTotal)

' with currency format.

CType(sender, XRLabel).Text = String.Format("{0:C}", ProductTotal)

' Plus the current Product Total, to Invoice Total

InvoiceTotal += ProductTotal

End Sub

[lblInvoiceTotal]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Assign the Invoice Total to this label (lblInvoiceTotal)

' with currency format.

CType(sender, XRLabel).Text = String.Format("{0:C}", InvoiceTotal)

' Restart the Invoice Total variable, to next invoice.

InvoiceTotal = 0.0

End Sub

5.- Invoices {Averages per Report}

This sample (based on Scripting Sample 1) describes how to calculate an average value in the report footer.

Labels with script code: [lblAverage], [lblProductTotal] and [lblInvoiceTotal].

Scripts

[lblAverage]

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

If InvoicesCount > 0 Then

CType(sender, XRLabel).Visible = True

CType(sender, XRLabel).Text = String.Format("Invoices Count: {0} Invoices Total Sum: {1:C} Invoices Average: {2:C}", InvoicesCount, InvoicesSum, InvoicesSum / InvoicesCount)

Else

CType(sender, XRLabel).Visible = False

End If

End Sub

[lblProductTotal]

' Declaring a global variable

Dim InvoiceTotal As Double = 0.0

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Declaring a local variable, wich calculates the

' Product Total [(UnitPrice - Discount) * Qty]

Dim ProductTotal As Double = (DetailReport.GetCurrentColumnValue("Unit_Price") - DetailReport.GetCurrentColumnValue("Discount")) * DetailReport.GetCurrentColumnValue("Qty")

' Assign the Product Total to this label (lblProductTotal)

' with currency format.

CType(sender, XRLabel).Text = String.Format("{0:C}", ProductTotal)

' Plus the current Product Total, to Invoice Total

InvoiceTotal += ProductTotal

End Sub

[lblInvoiceTotal]

Dim InvoicesCount As Integer = 0

Dim InvoicesSum As Double = 0.0

Private Sub OnBeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)

' Assign the Invoice Total to this label (lblInvoiceTotal)

' with currency format.