Invoice Report from Payment Schedule - D-Tools Forum

06-29-2009, 08:00 PM

I need to figure out how to convert my Crystal Invoice report to AR. I am having a difficult time figuring out how to come up with the totals I need. I see how to get access to the contract percentages and the total price, but nothing else.

I need
Total Taxable Equipment
Equipment Adjustment
Tax
Total Labor
MiscParts (We use this for Freight)
MiscCosts (Other Non-Taxable)

I can get the following:
totalFrieght= ReportUtilities.TotalMiscParts(rpt,true)
totalTax=ReportUtilities.TotalProjectTax(rpt,true)
totalProjectPrice=ReportUtilities.TotalProjectPric eNoFilter

I see no way to get totalLaborPrice & totalEquipmentPrice

In a proposal report I was able to use
totalEquipmentPrice+=itm.Price
in the FetchData section to get the totalEquipmentPrice, but that does not work in the payment schedule.

I guess I could pass this value to the Invoice subreport, but I will need to go figure out how to pass variables.

-ch

nkarthick · 06-30-2009, 01:45 PM

In order to get the Total equipment price and Total Labor price for the project in the custom report, can you try the below script and assign the totalEquipmentPrice and totalLaborPrice into the appropriate textbox control in your custom report

Code:

Sub ActiveReport_ReportStart()

        'Get the XML Data source
        Dim ds As DataDynamics.ActiveReports.Datasources.XMLDatasource = CType(rpt.Datasource, DataDynamics.ActiveReports.Datasources.XMLDatasource)

        If ds.EOF Then Return

        If ds.Document Is Nothing Then Return

        'Get the Root Element
        Dim xmlRootElement As XmlElement = ds.Document.DocumentElement

        If xmlRootElement Is Nothing Then Return

        'Filter the Labor Product
        Dim xmlDefinedFilter As String = "[dtr:Category != 'Labor']"

        Dim totalEquipmentPrice As Double
        Dim totalLaborPrice As Double


        Dim xmlns As XmlNamespaceManager = ReportingNamespaceUtility.ReturnXMLNamespaceManager(ds.Document.NameTable)

        'Get the Item Price nodes 	
        Dim nodes As XmlNodeList = xmlRootElement.SelectNodes(String.Format("//dtr:ProposalItems/dtr:ProposalItem{0}/dtr:Price", xmlDefinedFilter), xmlns)


        For Each node As XmlNode In nodes
            totalEquipmentPrice += Double.Parse(node.InnerText)
        Next

        'Get the TotalLaborPrice nodes
        nodes = xmlRootElement.SelectNodes(String.Format("//dtr:PhaseItems/dtr:Item/dtr:TotalLaborPrice", xmlDefinedFilter), xmlns)


        For Each node As XmlNode In nodes
            totalLaborPrice += Double.Parse(node.InnerText)
        Next

        'Asssigns the Total Equipment Price
        ReportUtilities.SetTextBoxValue(rpt, "GroupHeader1", "totPrice", totalEquipmentPrice)

        'Assign the Total Labor Price
        ReportUtilities.SetTextBoxValue(rpt, "GroupHeader1", "totLabor", totalLaborPrice)

    End Sub

Hope it helps.

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

Forget to mention that -

Please include the below line at the top of the script

Imports System.Xml
Imports DTools.SystemIntegrator.Reporting

Note: Include at the first line of the script

06-30-2009, 03:44 PM

Very cool, I will give it a shot. That's some fancy cypher'n ;-)

-ch

06-30-2009, 07:17 PM

Unfortunately I get "0" for both values (totalEquipmentPrice & totalLaborPrice)

Clue: I gave the variables values immediately after the DIM statements. Whatever value I assigned that is the value that is reported at the end. The code did not change the initial value.

BTW: I placed the "Imports" statements inside and outside the Subroutine, but the result was the same.

-ch

nkarthick · 07-01-2009, 12:02 PM

You have to place the import code at the top of the script as shown in fig.

import.JPG

Also can you check your Report Datasource of your report and check your Records Pattern and let us know the Records Pattern. Also can you give some screen shots of your script, so that I will try to figure out what is wrong in the report?

edit data source.JPG

records pattern.JPG

07-01-2009, 02:41 PM

nkarthick,

The Data Source was the issue, but this fix blew away the payment schedule. I realize I did not present my requirements very well.

I need to produce an Invoice report that looks just like the Payment Schedule, but with a lot more detail on each row. Each total would be multiplied by the appropriate Contract Percentage. The totals are shown in the Line Item Project Summary.

TotalEquipment, Discount, Freight(MiscParts), TotalTaxable, Sales Tax, TotalLabor, TotalMiscCosts

The above items are needed to produce an appropriate invoice.

I think I can do this by passing Parameters to the Invoice subreport. You posted an example that looks straightforward.

Please let me know, if there is an easier way.

-ch

07-01-2009, 05:23 PM

nkarthick,

I got the values to pass to the subreport via the Parameters. I should be able to make it work from here.

Thanks for the post on the Parameter passing.

-ch