How to Create Previous Page Totals with PixelPerfect Visual Basic Scripts

Frank_S — Wed, 25 Nov 2015 22:18:57 GMT

Description: This tutorial illustrates how create a PixelPerfect Report with Visual Basic Scripts. This Report calculates previous page totals using macros. You can use C#, Visual Basic or JavaScript to develop PixelPerfect Scripts. For a C# version of this tutorial refer to "How to Create Previous Page Totals with PixelPerfect C# Scripts".

Create a New PixelPerfect Report

Select Reports in the lower left-hand panel
Select the PixelPerfect report type in the panel just above
Select PixelPerfectReport in the New group of the tool bar.

Name the Report

Enter "PixelPerfect Previous Page Total with Visual Basic Script" as name for your report
Select New in the Template group of the tool bar.

Create a Table

Add CH294 as a Level. For more explanation on the Level creation please refer to How to Create a Simple PixelPerfect Report
Create the table into the report. Don't insert any column totals.

Add a PageFooter

Right-click on the empty area in the footer of the page
Select Insert Band
Choose PageFooter from the list.

Fill-in the PageFooter

From the Tool Box window, select Label and "drag-and-drop" it
Create six new Labels inside the PageFooter and rename the first three. Format these Labels as you prefer.

Set Data Binding

Select the Label
Click on the smart tag (">") button on the upper side of the selected Label
Select the Total Sales field from the drop-down menu in the Data Binding section.

Do these steps for the other two labels.

Set Format String

Click on the smart tag (">")
Click on the browse button at the far right of the Format String field to open the FormatString Editor.
Select Currency on the left side of the window
Select $0.00 from the list
Click OK

Do these steps for the other two labels.

Select the Script Language

Click on Report Explorer
Select the Visual Basic field from the drop-down menu in the Scripts section

Create the Script

Click on Script on the upper-right side of the window

VB Script: Variable Declaration

Const allocationStep = 100 Dim currentPageTotal As System.Decimal = 0 Dim runningPagesTotal As System.Decimal = 0 Dim PageNumber As System.Decimal = 0 Dim PageTotal(allocationStep) As System.Decimal

allocationStep: Additional allocations for the dynamic array
currentPageTotal: Total Sales on the current page
runningPagesTotal: Total Sales of all pages
PageNumber: Page counter
PageTotal: Array with the total of each single page.

Copy and paste this code inside the Script Editor.

VB Script: OnSummaryReset Subroutine

Private Sub OnSummaryReset_currentPageTotal(ByVal sender As Object, ByVal e As System.EventArgs) PageNumber += 1 if PageTotal.Length <= PageNumber then ReDim Preserve PageTotal(PageNumber + allocationStep) end if PageTotal(PageNumber) = currentPageTotal currentPageTotal = 0 End Sub

This sub-routine is executed at the beginning of each new page.

Code:

Increases the PageNumber value
Check if the array must be resized
Save the currentPageTotal value inside the array and reset its value.

Add this code below the previous code.

VB Script: OnSummaryRowChanged

Private Sub OnSummaryRowChanged_currentPageTotal(ByVal sender As Object, ByVal e As System.EventArgs)   currentPageTotal += DetailReport.GetCurrentColumnValue("Total Sales") End Sub

This code is executed for each row in the page. It retrieves the value of the Total Sales field and adds it to the previous total. Remember to change the field name if you want to re-use the code for another field.

Add this code below the previous code.

VB Script: OnSummaryGetResult Current Page

Private Sub OnSummaryGetResult_currentPageTotal(ByVal sender As Object, ByVal e As SummaryGetResultEventArgs) e.Result = currentPageTotal e.Handled = True End Sub

This code returns the sum for the current page. It is executed when changing pages. Add this code below the previous code.

VB Script: OnSummaryGetResult Previous Page

Private Sub OnSummaryGetResult_previousPageTotal(ByVal sender As Object, ByVal e As SummaryGetResultEventArgs) e.Result = PageTotal(PageNumber) e.Handled = True End Sub

This code return the total for the previous page. You don't need to insert a -1 in the array index. Add this code below the previous code.

VB Script: OnSummaryGetResult Total Sales

Private Sub OnSummaryGetResult_runningPagesTotal(ByVal sender As Object, ByVal e As SummaryGetResultEventArgs) runningPagesTotal += currentPageTotal e.Result = runningPagesTotal e.Handled = True End Sub

This code increase the value of runningPagesTotal by the currentPageTotal and returns the value of the Total Sales of each preceding page. Add this code below the previous code.

Associate the Sub-routines to the Events to Calculate Current Page Total

Now we associate sub-routines to the events specified on the left column.

Select the first Label
On Property Grid expand, the Scripts section
Insert:

OnSummaryGetResult_currentPageTotal on Summary Get Result field.
OnSummaryReset_currentPageTotal on Summary Reset field
OnSummaryRowChanged_currentPageTotal on Summary Row Change field.

Associate the Sub-routines to the Events to Calculate Preceding Page Total

Select the second Label
On Property Grid, expand the Scripts section
Insert:

OnSummaryGetResult_previousPageTotal on Summary Get Result field.

Do these steps also for the last Label. Insert OnSummaryGetResult_runningPagesTotal on Summary Get Result field.

Generate Preview

Select Preview in the Actions group in the Reports window

article How to Create Previous Page Totals with PixelPerfect Visual Basic Scripts in Member Articles