input the date range for update Pivot table

[Jason]

Dear all,

Could I create two cell for input the date range and update pivot table
(data from sql server) according the date range?

Thanks,
Jason

 

[Patrick Molloy]

you'll have some SQL which includes the parameters?
so we have two place holders (aka tokens) in the textual SQL query string
eg

sSQL =
"SELECT {...fields etc } FROM { blah } WHERE ( MYDATE > '%date1%' AND
MYDATE < '%date2%' )"

its the WHERE clause you need look
change MYDATE for the appropriate field from your database table

if your dates are in Ranges A1 abd B1 then the next line should be

sSQL = REPLACE(sSQL,"%date1%",Range("A1").Value)
sSQL = REPLACE(sSQL,"%date2%",Range("B1").Value)

 

[Jason]

Hi Patrick,

Where can I place the following statement ?
macro?

Best regards,
Jason

 

[Patrick Molloy]

i'd assumed that you already had the VBA code for getting the data out of
the sql server

 

[Colbert Zhou [MSFT]]

Hello,

The PivotTable's filter is discrete instead of a continuous range of date.
So, based on the dates in the two cells, we need to add all dates we want
into the VisibleItemsList. The followings are codes I record from VBA. It
updates the pivot table to only present data from 2009-01 to 2009-02.


ActiveSheet.PivotTables("PivotTable2").CubeFields(86).EnableMultiplePageItem
s _
= True
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Time].[Year - Month - Date].[Year Name]").VisibleItemsList =
Array("")
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Time].[Year - Month - Date].[Month Name New]").VisibleItemsList =
Array( _
"[Time].[Year - Month - Date].[Year Name].&[Calendar 2009].&[Month
01]", _
"[Time].[Year - Month - Date].[Year Name].&[Calendar 2009].&[Month
02]")

If you want to get notification when the date cell is modified, you can
just listen to the Sheet's Change event. It will pass the modified cell as
its parameter so that we can get the changed date and update our pivot
table there,

Private Sub Worksheet_Change(ByVal Target As Range)
'test if the target is one of the two date cells
'if yes, update your pivottable based on the two cells here
...
End Sub

Best regards,
Colbert Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).