Knowledgebase: Sp_commands & Reports
[QODBC-Desktop] How to run a Time by Name Report in QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

Typical Time By Name Report in QuickBooks

      The following interesting Time By Name Report was created in QuickBooks to show the number of hours/month for each employee:

Report Displayed in QODBC

Stored Procedure Command

     With QODBC the same report can be generated using stored procedure reports like this:

     sp_report TimeByName show Label, DurationHours_1 as Oct07, DurationHours_2 as Nov07,
     DurationHours_3 as Dec07, DurationHours_4 as Jan08, DurationHours_5 as Feb08,
     DurationHours_6 as Mar08, DurationHours_7 as Apr08, DurationHours_8 as May08,
     DurationHours_9 as Jun08, DurationHours_10 as Jul08, DurationHours_11 as Aug08,
     DurationHours_12 as Sep07, DurationHours_13 as TOTAL
     parameters DateMacro='ThisYear', SummarizeColumnsBy='Month' where Label like 'Total%'

Display in QODBC

Get Hire and Termination Date

     It's not possible to get the hire and termination date within the one SQL statement above, but if you needed them you can extract them by running:

     SELECT Name, SSN, HiredDate as "Hired Date", ReleasedDate as "Termination Date", IsActive as "Active" FROM Employee

 


Get Labels Next to the Values in Report

Stored Procedure Command

   You can't get the labels from the report to appear on top of the query, but you can get them next to the values like this:

     sp_report TimeByName show ReportSubtitle, Label, Duration_1_Title, DurationHours_1,
     Duration_2_Title, DurationHours_2, Duration_3_Title, DurationHours_3,
     Duration_4_Title, DurationHours_4, Duration_5_Title, DurationHours_5,
     Duration_6_Title, DurationHours_6, Duration_7_Title, DurationHours_7,
     Duration_8_Title, DurationHours_8, Duration_9_Title, DurationHours_9,
     Duration_10_Title, DurationHours_10, Duration_11_Title, DurationHours_11,
     Duration_12_Title, DurationHours_12, Duration_13_Title, DurationHours_13
     parameters DateMacro='ThisYear', SummarizeColumnsBy='Month' where Label like 'Total%'

Display in QODBC

(168 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).