Creating Custom Equations in Active Reports - D-Tools Forum

01-11-2008, 07:41 PM

Whomever,

I am trying to create some custom formulas to use in my reports so that we can calculate some information during report generation.

One formula, profit margin, can be calculated as:

[(Revenue - Cost)/Revenue] * 100

Basically, I am creating a Price, Cost, Retail, Profit Margin Report for us but for some reason, I can't figure out how to create the calculation for this.

In D-Tools XML form the above equation would be:

[(dtr:Price - dtr:Cost)/dtr:Price] * 100

How would this be created within Active Reports?

I tried simply creating a calculated field in the data explorer, putting in the equation and dragging it to the design field, but I get this error.

"Control Script Failed for control txtField11 , Source = Field1"

I haven't had time to figure out code and whatnot for ActiveReports, but I know it is a heck of a lot simpler to do this stuff in Excel!

Unfortunately, the report info is in D-Tools, not excel!

Please help!!

01-30-2008, 07:40 PM

Just a quick reference to the "calculating fields off of Total Project Price"

If you want to calculate fields off of "Project Subtotal" is there a way to do so?

I know you calculated the results from the Total Project Price off of

ReportUtilities.TotalProjectPrice(rpt)

but, because SubTotal is actually just the sum of the individual product parts, the control to calculate the SubTotal is done in the "properties" box.

If I want to refer to this number, what is the quickest way to do so? I assume it would be this:

Dim subtotal As Double = ReportUtilities.TotalProductPrice(rpt)

create a datasource for the sum of the objects I am calculating the subtotal off of....

then basically have a calculated field and refer to it back in design view.

It is basically creating a second "ProjectSummary" but without the fancy title, the divided section, and what not.

The layout I am going for looks like this (without the parentheses):

Equipment Total (previously Project SubTotal)
Labor Total
Management Fee:
Design Fee:
Parts & Materials:
____________________
Project Subtotal (New):

Using a rough guide that you graciously typed up with instructions on how to Calculate Fields off of GrandTotal, I was able to get the "New" SubTotal to Sum up everything except the Equipment Total, which used to be "Project Subtotal". The Subtotal is still there, but it doesn't add it in to the equation because I don't know what it is referred to as in the code.

Here is the equation minus the declarations.

rpt.Fields("SubTotal").Value = partstotal + labortotal + designtotal + managetotal

I purposely left out "equiptotal" since 1. It doesn't change anything 2. I don't know what I need to do to refer equiptotal to the existing subtotal.

Thanks!!!

***TO CLEAR UP ANY CONFUSION!!!**********

I created the partstotal, labortotal, designtotal, and managetotal from the ReportUtilities.TotalProjectPrice(rpt) directions Randy made, multiplied each as a percentage of the "GrandTotal" (ideally the SubTotal) and then referred to those in Design View. I am basically changing the name of the existing "Project Subtotal" to Equipment Total, adding the fees and labor as percentages/line items, and then recalculating a new SubTotal without having to create a line item in SI Text and calculating a new amount every time a change is made.

The reason I am not doing this in "SummaryDetail" is because we don't know what the tax nor actual labor will be until a long time later. This is specifically for customer use and internal estimating and forecasting only. That is why it sort of "has to happen" in the SubTotal.

I hope that clears up any confusion!

Jon

01-30-2008, 11:16 PM

Confusion not particularly cleared, but here are some things to consider based on some assumptions I'm reading into what you're saying.

Assumptions:

You Organization does not calculate any labor based on phases or hours associated with an item.
You do not use the "Misc Parts" and "Equipment +/-" factors in the application.
Your labor, Mgt Fee, and Design Fee are strictly a percentage of the total sale price of the equipment.

If all of those are true, then the "Price" and the "Installation Price" of an item in SI are the same.

The calculations within SI break down like this:
Installation Price = Price + Labor (install, misc, mgt, design) + misc parts + equip +/-

On a different note, the helper function "TotalProjectPrice" returns the sum of the Installation Price for all items + tax + Misc Costs

The values you're trying to calculate could get skewed if tax or Misc Costs, etc are introduced. It's a bit fragile and really...you should be calculating at the item level and letting the report total values up whenever possible.

I understand based on the other post why you went the route you did, but I think the context is different.

What I think you want to do is this:

In the DataInitialize event, add fields for your three percentage values and one for the New "Item Total Price"

Like:

Code:

