I am using Excel 2007 connected to a SQL DB and use pivot tables for
reporting purposes. I have multiple years worth of data that I access, but
want to limit my pivot table to display just the last 12 months. I can't
seem to find a way to do this, but am sure someone must have solved this
problem already. I consider myself pretty Excel savvy, but I'm no programmer
so hold the VB please.

Thansk!

Why don't you add a helper column that determines if the data is within the
last year.

Do something like
if(A1 date(year(Today())-1,month(today()),day(today())), "YES","NO")

hopefully I have all of the parens. PUt that helper column in as a page
field.
--
HTH,

Barb Reinhardt



"68jcode" wrote:

I am using Excel 2007 connected to a SQL DB and use pivot tables for
reporting purposes. I have multiple years worth of data that I access, but
want to limit my pivot table to display just the last 12 months. I can't
seem to find a way to do this, but am sure someone must have solved this
problem already. I consider myself pretty Excel savvy, but I'm no programmer
so hold the VB please.

Thansk!

I can't do a helper column because the data is in a SQL DB that I am
accessing, not just in another worksheet that I can manipulate. Any other
thoughts?


"Barb Reinhardt" wrote:

Why don't you add a helper column that determines if the data is within the
last year.

Do something like
if(A1 date(year(Today())-1,month(today()),day(today())), "YES","NO")

hopefully I have all of the parens. PUt that helper column in as a page
field.
--
HTH,

Barb Reinhardt



"68jcode" wrote:

I am using Excel 2007 connected to a SQL DB and use pivot tables for
reporting purposes. I have multiple years worth of data that I access, but
want to limit my pivot table to display just the last 12 months. I can't
seem to find a way to do this, but am sure someone must have solved this
problem already. I consider myself pretty Excel savvy, but I'm no programmer
so hold the VB please.

Thansk!

Hi,

Not sure if this will help, but worth a try.
How exactly are you accessing the information in the SQL DB, I'm assuming
you are using the Microsoft Query through ODBC etc...?

If so, you probably have a field available with a date of transaction,
restrict this field with a filter

In excel right click on the data you are accessing from SQL, and select
'Table' then 'edit query' this will bring up a query wizard.
CLick next and go to the 'Filter Data' screen, here you can select your date
field and apply the criteria to restrict it to the year you require.

Hopefully this will help, if not could you provide a bit more info on how
your data is extracted from SQL

Cheers

"68jcode" wrote:

I can't do a helper column because the data is in a SQL DB that I am
accessing, not just in another worksheet that I can manipulate. Any other
thoughts?


"Barb Reinhardt" wrote:

Why don't you add a helper column that determines if the data is within the
last year.

Do something like
if(A1 date(year(Today())-1,month(today()),day(today())), "YES","NO")

hopefully I have all of the parens. PUt that helper column in as a page
field.
--
HTH,

Barb Reinhardt



"68jcode" wrote:

I am using Excel 2007 connected to a SQL DB and use pivot tables for
reporting purposes. I have multiple years worth of data that I access, but
want to limit my pivot table to display just the last 12 months. I can't
seem to find a way to do this, but am sure someone must have solved this
problem already. I consider myself pretty Excel savvy, but I'm no programmer
so hold the VB please.

Thansk!

Thanks for the response. This really doesn't help me as it's the same
concept as applying the filter on the "date created" field in my pivot table.
I'm not just trying to limit the date range to greater than, less than or
between etc., which are the typical filters. I need to just pull the last 12
months, which I'm assuming requires some logic or a calculation, which I am
not sure can be done in a pivot table. Any thiughts?


"Paullyie" wrote:

Hi,

Not sure if this will help, but worth a try.
How exactly are you accessing the information in the SQL DB, I'm assuming
you are using the Microsoft Query through ODBC etc...?

If so, you probably have a field available with a date of transaction,
restrict this field with a filter

In excel right click on the data you are accessing from SQL, and select
'Table' then 'edit query' this will bring up a query wizard.
CLick next and go to the 'Filter Data' screen, here you can select your date
field and apply the criteria to restrict it to the year you require.

Hopefully this will help, if not could you provide a bit more info on how
your data is extracted from SQL

Cheers

"68jcode" wrote:

I can't do a helper column because the data is in a SQL DB that I am
accessing, not just in another worksheet that I can manipulate. Any other
thoughts?


"Barb Reinhardt" wrote:

Why don't you add a helper column that determines if the data is within the
last year.

Do something like
if(A1 date(year(Today())-1,month(today()),day(today())), "YES","NO")

hopefully I have all of the parens. PUt that helper column in as a page
field.
--
HTH,

Barb Reinhardt



"68jcode" wrote:

I am using Excel 2007 connected to a SQL DB and use pivot tables for
reporting purposes. I have multiple years worth of data that I access, but
want to limit my pivot table to display just the last 12 months. I can't
seem to find a way to do this, but am sure someone must have solved this
problem already. I consider myself pretty Excel savvy, but I'm no programmer
so hold the VB please.

Thansk!