Author Topic: Payroll Report  (Read 1263 times)

kentuckytaxguy@gmail.com

  • Member
  • *
  • Posts: 1
Payroll Report
« on: March 03, 2014, 12:45:48 PM »
I have the following script to request payroll detail report:
Pull Payroll Report For Single Period Amount #Request Payroll Report By DATE, EMPLOYEE & PAYROLL ITEM
Set Variable [$result; Value:PCQB_BeginSession ( "" ; "" )]
Set Variable[$result; Value:PCQB_RqNew ( "PayrollDetailReportQuery" ; "" )
Set Variable [$result; Value:PCQB_RqAddFieldWithValue ( "PayrollDetailReportType" ; "PayrollItemDetail" )
Set Variable [$result; Value:PCQB_RqAddFieldWithValue ( "DisplayReport" ; True )]
Set Variable[$result; Value:PCQB_RqAddFieldWithValue ( "ReportPeriod::FromReportDate" ; L_Paychecks::QB_Paydate )
Set Variable [$result; Value:PCQB_RqAddFieldWithValue ( "ReportPeriod::ToReportDate" ; L_Paychecks::QB_Paydate )
Set Variable [$result; Value:PCQB_RqAddFieldWithValue ( "ReportAccountFilter::FullName" ; L_Paychecks::PayrollItem)
Set Variable[$result; Value:PCQB_RqAddFieldWithValue ( "ReportEntityFilter::ListID" ; L_Paychecks::QB_EmployeeID )
#Summarize Data By Total Only
Set Variable [$result; Value:PCQB_RqAddFieldWithValue ( "SummarizeRowsBy" ; "TotalOnly" )]
#Items to be included in Columns Set Variable [$result; Value:PCQB_RqAddFieldWithValue ( "IncludeColumn" ; "Date" )]
Set Variable [$result; Value:PCQB_RqAddFieldWithValue ( "IncludeColumn" ; "Name" )]
Set Variable[$result; Value:PCQB_RqAddFieldWithValue ( "IncludeColumn" ; "PayrollItem" )]
Set Variable [$result; Value:PCQB_RqAddFieldWithValue ( "IncludeColumn" ; "Amount" )]
#Request Related Records Set Variable[$result; Value:PCQB_RsOpenFirstRelatedRecord ( "PayrollDetailReportQuery" )]
Set Variable [$result; Value:PCQB_RqExecute]
#Requested Completed Begin Record Response Request
Set Field[L_Paychecks::NumberRows; PCQB_RsGetFirstFieldValue ( "NumRows" )]
Set Field [L_Paychecks::Amount; PCQB_RqAddFieldWithValue( "ReportData::TotalRow" ; "" )]
Set Field [L_Paychecks::Response; PCQB_SGetXML( "Response" ; "" )]
Set Variable[$result; Value:PCQB_RqCloseRelatedRecord]
Set Variable [$result; Value:PCQB_EndSession]

I received the following response
<?xml version="1.0"?>
<QBXML>
   <QBXMLMsgsRs>
      <PayrollDetailReportQueryRs statusCode="0" statusSeverity="Info" statusMessage="Status OK">
         <ReportRet>
            <ReportTitle>Payroll Item Detail</ReportTitle>
            <ReportSubtitle>February 28, 2014</ReportSubtitle>
            <ReportBasis>Accrual</ReportBasis>
            <NumRows>3</NumRows>
            <NumColumns>5</NumColumns>
            <NumColTitleRows>1</NumColTitleRows>
            <ColDesc colID="1" dataType="STRTYPE">
               <ColTitle titleRow="1"/>
               <ColType>Blank</ColType>
            </ColDesc>
            <ColDesc colID="2" dataType="DATETYPE">
               <ColTitle titleRow="1" value="Date"/>
               <ColType>Date</ColType>
            </ColDesc>
            <ColDesc colID="3" dataType="STRTYPE">
               <ColTitle titleRow="1" value="Name"/>
               <ColType>Name</ColType>
            </ColDesc>
            <ColDesc colID="4" dataType="STRTYPE">
               <ColTitle titleRow="1" value="Payroll Item"/>
               <ColType>PayrollItem</ColType>
            </ColDesc>
            <ColDesc colID="5" dataType="AMTTYPE">
               <ColTitle titleRow="1" value="Amount"/>
               <ColType>Amount</ColType>
            </ColDesc>
            <ReportData>
               <TextRow rowNumber="1" value="Feb 28, 14"/>
               <DataRow rowNumber="2">
                  <ColData colID="2" value="2014-02-28"/>
                  <ColData colID="3" value="Clevenger"/>
                  <ColData colID="4" value="Occupational Tax"/>
                  <ColData colID="5" value="5.60"/>
               </DataRow>
               <TotalRow rowNumber="3">
                  <ColData colID="1" value="Feb 28, 14"/>
                  <ColData colID="5" value="5.60"/>
               </TotalRow>
            </ReportData>
         </ReportRet>
      </PayrollDetailReportQueryRs>
   </QBXMLMsgsRs>
</QBXML>


MY QUESTION IS
How do I pull the Col Data Values into FileMaker? What is the correct request script? PCQB_RqAddFieldWithValue( "ReportData::TotalRow ; "" )? I have read to OSR 1,000,000 and can not for the life of me understand what the correct way to request the correct row and/or column.

Any help would be great.
« Last Edit: March 03, 2014, 01:13:20 PM by kentuckytaxguy@gmail.com »

Chris Turner

  • Administrator
  • Member
  • *****
  • Posts: 28
    • Productive Computing, Inc.
Re: Payroll Report
« Reply #1 on: March 03, 2014, 01:19:00 PM »
There are a couple of ways to go about this.

The first would be to get the XML from the response using PCQB_SGetXML( "Response" ), and run that XML through a parser to access the ReportData element, and the subsequent TotalRow elements.

Based off of the XML you have posted, using the XPath query:

*/ReportData/TotalRow/ColData[@colID='5']/value()

should get you the value of "5.60"
Chris Turner
Product Support Specialist
Productive Computing, Inc.

Chris Turner

  • Administrator
  • Member
  • *****
  • Posts: 28
    • Productive Computing, Inc.
Re: Payroll Report
« Reply #2 on: March 03, 2014, 01:28:09 PM »
For reference, you can use the XPath syntax guide from W3 Schools, as linked below:

http://www.w3schools.com/xpath/xpath_syntax.asp

Hopefully this helps shed some light on a solution to your issue.
Chris Turner
Product Support Specialist
Productive Computing, Inc.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Payroll Report
« Reply #3 on: March 03, 2014, 07:56:47 PM »
I did not test the expression, but I don't think the XPath will work without amendment. I would have written the expression like this...

.//ReportData/TotalRow/ColData[@colID=5]/@value

...which uses a different method of identifying the starting point, omits the single quotes around the colID value (because it's a number, quotes are not needed) and fixes what I believe is a path error by adding the second @ symbol and omitting the parentheses. The first two changes may not be necessary, but I share them in case they're useful to someone else.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

Chris Turner

  • Administrator
  • Member
  • *****
  • Posts: 28
    • Productive Computing, Inc.
Re: Payroll Report
« Reply #4 on: March 04, 2014, 01:25:53 PM »
Thank you for the clarification, Geoffrey. :)
Chris Turner
Product Support Specialist
Productive Computing, Inc.