Sub ActiveReport_DataInitialize


	' CODE FOR PERCENTAGE PRICES - BEGIN
	' add calculated field to the data source.
	' this field will equal the integer equivalent of the Summarize Field 	
	' NOTE:  These values should really be added to the CalculatedFields collection, but a bug in AR sometimes prevents it
		'rpt.CalculatedFields.Add("DesignFee")
		'rpt.CalculatedFields.Add("MgtFee")
		'rpt.CalculatedFields.Add("LaborFee")
		'rpt.CalculatedFields.Add("NewTotalItemPrice")	
	rpt.Fields.Add("DesignFee")
	rpt.Fields.Add("MgtFee")
	rpt.Fields.Add("LaborFee")
	rpt.Fields.Add("NewTotalItemPrice")
	' CODE FOR PERCENTAGE PRICES - END

End Sub

In the FetchData event, you need to calculate out these values on a per item basis:

Code:

Function ActiveReport_FetchData(ByVal EOF As Boolean) As Boolean

	
	' default the calculated to 0
	rpt.Fields("DesignFee").Value = 0
	rpt.Fields("MgtFee").Value = 0
	rpt.Fields("LaborFee").Value = 0
	rpt.Fields("NewTotalItemPrice").Value = 0

	' Create variables to hold calced values for the addition of new total price
	Dim DesignFee As Double = 0
	Dim MgtFee As Double = 0
	Dim LaborFee As Double = 0
	Dim NewTotalItemPrice As Double = 0
	
	' get the current item the report is on...

	Dim itm As DTools.SystemIntegrator.Reporting.Item = ReportUtilities.ReturnItem(rpt)
	
	' If the item is not null, set value of the calculated field 
	If itm IsNot Nothing Then

		DesignFee = itm.Price * .2 ' assume the Design is 20%
		MgtFee = itm.Price * .25 ' assume the Mgt is 25%
		LaborFee = itm.Price * .3 ' assume the Labor is 30%
	' This new "Total Item Price" is the equipment + the calc'd values
		NewTotalItemPrice = itm.Price + DesignFee + MgtFee + LaborFee
	
rpt.Fields("DesignFee").Value = DesignFee
rpt.Fields("MgtFee").Value = MgtFee 
rpt.Fields("LaborFee").Value = LaborFee
rpt.Fields("NewTotalItemPrice ").Value = NewTotalItemPrice 
End If

End Function

In your report, in a group header section (not the Detail section), add five textbox controls, set the DataField property for each:
dtr:Price
LaborFee
MgtFee
DesignFee
NewTotalItemPrice

Set the summarytype = GrandTotal (this will sum up all the values)

From there you should be all set.

Disclaimer...the script above is untested so there may be typos.

01-31-2008, 08:06 PM

Rick,

That said, I did have a few problems integrating those changes. There was one typo, which was the space at the end of "NewTotalItemPrice"

I fixed that, was able to publish the report, but the Items in the Proposal came out blank and the NewTotalItemPrice was blank.

I did, however, take your instructions and tried to implement them in my own proposal changes (the ones I talked about in the above story). I was able to get the proposal to generate correctly, however, the NewProjectTotal is summing up the information weird. I have pasted my code below so maybe you can see what I can't.

Code:

	Dim ds As DataDynamics.ActiveReports.Datasources.XMLDatasource = ctype(rpt.Datasource, DataDynamics.ActiveReports.Datasources.XMLDatasource)

	If ds.EOF Then return True
	
	' default the calculated to 0
	rpt.CalculatedFields("SummarizeInt").Value = 0
	
	'ReportUtilities.Spot("FetchData")

	'ReportUtilities.SetCurrentProposalItem(rpt)
	
	' get the current item the report is on...
	' the report must be iterating over the Item or ProposalItem nodes in the data
	' return the current item that is being iterated over.  (this wors for all proposals and most reports
	Dim itm As DTools.SystemIntegrator.Reporting.Item = ReportUtilities.ReturnItem(rpt)
	' get the equipment total
Dim total As Double = ReportUtilities.TotalProjectPrice(rpt)

' get the percentage total 
Dim designtotal As Double = total * .02D

'set the value of the textbox control to the percentage balue
' this assumes the textbox control is named "txtPercent" and
' in the "ReportFooter1" section of the report
ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtDesign", designtotal)

' get the parts and materials total
Dim partstotal As Double = total * .02D

'set the value of the textbox control to the percentage value
' this assume the textbox control is named "txtParts" and 
' in the "grpSubTotal" section of the report
ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtParts", partstotal)

' get the labor total
Dim labortotal As Double = total * .12D

'set the value of the textbox control to the percentage value
' this assumes the textbox control is named "txtLabor" and 
' in the "grpSubTotal" section of the report
ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtLabor", labortotal)

' get the management fee total
Dim managetotal As Double = total * .05D

