This report template is based on that provided in http://forum.ayanova.com/Topic2119-100-1.aspx

Sample User Billing Hours Report with Grand Total & Commission

Download and extract, and import into AyaNova. Report will display for selection from the Labor grid in the Service navigation pane.

This report:

• is grouped by the schedulable user in Labor 
• displays the quantity of No Charge hours, Billable hours, NC and Billable combined, and the Net Total for each schedulable user 
• displays grand totals at the bottom of the report template of all data combined
• Have also added so that it displays a 7% commission a schedulabel user may receive on the Net Labor charges the user bills out
• And totals this 7% commission per schedulable user
• And provides a grand total of all commissions for all schedulable users at the very bottom

I suggest downloading and importing the report template, open it in custom report designer, and follow along by selecting the properties where indicated so that you can see how done.

I took the existing sample report template, opened it in report designer, selected Save As, renamed it to Sample User Billing Hours Report with Grand Total and Commission, and than began customizing the report template.

I added additional variables where the running totals would be kept. I opened the existing script in the ReportHeader band OnBeforePrint script property and added the variables for GrandCommisTotal and CommisTotal

Select the Reportheader band
Select the Properties tab
Expand the Scripts property
Open the OnBeforePrint script
Edited by adding the variables for GrandCommisTotal and CommisTotal
Clicked OK

I added a new label field within the groupHeaderBand2 for the label 7% Commission (if you select it you will see in the Properties tab, this field is named xrLabel20)

I added a new label field within the detailBand1 (if you select it you will see in the Properties tab, this field is named xrLabel21)

I selected detailBand1, selected the Properties tab, and opened the existing OnBeforePrint and added to the script so that it maintains a running total (CommisTotal) of each Labor Net value multipled by .07 (the 7% commission)

//This gets the value of indivdual 7% commission, converts it to decimal value and adds it to the running total of CommisTotal
CommisTotal +=  (System.Decimal)(.07 * Convert.ToDouble(GetCurrentColumnValue("LT_UI_Label_NetValue")));

If your commission to your staff is different, you would edit the .07 to whatever your commission percentage is.

I selected the field xrLabel21, opened Properties tab, opened Scripts and created an OnBeforePrint script to display each labor records 7% commission

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{

//This gets the value of Net, converts it to a Double value, multiples it by the percentage commision
// converts it to a string format of currency so that it can be displayed as currency in the field
 xrLabel21.Text = string.Format ("{0:c2}",(.07 * Convert.ToDouble(GetCurrentColumnValue("LT_UI_Label_NetValue"))));
   //**************************
}

Again, if you your commission to your staff is different, you would edit the .07 here as well to whatever your commission percentage actually is.

I added a new column to the table in GroupFooter1 so that I could have a place to display the 7% total commission for this schedulable user as well as the label above it.

I selected the new table cell xrTableCell13 (use your mouse to select the field directly under that labeled Total 7% Commission), expanded scripts, and created an OnBeforePrint script

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
//this converts the total to date for NCBillTotal to a string value, and places it into the xrTableCell8 field
xrTableCell13.Text = string.Format ("{0:c2}",CommisTotal);
}

And than I created an OnAfterPrint script

private void OnAfterPrint(object sender, System.EventArgs e)
{
//this first checks to see if the xrTableCell13 is empty, if it isn't empty it takes the value of
//CommisTotal to date and adds it to the running total GrandCommisTotal for the end of the report
if(xrTableCell13.Text != null)
  GrandCommisTotal += Convert.ToDecimal(CommisTotal);
}

I than selected the GroupFooter1 band, selected Properties tab, expanded Scripts and added to the existing OnAfterPrint

private void OnAfterPrint(object sender, System.EventArgs e)
{
//This resets the running total to 0 for the next group of hours for the next schedulable user
NCBillTotal = 0;
//This resets the running total to 0 for the next group of commission total for the next schedulable user
CommisTotal = 0;
}

I added a new column to the table in ReportFooter so that I could have a place to display the 7% grand total commission for all schedulable users displaying in this report as well as the label above it.

I renamed the field under the label to xrGrandCommisTotal just for ease of finding it, and added the OnBeforePrint script

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
xrGrandCommisTotal.Text = string.Format("{0:c2}", GrandCommisTotal);

}

I ran the Preview, confirmed report template was multipling and adding correctly, and than saved the report template.

- Joyce

AyaNova Sales & Technical Support

http://www.ayanova.com