Subtotaling accessories per area and project on a report - D-Tools Forum

06-01-2009, 08:07 PM

We would like to be able to show accessories and labor in a proposal report so that they subtotal per area and entire project seperately from items. For instance... each line item would show its sell price and display the accessories (if any) but hiding the accessories' price. There would then be a subtotal for each area (and for the entire project) for labor as well as any accessories tied to parent items in that area (and again for the entire project). Can this be done and how? Thank you!

nkarthick · 06-04-2009, 11:17 AM

Do the following steps to include the subtotal of the accessories of the parent items in each area/section.

Create a Custom report based on existing Proposal report.
Changes in “GrpDynamicFooter3” section -

In GrpDynamicFooter3 section, add a label and textbox control above the existing controls as shown in the below fig.

Include the Text for the label say “Accessories Sub Total :”

Name the Textbox as “txtG3AccessoryPrice” and set outputformat property to “C”

Add one more textbox name it as “txtNAPrice”, set the SummaryGroup property to “GrpDynamicHeader3” and SummaryType property to “SubTotal” and Visible property to false for this textbox.

Set DataField property value for the txtNAPrice textbox to dtr:Price_NA

Changes in “GrpDynamicFooter2” section -

In GrpDynamicFooter2 section, add a label and textbox control above the existing controls.

Include the Text for the label say “Accessories Sub Total :”

Name the Textbox as “txtG2AccessoryPrice” and set outputformat property to “C”

Add one more textbox name it as “txtG2NAPrice”, set the SummaryGroup to “GrpDynamicHeader2” and SummaryType to “SubTotal” and Visible to false for this textbox.

Set DataField property for the txtG2NAPrice textbox to dtr:Price_NA

Changes in “GrpDynamicFooter1” section -

In GrpDynamicFooter1 section, add a label and textbox control above the existing controls.

Include the Text for the label say “Accessories Sub Total :”

Name the Textbox as “txtG1AccessoryPrice” and set outputformat property to “C”

Add one more textbox name it as “txtG1NAPrice”, set the SummaryGroup to “GrpDynamicHeader1” and SummaryType to “SubTotal” and Visible to false for this textbox.

Set DataField property for the txtG1NAPrice textbox to dtr:Price_NA

Changes in “grpSubTotal” section -

In grpSubTotal section, add a label and textbox control above the existing controls.

Include the Text for the label say “Accessories Grand Total :”

Name the Textbox as “txtprojectAccessoryPrice” and set outputformat property to “C”

Add one more textbox name it as “txtProjectNAPrice”, set the SummaryGroup to “grpItemHeader” and SummaryType to “GrandTotal” and Visible to false for this textbox.

Set DataField property for the txtProjectNAPrice textbox to dtr:Price_NA

Accessories Sub Total.JPG

Go to Script tab and include the following code below the existing code.

Code:

'Assigns the Sub total of Accessory price in dynamic footer 1 section
Sub GrpDynamicFooter1_Format 
   
   AssignAccessorisedPrice("GrpDynamicFooter1","txtG1NAPrice","txtGrp1Price","txtG1AccessoryPrice")

End Sub

'Assigns the Sub total of Accessory price in dynamic footer 2 section
Sub GrpDynamicFooter2_Format
 
   AssignAccessorisedPrice("GrpDynamicFooter2","txtG2NAPrice","txtGrp2Price","txtG2AccessoryPrice")

End Sub

'Assigns the Sub total of Accessory price in dynamic footer 3 section
Sub GrpDynamicFooter3_Format
   
   AssignAccessorisedPrice("GrpDynamicFooter3","txtNAPrice","txtGrp3Price","txtG3AccessoryPrice")
   
End Sub

'Assigns the Grant total of accessories price for the project
Sub grpSubTotal_BeforePrint

 AssignAccessorisedPrice("grpSubTotal","txtProjectNAPrice","txtProjectPrice","txtprojectAccessoryPrice")

End Sub

'Calculates the Accesories price and assigns it in the correponding textbox control
Sub AssignAccessorisedPrice(groupFooterName As String,NAPriceTextbox As String,PriceTextBox As String, AccessoryPriceTexBox As String)

 Dim priceNA As Double = Ctype(ReportUtilities.ReturnTextBoxValue(rpt,groupFooterName,NAPriceTextbox), Double)
   
   Dim price As Double =  Ctype(ReportUtilities.ReturnTextBoxValue(rpt,groupFooterName,PriceTextBox),Double)
   
   Dim accesoryPrice As Double = price - priceNA
   
   ReportUtilities.SetTextBoxValue(rpt,groupFooterName,AccessoryPriceTexBox,accesoryPrice.ToString())

End Sub

Publish the report

Let us know if any problem in creating the custom report based on the above steps.

Hope it helps.

06-04-2009, 08:39 PM

I have followed the above instructions... thank you... but I get an error message.

Run validation for this report failed. Error detected was:
Exception has been thrown by the target of an invocation...
Please fix before publishing this report again.

nkarthick · 06-06-2009, 09:53 AM

I have no idea what you have missed in the instructions or what causes the error for you.

Please download the custom report from here and import it.

http://downloads.d-tools.com/SI5/rep...esSubTotal.zip

Hope it helps.

06-08-2009, 07:01 PM

Excellent, we're almost there. The accessories sub total works but the prices shown for the parent item(s) still includes the accessories. We would like to be able to show just the sell price for the parent item(s) not including the accessories, then have sub totals for the equipment (totals just the parent items) and sub total for the accessories (as given in the report you sent, thanks), a subtotal for labor for that area then a total for that area which is the sum of all three of these. Thanks again!!

nkarthick · 06-17-2009, 09:03 AM

JSpence,

Sorry for the delay in repsonse. Updated the custom report, now it will show sub total for equipment (parent item), Accessory and Labor in each section/area.

Please download the custom report from here
http://downloads.d-tools.com/SI5/rep..._and_Labor.zip

Hope it helps.

06-22-2009, 04:23 PM

This is exactly what we were looking for. There is one issue though. On some proposals the "Grand Total of Labor" as part of the Project Subtotal is lower, not equal to, the "Labor" line item in the Project Summary on the last page of the propowal. I have not found any red flag as of yet as to why that is except that on several proposals it is off by $85 exactly and on one it is off by several hundred dollars. On some of the smaller basic proposals these two numbers match. I will continue trying to find the culprit. In the mean time is there a way I could send you these proposals to see if you might be able to figure it out. I try and look at the script but I am not XML inclined. Let me know if this is an option. Thanks again for all your help!

Jason Spence

nkarthick · 06-23-2009, 02:05 PM

Can you send your proposals to support at d-tools dot com and refer this forum post? Ask them to send to Karthik or Naresh.

nkarthick · 06-27-2009, 08:53 AM

Can you edit the custom report “Proposal with Sub Total of Equip, Labor and Accessories” and set “ShowLaborItems” option in Report Parameters to true as illustrated in figure.

show labor items.JPG

Also set the same option for each report definition as illustrated in figure.

report definition.JPG

Publish the report. Then try running this report.

Hope it helps.