locked
Parameter in Pivot Chart with SQL source RRS feed

  • Question

  •  

    Hello,

     

    I have a SQL view that includes the following payroll data:

     

    Employee#

    PeriodEndDate

    HireDate

    Amount

     

    What I'm trying to do is filter the data by choosing the desired period end dates and then totalling the Amount and dividing that amount to get an average daily rate. To do this I'm pulling the data from the SQL 2000 source view into Excel 2007 and creating a pivot chart.

     

    In that pivot chart I'm able to select the desired period end dates and create a calculated field for my daily rate.

     

    My problem is that I want to exclude employee's that have a Hire Date that is within 90 days of the latest selected period end date. For example, if the Period End dates selected are 3/15/2008 and 3/30/2008, then any employees with a Hire Date greater than 12/31/2007 (3/30 less 90 days) should NOT be included in the pivot chart.

     

    Is there an easy way for me to do this, can I pass parameters to SQL via Excel?

     

    Thanks for any help

    Leo

    Wednesday, March 26, 2008 2:43 PM

Answers

  • Hello

    Add this into the WHERE clause:
    WHERE DATEDIFF(d,[Hire Date],[End Date]) <= 90
    Monday, August 17, 2009 5:04 AM
  • use DATEDIFF(d,HireDate,PeriodEndDate)>90 in where clause
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, August 17, 2009 9:22 AM
    Answerer
  • if you mix both above answers then you got right or use this


    in where clause

    Where  HireDate < =  dateadd (dd, -90 , PeriodEndDate )

    this will return 90 days old date and check data up to that date.

    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Wednesday, August 19, 2009 5:40 AM

All replies

  • Hello

    Add this into the WHERE clause:
    WHERE DATEDIFF(d,[Hire Date],[End Date]) <= 90
    Monday, August 17, 2009 5:04 AM
  • use DATEDIFF(d,HireDate,PeriodEndDate)>90 in where clause
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, August 17, 2009 9:22 AM
    Answerer
  • if you mix both above answers then you got right or use this


    in where clause

    Where  HireDate < =  dateadd (dd, -90 , PeriodEndDate )

    this will return 90 days old date and check data up to that date.

    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Wednesday, August 19, 2009 5:40 AM