'set the value of the textbox control to the percentage value
' this assumes the textbox control is named "txtManage" and
' in the "grpSubTotal" section of the report
ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtManage", managetotal)

	
	
	
	' If the item is not null, set value of the calculated field 
	If itm IsNot Nothing Then
	
		rpt.CalculatedFields("SummarizeInt").Value = Ctype(itm.Summarize, Integer)
		' set the value of the calculated field.
		rpt.Fields("ManModelDesc").Value = itm.Manufacturer & ":  " & itm.Model & "   -    " & itm.ClientDescription
		rpt.Fields("SubTotal").Value = itm.Price + partstotal + labortotal + designtotal + managetotal
	End If 
	
	


	'ReportUtilities.Spot("FetchData Detail:" & showDetail)


	' Return whether this is the last record.  
	' This Is required Or the report will iterate forever over And never return
	return (ds.NodeList.Count = ds.CurrentPosition)

I tried to figure out with the grandtotal (in the case of the test project that comes with D-Tools it is spitting out $276,125.93 when the GrandTotal Sum is selected but $0.00 if only SubTotal is selected.

As for the test project, here are the values I am getting for the test:

(Keep in mind, this assumes, with the calculations coming off of GrandTotal (which is fine, since we don't calculate misc fees, labor, etc., SubTotal and GrandTotal are the same)

$51,000 (Equipment)
$1235.67 (Parts and Materials @ 2% of GrandTotal)
$7414.00 (Labor @ 12% of Grand Total)
$3089.17 (Management Fee @ 5% of GrandTotal)
$1235.67 (Design Fee @ 2% of GrandTotal)
___________________________________
$276,125.93 (New SubTotal)

I assume the issue is in the Equipment Subtotal/GrandTotal or in the coding adding the values together.

It seems simple enough to go A+B+C+D+E+F = G

But now I don't know if equipment is being multiplied for some reason, added up multiple times, or what.

*NOTE* In the above example, dtr:Price is set to the equipment datafield (for price).

If you change it back to Installation Price (since labor and fees are null, it is the same as dtr:Price) the equipment number changes how it should be in the GrandTotal, but the New Sub Total is still off.

01-31-2008, 09:22 PM

OK...let's back up a bit...

Here's a brief run down of the events in the script we are concerned with and how often they fire:

Events for the report:
DataInitialize - Fires Once at the beginning of the report
FetchData - Fires Once for every record in the data (if there are 100 items, it fires 100 times...)

for a section (be it GroupHeader, GroupFooter, Detail)
Format - Fires once for each instance of the section. this means, if a project has 100 items the format event fires 100 times for the detail section. If you are grouping by Zone and there are 2 zones in the project, the group (header or footer) format event will fire once for each instance.

Make sense so far?

Looking at your script, you're setting the value of a texbox control that lives in a group (in you case grpSubTotal) every time the report fetches the next record (in the FetchData event)

Couple of things are going on in your script:

First thing:
The FetchData event happens once for every record in the data you data. This means that you are setting the values of the design,multiple times when you execute this:

ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtDesign", designtotal)

in the fetchdata event. If you want to do this. Select the "grpSubTotal' from the object dropdown and "Format" from the Event drop down and place this there. this is real redundant and pretty inefficient.

I'd move those calls to the Format event of the grpSubtotal section. Since I'd guess this section is not bound to a datafield, it is only generated once. Much more efficient.

The format event would look something like this:

Code:

Sub grpSubtotal_Format


' get the project total (equipment)
Dim total As Double = ReportUtilities.TotalProjectPrice(rpt)

' get the design total
Dim designtotal As Double = total * .02D

' get the parts and materials total
Dim partstotal As Double = total * .02D

' get the labor total
Dim labortotal As Double = total * .12D

' get the management fee total
Dim managetotal As Double = total * .05D


' set the textbox values
ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtDesign", designtotal)
ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtParts", partstotal)
ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtLabor", labortotal)
ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtManage", managetotal)

' ************
' ****This is New
' **** Instead of binding your total textbox to the dtr:Price field in the data, you can clear the DataField property and just set it to the calc'd field
' ************
' calculate the new project total
Dim NewTotal as Double = total + designtotal + partstotal + labortotal + managetotal 


ReportUtilities.SetTextBoxValue(rpt, "grpSubTotal","txtProjectTotal", NewTotal )

End Sub

OK...so that's the first thing. do that and I think you'll get the numbers you expect... Make sure you notice the comment where I say unbind the NewTotal textbox and set it in code. I'm not sure what it's called, so I called it txtProjectTotal.

As to why you're not getting the numbers you expect. I think it's in the fact that you are mixing metaphors so to speak...

On the one hand you're getting the TotalDesign (labor, mgt, etc...) and then adding those totals to the price of each item and letting report add those fields up...

you're getting 12% of the total equipment for labor and then saying the value of Subtotal (field added *PER ITEM*)
rpt.Fields("SubTotal").Value = itm.Price + partstotal + labortotal + designtotal + managetotal

Item Subtotal = item price + TotalLabor + TotalDesign + ...

Anway, maybe the step back at the beginning of the post explaining how the events fire will help make sense of things.

-R

01-31-2008, 09:42 PM

Rick,

That simple explanation in the beginning completely cleared up my understanding of how data is calculated within the report itself (the number of times things are "fired" etc.).

I think this type of information is extremely beneficial for people such as myself who have a grasp of what things are but not how to make things happen.

As I was reading I had the "realization" bubble that popped and the light came on. It really makes sense now.

When I first implemented the changes I was receiving a "Expected" error, and determined where it was almost immediately from my understanding of your tutelage of the report code.

03-24-2009, 06:53 AM

Tried to apply this to another application yesterday;

We base our anual service- and support fee on a percentage of the equipment cost (typically 1.4%).

Scripted the following in ReportFooter1_Format

'LF get total project price
'Dim itemtotal As Double = ReportUtilities.TotalProjectPrice(rpt)
'LF code above gave total incl labour, trying to get value based on total itemprice
Dim itemtotal As Double = ReportUtilities.TotalItemPrice(rpt)
'LF get percentage total (in my case 1.4%)
Dim SerSup As Double = itemtotal * .014D
'LF set value of txtbox sersupfee
ReportUtilities.SetTextBoxValue(rpt, "ReportFooter1", "txtSerSup", SerSup)

TotalProjectPrice rendered 1.4% of grand total
TotalItemPrice rendered just 0

Rick - any ideas of what to script to make it work?

Another idea is to use for example custom property 10 as a editable value per project as to the size of the service and support agreement. Is that doable?

Lars

03-24-2009, 01:04 PM

I'm out of town and just checking in today so I won't be able to respond back until tomorrow, but I'm not sure what kind of report you're doing this in, but the TotalItemPrice method on the report utilities helper will only return a value if the report is iterating over dtr:Items, dtr:PhaseItems, or dtr:ProposalItems.

It you're trying to get this value in a report that iterates over other data, you'll have to try a different approach.

-R

03-24-2009, 01:43 PM

I'm doing this in the subreport Project Summary Detail, ReportFooter1

03-25-2009, 04:19 PM

Originally Posted by Lars the Viking

Tried to apply this to another application yesterday;

We base our anual service- and support fee on a percentage of the equipment cost (typically 1.4%).

Scripted the following in ReportFooter1_Format

'LF get total project price
'Dim itemtotal As Double = ReportUtilities.TotalProjectPrice(rpt)
'LF code above gave total incl labour, trying to get value based on total itemprice
Dim itemtotal As Double = ReportUtilities.TotalItemPrice(rpt)
'LF get percentage total (in my case 1.4%)
Dim SerSup As Double = itemtotal * .014D
'LF set value of txtbox sersupfee
ReportUtilities.SetTextBoxValue(rpt, "ReportFooter1", "txtSerSup", SerSup)

TotalProjectPrice rendered 1.4% of grand total
TotalItemPrice rendered just 0

Rick - any ideas of what to script to make it work?

Another idea is to use for example custom property 10 as a editable value per project as to the size of the service and support agreement. Is that doable?

Lars

OK Lars, here's what's going on...

The "TotalItemPrice" method is valid when iterating over items. In the context of the report, the "Report Footer" is rendered after all items have been iterated over.

What's this mean? Well, if a report has 10 items, you can call TotalItemPrice in any of the grouping sections (headers or footers) or the detail section, and it will return the totalprice of the item that the report engine is currently processing. Keep in mind, that the detail section is the only section guaranteed to processed in a 1 to 1 ratio of items in the data to times the section is processed/rendered.

So, what you will need to do is this:

At the Top of the script window (outside of any function definitions), add a variable to hold the sum of all total item prices:

Code:

Private SerSup As Double

In the "FetchData" event add the total item price for each item to your new variable. The fetchdata event is fired once for every record in the data that is being iterated over:

Code:

 
 

Function ActiveReport_FetchData(ByVal EOF As Boolean) As Boolean' Add the "TotalItemPrice" * .014 to the SerSup
SerSup += ReportUtilities.TotalItemPrice(rpt) * .014D
' Make sure you return EOF, or this will infinite loop on you and ultimately crash
Return EOF
End Function

In the ReportFooter Format event, you should be able to do this:

Code:

 
ReportUtilities.SetTextBoxValue(rpt, "ReportFooter1", "txtSerSup", SerSup)

The SerSup should now be the value you were expecting.

That should do it for you.

-R